0

I have a daily routine that runs over a SQL db and exports some data with BCP. I need to move these files to a Linux machine, so I shared the folder using Samba.

The local export works fine, yet when I try to export to the Linux directly it triggers an error.

If I try to copy files manually there is no error and I can do that without any problem.

The SQL query is running under SQL Server Management Studio

DECLARE @sql VARCHAR(1000)
set @sql = 'BCP "SELECT rawdata FROM database.dbo.file where [id] = 123" queryout "Z:\files\123.jpg" -S SERVER\SQLINST -T -f D:\file.fmt'
EXEC xp_cmdshell @sql

The error is:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file
NULL

The Samba config looks like this:

[doc_sync]
path = /var/www/html
valid users = filesync
read only = no
writeable = yes
browseable = yes
public = yes
guest ok = yes

File permissions over the folder are 777. Also I have created a local user in windows (filesync) that is mirrored in the Linux machine so that there are no issues with the users.

I also tried to see if there is any issue with the xp_cmdshell command and tried this

EXEC xp_cmdshell 'MKDIR "Z:\Data\"'

where Z: is the Samba monunted drive and the error is:

output
The system cannot find the drive specified.
Mike
  • 113
  • 1
  • 1
  • 7

1 Answers1

0

XP_CMDSHELL cannot use the drive letters mounted already in the system so one will need to export it for the SQL query like this

EXEC XP_CMDSHELL 'net use Z: \\SRV\files /USER:username pass'
Mike
  • 113
  • 1
  • 1
  • 7