The multi-part identifier could not be bound in SQL Server 2008

Hi all.

I get a multi-part identifier error when I run the code below.

It lists all of the linked server table and fields in the error. What am I doing incorrectly?

Thank you in advance.

update t1
set [myID]  = t2.myID
from GPO_Rosters.dbo.All_Rosters t1 join 
( SELECT     CAST([myLinkedServer].[myDB].dbo.tblReference.Division AS nvarchar(10)) 
                      + N'-' + CAST([myLinkedServer].[myDB].dbo.tblReference.ReferenceID AS nvarchar(10)) AS myID, [myLinkedServer].[myDB].dbo.tblGPODetails.GPOName, 
                      [myLinkedServer].[myDB].dbo.GPOSub1A.CompanyNumber
FROM         [myLinkedServer].[myDB].dbo.GPOSub1A INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblGPODetails ON 
                      [myLinkedServer].[myDB].dbo.GPOSub1A.GPODetailsID = [myLinkedServer].[myDB].dbo.tblGPODetails.GPODetailsID INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblReference ON 
                      [myLinkedServer].[myDB].dbo.tblGPODetails.ReferenceID = [myLinkedServer].[myDB].dbo.tblReference.ReferenceID
WHERE     ([myLinkedServer].[myDB].dbo.GPOSub1A.CompanyNumberStatus = N'Active')) t2 on t1.[GPO Name] = t2.GPOName AND t1.[Company Number] = t2.CompanyNumber
where t1.[myID] IS NULL

Open in new window

==========================================
**Author Accepted Solution, Original Points: 500**
printmediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<wild guess>

>update t1
>set [myID] = ...
Eyeballeth thy tables in this query and see if there is more than one with a column named myId.
Assuming yes, you'll have to prefix the above myID with the correct able name.
0
chaauCommented:
Check the column names in GPO_Rosters.dbo.All_Rosters. Do you have space in these fields:
[GPO Name], [Company Number]

Open in new window

0
printmediaAuthor Commented:
The columns names do not have spaces.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

chaauCommented:
OK then, modify your statement to this and see if it works:
update t1
set [myID]  = t2.myID
from GPO_Rosters.dbo.All_Rosters t1 join 
( SELECT     CAST([myLinkedServer].[myDB].dbo.tblReference.Division AS nvarchar(10)) 
                      + N'-' + CAST([myLinkedServer].[myDB].dbo.tblReference.ReferenceID AS nvarchar(10)) AS myID, [myLinkedServer].[myDB].dbo.tblGPODetails.GPOName, 
                      [myLinkedServer].[myDB].dbo.GPOSub1A.CompanyNumber
FROM         [myLinkedServer].[myDB].dbo.GPOSub1A INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblGPODetails ON 
                      [myLinkedServer].[myDB].dbo.GPOSub1A.GPODetailsID = [myLinkedServer].[myDB].dbo.tblGPODetails.GPODetailsID INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblReference ON 
                      [myLinkedServer].[myDB].dbo.tblGPODetails.ReferenceID = [myLinkedServer].[myDB].dbo.tblReference.ReferenceID
WHERE     ([myLinkedServer].[myDB].dbo.GPOSub1A.CompanyNumberStatus = N'Active')) t2 on t1.[GPOName] = t2.GPOName AND t1.[CompanyNumber] = t2.CompanyNumber
where t1.[myID] IS NULL

Open in new window

0
printmediaAuthor Commented:
Thanks chaau, but that's my exact original code. I tried the following and it worked:

update t1
set [myID]  = t2.myID
from GPO_Rosters.dbo.All_Rosters t1 join 
( SELECT     CAST([t1a.Division AS nvarchar(10)) 
                      + N'-' + CAST([t1a.ReferenceID AS nvarchar(10)) AS myID, t1b.GPOName, 
                      t1c.CompanyNumber
FROM         [myLinkedServer].[myDB].dbo.GPOSub1A t1c INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblGPODetails t1b ON 
                      t1c.GPODetailsID = t1b.GPODetailsID INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblReference t1a ON 
                      t1b.ReferenceID = t1a.ReferenceID
WHERE     (t1c.CompanyNumberStatus = N'Active')) t2 on t1.[GPOName] = t2.GPOName AND t1.[CompanyNumber] = t2.CompanyNumber
where t1.[myID] IS NULL

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
printmediaAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for printmedia's comment #a40199194

for the following reason:

I figured it out on my own.
0
chaauCommented:
Just to be clear your original code is not exactly as the last code. If you can't see the difference I am showing you the two lines below:
on t1.[GPO Name] = t2.GPOName AND t1.[Company Number] = t2.CompanyNumber

Open in new window

on t1.[GPOName] = t2.GPOName AND t1.[CompanyNumber] = t2.CompanyNumber

Open in new window

The first line from the original code, that was not worked. The second line is from the code I have provided and you have copied as your last comment, which you think is your
exact original code
Go figure....
0
printmediaAuthor Commented:
chaau,

The code you provided is incorrect. The field name in t1.[GPO Name] has a space while the field name in t2.GPOName does not. That's the way the fields are in each respective table. So doing it your way gives me another error because t1.[GPOName] and t1.[CompanyNumber] DOES NOT exist.
0
chaauCommented:
Ok then. When you have time just reread my answer at http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28475480.html#a40198242 and compare it with yours.
0
printmediaAuthor Commented:
chaau.

I tried your code again:

update t1
set [myID]  = t2.myID
from GPO_Rosters.dbo.All_Rosters t1 join 
( SELECT     CAST([myLinkedServer].[myDB].dbo.tblReference.Division AS nvarchar(10)) 
                      + N'-' + CAST([myLinkedServer].[myDB].dbo.tblReference.ReferenceID AS nvarchar(10)) AS myID, [myLinkedServer].[myDB].dbo.tblGPODetails.GPOName, 
                      [myLinkedServer].[myDB].dbo.GPOSub1A.CompanyNumber
FROM         [myLinkedServer].[myDB].dbo.GPOSub1A INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblGPODetails ON 
                      [myLinkedServer].[myDB].dbo.GPOSub1A.GPODetailsID = [myLinkedServer].[myDB].dbo.tblGPODetails.GPODetailsID INNER JOIN
                      [myLinkedServer].[myDB].dbo.tblReference ON 
                      [myLinkedServer].[myDB].dbo.tblGPODetails.ReferenceID = [myLinkedServer].[myDB].dbo.tblReference.ReferenceID
WHERE     ([myLinkedServer].[myDB].dbo.GPOSub1A.CompanyNumberStatus = N'Active')) t2 on t1.[GPOName] = t2.GPOName AND t1.[CompanyNumber] = t2.CompanyNumber
where t1.[myID] IS NULL

Open in new window


And I still get the error.
0
chaauCommented:
That's OK, as long as the query works for you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.