Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Working with Results from Remote Procedure Call

Author  Topic 

Ambulare
Starting Member

8 Posts

Posted - 2013-02-01 : 09:05:46
Hi all,

I'm trying to import data resulting from a stored procedure on a linked server. I have admin rights on the local server but very limited permissions on the remote server as it's a commercial install managed by an external company, so I can't change any settings.

I can run the remote stored procedure call and it returns a result set, but any time I try to actually do anything with those results other than look at them on the screen, I get error messages. I can't put them in a table, table variable or a temp table, which pretty much makes the remote call useless as the result set can't be transformed or loaded.

Has anyone found a way around this?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-01 : 09:11:16
How are you trying to insert the data into a table? If it is a stored procedure, you would need to use something like the insert..exec command to grab the data from the stored procedure and insert into a table. Look at Sommarskog's article http://www.sommarskog.se/share_data.html

Another option is to use SSIS to import the data to your server.
Go to Top of Page
   

- Advertisement -