August 22, 2011 at 9:47 am
WE have a database that was on SQL 2005 in 2000 compatibility mode and now we're trying to get it to SQL 2005 compatibility mode on SQL 2008.
I'm not sure what's wrong with this.
ALTER PROCEDURE [dbo].[get_contact]
(
@accountIdvarchar(100)
)
AS
Begin
Declare @Select varchar(1000)
DECLARE @zeroes varchar(10)
SELECT @zeroes=REPLICATE('0',(10 - (LEN(@accountid))))
SET @accountid = @zeroes + @accountid
Select Account_Id SAccountId, Account_Name,
Address1, Address2, City, State, Zip, Phone, Fax_phone, Website_addr
From accountdb.dbo.accounts
Where
ACCOUNT_ID = @accountId
and Parent_Flag = 'Y'
End
Msg 4147, Level 15, State 1, Procedure get_contact, Line 18
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
August 22, 2011 at 9:51 am
Is accountdb.dbo.accounts a view? And is *it* using old style joins? If so you need to re-write it.
HTH
Dave J
August 22, 2011 at 9:51 am
The query you posted did not have a join statement, but the error message says differently. Is the procedure complete? or you just posted part of the SP?
August 22, 2011 at 9:54 am
ColdCoffee (8/22/2011)
The query you posted did not have a join statement, but the error message says differently. Is the procedure complete? or you just posted part of the SP?
Yes you either have a view or a trigger. This is definitely the time to use real joins rather than old syntax.
August 22, 2011 at 10:55 am
That's the complete sp. Accountdb is a database, accounts is a table.
August 22, 2011 at 10:58 am
Are you absolutely sure it's a table? Because there's no join at all in that procedure, it's valid for SQL 2005/2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 11:06 am
GilaMonster (8/22/2011)
Are you absolutely sure it's a table? Because there's no join at all in that procedure, it's valid for SQL 2005/2008.
+1
Or maybe a synonym?
Trigger is my default answer to this type of problems. Of course it won't be the case here since you're certain there's no DML, but I wouldn't discount a logon trigger as a freakishly long shot to explain this.
August 22, 2011 at 11:23 am
Can't be a trigger. Error specifically lists the proc in question.
Errors from login triggers don't go to the messages (because the login fails as a result)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply