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.
