The multi-part identifier could not be bound.

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

SSIS and Import/Export (2005)

The multi-part identifier could not be bound.

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-07-13 : 13:09:27

I am getting the following errors:

Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "FIX_DBA..FIX_DBA.CLIENTS.ADDRESS_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "FIX_DBA..FIX_DBA.ADDRESSES.ADDRESS_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "FIX_DBA..FIX_DBA.CLIENTS.CLNT_ID" could not be bound.

SELECT [FIX_DBA]..[FIX_DBA].[CLIENTS].CLNT_ID
FROM [FIX_DBA]..[FIX_DBA].[CLIENTS]
INNER JOIN [FIX_DBA]..[FIX_DBA].[ADDRESSES]
ON [FIX_DBA]..[FIX_DBA].CLIENTS.[ADDRESS_ID] =
[FIX_DBA]..[FIX_DBA].[ADDRESSES.ADDRESS_ID]

Dave
Helixpoint Web Development
http://www.helixpoint.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 13:14:17

SERVER.DATABASE.SCHEMA.TABLE.COLUMN
What is it in your query?
The .. is used normally in
DATABASE..TABLE.COLUMN

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

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-07-13 : 13:15:56

I am using a link server to Oracle. Sorry

Dave
Helixpoint Web Development
http://www.helixpoint.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 13:36:29

Oracle as linked server
linked_server_name.catalog_name.schema_name.table_name

have you tried to qualify the catalog_name instead of using ..

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

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-07-13 : 13:53:56

I did this and it worked. however. It took forever to bring back the date. 2.09 minutes. It only took 3 seconds in SQL Developer. What would cause this?

SELECT clint.CLNT_ID
FROM [FIX_DBA]..[FIX_DBA].[CLIENTS] clint
INNER JOIN [FIX_DBA]..[FIX_DBA].[ADDRESSES] addd
ON clint.[ADDRESS_ID] =
addd.[ADDRESS_ID]

Dave
Helixpoint Web Development
http://www.helixpoint.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 13:57:37

Don't know.
Maybe much more data than in developer database or the Server is busy?

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

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 14:12:19

This any better? (Not sure I've got the syntax quite right, but hopefully you can sort that out)


SELECT *
FROM OPENQUERY(FIX_DBA,
'SELECT clint.CLNT_ID 
FROM FIX_DBA.CLIENTS clint 
    INNER JOIN FIX_DBA.ADDRESSES addd
      ON clint.ADDRESS_ID = addd.ADDRESS_ID')

in your original there is a chance that the JOIN is being done locally on SQL - i.e. all data from both tables has to be retrieved from Remote to Local, and then the JOIN done (locally), which is likely to be slow!

Using OPENQUERY the inner query should be done, remotely, on the linked server, and only the results of that pulled to SQL server.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-07-13 : 14:19:06

It took the same amout of time to bring back the top 200

Dave
Helixpoint Web Development
http://www.helixpoint.com

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 02:54:27

OK, so JOIN is (I think) happening at the remote end, which is good

When you say "To bring back the TOP 200" how are you controlling that?

If you are doing SELECT TOP 200 ... FROM ... some remote query ... it is most probably retrieving all rows from remote, and then creaming off the first 200 locally.

In case helpful the queries we send to Oracle are formed like this:


SELECT *
FROM	OPENQUERY(OracleLinkedServerName,
'SELECT *
FROM
(
	SELECT	Col1,
		Col2,
		...
	FROM	schema_name.table_name
	WHERE	Col3 = ''XXX'' ...
	ORDER BY Col4, ...
) X
WHERE ROWNUM <= 200
'
)

this limits the number of rows returned. This happens at the Oracle end (but AFTER Oracle has made the Query, so it Oracle still has to prepare the whole dataset - my understanding is that it is not possible to restrict the rows that Oracle considers for the query without specific programming for each case-scenario)

P.S. we also always use a Wrapper Query within the OPENQUERY because it seems to pass-through the main query without mucking about with it. If we don';t do that we find that some drivers / transport layers try to parse / optimise / "improve" !! the query and we spend hours scratching our heads.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources