1

I am trying to execute the below BCP query in my TEST Server, where BCP has a connection string to the production DB, so my destination folder is in my Test Server and I am getting an error, I think it's a permission related issue.

DECLARE @str NVARCHAR(2000), @path NVARCHAR(200)='Z:\SQL' , @TableName NVARCHAR(100)='tblUsers' Set @str = 'BCP IDB.dbo.'+@TableName+ ' OUT '+@path +'\'+@TableName+'.csv -c -t^| -T -S ProductionServerName\SQL2012' Exec xp_cmdshell @str

Print (@str) = BCP IDB.dbo.tblUsers OUT Z:\SQL\tblUsers.csv -c -t^| -T -S ProductionServerName\SQL2012

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

I shared my Test Server folder to the Production User account. Also I have mapped the shared folder drive as a network share in production and run the below SQL statements:

EXEC XP_CMDSHELL 'net use Z: \\TestServevrName\Test'

EXEC XP_CMDSHELL 'Dir Z:'

Still I am facing the same Issue.

Please guide me what steps are required to fix this permissions issue.

  • 1
    Specify a UNC path to the file instead of a mapped drive. For example, if your share name is SQL, specify \\TestServer\SQL\tblUsers.csv. – Dan Guzman Jun 20 '17 at 11:34
  • I given the same it is not working – Sathyanath Ravichandran Jun 22 '17 at 5:18
  • Did you grant the account permissions to both the share and the folders/files within? If the invoking user a member of the sysadmin role on the test server, it is the test server SQL Server service account that needs permissions. For non-sysadmin role members, the xp_cmdshell Proxy account needs permissions (docs.microsoft.com/en-us/sql/relational-databases/…). – Dan Guzman Jun 22 '17 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.