Error multi-part identifier - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Error multi-part identifier

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-10-19 : 16:08:23

Have following query:


CREATE TABLE	#JDE_Table
		(jde_job_numbers char(12)
                                )

declare @LibFile as char(18)

declare @Location char(10)
set @Location = 'South'

declare @SQLSTRING as varchar(4000)

SET NOCOUNT ON

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   @@mcu as job_number
           from  ' + @LibFile + '
order by @@mcu'')'


insert into #JDE_Table
                   (jde_job_numbers
                                   )
exec (@SqlString)

select     CompanyJobId as JobName
from       [VGIWPSQL2].goLabor30.dbo.Job
           inner join #JDE_Table on #JDE_Table.jde_job_numbers =  [VGIWPSQL2].goLabor30.dbo.Job.CompanyJobId
order by   CompanyJobId

On my last select I get error:

Msg 4104, Level 16, State 1, Line 47
The multi-part identifier "VGIWPSQL2.goLabor30.dbo.Job.CompanyJobId" could not be bound.

If I remove this line it works fine:

inner join #JDE_Table on #JDE_Table.jde_job_numbers =  [VGIWPSQL2].goLabor30.dbo.Job.CompanyJobId

Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 16:17:46

Try it with using alias names.

No, you're never too old to Yak'n'Roll if you're too young to die.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-10-19 : 16:26:09

I think,
you may have to double check your sql server name, database name, schema name and table name.

[VGIWPSQL2].goLabor30.dbo.Job

on your script, the following are you have to check,

server name is: [VGIWPSQL2]
database name is :goLabor30
schema name is :dbo
table name is : job

try this :

CREATE TABLE #JDE_Table
(jde_job_numbers char(12)
)

declare @LibFile as char(18)

declare @Location char(10)
set @Location = 'South'

declare @SQLSTRING as varchar(4000)

SET NOCOUNT ON

SET @SQLSTRING =
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL,
''select @@mcu as job_number
from ' + @LibFile + '
order by @@mcu'')'

insert into #JDE_Table
(jde_job_numbers
)
exec (@SqlString)

select CompanyJobId as JobName
from goLabor30.dbo.Job
inner join #JDE_Table on #JDE_Table.jde_job_numbers = goLabor30.dbo.Job.CompanyJobId
order by CompanyJobId

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-10-19 : 16:34:20

Got it working like this (at least no errors)



select     CompanyJobId as JobName
from       [VGIWPSQL2].goLabor30.dbo.Job
inner join #JDE_Table on #JDE_Table.jde_job_numbers =  Job.CompanyJobId
order by   CompanyJobId

[url]http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23666042.html[/url]

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources