SQL Server 2008 – Inserting from a Stored Procedure via Linked Servers

I run three SQL Servers on a single Windows 2008 domain.

One is SQL 2005 but the other two are SQL 2008.

This isn’t the most I’ve ever dealt with by any means (three is a drop in the ocean compared to the setup we had when I worked in direct marketing) but the level of cross communication I am dealing with far surpasses anything I have ever dealt with before.

For the last year the servers have been happily querying each other’s data through their “Linked Server” entities with the only slight issue being SQL’s inability to query table value functions remotely; an issue that is easily bypassed with proper planning and well designed scripts.

Today I have set myself the task of creating a dependency report for some tables that we are considering retiring over the next 6 months.  The intention is to identify the entities that depend on these tables so that I can plan how we are going to hook the entities into the tables that will replace the redundant ones.

I’ll write more on the dependency report later but this morning I encountered an issue.

The dependency report relies on the results of a stored procedure being inserted into a table on one server; the stored procedure may need to be run on a linked server.

This shouldn’t be an issue; after all, stored procedures execute across linked servers without a problem normally.  What I have discovered today is that there seems to be an issue with transaction processing across linked servers and that this can cause an error when attempting to insert data, from one server, into a table on another server.

When I attempt insertion in this manner:

SQL ScriptI received the following error message:

OLE DB provider “SQLNCLI10” for linked server “DREADNOUGHT” returned message “The partner transaction manager has disabled its support for remote/network transactions.”.

Msg 7391, Level 16, State 2, Line 1

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

Luckily, I found a number of people with similar issues.

It seems that, by default, SQL Server promotes transaction handling to the remote server and that this can cause the error 7391.  The quick and dirty work around is to disable the promotion of remote transaction handling.

SQL ScriptAs simple a script as this will disable the relevant server option.

EXECUTE sp_serveroption    @server = ‘RemoteServerName’, @optname = ‘remote proc transaction promotion’, @optvalue = ‘false’ ;

Of course, I’ve enabled it again on my server… it must be on by default for a reason.

With that little nugget, my dependency report is on track to being completed and I’m a little better prepared for future linked server high jinx.

Advertisements

5 thoughts on “SQL Server 2008 – Inserting from a Stored Procedure via Linked Servers

  1. thank you, i think you don’t need to activate it again, it’s just a setting in the server you are connected to, it must not have secondary consecuences

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s