Executing a stored procedure to insert records into a table - SQL Server 2000

Question

Hi,

I'm working in SQL Server 2000. The following query is not working

declare @TempAccountKey Table (AccKey int,SitName varchar(1000),SitKey int) 
insert into @TempAccountKey(AccKey,AccName) 
exec [usp_Get_AccountForUser] @UserName 

It is throwing the error

EXECUTE cannot be used as a source when inserting into a table variable.

Any ideas?

#1

---

The linked server is properly set up. If i execute the following query

exec [ABC-SQL-PROD.DBabc.dbo.usp_Get_ABCForUser]

it is showing the error 'Could not find stored procedure'.

Any ideas?

NLV

Answer 1

Use a temp table instead

CREATE TABLE #TempSiteKey (AccKey int,SitName varchar(1000),SitKey int) 

insert into #TempSiteKey (AccKey,AccName) 
exec [usp_Get_AccountForUser] @UserName 

DROP TABLE #TempSiteKey

And the reason you can't call the procedure is because you've delimited it incorrectly

exec [ABC-SQL-PROD.DBabc.dbo.usp_Get_ABCForUser]

Actually means execute the procedure called 'ABC-SQL-PROD.DBabc.dbo.usp_Get_ABCForUser'.

What you want is:

exec [ABC-SQL-PROD].[DBabc].[dbo].[usp_Get_ABCForUser]