bcp command failing

[mkal]

I have two servers lets call them A and B. Server A is in one domain and Server B is in another domain. I've created a local login on both servers and given it the same username and password. I've given this account access to share(s) on each server so it can read and write to the specified folders.

So I logon to Server A using the above username and run the following bcp command:
bcp "select * from dbName.dbo.tableName with (nolock) where date > 'Jun 30 2008 11:55PM'" queryout "\\<ip address>\Share\tblName_09-03-08.txt" -c -S<ip address> -U"userName" -P"password". Where the "ip address" is the IP of Server B.

This is executed using the stored procedure master.dbo.xp_cmdshell and fails with the error message: "Unable to open bcp host data-file." However, when I open a command window and execute the same bcp commmand it works fine.

I guessing this is a permissions issue but I'm at a loss of how to fix it. Any ideas?

Thanks

 

[gmmastros]

Folder permissions are controlled by the windows account used to start the SQL Server service.

Click Start->Run
Type services.msc
Click OK

for SQL 2000, scroll down to MSSQLSERVER
for SQL 2005, scroll down to SQL Server

Right click -> Properties
Click Log On tab

Normally (by default), SQL Server starts with 'Local System Account', which doesn't have any permissions to any shares on any domain.

You can use another account to start the service. Then, you could give permissions to a share on another computer to that account. If you choose to do this, I encourage you to create an account specifically for this service. This way, you can completely control the permissions. While you're at it, set this account up with a password that will never expire.

It's fun troubleshooting that particular problem. Imagine... everything is fine for 90 days, then the password expires. 2 weeks later you re-boot the SQL Server and the service doesn't start because the password silently changed on you. Ugh.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mkal]

The account that starts/stops the two services on Server B is a domain account and it has Read/Write permissions to the share.

Other thoughts?

btw there is no trust between the domains.