Technical – Microsoft – SQLServer – Linked Server – Configuration – Error

Technical – Microsoft – SQLServer – Linked Server – Configuration – Error

Background

In the middle of development, we sometimes introduce subtle bugs. The error message in this case is simple, and it reads:

Msg 15600, Level 15, State 1, Procedure sp_serveroption, Line 120
An invalid parameter or option was specified for procedure 'sp_serveroption'.

Problem Identification

Traced it back to a code-line that reads as follow:


declare @lsServer sysname
set @lsServer = 'HRDB'
EXEC master.dbo.sp_serveroption
          @server=@lsServer
        , @optname=N'remote proc transaction promotion'
        , @optvalue=N'false'

Problem Resolution

The code should usually work, but as we have various versions of MS SQL Server, we need to be a bit more defensive in our coding.

The Linked Server option that we are trying to use “remote proc transaction promotion" was introduced in MS SQL Server v2005.

As the server that we were targeting during the failure is version 2000, the system is throwing warts at us.

To safely invoke, please place condition checks around it.  Sample checks includes checking for system columns …


if exists
	(
	   --sys.sysservers
	   select * --, is_remote_proc_transaction_promotion_enabled
	   from   master.dbo.syscolumns tblColumn
	   where  (
		    (tblColumn.name = 'is_remote_proc_transaction_promotion_enabled')
		   )
		)
	begin

		EXEC master.dbo.sp_serveroption 
                         @server=@lsServer
                       , @optname=N'remote proc transaction promotion'
                       , @optvalue=N'false'

	end

Dedication

This post is dedicated to Scott Guthrie.

His blog (http://weblogs.asp.net/scottgu/default.aspx) states:

I live in Seattle and build a few products for Microsoft“.

The humility of the man!

Though, I could easily say that I grew up Carolina Blue, “Duke Blue Devils” is tops in B-Ball.

Scott Gu and Mark Anders developed Microsoft ASP.Net.  They picked up “Anders Hejlsberg” (http://en.wikipedia.org/wiki/Anders_Hejlsberg) foundational work on Microsoft C#.

Earlier today, Re/Code reported that Scott Gu will be the Acting Head of Microsoft’s Server and Enterprise Group.

It feels good to see good Tech Minds who share effortlessly get rewarded.

References

Leave a Reply

Please log in using one of these methods to post your comment:

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