Inconsistent behaviour of SQL

Folks, We have the following sql statement which we run against two
databases of same structure
But return different returns. Any help is appreciated.
Select @.@.version
go
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
--
Calist Site
--
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE = 'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM = 016598
go
The above works fine
Dub1 Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE = 'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM = 004811
go
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
Go
The red statement gets...
Fatal Error: Syntax error converting the varchar value '.W
_____
. ' to a column of data type int.
The Table structure is as under (same for both the databases)
CREATE TABLE [dbo].[PLAYER] (
[INDIVIDUAL_ID] [int] NOT NULL ,
[PLAY_JOIN_DATE] [datetime] NULL ,
[PLAY_MBRSHIP_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_FINANCIAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_LEVEL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PLAY_BADGE_NUM] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[PLAY_OCCUPATION] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_MARITAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_INCOME_LEVEL] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUMBER] [int] NOT NULL ,
[PLAY_INPLAY_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_CREATE_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_USERID] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] WITH NOCHECK ADD
CONSTRAINT [PK_PLAYER] PRIMARY KEY CLUSTERED
(
[INDIVIDUAL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] ADD
CONSTRAINT [FK_PLAYER_ACCOUNT] FOREIGN KEY
(
[ACCT_NUMBER]
) REFERENCES [dbo].[ACCOUNT] (
[ACCT_NUMBER]
),
CONSTRAINT [FK_PLAYER_INDIVIDUAL] FOREIGN KEY
(
[INDIVIDUAL_ID]
) REFERENCES [dbo].[INDIVIDUAL] (
[INDIVIDUAL_ID]
)
GOYour problem (which you described with so much detail) can be
summarized by the following repro script:
CREATE TABLE Test (A varchar(5) PRIMARY KEY)
INSERT INTO Test VALUES ('123')
SELECT * FROM Test WHERE A=1
-- runs fine
INSERT INTO Test VALUES ('XYZ')
SELECT * FROM Test WHERE A=1
-- Syntax error converting the varchar value 'XYZ' to a column of data
type int.
This is expected behaviour, because when comparing a varchar column to
an int constant, SQL Server tries to convert the varchar to an int,
according to the data type precedence rules.
For more informations, see:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp
The solution is, of course, to specify a varchar constant (this way SQL
Server would not need to do any conversions, so it can also use an
index, if one exists).
Razvan|||THX for the summary Razvan.
My question was:
SQL server does the conversion in all the databases my databases with the
same structure on the same sql server except this particular one.
Wondering if anyone has come across the situation where the SQL implicitly
converts (as per
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)
Is this a particular patch thing, etc.
Regards,
Subhash
"Razvan Socol" wrote:
> Your problem (which you described with so much detail) can be
> summarized by the following repro script:
> CREATE TABLE Test (A varchar(5) PRIMARY KEY)
> INSERT INTO Test VALUES ('123')
> SELECT * FROM Test WHERE A=1
> -- runs fine
> INSERT INTO Test VALUES ('XYZ')
> SELECT * FROM Test WHERE A=1
> -- Syntax error converting the varchar value 'XYZ' to a column of data
> type int.
>
> This is expected behaviour, because when comparing a varchar column to
> an int constant, SQL Server tries to convert the varchar to an int,
> according to the data type precedence rules.
> For more informations, see:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp
> The solution is, of course, to specify a varchar constant (this way SQL
> Server would not need to do any conversions, so it can also use an
> index, if one exists).
> Razvan
>|||> SQL server does the conversion in all the databases my databases with the
> same structure on the same sql server except this particular one.
Probably, this is the only database that has some non-numeric values in
that char(6) column.
Razvan