Maximum number of prefixes Exceeded.

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 number of prefixes Exceeded.

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-01 : 12:30:44

Good morning, I am getting the above error from the following query...any ideas

select accountid,c_acct_engagementID,client_code,engagement,ltrim(rtrim(client_code)) + ltrim(rtrim(engagement)) as
EngCode from saleslogix.sysdba.c_acct_Engagement B

where
ltrim(rtrim(B.Client_Code)) + ltrim(rtrim(B.Engagement))
not in (select ltrim(rtrim([rl-sql02].abc_sql.dbo.clients.cltnum)) + ltrim(rtrim([rl-sql02].abc_sql.dbo.clients.clteng))
from [rl-sql02].abc_sql.dbo.clients)

--------------------------------------------------------------

Server: Msg 117, Level 15, State 2, Line 6
The number name 'rl-sql02.abc_sql.dbo.clients' contains more than the maximum number of prefixes. The maximum is 3.

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 12:34:06

try this


select accountid,c_acct_engagementID,client_code,engagement,ltrim(rtrim(client_code)) + ltrim(rtrim(engagement)) as
EngCode from saleslogix.sysdba.c_acct_Engagement B

where
ltrim(rtrim(B.Client_Code)) + ltrim(rtrim(B.Engagement)) 
not in (select ltrim(rtrim(cltnum)) + ltrim(rtrim(clteng)) 
from [rl-sql02].abc_sql.dbo.clients)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-01 : 12:42:56

Thank you Visakh16

Perfect as always. How can I add the 2 fields cltnum and clteng from the client table to the output.

Thanks

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 12:50:54

you cant as you're getting the records not existing in clients table in the above statement.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-01 : 13:33:20

Your correct....forgot about that. How bout the reverse of this query which will show be c_acct_engagements that ARE in the clients table? How would I show the 2 fields then ?

Thanks

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 13:38:37

[code]
select ltrim(rtrim(cltnum)) , ltrim(rtrim(clteng))
from [rl-sql02].abc_sql.dbo.clients
where ltrim(rtrim(cltnum)) + ltrim(rtrim(clteng))
not in (select ltrim(rtrim(B.Client_Code)) + ltrim(rtrim(B.Engagement)) from saleslogix.sysdba.c_acct_Engagement)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources