0

I'm trying export my results using xp_cmdshell but I recive this error :

"Sqlcmd: error: error while opening or using file (my_path)".

This is what I've wrote on sqlserver:

exec xp_cmdshell 'sqlcmd -S my_server -d my_db -E -Q "my_query" -s "," -o "my_path"'.

I've tried using command line and on it my instructions works.

I think its can be an permissions trouble but I'm new on this type of things and I can't solve by myself. I hope that some of you can help me, thanks

Antonio B
  • 1
  • 3
  • `xp_cmdshell` executes on the server whereas the command-line executes on your client machine. The destination path is relative to the server and the SQL Server service account needs permissions to the path (assuming executed by a sysadmin role member). – Dan Guzman Mar 07 '22 at 11:32
  • 2
    Honestly, just don't do this. Using `xp_cmdshell` to call `sqlcmd` to connect back the the server that is using `xp_cmdshell` is such a "cludgy" solution. Use a proper ETL layer and do this outside of T-SQL. – Thom A Mar 07 '22 at 11:35
  • Agreed, why use `xp_cmdshell` when you can open an actual Command Shell and do it yourself. T-SQL is not a generalized scripting language, do not use it as such – Charlieface Mar 07 '22 at 12:39
  • I tried to do this because I had to create a stored procedure that runs automatically and at the end exports the result to csv. There are some alternative ways in T-SQL for doing this? – Antonio B Mar 07 '22 at 13:44
  • @AntonioB, exporting data to files is a job best done by the client application/tool, not the database. You could schedule a SQL Agent job that execute an SSIS package or PowerShell script that executes the proc and exports the data to a file. – Dan Guzman Mar 07 '22 at 13:48

0 Answers0