Avatar of fmichail
fmichail
Flag 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
Microsoft SQL Server

Avatar of undefined
Last Comment
Arana (G.P.)

8/22/2022 - Mon
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
fmichail

ASKER
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?
Arana (G.P.)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
fmichail

ASKER
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
ASKER CERTIFIED SOLUTION
Arana (G.P.)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
fmichail

ASKER
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
Arana (G.P.)

Glad you could sort it out.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.