Failed select query over linked server

help please@discussions.microsoft.com need help please

unread,

Jun 2, 2010, 1:31:12 PM6/2/10

to

We have 2 sql 2008 servers (10.0.2531) linked with current user contexts.
Windows authentication used.

Connections working, and in general we can query DBs from another server
without problem. But we have some that gives us a hard time:

DECLARE @p0 Int; SET @p0 = 1

SELECT [t0].[ID]
FROM [Server].[database].[dbo].[table1] AS [t0]
INNER JOIN [Server].[database].[dbo].[table2] AS [t1] ON [t0].[field1] =
[t1].[field1]
INNER JOIN [Server].[database].[dbo].table3] AS [t14] ON [t0].[field2] =
[t14].[field2]
INNER JOIN [Server].[database].[dbo].table4] AS [t15] ON [t14].[field3] =
[t15].[field3]
INNER JOIN [Server].[database].[dbo].[table5] AS [t17] ON [t0].[field4] =
[t17].[field4]
WHERE
[t0].keyfield = @p0

I have replaced server, db, table and field names. But it's aliased in
original query as well.

Running this query on our db gives us error:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1001.ID" could not be bound.

If I remove WHERE clause it's working. If I replace parameter in WEHERE with
hardcoded number it's working.

I have rebuild indexes and recollected statistics on those tables, it's
sometimes helps and sometimes not. I have cleared our execution plan cashes
and it does not helping.

Need help ASAP.

Erland Sommarskog

unread,

Jun 2, 2010, 6:03:44 PM6/2/10

to

This sounds like a bug to me. Any possibility you can post a complete
script that demonstrates the problem? Such a script would include
CREATE TABLE statements for all tables. Don't forget to include indexes
and constraints.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

ilya

unread,

Jun 2, 2010, 6:27:31 PM6/2/10

to

> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Not sure if it will help but I'll try to email it to you. Problem is
it's not always happens. It's data depended. Also, adding option
(recompile) seems to help even I always thought it's the same as
cleaning execution plans cache.

Erland Sommarskog

unread,

Jun 3, 2010, 3:12:20 AM6/3/10

to

ilya (iand...@gmail.com) writes:
> Not sure if it will help but I'll try to email it to you. Problem is
> it's not always happens. It's data depended. Also, adding option
> (recompile) seems to help even I always thought it's the same as
> cleaning execution plans cache.

Please post it to the newsgroup. Or file a bug on
http://connect.microsoft.com/SqlServer/Feedback/

But it is pointless to file a bug without a repro. I can appreciate that
it can be difficult to produce a repro when it only happens sometimes.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx