fmichail
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
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.
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
ASKER
arana
When I used your approach I got a message
Sqlcmd: Error: Error occurred while opening or operating on file "\\myserer\mypath\MyFile.c sv (Reason: The filename, directory name, or volume label syntax is incorrect).
Does the approach accept the \\ format of the path?
When I used your approach I got a message
Sqlcmd: Error: Error occurred while opening or operating on file "\\myserer\mypath\MyFile.c
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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