I'm running SQL server on my machine, so have BCP installed locally, but I want
to execute a SP on a remote server, and output to a text file locally...
So my SP is of the format :
CREATE PROCEDURE test AS
SELECT TOP 100 * INTO #tblUsers FROM [REMOTESERVER].MyDatabase.dbo.MyTable
SELECT * FROM #tblUsers
DROP TABLE #tblUsers
pretty simple stuff eh - and it runs through Query Analyser fine...
however, if I choose to run BCP on my machine with this command line
bcp "exec MyDatabase.dbo.test" queryout C:\output.txt -c -SREMOTESERVER -Uuser
It fails with this error :
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
I understand that BCP is running on my machine, but If the SP was JUST a SELECT
statement, it would work. It's the use of the temporary table which is not
working. But that table is created and used in the SP - so it shouldn't affect
Anyone have any ideas?
Thanks in advance