Link to home
Avatar of fmichail
fmichailFlag for Canada

asked on

use of sqlCmd command through exec master..xp_cmdshell

Hi Experts,
I am trying to execute the following command from inside a stored procedure
exec master..xp_cmdshell 'sqlcmd -d myDatabase -E -Q "SET NOCOUNT ON select * from myTable" -o "c:\myfolder\myoutputFile.csv" -h-1 -W -s","'

The command works fine and producing the resulting csv file. My issue here is how to make the file path changeable (i.e. Parameter) to be something like

exec master..xp_cmdshell 'sqlcmd -d myDatabase -E -Q "SET NOCOUNT ON select * from myTable" -o "<path to output csv> " -h-1 -W -s","'

I tried using the bcp utility, however, I had very hard time to get an excel output, it always is created as txt, so I decided to try the sqlCmd approach.
Your help is appreciated. Thanks
Avatar of Arana (G.P.)
Arana (G.P.)

you need something like this: (untested)

DECLARE @cmd sysname, @vpath sysname
SET @vpath = 'c:\newpath'
SET @cmd = 'sqlcmd -d myDatabase -E -Q "SET NOCOUNT ON select * from myTable" -o "'+ @vpath+ '" -h-1 -W -s","'
EXEC master..xp_cmdshell @cmd
Avatar of fmichail


When I used your approach I got a message

Sqlcmd: Error: Error occurred while opening or operating on file "\\myserer\mypath\MyFile.csv (Reason: The filename, directory name, or volume label syntax is incorrect).

Does the approach accept the \\ format of the path?
the approach does, however the xp_cmdshell is using the sqlcmd credentials, not your user credentials, so you must give access rights to the sql service account.

In fact I am the owner of the folder path, and I granted full control access to authenticated users, same error showed.
I tried to use EXECUTE AS login = 'retro\mainserviceAccount'
then executed the commands and got the same error

From the error message I have a feeling that the path is truncated... I tried to select a short path and it worked... Is there a way to increase the size of the @vpath variable.... That will solve it. Please help. Thanks
Avatar of Arana (G.P.)
Arana (G.P.)

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial

I increased the variable to varchar(1000)
Still having same problem.
It worked now after changing the @cmd to be varchar(500)

I appreciate it... It was a great help. Thanks arana
Glad you could sort it out.