.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Linked Servers Hangs

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

Hello all,

I've been having a very tough time trying to get the simplest of linked server to work. I've managed to create similar linked servers on my home PC, but the Management Studios on my office PC is hanging whenever I try to do anything with a linked server.

I have both SQL Server 2005 and 2008 express editions installed on my work PC. I also have a number of ODBC DSNs to a variety of data sources (ACCESS DBs, another MS SQL server (2005), an Oracle server, EXCEL files, .gdb databases, etc.). These DSNs all work properly to create connections, all have been tested outside of the Management Studio without any problem. 

I can create the Linked Servers in both 2005 and 2008 Servers, but any time I try to interact with them, the Management Studio hangs.

For example, if I:

     - Test Connection in 2008

     - Expand the Linked Server catalog in 2008

     - Run any form of query against the Linked server (This includes both direct queries and OpenQuery() queries)

     - Alter any of the Linked Server Properties.

Pretty much the only thing that I can do is create the linked server. Any other action on the linked server causes the Management Studio the hang, regardless of any timeout that I specify.

This has m

View Complete Post

More Related Resource Links

Data Points: Updating Data in Linked Servers, Information Schema Views, and More


Every day a developer somewhere needs to write code to iterate through SQL ServerT system objects, query and update tables in linked servers, handle optimistic concurrency, and retrieve column and stored procedure metadata.

John Papa

MSDN Magazine November 2004

OpenQuery() to Linked Server hangs, leaving SPID with open tran, then templog.ldf grows without limi

Hi, We have a customer that is using SQL Server 2000, and experiencing the following intermittent problem -- occasionally templog.ldf will grow and grow until it fills the entire disk. This is a rare problem, it has happened less than 10 times in the past two years, for a job that runs once a minute. But it has happened on 3 different SQL Servers, (two production servers, and one test server). Our suspicion is that the root cause is an OPENQUERY() to pull data from a linked server (Oracle database on Unix). We have seen that these OPENQUERY() statements occasionally hang and cannot be killed. The OPENQUERY() is used to populate a local table, and when we originally populated permanent tables with OPENQUERY(), then there would be a lock on this permanent table and nothing could be done until SQL Server was stopped and restarted. To workaround the immediate problem, last year we modified all OPENQUERY() statements so that the local table was a #temp table. In that case, cancelling the job left the hung process, but the hung process had a lock on a #temp table and so that didn't keep the next execution of the job from creating a new #temp table and populating it. However... it appears that the hung process, because it leaves an SPID with an open transaction, keeps the tempdb log file from being truncated. And eventually you run out of disk space, no matter how much disk space is

Using Impersonation or Certificates Across Linked Servers

