Wednesday, March 7, 2012

Only one row returned from linked server while using INTO clause

Hello,
We have encountered the following behaviour / problem:
There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
(LINKED).
On the local server we run the following query:
SELECT * FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
ColumnA is varchar(3), ColumnB is varchar(4)
The query returns approx. 800 rows - that's correct.
However, if we run the same query using the INTO clause:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
the message is
(1 row(s) affected)
and there is only one row in #MyTemp table, what is obviously wrong.
What might be the problem?
Investigating the problem further we've found out that the problematic is
WHERE clause for ColumnB - if we rewrite it as follows:
SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
the query returns the correct number of rows.
But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
make much sense to me - but it works!
Would anybody be so kind to explain that behaviour?
Best regards,
AndrewJust a guess, but ensure the collation compatibility settings for the linked
server are correct and the settings for things like Ansi Null, etc match...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eO5X4eQkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have encountered the following behaviour / problem:
> There are two SQL 2000 Servers: Local Server (LOCAL) and Linked Server
> (LINKED).
> On the local server we run the following query:
> SELECT * FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> ColumnA is varchar(3), ColumnB is varchar(4)
> The query returns approx. 800 rows - that's correct.
> However, if we run the same query using the INTO clause:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB = 'YYYY'
> the message is
> (1 row(s) affected)
> and there is only one row in #MyTemp table, what is obviously wrong.
> What might be the problem?
>
> Investigating the problem further we've found out that the problematic is
> WHERE clause for ColumnB - if we rewrite it as follows:
> SELECT * INTO #MyTemp FROM LINKED.MyDatabase.dbo.MyTable
> WHERE ColumnA = 'XXX' AND ColumnB LIKE 'YYYY%'
> the query returns the correct number of rows.
> But the ColumnB is varchar (4), so the clause ColumnB LIKE 'YYYY%' doesn't
> make much sense to me - but it works!
> Would anybody be so kind to explain that behaviour?
> Best regards,
> Andrew
>
>

No comments:

Post a Comment