We help IT Professionals succeed at work.

use of sqlCmd command through exec master..xp_cmdshell

fmichail
fmichail asked
on
557 Views
Last Modified: 2017-03-22
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

Author

Commented:
arana
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?
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
arana

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
arana

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
CERTIFIED EXPERT

Commented:
Glad you could sort it out.