0

So its kind of complicated I think, I've been searching all day for a solution.

I have a SQL Server 2008 R2 and with xp_cmdshell I currently generate some .csv files in C:\

What I want to do now is generate them directly into a linux server in the network.

I tried this :

EXEC xp_cmdshell 'NET USE T: \\192.168.0.25\PERSONS 123456 /user:linuxuser  /PERSISTENT:yes'
set @filepath = 'T:'

set @command ='echo '+@customerheader+' > '+@filepath+'\PERSONS\'+convert(varchar,123456)+'.csv'
set @command = replace(@command,'&','e')
exec master..xp_cmdshell @command

EXEC xp_cmdshell 'net use T: /delete'

And what I get is: "The system cannot find the path specified."
While the mapped drive is created succesfully: "The command completed successfully." and "T: was deleted successfully."

Thank you in advance

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
Elgert
  • 470
  • 2
  • 5
  • 19
  • Outside of SQL Server/ when using SQLCMD or OSQL.. Can you simply use your `NET USE` commands and `Dir C:somefolder\*.* > T:\somefile.txt` – Leptonator Sep 01 '15 at 15:17
  • ye it works, it created the file – Elgert Sep 01 '15 at 15:24
  • Can you show us your entire SQL Code? I don't think you are showing us everything.. Understand NDA and such.. But, you need to show us how you are calling your SQL Query.. – Leptonator Sep 01 '15 at 15:28

1 Answers1

0

I think you need to get away from: xp_cmdshell

What about the following?

NET USE T: \\192.168.0.25\PERSONS 123456 /user:linuxuser  /PERSISTENT:yes

SET SQLSERVER=YOUR_sql_SERVER
SET SQLQUERY=C:\LOCALFOLDER\somequery.sql
SET OUTPUTFILE=T:\somefolder\someoutput.csv
sqlcmd -S %SQLSERVER% -d YOUR_DATABASE -U SQL_USER -P SQLPASSWORD -i"%SQLQUERY%" -o "%OUTPUTFILE%" -s"," -y30

net use T: /delete

I think the problem is that each time you invoke xp_cmdshell, you invoke a separate PID for CMD.exe and is a different session or instance and is causing you trouble.

Hope this helps!

Leptonator
  • 3,379
  • 2
  • 38
  • 51