Re: Possible Bug in SQL Server 2000?
From: Ross Presser (rpresser_at_imtek.com)
Date: 07/08/04
- Next message: BB: "Re: Question about sql job, SQL 7 versus upgrade to SQL 2000"
- Previous message: Andrew J. Kelly: "Re: SQL Server 2000 Hardware Recommendations?"
- In reply to: Dave: "Possible Bug in SQL Server 2000?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 8 Jul 2004 13:29:57 -0400
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
- Next message: BB: "Re: Question about sql job, SQL 7 versus upgrade to SQL 2000"
- Previous message: Andrew J. Kelly: "Re: SQL Server 2000 Hardware Recommendations?"
- In reply to: Dave: "Possible Bug in SQL Server 2000?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
