I was able to reproduce this on two machines running post SP1 builds of Developer 2008 using SSMS 2005 and 2008. It happens sporadically on one server and everytime on the other.
1. Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (Intel X86) Nov 9 2009 16:59:31 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2. Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (Intel X86) Sep 11 2009 15:12:52 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1
(Build 2600: Service Pack 3)
Remote Servers (servers linked connection is made to):
1. Microsoft SQL Server 2005 - 9.00.4220.00 (Intel X86) Apr 2 2009 18:42:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2. Microsoft SQL Server 2005 - 9.00.4226.00 (X64) May 26 2009 14:58:11 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Steps to recreate behavior:
1. create linked server using SQLNCLI provider on a 2008 server pointing to any 2005 server
EXEC master.dbo.sp_addlinkedserver @server = N'USESQLNCLI', @srvproduct=N'SQL', @provider=N'SQLNCLI', @datasrc=N'NAMEOF2005INSTANCE'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'USESQLNCLI',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
2. from SSMS, expand the "Catalogs" folder under "Linked Servers" in the object explorer.
Step 2 above eventually times out with "Failed to retrieve data for this request", SQL stack dumps and the MSSQL service restarts. I was able to reproduce this everytime on one server and sporatically on the other.
The linked server connection itself works fine when you use it in a query but fails when you attempt to view the "Catalogs" through SSMS as above. The stack dump occurs when it attempts to populate the catalog information in #tmp_sp_catalogs.
* Exception Address = 00000006 Module(UNKNOWN+00000000)Event Viewer entries from each 2008 server:
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000006
* Input Buffer 510 bytes -
* create table #tmp_sp_catalogs (is_catalog_s
* upport bit null,server_name nvarchar(128) null, product_name nvarchar(12
* 8) null,provider_name nvarchar(128) null,catalog_name nvarchar(128) null
Attempt to fetch logical page (1:200) in database 2 failed. It belongs to allocation unit 25896092997713920 not to 196608.If the service doesn't happen to restart you can run a checkdb on tempdb and generate the error as well.
Attempt to fetch logical page (1:161) in database 2 failed. It belongs to allocation unit 25896092997713920 not to 281474980642816
CHECKDB found 0 allocation errors and 0 consistency errors in database 'tempdb'.
Msg 605, Level 21, State 3, Line 2
Attempt to fetch logical page (1:161) in database 2 failed. It belongs to allocation unit 25896092997713920 not to 281474980642816.
If I create the same linked server connection instead using SQLNCLI10 from 2008 to 2005, I don't encounter this same behavior, and expanding "Catalogs" on the linked server in SSMS works as it should.
Maybe this is already a known issue using SQLNCLI from 2008, especially since it's at SP1 CU5, but I wasn't able to find anything online about this behavior. We're going to make sure to use the SQLNCLI10 provider going forward but curious if anyone else has noticed this same issue.
Update 12/24/09: Submitted this as bug id 522048.
Update 1/27/10: Another user was able to reproduce this connecting to SQL 2000 from SQL 2008. Our workaround of using the SQLNCLI10 provider has worked out well.