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