I have to connect to 2 separate databases present on 2 separate SQL Servers in a single Stored Procedure.
Basically the stored procedure is present on a database D1 on SQL Server A. It needs to Query Table of Database D2 on SQL Server B. It then has to do a UNION of the results of the Query on Database D2 and insert into a table in the same database (D1) where the procedure is residing. Now I know this can be done through DTS. But I do not have the rights to create DTS packages on either of the SQL Servers, whereas I can create Stored Procedures.
Both the server are SQL Server 2000 and I have full access to the 2 databases (D1 and D2) that are involved.
Any suggestions on how to go about doing this through a Stored Procedure? Prompt Help will be much appreciated.