Conversion from 2000 to 2005 - 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)

Conversion from 2000 to 2005

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-12-12 : 10:09:09

Hi,

I've been moving a 2000 database to 2005, all is fine apart from one stored procedure which is as follows


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DBAuthenticate]

(
  @username Varchar( 100 ),
  @password Varchar( 100 )
)
As

DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )

SELECT
  @ID = IdentityCol,
  @actualPassword = u_password
  FROM tblUsers
  WHERE u_name = @username

IF @ID IS NOT NULL
  IF @password = @actualPassword
    RETURN @ID
  ELSE
    RETURN - 2
ELSE
  RETURN - 1

It works fine on the 2000 server, but on 2005 the script gives this error:

Invalid column name 'identitycol'

I didn't write the SP, can anyone tell what the problem is, there is no column identitycol in tblUsers on either server.

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-12 : 10:17:04

Are you sure this SP works in SQL 2000? and that there is no IdentityCol in tblUsers table on SQL 2000 as well?

Try running the same SP but prefix table name with dbo. and check whether you get any error in SQL 2000.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 10:20:47

Replace the identitycol alias with the real column name.
A stab in the dark would be

ALTER PROCEDURE dbo.DBAuthenticate
(
	@UserName VARCHAR(100),
	@Password VARCHAR(100)
)
AS

SET NOCOUNT ON

DECLARE @ActualPassword VARCHAR(100)

SELECT	@ID = u_ID,
	@ActualPassword = u_Password
FROM	tblUsers
WHERE	u_Name = @UserName

IF @ID IS NULL
	RETURN -1
ELSE
	IF @Password = @ActualPassword
		RETURN @ID
	ELSE
		RETURN -2

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-12 : 10:21:03

In SQL 2000, this gets you the Identity Column for that table where as in SQL 2005, this is a reserved word, change IdentityCol to the Identity Column of tblUsers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 10:25:23

quote:
You can refer to the column by using the keyword IDENTITYCOL instead of the proper column name. Because only one column in each table can have the Identity property set, SQL Server always can figure out which column you're referencing.

It seems this functionality is withdrawn on SQL Server 2005.

E 12°55'05.25"
N 56°04'39.16"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-12-12 : 10:42:48

Thats great, changing it to

SELECT @ID = ID,

works fine!

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources