Need to use 2 databases for a simple query

1

HI I have never used 2 databases before and am trying unable to find the syntax. I understand that you have to login to both systems. What I want to do here is see which staff have been entered into the production system but were not entered into the development system: I would be expecting to see between 10-40 results. The below query is getting this:Msg 208, Level 16, State 1, Line 1
Invalid object name 'evolv_cs_dev.staff_view'.

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

2

you should use [database].[schema].[object], if dbo is your default schema:

select
psv.first_name, psv.last_name, psv.login_name, psv.id_number
from [evolv_cs_dev].dbo.staff_view

3

I have an issue to run this. I have opened both databases in the object explorer they are both there. When I run the select statement against the dev db (evolv_cs_dev) it works, but when I change to production (evolv_cs), then this statement fails with error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'evolv_cs.dbo.staff_view'.

When I click on "available databases" only one or the other is in the list. How do I run this so that both databases are present to the query?

4

select name from sys.databases
where name in ('evolv_cs_dev','evolv_cs');

what do you see in the output if you run the above query.

5

evolv_cs.
How do I have both databases available? Do I need to use 'Linked Servers'?

6

So, you have 2 databases on 2 different SQL instances.
Yes, you will need to setup linked server to access the database from another instance.

1 Like
7

Well, this error may also be due to an incorrect database name and a mismatch of schemas. You need to correct and check both of them, then try again.
Thanks