We are trying to isolate the custom reporting stored procedures from our third-party SQL Server database ("SourceData" database) by placing all of those procedures in a separate database ("MyReporting" database) on a separate server. In addition, and most critically, I am trying to prevent all ad-hoc reporting (MS Access, Excel, developers) against the third-party database. To accomplish this, I grant permission to a specific user to execute a stored proc in the reporting database and then the stored proc accesses the data in the third-party database either by impersonation using EXECUTE AS or by certificates (following the model described in this MSDN article: http://msdn.microsoft.com/en-us/library/ms188304(SQL.90).aspx). However, as soon as I try to implement this model across multiple servers using Linked Servers, I am unsuccessful (with one unsatisfactory exception). As far as I can tell, I've tried every combination of option in the Linked Server setup. The unsatisfactory option it to map the reporting user to the "SourceDataID" user in the SourceData database. That works, however, that simply opens up the ability to perform ad-hoc reporting if a user connects to the reporting database since the Linked Server exposes all of the tables that the SourceDataID has access to.Below are diagrams describing how these models are setup:In this Imperso

MERGE command w/ linked servers & udfs

Hi guys - I have a MERGE statement I'm trying to fight with and losing. I'm  calling data from 2 linked servers and one of them has a user defined functions on it.. I have database A on one server (2008 R2) - the one with the UDFs and the source of the MERGE command. And database B on the other server (2008 sp1) - the target. The source of the MERGE does contain a call to a table function in server A. When both databases are on the same server, it runs fine and I'm not entirely sure how to fix it so I can get it to run between linked servers. I've tried both using OPENQUERY and just a normal statement...on the "normal" statement (Query #1) I get "Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database" when I run it "normally" . Which the compatibility of both 2008 and 2008R2 are set to 100. When i try with OPENQUERY (#2) I get "Incorrect syntax near the keyword 'AS" . It is a dynamic query because I need to pass in my database names. @A and @B are the linked server names. Query #1 (the "normal" one just using MERGE) DECLARE @A VARCHAR(50) DECLARE @B VARCHAR(50) DECLARE @LastUpdated DATETIME DECLARE @query VARCHAR(8000) SET @A = ? SET @B = ? SET @LastUpdated = ? SET @query = ' MERGE '+@B+'.database.dbo.tblFieldVersion as target USING ( SELECT c.x, st

Problem with Linked servers from sql 2005 to Excel file


I am probably doing something stupid BUT I am creating a linked server to an excel file, I have done this on sql2005sp2x64 using the ace ole 12 provder, and replicated on another machine running sql2005sp2x32 using the jet4 provider and I am having the same problem on both.  I have turned allow inprocess option on on both providers.  I as an administrator on the machine where the database and datafile are residing have no problem accessing the linked server and the file underneath however any enduser that is not in the local administrators group gets the error   Cannot initialize the data source object of OLE DB provier "Either provider I have tried" for linked server    SQL Server Error 7303.  I have checked file permissions, and they are open to the world, I have tried both use existing authentication and use no authentication when accessing the linked server.  and I have tried endless different combinations of providers and provider strings.  I have went as far as having the enduser create a linked server to an excel file on his local machine (which he is admin on and I am not)   and he can access the file .. and I can not.  so I am just assuming there is some sort of security setting somewhere .. that I am just missing.



Get Value from table in a Case statement with Linked Servers


I have 2 SQL Server instances that I need to query from.  I have figured out how link the 2 servers, I just don't know how to get the value from the other instance/table and put it in the case statement. 

Here are the tables:

Instance: Default     Database:GAD

Table Sample1

ID          Integer
V301054     Float
V301060     Float

Instance: MSSQL     Database:SCA

Table DESI
ID          Integer
Unit_Code    smallint
Unit_Name    Varchar(50)
Amount      Float

Here is the Select Statement with the when clause in it.

Select top (1)V301054A as MW, Availability = case when V301054A <= '.1' then 'Deployed' else '0' end FROM [Default].[SCA].[dbo].[SAMPLES1] AS SAMP1
Group by V301054A
order by MW desc

What I need to do is get the value from this Select Statement and put it where the 'Deployed' is in the above When Clause.

Cannot use Serializable Transaction Level with Linked Servers


I am testing an upgrade from SQL Server 2005 to SQL Server 2008 R2.  I have a stored procedure which attempts to use the serializable transaction isolation level on a distributed transaction (linked server). 

The query fails on SQL Server 2008 R2 with 


OLE DB provider "SQLNCLI10" for linked server "SERVER1" returned message "No transaction is active.".

Msg 7391, Level 16, State 2

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVER1" was unable to begin a distributed transaction.


If I change the isolation level to read committed, the transaction succeeds.  This same procedure worked on SQL Server 2005 but now fails on SQL Server 2008 R2 using the same isolation level. 

Is there a way to get this isolation level to work with linked servers on SQL Server 2008 R2?

Data transfer between two linked SQL servers



I need to build a service that copies new rows from SqlServerA.DB1.TABLE_X to SqlServerB.DB2.TABLE_X. Direction is from SqlServerA to SqlServerB

Source and destination tables are the same structure. SqlServerA and SqlServerB are located in two different cities and there is VPN connectivity between them. The VPN is based on Internet and reasonably fast.

The service need to be able to recover when VPN is down and try again later when VPN is running again.

I wonder if linked servers are suitable for this scenario? I can avoid joining distributed tables together and leverage linked servers by using INSERT statements.

I am aware of MERGE replication and I wonder if MERGE replication is a simpler alternative?

SQL Standard: Can we compress the data passed between linked servers?



I have to connect two sql server 2008 standard edition as linked servers. Is there any possible way to compress the data passed between two linked servers?

Thank you.

Linked Servers Between Enterprise and Web Edition



I'm having a strange time trying to get an instance of Enterprise Edition SQL 2008 R2 to link with a Web Edition. We've been able to link to other instances without issues, and whenever we go to link with the Web Edition, we get the error:


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

The two VMs are on the same domain, with the same permissions, and this error does not appear when we link two Enterprise servers. I've googled around and I couldn't see anything that says we can't link the two versions up, and the error implies some kind of permissions thing, but both servers share the same Active Directory, and have the same logins.


I'm stumped. Any ideas on how to start solving this?

Many thanks



Edit: to clarify, the users are in AD Groups, "user_Admin". The group is added as a login and mapped to the various DBs on both servers. We can only use SSPI authentication - no SQL Server Authentication.

Using Linked Servers within SqlCommand

Anyone have a clue why I can run "SELECT TOP 10 * FROM [myServer].[myDatabase].[dbo].[myTable]" from my SQL server but I can't run the following from a page hos

Are linked servers an issue on SQL Server 2008 R2 64 bit platform?

Recently a Microsoft employee strongly recommended not using linked servers at all.   I have a different thread on this topic which died.  However, I'd like to get feedback regarding linked servers on SQL Server 2008 R2 64 bit platforms.
michelle jenks

how to make a css style background image be linked to an image in the resource file


hey guy, i need help in a very troubling matter. I want to make a textbox with a specific style, within this style a set of images as background parts, how can i attach these images to the custom control as whenever i take the .dll file and add it to the toolbar the images do not appear. So i tried to make them as resource files and their property as embedded in .resx file, so how to make the css style background image url to be linked to any of those image.

thanks in advance

No More Hangs: Advanced Techniques To Avoid And Detect Deadlocks In .NET Apps


You can combat deadlock using a combination of disciplined locking practices which Joe Duffy aptly explains in this article.

Joe Duffy

MSDN Magazine April 2006

Taming the Stateless Beast: Managing Session State Across Servers on a Web Farm


Running a Web farm means managing session state across servers. Since session state can't be shared across a Web farm with Internet Information Services 5.0, a custom solution is required. One such solution using a tool called the session manager is described here. The article begins with a description of the SQL Server database used to store state information, the stored procedures used to update it, and the retrieval of session data. ASP code drives the session manager tool and the COM and COM+ components that run the stored procedures.

John Papa

MSDN Magazine October 2000

Windows 2000: Asynchronous Method Calls Eliminate the Wait for COM Clients and Servers


Windows 2000 is the first version of COM to support asynchronous method calls, which permit clients to make nonblocking calls to COM objects and objects to process incoming calls without blocking the calling threads. COM clients benefit from asynchronous method calls because they can continue working while waiting for outbound calls to return. Objects benefit because they can queue incoming calls and service them from a thread pool. Our SieveClient and SieveServer sample apps demonstrate how to create and use asynchronous clients and servers in COM-based distributed applications.

Jeff Prosise

MSDN Magazine April 2000

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend