Re: Possible Bug in SQL Server 2000?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Kass (skass_at_drew.edu)
Date: 07/08/04 

Date: Thu, 08 Jul 2004 13:17:50 -0400

[comp.databases.ms-sqlserver removed - can't send to more than 1 server]

Dave,

  This isn't a bug. The optimizer is free to evaluate this query by
doing the comparison on A.nId = LogTable.cId before checking whether
cTableName is 'TableA', and presumably there are indexes in place that
make that approach better. Since you are comparing a character type to
an integer type, the character values are convered to int before the
comparison takes place. Since you can't be using an index on
LogTable.cId anyway, you can try this:

...
where A.nId IN (
  select case when cTableName = 'TableA' then cast(cId as int) else NULL end
  from ...
  where cTableName = 'TableA'
)

If there are no non-convertible-to-int cId values in rows where
cTableName = 'TableA', the error should go away.

Steve Kass
Drew University

Dave wrote:

>Hi guys,
>
>I have hit this bug more than once and was wondering if anyone else
>has ever seen it?
>
>SELECT A.nId
> ,B.nId
>FROM Server1.myDB_1.dbo.TableA A
> LEFT OUTER JOIN Server2.myDB_2.dbo.TableB B ON B.nId = A.nId
>WHERE A.nId IN (SELECT cId
> FROM Server1.myDB_3.dbo.LogTable
> WHERE cTableName = 'TableA')
> or
> B.nId IS NULL
>
>
>LogTable.cId is a varchar(15) but it only has numeric data where
>cTableName = 'TableA'.
>
>I get the following error when I execute the statement.
>
>Server: Msg 245, Level 16, State 1, Line 1
>Syntax error converting the varchar value '1-L7Z5X' to a column of
>data type int.
>
>'1-L7Z5X' does exist in LogTable but not for 'TableA'.
>
>Any thoughts?
>
>

Relevant Pages

  • Re: Possible Bug in SQL Server 2000?
    ... This isn't a bug. ... select case when cTableName = 'TableA' then castelse NULL end ... Dave wrote: ...
    (microsoft.public.sqlserver.server)
  • Re: Possible Bug in SQL Server 2000?
    ... It's not a bug. ... (SELECT cID FROM Server1.MyDB_3.dbo.LogTable ... WHERE cTableName = 'TableA' and ISNUMERIC) ...
    (microsoft.public.sqlserver.server)
  • Re: Possible Bug in SQL Server 2000?
    ... It's not a bug. ... (SELECT cID FROM Server1.MyDB_3.dbo.LogTable ... WHERE cTableName = 'TableA' and ISNUMERIC) ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL to query columns that are in seconds since 1/1/1970
    ... Dave Hargis, Microsoft Access MVP ... The actual type of these columns is Number, not Date/Time. ... SELECT Column1, Column2 FROM TableA ... I don't know if access SQL supports such a method (I'm guessing it ...
    (microsoft.public.access.queries)