write xp_cmdshell with params to text file
-
Hello All,
DECLARE @query VARCHAR(8000)
DECLARE @db VARCHAR(20)
DECLARE @file VARCHAR(30)
SET @query = "usp_ClientNumer"
SET @query = @query + CHAR(32) + CHAR(39) + '12345' + CHAR(39)--Client Number
SET @query = @query + CHAR(44) + CHAR(32) + CHAR(39) + '01/01/2000' + CHAR(39) --Member From
SET @query = @query + CHAR(44) + CHAR(32) + CHAR(39) + '12/31/2004' + CHAR(39)--Member To
SET @db = 'MyDatabase'
SET @file ='c:\result.txt'
EXEC ('master..xp_cmdshell ''isql /o' + @file + ' /d' + @db + ' /Q"' + @query + '" /E''')
I am trying to pass params to stored proc and write the resultset to ascii file.
Somehow it doesn`t work. When I run the above for a table or stored proc without params it works just fine
As far as remember there were a discussion here a while back stating that you cannot pass params to
xp_cmdshell?
I hope I am wrong?
Any ideas,
TIA
-
You actually need to build the xp_cmdshell statement and then execute that...try this....
note the extra quotes and the change to OSQL instead of ISQL
DECLARE @query VARCHAR(8000)
DECLARE @db VARCHAR(20)
DECLARE @file VARCHAR(30)
declare @sql varchar(8000)
SET @query = 'exec usp_ClientNumer'
SET @query = @query + CHAR(32) + CHAR(39) + '''12345''' + CHAR(39)--Client Number
SET @query = @query + CHAR(44) + CHAR(32) + CHAR(39) + '''01/01/2000''' + CHAR(39) --Member From
SET @query = @query + CHAR(44) + CHAR(32) + CHAR(39) + '''12/31/2004''' + CHAR(39)--Member To
SET @db = 'MyDatabase'
SET @file ='c:\result.txt'
set @sql = 'master..xp_cmdshell ''osql /o'+@file+' /d'+@db+' /Q"'+@query+'" /E"'''
--print @sql
EXEC (@sql)
-
Please notice that xp_cmdshell only receive command string with varchar(255) or nvarchar(4000) only. Do not put command string longer that the specify length or it will truncated without any warnings.
Regards,
kokyan 
-
Thanks a lot guys,
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply