| Author |
 Topic  |
|
|
khartoum
Starting Member
5 Posts |
 Posted - 12/17/2012 : 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
USA
35020 Posts |
|
|
khartoum
Starting Member
5 Posts |
Posted - 12/19/2012 : 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
USA
35020 Posts |
|
|
khartoum
Starting Member
5 Posts |
Posted - 12/19/2012 : 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
USA
35020 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35020 Posts |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 12/19/2012 : 23:10:07
|
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
|
|
|
|
khartoum
Starting Member
5 Posts |
Posted - 12/20/2012 : 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
USA
35020 Posts |
|
|
khartoum
Starting Member
5 Posts |
Posted - 12/20/2012 : 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
USA
35020 Posts |
|
| |
Topic |
|
Join from Two Databases
