xp_cmdshell

Question

Post reply

xp_cmdshell

ZA_Crafty

Ten Centuries

Points: 1228
More actions
July 18, 2008 at 12:02 am

#77044

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

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 @mycmd

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

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 2

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?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

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 !