xp_cmdshell
-
this is what I have
select 'BCP "Select * from MyTestDB.dbo.v_CSVExport where DateRegister > dateadd(d, -1, left(getdate(), 11)) and DateRegister < dateadd(s, -1, (left(getdate(), 11) + '' 00:00:00.000''))" queryout AA_'
+ cast(convert(varchar,getdate(),112)as varchar(10)) + '.csv -c -t "," -U sa -P mypass -S serversql01'
If I output to text and copy the results and paste it in 'n cmd shell it works, but if i replace select with xp_cmdshell it has a syntax error near the +
What on earth am I missing here?
-
you may be better off putting this into a sqlagent job, owned by sa.
This avoids opening up xp_cmdshell for everybody :sick:
If you still encist on having this in tsql, put your exe-statement in a varriable, then execute it.
declare @mycmd varchar(1000)Set @mycmd = 'BCP "Select * from MyTestDB.dbo.v_CSVExport where DateRegister > dateadd(d, -1, left(getdate(), 11)) and DateRegister < dateadd(s, -1, (left(getdate(), 11) + '' 00:00:00.000''))" queryout AA_'+ cast(convert(varchar,getdate(),112)as varchar(10)) + '.csv -c -t "," -U sa -P mypass -S serversql01'exec xp_cmdshell @mycmdJohan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Well, this will go into a sql job. The reason I have it as tsql, is so that the file name can be dynamic.
the idea is to email a csv file with the previous day's sales figures.
Now security is a concern, And I do realize the implication of using xp_cmdshell.
My dos scripting is very rusty. How can i generate that dynamic filename using dos scripting alone, and not tsql?
-
ZA_Crafty (7/18/2008)
Well, this will go into a sql job. The reason I have it as tsql, is so that the file name can be dynamic.the idea is to email a csv file with the previous day's sales figures.
Now security is a concern, And I do realize the implication of using xp_cmdshell.
My dos scripting is very rusty. How can i generate that dynamic filename using dos scripting alone, and not tsql?
In that case, use SMTP mail ! (don't use xp_sendmail ! because it can have leaks)
it can execute a query and mail the results.
Check out Gert Drapers site www.sqldev.net for the download.
http://www.sqldev.net/xp/sp_smtp_sendmail.htm
No more need for xp_cmdshell !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply