May 11, 2009 at 6:15 am
I discovered this "quirk" of using a linked server recently, and cannot find any reference to it in BoL or Google, so for those that may be interested, here is a summary. I am using SQL Server 2000, but I suspect that it applies equally to 2005 and 2008 if using a linked server with the SQL Server native client drivers.
In the linked server, we have a table keeping track of a counter value, which is defined as decimal
CREATE TABLE [dbo].[CentralCounter](
[COUNTER_VALUE] [decimal](14, 0) NOT NULL
) ON [PRIMARY]
GO
In the local server, we have a stored procedure to increment the counter.
This used to contain:
declare @new_counter decimal(14,0)
select @new_counter = max(COUNTER_VALUE)+1 from linkedServer.ServerName.dbo.CentralCounter
update linkedServer.ServerName.dbo.CentralCounter cc
set COUNTER_VALUE = @new_counter
which worked, but caused problems where multiple clients updated the CentralCounter simultaneously.
To reduce concurrency errors, we changed the stored procedure such that it now contains:
declare @new_counter decimal(14,0)
update cc
set @new_counter = COUNTER_VALUE = (select max(COUNTER_VALUE)+1 from linkedServer.ServerName.dbo.CentralCounter)
from linkedServer.ServerName.dbo.CentralCounter cc
Although the column "COUNTER_VALUE" and the variable "@new_counter" are both declared as decimal, the updated SQL then errored with:
Msg 425, Level 16, State 1, Line 3
Data type decimal of receiving variable is not equal to the data type numeric of column 'COUNTER_VALUE'.
It seems as thought the Linked Server is actually treating COUNTER_VALUE as if it has been declared as numeric(14,0) and not decimal(14,0).
BoL says that numeric and decimal datatypes are "functionally equivalent", but in this case the UPDATE statement requires that the variable and the column are identical datatypes, whereas in the original two stage SQL, the values happily being implicitly converted between decimal and numeric without error.
The solution was to declare both CentralCounter.COUNTER_VALUE and @new_counter as numeric(14,0) instead of decimal(14,0), which then works as expected.
May 11, 2009 at 7:57 am
By the way, if you want to reduce concurrency issues with this to a linked server, you should probably use a passthrough-type query using "EXEC linkedServer.master.dbo.sp_ExecuteSQL ...". That should take the network latency out of the concurrency window.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply