Technical – Microsoft – SQLServer – Linked Server – Configuration – Error
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'.
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'
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
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.
- How to create an autonomous transaction in SQL Server 2008
- sys.servers (Transact SQL)