SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join from Two Databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

khartoum
Starting Member

5 Posts

Posted - 12/17/2012 :  17:12:04  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 12/17/2012 :  20:12:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

khartoum
Starting Member

5 Posts

Posted - 12/19/2012 :  10:58:53  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 12/19/2012 :  13:20:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

khartoum
Starting Member

5 Posts

Posted - 12/19/2012 :  17:52:06  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 12/19/2012 :  17:58:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 12/19/2012 :  17:58:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 12/19/2012 :  23:10:07  Show Profile  Reply with Quote

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'



KH
Time is always against us

Go to Top of Page

khartoum
Starting Member

5 Posts

Posted - 12/20/2012 :  14:21:05  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 12/20/2012 :  14:31:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

khartoum
Starting Member

5 Posts

Posted - 12/20/2012 :  15:24:26  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 12/20/2012 :  17:44:51  Show Profile  Visit tkizer's Homepage  Reply with Quote


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

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000