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.

Answer 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:

1. 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.
2. 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.