BCP Issue in SQL
-
I have issue in executing the command below
SET @filePath = '\\i.p.a.d.d.r.e.s.s\Shared\Output1\'
SET @fileName = 'output_' + CONVERT(VARCHAR, GETDATE(), 112) + '_' +
RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(HOUR, GETDATE())), 2) +
RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MINUTE, GETDATE())), 2) + '.txt'
SET @sqlCommand = 'bcp dbname.[dbo].[set_ExportData] queryout ' + @filePath + @fileName + ' -c -T -t, "|"'
EXEC master..xp_cmdshell @sqlCommand:
Scenario1(It works fine)
- I open the SSMS in my local pc. The db and script are in my local pc
- The output file is being written in my local pc folder
- I have admin access to my local pc
- I use windows authentication to open the ssms
Scenario 2(Error)
- I open the SSMS in my local pc but I am accessing and running the similar script in the remote sql server
- Error: "Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file" when the sqlcommand is executed. The command is supposed to write a file to my local folder and the local folder is shared for everyboby
- I use windows authentication to open the ssms
Any ideas?
-
bcp called via xp_cmdshell runs under the context of the service account running SQL, and not as the logged in user who executed it.
the account is probably not a domain account, so it cannot access \\i.p.a.d.d.r.e.s.s\Shared\Output1
the logon as in services will need to change to a domain account, or you need to move the file to a location on local disk the server itself it can access.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply