Join from Two Databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join from Two Databases

Author

Topic

khartoum
Starting Member

5 Posts

Posted - 2012-12-17 : 17:12:04

I need to join data from tables existing in 2 databases on 2 different servers. I have successfully added the second SQL server (2005) in Server Objects/Linked Servers.

Now I receive the message "Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "sql.tracking.dbo.confirmmaster.jobnumber" could not be bound."

Here is my query:

SELECT [sql].tracking.dbo.confirmmaster.jobnumber,
[sql].tracking.dbo.confirmmaster.partnumber,
[sql].tracking.dbo.confirmmaster.scfid,
[sql].tracking.dbo.confirmmaster.days,
[sql].tracking.dbo.jobmaster.mastervalidate,
[sql2005_1].fp_vticket.dbo.vt_mailingpart.vt_folder_id,
[sql2005_01].fp_vticket.dbo.vt_mailingpart.vt_mail_class3
FROM [sql].tracking.dbo.jobmaster
JOIN [sql].tracking.dbo.confirmmaster
ON [sql].tracking.dbo.confirmmaster.jobnumber = [sql].tracking.dbo.jobmaster.jobnumber
INNER JOIN [SQL2005_01].fp_vticket
ON [sql2005_01].fp_vticket.dbo.vt_mailingpart.vt_folder_id = [sql].tracking.dbo.jobmaster.jobnumber
where [sql].tracking.dbo.jobmaster.jobnumber > '40034'
and [sql].tracking.dbo.confirmmaster.scfid = '700' and [sql].tracking.dbo.confirmmaster.days is not null

Can anybody help with this? Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-17 : 20:12:40

Use aliases for the tables:

select a.column1, b.asdf, ...
from [ls].db1.dbo.table1 a
join [ls].db2.dbo.table2 b

Reference the table via its alias everywhere except in the FROM/JOIN.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khartoum
Starting Member

5 Posts

Posted - 2012-12-19 : 10:58:53

Thank you for that. But, I posted this to the "New to SQL Server Programming" because I am just that. As such, I am unable apply the answer to my problem. I wonder if anyone could be a bit more specific by including an example using my code. Thank you in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-19 : 13:20:55

I pressed for time, but here is a start:

SELECT cm.jobnumber,
cm.partnumber,
cm.scfid,
cm.days,
jm.mastervalidate,
...
FROM [sql].tracking.dbo.jobmaster jm
JOIN [sql].tracking.dbo.confirmmaster cm

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khartoum
Starting Member

5 Posts

Posted - 2012-12-19 : 17:52:06

I think I'm making some progress, but still have these errors:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "sql.tracking.dbo.jobnumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "sql.tracking.dbo.confirmmaster.jobnumber" could not be bound.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'vt_mailingpart.vt_folder_id'.

My new query is:

SELECT cm.jobnumber,
cm.partnumber,
cm.scfid,
cm.days,
jm.mastervalidate,
mp.vt_mail_class_3,
mp.vt_folder_id
FROM [sql].[tracking].dbo.jobmaster jm
JOIN [sql].[tracking].dbo.confirmmaster cm
ON [sql].[tracking].dbo.jobnumber = [sql].[tracking].dbo.confirmmaster.jobnumber
JOIN vt_mailingpart.vt_folder_id mp
ON mp.vt_folder_id = [sql].tracking.dbo.jobmaster.jobnumber
JOIN [sql].[tracking].dbo.jobmaster jm
ON [sql].tracking.dbo.confirmaster.jobnumber = [sql].tracking.dbo.jobmaster.jobnumber
WHERE cm.jobnumber > '40000'

Since I am using a linked server, am I correct in thinking that I must use the full path "[sql].[tracking].dbo.jobmaster jm" for example?

Thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-19 : 17:58:17

Use the aliases in the ON as well. Only the FROM/JOIN should have the full path. You only specify it once for each table (unless you are doing a self join or more complex things such as a derived table, but you aren't doing any of that).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-19 : 17:58:51

The invalid object error is being you aren't referencing it correctly. Did you forget the dbo or schema owner?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-19 : 23:10:07

[code]
SELECT cm.jobnumber,
cm.partnumber,
cm.scfid,
cm.days,
jm.mastervalidate,
mp.vt_mail_class_3,
mp.vt_folder_id
FROM [sql].[tracking].dbo.jobmaster jm
JOIN [sql].[tracking].dbo.confirmmaster cm
ON jm.jobnumber = cm.jobnumber
JOIN vt_mailingpart.vt_folder_id mp
ON mp.vt_folder_id = jm.jobnumber
JOIN [sql].[tracking].dbo.jobmaster jm
ON cm.jobnumber = jm.jobnumber
WHERE cm.jobnumber > '40000'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khartoum
Starting Member

5 Posts

Posted - 2012-12-20 : 14:21:05

Getting closer. I had to add the full path on line 11 . .

SELECT cm.jobnumber,
cm.partnumber,
cm.scfid,
cm.days,
jm.mastervalidate,
mp.vt_mail_class_3,
mp.vt_folder_id
FROM [sql].[tracking].dbo.jobmaster jm
JOIN [sql].[tracking].dbo.confirmmaster cm
ON jm.jobnumber = cm.jobnumber
JOIN [sql2005_01].fp_vticket.dbo.vt_mailingpart.vt_folder_id mp
ON mp.vt_folder_id = jm.jobnumber
JOIN [sql].[tracking].dbo.jobmaster jm
ON cm.jobnumber = cm.jobnumber
WHERE cm.jobnumber > '40000'

. . . which, of course causes this error:

Msg 117, Level 15, State 1, Line 11
The object name 'sql2005_01.fp_vticket.dbo.vt_mailingpart.vt_folder_id' contains more than the maximum number of prefixes. The maximum is 3.

Looking online for a solution. Thank you for your help with this. This is a great forum!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-20 : 14:31:23

You join to a table or view, but your code added a column. Change that problem line so that you remove ".vt_folder_id".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khartoum
Starting Member

5 Posts

Posted - 2012-12-20 : 15:24:26

Got it! Here is the query that worked:

SELECT distinct cm.jobnumber,
cm.partnumber,
cm.scfid,
cm.days,
jm.jobnumber,
jm.mastervalidate,
mp.vt_mail_class_3,
mp.vt_folder_id
FROM [sql].[tracking].dbo.jobmaster jm
JOIN [sql].[tracking].dbo.confirmmaster cm
ON jm.jobnumber = cm.jobnumber
JOIN [sql2005_01].fp_vticket.dbo.vt_mailingpart mp
ON mp.vt_folder_id = jm.jobnumber
WHERE cm.jobnumber > '40000' and mp.vt_mail_class_3 = 'standard' and cm.days is not null

Thank you so much for taking the time to help me with this!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-20 : 17:44:51

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources