How to setup to run a query against 2 databases

Question

I am trying to find out which staff records are in the Production system and not in development with this query: If the id_number is not in the dev system then we need to update. They both have different ip addresses.

select     psv.first_name, psv.last_name, psv.login_name, psv.id_number
from       [evolv_cs].dbo.staff_view psv
where      psv.id_number not in (select psv.id_number
                                 from   [evolv_cs_dev].dbo.staff_view dsv)

Which gives this error:

Msg 208, Level 16, State 1, Line 1 Invalid object name 'evolv_cs.dbo.staff_view'.

I am not seeing how to run the query with both databases. When I look at object explorer only one of the databases appear so we get the error message. When I click on one database in Object explorer, then that is the one that is available.

I am sorry if this is so basic but - How do I run this so that both databases are present to the query?

I am trying to get both databases to be present for the query. So far only 1 is. Therefore, this query fails.

Did you create a linked server? Check out this article: learn.microsoft.com/en-us/sql/relational-databases/… — Robert Kock
– Robert Kock, Commented Jul 6, 2023 at 13:14
You have to register the other server on the server you are connecting to -- that server will perform the connection. So if you connect to evolv_cs you need to register evolv_cs_dev on that server in management studio — Hogan
– Hogan, Commented Jul 6, 2023 at 13:14

PankajSanwal · Accepted Answer · 2023-07-06 16:13:47Z

-1

Looking at the error message, it seems you are connected to your Dev server where you should be seeing evolv_cs_dev in your object explorer under databases.

To access tables from [evolv_cs] which is on a different server, you need to create a linked server.

To make it more clear:

Tables from a different database in same server can be accessed by databaseName.schema.tablename notation. Which your trying to do in your query but since your prod db is on different server its not working.
Tables in a database on a different server will have to use LinkedServerName.DatabaseName.Schema.TableName notation, which you need to do in your case.

answered Jul 6, 2023 at 16:13

PankajSanwal

1,0398 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to setup to run a query against 2 databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related