Data or index corruption?

Since this morning we have strange behaviour on our production SQL Server
With a simple select I have those results :
select serialno from table1 where serialno=205749
'Syntax error converting the varchar value '15LA02269' to a column of data
type int.'
But this one work :
select serialno from table1 where serialno='205749'
Of course serialno is a auto increment of type 'INT', but some other columns
are varchar
Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
error in SQL error Log.
I don't know if one or several tables are corrupted because I ve got those
type of message on other simple query with other tables.
I don't know what to do now... I hope someone have an idea.
Thanks in advanceOuch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> Since this morning we have strange behaviour on our production SQL Server
> With a simple select I have those results :
> select serialno from table1 where serialno=205749
> 'Syntax error converting the varchar value '15LA02269' to a column of data
> type int.'
> But this one work :
> select serialno from table1 where serialno='205749'
> Of course serialno is a auto increment of type 'INT', but some other columns
> are varchar
> Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> error in SQL error Log.
> I don't know if one or several tables are corrupted because I ve got those
> type of message on other simple query with other tables.
> I don't know what to do now... I hope someone have an idea.
> Thanks in advance|||Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
No error or warning.
Arrghhh : I've just realized something : serialno is not 'int' it's a
VARCHAR !!! And it appears in lot of tables, that why I have this message in
several cases.
I've never had this sort of message before. But few days ago we have changed
SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
my problems.
I think I have now to change all query for that columns (change int
parameters to string in ADO) ... If SantaClauss have nothing to do now
perhaps could he help me?
Many thanks for your help
"Tibor Karaszi" wrote:
> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "smf" <smf@.discussions.microsoft.com> wrote in message
> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> > Since this morning we have strange behaviour on our production SQL Server
> > With a simple select I have those results :
> >
> > select serialno from table1 where serialno=205749
> > 'Syntax error converting the varchar value '15LA02269' to a column of data
> > type int.'
> >
> > But this one work :
> > select serialno from table1 where serialno='205749'
> >
> > Of course serialno is a auto increment of type 'INT', but some other columns
> > are varchar
> > Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> > error in SQL error Log.
> >
> > I don't know if one or several tables are corrupted because I ve got those
> > type of message on other simple query with other tables.
> >
> > I don't know what to do now... I hope someone have an idea.
> > Thanks in advance
>
>|||That explains it.
In SQL Server 2000, a WHERE clause when you don't have the same datatype is evaluated according to
the section in Books Online called "datatype precedence". According to this, a varchar is converted
to an int. So your where clause would be the same as
select serialno from table1 where CAST(serialno AS int) = 205749
And not only can't above use an index on the serialno column, if you have something which isn't
convertible to an int, you get that error message.
In earlier releases the constant was converted to the columns datatype. I.e.:
select serialno from table1 where serialno= CAST(205749 AS varchar(nn))
Above can use an index on the serialno column and will also work for not numeric values in the
serial no column.
So, yes. changing compatibility level is most probably what made you see these run-time errors.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:D9CCEA0A-8754-421C-A57E-FB286A5E32E5@.microsoft.com...
> Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
> No error or warning.
>
> Arrghhh : I've just realized something : serialno is not 'int' it's a
> VARCHAR !!! And it appears in lot of tables, that why I have this message in
> several cases.
> I've never had this sort of message before. But few days ago we have changed
> SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
> my problems.
> I think I have now to change all query for that columns (change int
> parameters to string in ADO) ... If SantaClauss have nothing to do now
> perhaps could he help me?
> Many thanks for your help
>
> "Tibor Karaszi" wrote:
>> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "smf" <smf@.discussions.microsoft.com> wrote in message
>> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
>> > Since this morning we have strange behaviour on our production SQL Server
>> > With a simple select I have those results :
>> >
>> > select serialno from table1 where serialno=205749
>> > 'Syntax error converting the varchar value '15LA02269' to a column of data
>> > type int.'
>> >
>> > But this one work :
>> > select serialno from table1 where serialno='205749'
>> >
>> > Of course serialno is a auto increment of type 'INT', but some other columns
>> > are varchar
>> > Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
>> > error in SQL error Log.
>> >
>> > I don't know if one or several tables are corrupted because I ve got those
>> > type of message on other simple query with other tables.
>> >
>> > I don't know what to do now... I hope someone have an idea.
>> > Thanks in advance
>>