[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 903
  • Last Modified:

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**
0
printmedia
Asked:
printmedia
  • 5
  • 5
1 Solution
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now