Possible Bug in SQL Server 2000?

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?
Is LogTable.cId varchar? You might want to restrict the where clause not
only to TableA but also to those rows where the value is actually numeric.
Or, convert A.nId to a VARCHAR before comparing.
http://www.aspfaq.com/
(Reverse address to reply.)
"Dave" <david_green@.softhome.net> wrote in message
news:14ec5400.0407080852.50c22022@.posting.google.c om...
> 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?
|||Dave,
WHERE A.nId IN (SELECT cId
Are you comparing A.nId (int) to cId (varchar) and cId contains alpha characters?
Mark H
"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?
>
|||[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?
>
|||"Dave" <david_green@.softhome.net> wrote in message
news:14ec5400.0407080852.50c22022@.posting.google.c om...
> 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
>
Sql Server is free to reorganize this query, transform the subquery into a
join, and evaluate the join criterion before the where-clause criterion. Try
casting nID to a varchar before the comparison.
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 cast( A.nId as varchar(15)) IN (SELECT cId
FROM Server1.myDB_3.dbo.LogTable
WHERE cTableName = 'TableA')
or B.nId IS NULL
David
|||On 8 Jul 2004 09:52:24 -0700, 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?
It's not a bug. You're asking SQL to compare A.nID to each value in the
subquery. It tries to do that by (an implied) convert(int,...) operation,
which fails for the value in LogTable.
Either convert a.nID to varchar, or restrict the values in the subquery to
those that are actually numeric.
SELECT A.nID, B.nID
FROM Server1.myDB_1.dbo.TableA A
LEFT OUTER JOIN Server2.myDB_2.dbo.TableB ON B.nID = A.nID
WHERE convert(varchar(15),A.nID) IN
(SELECT cID FROM Server1.MyDB_3.dbo.LogTable
WHERE cTableName = 'TableA')
OR b.nID IS NULL
..... or .......
SELECT A.nID, B.nID
FROM Server1.myDB_1.dbo.TableA A
LEFT OUTER JOIN Server2.myDB_2.dbo.TableB ON B.nID = A.nID
WHERE A.nID IN
(SELECT cID FROM Server1.MyDB_3.dbo.LogTable
WHERE cTableName = 'TableA' and ISNUMERIC(cID))
OR b.nID IS NULL