468,533 Members | 1,909 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,533 developers. It's quick & easy.

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?
Jul 20 '05 #1
3 1689
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" <da*********@softhome.net> wrote in message
news:14**************************@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?

Jul 20 '05 #2

"Dave" <da*********@softhome.net> wrote in message
news:14**************************@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
Jul 20 '05 #3
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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Developer | last post: by
1 post views Thread by Jeremy Pavleck | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.