July 31, 2017 at 6:34 am
Hello,
We have run into an issue with our transactional replication since the weekend.
Nothing as far as im aware has been changed, but when I came back into the office after the weekend, our tx replication was broken.
Its replicating from sql2014 to 2005 via a 2014 distributor.
It has been working fine for the past year or so, until it just stopped working, throwing up this error:
create procedure [sp_MSupd_dbotblReturnsOrderException]
@c1 varchar(20) = NULL,
@c2 varchar(128) = NULL,
@c3 smalldatetime = NULL,
@pkc1 varchar(20) = NULL,
@bitmap binary(1)
as
begin
declare @primarykey_text nvarchar(100) = ''
if (substring(@bitmap,1,1) & 1 = 1)
begin
update [dbo].[tblReturnsOrderException] set
[strBxOrderNo] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [strBxOrderNo] end,
[strUserName] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [strUs
(Transaction sequence number: 0x00124DE9009FB8A9002700000000, Command ID: 8)
Error messages:
Cannot assign a default value to a local variable. (Source: MSSQLServer, Error number: 139)
Get help: http://help/139
Cannot assign a default value to a local variable. (Source: MSSQLServer, Error number: 139)
Get help: http://help/139
Must declare the scalar variable "@primarykey_text". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Must declare the scalar variable "@primarykey_text". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Must declare the scalar variable "@primarykey_text". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Must declare the scalar variable "@primarykey_text". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
So obviously its trying to create its update procs and assign a value to a variable inline with the declaration, which is not compatible with sql2005.
My question is why has it suddenly started to do this? It was working fine. for over a year.
Any ideas? Suggestions?
July 31, 2017 at 8:21 am
2 hours, 34 views, no replies.
Must be a good one!
I have found a way to get it working though, feel free to read, but don't bash me for not strictly following MS best practise!
I know its not technically supported, but I was very curious as to why this suddenly stopped working.
====>
Essentially I had to edit the .sch files that were generated from the snapshot process in the repldata dir. Once these had been generated, I edited them and removed any inline default settings for variables and explicitly set @variable = ''
<=====
The replication process then picked up these files and processed them without error.
I am still in the dark as to why this worked months ago with no intervention, but not any more. We didn't update anything.
Seen this one before?
Cheers.
July 31, 2017 at 10:43 am
Rin Sitah - Monday, July 31, 2017 8:21 AM2 hours, 34 views, no replies.
Must be a good one!I have found a way to get it working though, feel free to read, but don't bash me for not strictly following MS best practise!
I know its not technically supported, but I was very curious as to why this suddenly stopped working.====>
Essentially I had to edit the .sch files that were generated from the snapshot process in the repldata dir. Once these had been generated, I edited them and removed any inline default settings for variables and explicitly set @variable = ''
<=====The replication process then picked up these files and processed them without error.
I am still in the dark as to why this worked months ago with no intervention, but not any more. We didn't update anything.
Seen this one before?Cheers.
The error often comes up due to incompatible versions in the topology. It may run find for awhile and then error with patches.
It's not specified but 2014 publisher and 2005 subscriber would be a likely guess and that is not supported. A subscriber to a publication needs to be within two versions of the publisher version
When things work for a year and then start having errors, it's highly, highly likely something changed. And going awhile in a non-supported configuration and not having errors is usually just luck. Eventually, you get burned.
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply