November 20, 2003 at 3:38 am
Hi All
I have a 2 databases, A and B, both running on the same instance of SQLServer. It is not expected that they will ever be located on separate servers but if this were to happen the servers would be linked.
I have a stored procedure that is compiled into database A and is therefore invoked using a connection to this database. The porblem is that from this stored procedure I need to invoke a second stored procedure that is compiled into database B.
If I were executing a string I would use sp_executesql with the full database name or if the procedure were also in database A simply call EXECUTE.
However I would like to know how I can call a stored procedure on a different database and also how I then capture the resultset returned by this procedure into a temp table in the calling stored procedure.
Basically I need some kind of INSERT INTO statement that executes a stored procedure on a separate database to get the resultset to insert.
If anyone knows how to do this I would really appreciate it.
Many thanks
Sam
[:-)]
November 20, 2003 at 3:58 am
Somthing like this would do the trick:
create #temptable
( <column definitions> )
insert #temptable
exec [database_name].[object_owner].[stored_proc_name] <parmlist>
drop table #temptable
If the second database is on a different server then all you need do is to add the server name to the exec statement.
As always, there is more than one way to skin a cat and others may come up with a different solution.
Jeremy
Edited by - Jeremy Kemp on 11/20/2003 03:57:59 AM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply