How do I query 2 different databases in same domain but different servers using single SQL statement/stored procedure

Question

1.00/5 (1 vote)

See more:

, +

I have identical mssql databases hosted in 2 different servers.
1st database replicates to update the 2nd database.

Once I have connected to the 1st database, I need a query (stored procedure) to run and query data from the 2nd database.

The query (stored proc) should include connection string details of 2nd database ie {serverinstance}{username}{password}

Psss: I need this ASAP so as to automate some daily common tasks.

What I have tried:

Currently, Im using a program I've written in c#.
A database connector class whose constructor takes {serverinstance}{username}{password} parameters to return dbObject instance.
With 2 dbObject instances of respective databases, I then create my data list fetched from various tables in the 2 databases.
NB: the 2 databases can contain different set of data for similar tables.

Posted 30-Dec-20 13:06pm

onyangofred

Updated 1-Jan-21 3:48am

Add a Solution

Comments

BillWoodruff 30-Dec-20 19:48pm

"What I have tried:" Okay, that sounds good, and, now, what is the question ?

fwiw: the idea of doing things at the same time with two open db's which somehow replicate, and "can contain different set of data for similar tables" ... fills me with horror :)

FranzBe 31-Dec-20 3:01am

As you already have one stored procedure in place in database #1 you could set up a 'linked server' in database #1 that points to database #2. Having done this the stored procedure can access database #2 with the 4-dot notation select * from [linked-server-name].[db-name].[schema].[table]. Search for "linked server" or "sp_addlinkedserver".
As already noted: doing stuff in 2 databases that are somehow replicating on their own may lead to some nightmare.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Answer 1 · 2021-01-01T03:48:00

(I'm assuming SQL Server)

First off, you could look into linked servers, but I do not recommend that. Linking servers typically ends in woe. And, if you need this only periodically, it definitely is not worth the performance hit. I have never seen acceptable results from linked servers.

Second, you could look into writing a CLR function which uses ADO.net to connect to the other server and execute the query. I have had some success doing that, but I can't use it in a production environment at work.

Third, (recommended) use some form of simple ETL tool to query the databases in question.

CHill60 · Answer 2 · 2020-12-31T00:48:00

If you are replicating from #1 to #2 then there is no need to query both databases, the data you are trying to access will be in both so just query one of them.

If "NB: the 2 databases can contain different set of data for similar tables." is also true then you only need to query database #2 as all of the data from #1 will have been replicated to #2.

Alternatively, describe these "daily common tasks" you are trying to achieve and you might get better solutions.

However, there are three ways you can access remote databases in MSSQL - all of which are described here
https://www.sqlshack.com/querying-remote-data-sources-in-sql-server/[^]
Namely (all links are to Microsoft Docs)
- OPENDATASOURCE[^]
- OPENROWSET[^] (From your description I think this is what you are after)
- Linked Servers[^] (possibly in conjunction with OPENQUERY[^])

How do I query 2 different databases in same domain but different servers using single SQL statement/stored procedure

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How do I query 2 different databases in same domain but different servers using single SQL statement/stored procedure

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us