• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

Connecting to 2 SQL Servers in the same query

Hello All,

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.
0
Adyjain
Asked:
Adyjain
  • 5
  • 5
1 Solution
 
zupi5Commented:
I would use linked servers and link the SQL server b to sqlserver A and then acces tha database D2 like so sqlserverb.d2..table.

you can link servers through the enterprise manager under security you should see linked servers, alternativelly you can use the sp_addlinkedserver to link the servers.

If its only a one time query or a seldom used one you use execute OPENROWSET or OPENDATASOURCE in your query.

sample from Books online for open datasource
SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerB;User ID=MyUID;Password=MyPass'
         ).D2.dbo.Categories

if you have any questions, do ask
0
 
AdyjainAuthor Commented:
Thanks for this.

 I am trying to use sp_addlinkedserver to connect to the remote server. The command that I give is as follows

EXEC sp_addlinkedserver
@server='Genesys', @provider = 'SQLOLEDB',@srvproduct = '',@provstr='Data Source=XXX;UID=XXX;PWD=XXX;', @catalog = 'GS_DataMart'

Upon running the below command

Select * from OPENQUERY(Genesys, 'Select * from V_Queue_Day')

I am getting an error as follows:
Cannot open database requested in login 'GS_DataMart'. Login fails.
[OLE/DB provider returned message: Invalid connection string attribute]

I have access to the database as mentioned above becuase I can connect to the database through the enterprise manager (but not through Security option).

Any ideas what may be worng with this. I have tried to look for help in MDSN but it does not look like I am doing anything wrong here.
0
 
zupi5Commented:
Execute permissions for sp_addlinkedserver default to members of the sysadmin and setupadmin fixed server roles.
you add linked server string is ok, in your select you should be able to do this

SELECT * FROM Genesys.GS_DataMart.dbo.V_Queue_Day

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
AdyjainAuthor Commented:
Do you mean sysadmin or setupadmin on the remote server? I think I am in this group on the local server but definately cannot become member for remote server. If this is the case then do we have any other options?

I tried executing the Select statement that you have provided but am getting the same message.
0
 
zupi5Commented:
on the local server,
is the server Genesys displayed under linked servers (security folder in the enterprise manager)?
0
 
AdyjainAuthor Commented:
It is available under the security folder however it is only showing Tables and Views under the catalog 'master'. Not under catalog GS_DataMart as I require.
0
 
zupi5Commented:
what is the catalog name in linked server properties, if it is not GS_DataMart change to GS_DataMart
0
 
AdyjainAuthor Commented:
Catalog Name in the linked server properties is being shown as GS_DataMart.
0
 
zupi5Commented:
you will have to look at the security settings for the server

select be made using this security context and type in the remote login and password (this login must have access to  GS_DataMart)
0
 
AdyjainAuthor Commented:
Thanks was able to solve this.

There was some issue with the rights for the remote server logon Id that was being provided.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now