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" <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