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

Top 5 Contributors of the Month
Post New Web Links

Cannot use Serializable Transaction Level with Linked Servers

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

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?

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

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

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

SQL Server + .Net | Transaction | Can Transaction level be esclated

Hi We are facing a unique problem which ends up causing deadlocks. We have found that We have a very heavy load OLTP Sql server 2008. In .net code we set the Transaction isolation level to Read commited while executing a SP. We find deadlocks and in the dead lock details it is explictly called out that the SP in question was executing in transaction level serializable. Lets call this SP1 The only other thing we can say is that there are some other unrelated stored procedures which access the same tables as SP1 and are executing in isolation level serializable. Is it possible for one SP to escalate isolation level like locks are escalated? We found the following links which hint this, by saying that isolation level is just a hint. http://msdn.microsoft.com/en-us/library/ms229978(v=VS.90).aspx http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx?pr=blog http://msdn.microsoft.com/en-us/library/ms189857.aspx http://aartemiou.blogspot.com/2009/01/table-level-locking-hints-in-sql-server.html Any help in this regard will be great.

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.



Distributed transaction using linked server not working in SQL Server 2008 64 bit


Hi. I have had an issue trying to get distributed transactions to work in SQL Server 2008 using a linked server. The error message I get is

OLE DB provider "SQLNCLI10" for linked server "pod1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "pod1" was unable to begin a distributed transaction.

My Environment:

Windows Server 2008 Enterprise 64 bit, SQL Server 2008 Enterprise 64 bit

Problem occurs with multiple different builds of SQL Server 2008 - I have been able to reproduce the problem with 10.0.1600.0, 10.0.1779.0 (CU 2), as well as 10.0.2531 (SP1)


I am aware that other people have had this issue and have reviewed all existing posts. I have verified that the MSDTC is configured correctly on both machines. I have also used DTCTester to verify that DTC is working correctly on all machines in question. None of the mentioned resolutions has solved this problem for me.

I am not seeing this problem occur on my 32 bit test machines - it is only occurring if at least one of the two machines is 64 bit.


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.

Linked Servers Hangs


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

Run Distrubited Transaction from Linked Server


Hi All,

I guess you all know that famous error when trying to run DT using linked server ,

Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I followed all instruction listed mentioned at MS support and nothing change still have the same error



my enviroment is run distrubited transaction using linked server from sql server 2000 Ent Sp4 on win 2003 SP2 to SQL Server 2008 Sp1 x64 For IA on win server 2008(cluster enviroment active\active) IA .


appricaite you suuport, thanks in advance

Transaction across local and Linked Server


I have some requirement where my procedure will do several operations (Add, Update etc) in my Local SQL Server + Add some records on a remote server (Linked Server).

I have one transaction around my local server tasks. But writing to Linked Server is out of this transaction block. And there is chance that writing to Linked server may fail.

So what are the best way I can make both task (Local task + writing to LinkedServer) with in a single transaction? Is that suggested?


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

A Low Level Look at ASP.NET Architecture

ASP.NET is a powerful platform for building Web applications, that provides a tremendous amount of flexibility and power for building just about any kind of Web application. Most people are familiar only with the high level frameworks like WebForms and WebServices which sit at the very top level of the ASP.NET hierarchy. In this article I'll describe the lower level aspects of ASP.NET and explain how requests move from Web Server to the ASP.NET runtime and then through the ASP.NET HTTP pipeline to process requests.
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