| Author |
 Topic  |
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
 Posted - 07/10/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 07/10/2012 : 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
USA
261 Posts |
Posted - 07/10/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 07/10/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 07/10/2012 : 08:52:52
|
you can assign directly as long as you are using 2008.
On earlier editions you would probably do something likeBEGIN 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. |
Edited by - Transact Charlie on 07/10/2012 08:53:54 |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 07/10/2012 : 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
USA
261 Posts |
Posted - 07/10/2012 : 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
-------------------------- |
|
|
| |
Topic |
|
Execute cmd shell from a TSQL
