Very Computer

by **Cand** » Fri, 13 Jul 2001 02:01:15

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
-Ppwd

It fails with this error :

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#tblUsers'.

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
me!!!

Anyone have any ideas?

Thanks in advance

CandT

by **Wayne Gadber** » Fri, 13 Jul 2001 09:11:13

> 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
> -Ppwd

> It fails with this error :

> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> '#tblUsers'.

> 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
> me!!!

> Anyone have any ideas?

> Thanks in advance

> CandT

Try creating a non-temporary table and drop it at the end of the proc.

Wayne Gadberry

by **Cand** » Fri, 13 Jul 2001 19:45:07

Nope - exactly the same. Did a create table first, then inserted into it - but
it just says that it's an invalid object name....

Andrew

>> 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
>> -Ppwd

>> It fails with this error :

>> SQLState = S0002, NativeError = 208
>> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
>> '#tblUsers'.

>> 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
>> me!!!

>> Anyone have any ideas?

>> Thanks in advance

>> CandT

>Try creating a non-temporary table and drop it at the end of the proc.

>Wayne Gadberry

Very Computer

BCP and stored procedure

BCP and stored procedure

BCP and stored procedure

BCP and stored procedure