Execute cmd shell from a TSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Execute cmd shell from a TSQL

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-10 : 08:30:55

Hi all,
I have the following statement that is part of my TSQL transactions. I'm trying to select multiple rows with a Available condition in the where clause. However, the single quotes issues has emerged because the xp_cmdshell procedures needs too.
example:
exec master..xp_cmdshell 'isql /Uxx /Pxx /db /h-1 /Q"set nocount on select distinct device from devices where online = '+'''Available'''+'" /C:\tmp\work\file1.txt'

when I try to run, this is what I get:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+'.

Any idea please...or suggestions.

Thanks,

--------------------------
Get rich or die trying
--------------------------

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-10 : 08:33:38

you can't do a calculation when passing values as parameters. This should word


BEGIN TRAN

DECLARE @foo VARCHAR(8000) =  'isql /Uxx /Pxx /db /h-1 /Q"set nocount on select distinct device from devices where online = '+'''Available'''+'" /C:\tmp\work\file1.txt'
exec master..xp_cmdshell @foo

ROLLBACK

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-10 : 08:47:48

quote:
Originally posted by Transact Charlie

you can't do a calculation when passing values as parameters. This should word

BEGIN TRAN

DECLARE @foo VARCHAR(8000) = 'isql /Uxx /Pxx /db /h-1 /Q"set nocount on select distinct device from devices where online = '+'''Available'''+'" /C:\tmp\work\file1.txt'
exec master..xp_cmdshell @foo

ROLLBACK

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Thanks a lot for your help.
this works perfect. I'm wondering if is it possible to assign directly as you wrote in your example or should I use SELECT/SET keywords?

Thanks.

--------------------------
Get rich or die trying
--------------------------

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-10 : 08:52:09

Since sql server 2008 it is possible to assign the value in the declare statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-10 : 08:52:52

you can assign directly as long as you are using 2008.

On earlier editions you would probably do something like

BEGIN TRAN

DECLARE @onlineParam VARCHAR(8000)
DECLARE @command VARCHAR(8000)
DECLARE @outputFile VARCHAR(8000)

SET @onlineParam = 'Available'
SET @outputFile = 'C:\tmp\work\file1.txt'
SET @command = 'isql /Uxx /Pxx /db /h-1 /Q"set nocount on select distinct device from devices where online = ' + QUOTENAME(@onlineParam, '''')  + '" /' + @outputFile

exec master..xp_cmdshell @command

ROLLBACK

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-10 : 08:54:45

Note that

QUOTENAME(@var, '''')

Will escape the value of @var in single quotes -- it's a nice safe way of dealing with strings (avoiding sql injection of the parameter)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-10 : 09:09:10

quote:
Originally posted by Transact Charlie

Note that
QUOTENAME(@var, '''')

Will escape the value of @var in single quotes -- it's a nice safe way of dealing with strings (avoiding sql injection of the parameter)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Thank you T_charlie, and Webfred too.

This tricks were helpful.

Thanks again.

--------------------------
Get rich or die trying
--------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources