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
fmichailAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aranaCommented:
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
0
fmichailAuthor 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?
0
aranaCommented:
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.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

fmichailAuthor 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
0
aranaCommented:
@vpath varchar(1000)
or more
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fmichailAuthor 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
0
aranaCommented:
Glad you could sort it out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.