Outlook

Hi,
try to use xp_sendmail. but you don't need the database in
single user to run dbcc checkdb, you only need single user
mode if you use the repair option.
CMLC

>--Original Message--
>Folks,
>Can you point me to where I can look for more info to
complete this task.
>There is a proprietary app running with SQL 2000 as
backend. Everynight the
>app put DB in single user mode and runs DBCC itself to
check data. That
>runs well except few times it failed to get exclusive
login in order to
>clear single user mode. It causes the app fails to
open. I think I may
>want to run a script at my computer querying the DB and
notify someone by
>using Outlook on my computer. Is there any info that can
tell me how to
>open Outlook from in inside sql script?
>Thanks,
>
>.
>
hi,
to use xp_sendmail we must have SQL mail configured on the SQL box. This is
not my case because I can't touch the box. What I wanted is to use Outlook
on my pc to send email out. Is it possible?

> you don't need the database in single user to run dbcc checkdb, you only
> need single user mode if you use the >repair option
the app is proprietary so ... no touching. It seems that it would repair
data if possible.
"CMLC" <anonymous@.discussions.microsoft.com> wrote in message
news:196d01c4a188$bc87d820$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> try to use xp_sendmail. but you don't need the database in
> single user to run dbcc checkdb, you only need single user
> mode if you use the repair option.
> CMLC
>
> complete this task.
> backend. Everynight the
> check data. That
> login in order to
> open. I think I may
> notify someone by
> tell me how to

OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.

other characters (codes) that represent single quote

Hi,
I am trying to replace every single quote with two "single quotes", prior to
sending the dynamic sql string to sql server.
( from a C# application)
(in this case I have to send a dynamic string, cannot do parametarized
queries)
Question is....
Is there any other character that sql server will interpret as a "single
quote"?
Any help is deeply appreciated.
Thanks
NalakaHi Nalaka,
Thank you for your post.
Based on my scope, I don't think SQL Server will interpret other character
as single quote.
Here I would like provide some information about passing dynamic string to
SQL Server:
When possible, reject input that contains the following characters.
Input character Meaning in Transact-SQL
; Query delimiter
' Character data string delimiter
-- Comment delimiter
/* ... */ Comment delimiters. Text between /* and
*/ is not evaluated by the server.
Xp_ Begins the name of catalog extended stored
procedures such as xp_cmdshell.
You may need to refer the following article:
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei,
This is what I don't understand... theoretically if I replace every single
quote with two single quotes.. all is well.
But there is a ton of articles suggesting all these other precautions.....
Nalaka
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for your post.
> Based on my scope, I don't think SQL Server will interpret other character
> as single quote.
> Here I would like provide some information about passing dynamic string to
> SQL Server:
> When possible, reject input that contains the following characters.
> Input character Meaning in Transact-SQL
> ; Query delimiter
> ' Character data string delimiter
> -- Comment delimiter
> /* ... */ Comment delimiters. Text between /* and
> */ is not evaluated by the server.
> Xp_ Begins the name of catalog extended
> stored
> procedures such as xp_cmdshell.
> You may need to refer the following article:
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:%23CKpYJOhGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Thanks Wei,
> This is what I don't understand... theoretically if I replace every single
> quote with two single quotes.. all is well.
> But there is a ton of articles suggesting all these other
precautions.....
>
Most likely their concern for SQL injection attacks.
Basically any place you accept input, if you're not careful, a bad user
could do something like:
Enter First Name: dummy; select * from sysobjects
And if your select query isn't written well, it now becomes something like:
Select id from table where fname=dummy; select * from sysobjects and now
your sysobjects table has been returned to the hacker.
> Nalaka
>
> "Wei Lu" <weilu@.online.microsoft.com> wrote in message
> news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> > Hi Nalaka,
> >
> > Thank you for your post.
> >
> > Based on my scope, I don't think SQL Server will interpret other
character
> > as single quote.
> >
> > Here I would like provide some information about passing dynamic string
to
> > SQL Server:
> >
> > When possible, reject input that contains the following characters.
> >
> > Input character Meaning in Transact-SQL
> > ; Query delimiter
> >
> > ' Character data string delimiter
> >
> > -- Comment delimiter
> >
> > /* ... */ Comment delimiters. Text between /*
and
> > */ is not evaluated by the server.
> >
> > Xp_ Begins the name of catalog extended
> > stored
> > procedures such as xp_cmdshell.
> >
> > You may need to refer the following article:
> >
> > SQL Injection
> > http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> >
> > Hope this will be helpful!
> >
> > Sincerely,
> >
> > Wei Lu
> > Microsoft Online Community Support
> >
> > ==================================================> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> >
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
>|||Hi Nalaka,
Thank you for the update.
As Greg mentioned, there is some secury issue you should considered in you
application code.
SQL injection should be avoid.
You could refer the article I posted in the previous post.
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks...
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:ugdnCtVhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for the update.
> As Greg mentioned, there is some secury issue you should considered in you
> application code.
> SQL injection should be avoid.
> You could refer the article I posted in the previous post.
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Nalaka,
Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

other characters (codes) that represent single quote

Hi,
I am trying to replace every single quote with two "single quotes", prior to
sending the dynamic sql string to sql server.
( from a C# application)
(in this case I have to send a dynamic string, cannot do parametarized
queries)
Question is....
Is there any other character that sql server will interpret as a "single
quote"?
Any help is deeply appreciated.
Thanks
NalakaHi Nalaka,
Thank you for your post.
Based on my scope, I don't think SQL Server will interpret other character
as single quote.
Here I would like provide some information about passing dynamic string to
SQL Server:
When possible, reject input that contains the following characters.
Input character Meaning in Transact-SQL
; Query delimiter
' Character data string delimiter
-- Comment delimiter
/* ... */ Comment delimiters. Text between /* and
*/ is not evaluated by the server.
Xp_ Begins the name of catalog extended stored
procedures such as xp_cmdshell.
You may need to refer the following article:
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei,
This is what I don't understand... theoretically if I replace every single
quote with two single quotes.. all is well.
But there is a ton of articles suggesting all these other precautions.....
Nalaka
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for your post.
> Based on my scope, I don't think SQL Server will interpret other character
> as single quote.
> Here I would like provide some information about passing dynamic string to
> SQL Server:
> When possible, reject input that contains the following characters.
> Input character Meaning in Transact-SQL
> ; Query delimiter
> ' Character data string delimiter
> -- Comment delimiter
> /* ... */ Comment delimiters. Text between /* and
> */ is not evaluated by the server.
> Xp_ Begins the name of catalog extended
> stored
> procedures such as xp_cmdshell.
> You may need to refer the following article:
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:%23CKpYJOhGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Thanks Wei,
> This is what I don't understand... theoretically if I replace every single
> quote with two single quotes.. all is well.
> But there is a ton of articles suggesting all these other
precautions.....
>
Most likely their concern for SQL injection attacks.
Basically any place you accept input, if you're not careful, a bad user
could do something like:
Enter First Name: dummy; select * from sysobjects
And if your select query isn't written well, it now becomes something like:
Select id from table where fname=dummy; select * from sysobjects and now
your sysobjects table has been returned to the hacker.

> Nalaka
>
> "Wei Lu" <weilu@.online.microsoft.com> wrote in message
> news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
character[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
>|||Hi Nalaka,
Thank you for the update.
As Greg mentioned, there is some secury issue you should considered in you
application code.
SQL injection should be avoid.
You could refer the article I posted in the previous post.
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks...
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:ugdnCtVhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for the update.
> As Greg mentioned, there is some secury issue you should considered in you
> application code.
> SQL injection should be avoid.
> You could refer the article I posted in the previous post.
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Nalaka,
Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

OSQL.exe question

Is there a way to process multiple SQL scripts in a single transaction using
osql.exe ?
Ideally, I'd like to be able to start transaction, execute multiple sql
scripts and commit transaction when all of them succeeded or rollback when
there was a failure.
I appreciate your help.
I would copy and paste them together.
-jens S=FC=DFmeyer.
|||"Marek" <nospam@.nowhere.com> wrote in
news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:

> Is there a way to process multiple SQL scripts in a single transaction
> using osql.exe ?
> Ideally, I'd like to be able to start transaction, execute multiple
> sql scripts and commit transaction when all of them succeeded or
> rollback when there was a failure.
> I appreciate your help.
As far as I know this is not possible in osql, but if you try sqlcmd from
SQL Server 2005, I do believe that you can. However, I have not tried this.
SqlCmd is also available in SQL Server 2005 Express, which is free.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16:

> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd
> from SQL Server 2005, I do believe that you can. However, I have not
> tried this. SqlCmd is also available in SQL Server 2005 Express, which
> is free.
And as Tibor so kindly answered in another group: You can download SqlCmd
here:
http://www.microsoft.com/downloads/d...D09C1D60-A13C-
4479-9B91-9E8B9D835CDC&displaylang=en
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||> As far as I know this is not possible in osql
I guess you could try the :r option to have OSQL read sub-scripts from a mother script. The
sub-scripts cannot have GO. A test is needed to verify that new connections will not be opened for
subscripts, use Profiler for that. Also, :r is not documented for OSQL, but I think it is for
SQLCMD. However, I don't think that error handling will be the easiest thing to accomplish.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16...
> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd from
> SQL Server 2005, I do believe that you can. However, I have not tried this.
> SqlCmd is also available in SQL Server 2005 Express, which is free.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging