Maximum Length for Multi-Part Identifiers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Maximum Length for Multi-Part Identifiers?

Author

Topic

DRJD
Starting Member

2 Posts

Posted - 2008-10-13 : 07:59:24

Hi

I'm trying to join two tables located on different databases using the addlinkedserver stored procedure, but I've come up against the following problem.

To test the linked server functionality, I am trying to do a simple select:

SELECT exposureid
FROM AEGSQL006.Onshore.dbo.OnshoreExposures

I run this command on the main server and it works correctly.

However, if I try to run the following command (still on the main server):

SELECT AEGSQL006.Onshore.dbo.OnshoreExposures.exposureid
FROM AEGSQL006.Onshore.dbo.OnshoreExposures

Then I get this error:

'The multi-part identifier "AEGSQL006.Onshore.dbo.OnshoreExposures.exposureid" could not be bound.'

That the SELECT works in the first instance but not the second leads me to believe that there is a limit to the length or number of parts that a multi-part identifier can have. Can anyone confirm if this is the case? Or is there another error that I haven't spotted in my SQL?
If there is a maximum length, then is there a workaround for this as if I want to join two tables on different databases then I will not be able to unless I can specify their full paths?

Thanks in advance for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 08:01:12

does this work?

SELECT t.exposureid
FROM AEGSQL006.Onshore.dbo.OnshoreExposures t

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 08:06:57

You have a FIVE part naming convention in your select. That's the problem.
With a SELECT statement, you use the database name in the FROM part.
In the SELECT statement, you use the table name in the SELECT part.

SELECT ~~AEGSQL006.~~Onshore.dbo.OnshoreExposures.exposureid
FROM AEGSQL006.Onshore.dbo.OnshoreExposures

E 12°55'05.63"
N 56°04'39.26"

DRJD
Starting Member

2 Posts

Posted - 2008-10-13 : 08:13:07

Thanks very much for your help guys.

The query works if I only use the table name and not the whole SERVER.DATABASE.TABLE reference.

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 08:21:48

Yes. That's because you now have a maximum of four-part naming convention.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 08:27:56

always better to use short aliases rather than repeating four part convention everywhere.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources