SQLTeam.com | Weblogs | Forums

Failed to run bcp from t-sql


#1

I was able to run the bcp from command line for remote server:

bcp "select Data from [Test_Db21].[Test_Db21].[attachments_blob] where attachments_dbid=33556500" queryout c:\blob\outofsql.png -T -L 1 -S " stats005.xzp.com" -f c:/blob/bcp.fmt

The same command from within T-sql failed:

SET @cmd = 'bcp "select Data from [Test_Db21].[Test_Db21].[attachments_blob] where attachments_dbid=33556500" queryout c:\blob\outofsql.png -T -L 1 -S " stats005.xzp.com" -f c:/blob/bcp.fmt'

EXEC @result = xp_cmdshell @cmd

SELECT @result

SQLState = 28000, NativeError = 18456

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'USC\MYPC$'.

Thank you in advance


#2

xp_cmdshell doesn't pass your credentials


#3

Thank you for your response.
Can you suggest how to resolve this?
I've similar query that runs well from the t-sql but bcp to local server using WNA. Why this works:
SET @cmd = ' bcp "select Data from mysail.scott.[Thumbnail] where Id=2" queryout '+ @pathAndFileName +' -T -L 1 -S "MYPC\SAILSQL" -f c:/blob/bcp.fmt'
EXEC @result = xp_cmdshell @cmd


#4

put it in a batch file and use runas


#5

As @jotorre_riversidedpss.org suggested, xp_CmdShell doesn't pass your credentials. If you have sys.admin privs, it WILL pass the credentials of the SQL Server login. Give that login privs on the remote box.