January 26, 2018 at 3:42 am
We're in the process of migrating out whole system from SQL 2008 R2 to 2014. The powers-that-be decided to bundle a few other changes in with this, one of which has already been rolled back out of our current live system. As far as I know the problem we're having didn't happen when replicating from 2008 R2 to 2008 R2. This may be relevant but I don't know.
The table being replicated is below, names have been changed to protect the guilty.
CREATE TABLE [dbo].[TargetTable](
[TargetTableID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Column1] [int] NULL,
[Column2] [varchar](50) NOT NULL,
[Column3] [varchar](50) NOT NULL,
[Column4] [datetime] NOT NULL,
[Column5] [datetime] NULL,
[Column6] [datetime] NULL,
[Column7] [varchar](10) NULL,
[Column8] [varchar](10) NULL,
[Column9] [varchar](6) NULL,
[Column10] [varchar](4) NULL,
[Column11] [varchar](255) NULL,
[TimeStamp] [datetime] NULL CONSTRAINT [DF_TargetTable_TimeStamp] DEFAULT (getdate()),
CONSTRAINT [PK_KitAllocation] PRIMARY KEY CLUSTERED
(
[TargetTableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
We're getting the error 'Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)' repeatedly.
After breaking open the Command Attempted, we found it was this:{CALL [sp_MSupd_TargetTableID] (NULL,NULL,NULL,NULL,NULL,2018-01-24 13:46:13.283,NULL,NULL,NULL,NULL,NULL,NULL,NULL,6055020,0x2000)}
This is where things get tricky. The proc looks like:ALTER procedure [sp_MSupd_TargetTableID]
@c1 int,
@c2 int,
@c3 varchar(50),
@c4 varchar(50),
@c5 datetime,
@c6 datetime,
@c7 datetime,
@c8 varchar(10),
@c9 varchar(10),
@c10 varchar(6),
@c11 varchar(4),
@c12 varchar(255),
@c13 datetime,
@pkc1 int,
@bitmap binary(2)
as
begin
declare @primarykey_text nvarchar(100) = ''
update [dbo].[TargetTable] set
[Column1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [Column1] end,
[Column2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Column2] end,
[Column3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [Column3] end,
[Column4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [Column4] end,
[Column5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [Column5] end,
[Column6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [Column6] end,
[Column7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [Column7] end,
[Column8] = case substring(@bitmap,2,1) & 1 when 1 then @c9 else [Column8] end,
[Column9] = case substring(@bitmap,2,1) & 2 when 2 then @c10 else [Column9] end,
[Column10] = case substring(@bitmap,2,1) & 4 when 4 then @c11 else [Column10] end,
[Column11] = case substring(@bitmap,2,1) & 8 when 8 then @c12 else [Column11] end,
[TimeStamp] = case substring(@bitmap,2,1) & 16 when 16 then @c13 else [TimeStamp] end
where [TargetTableID] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
Begin
if exists (Select * from sys.all_parameters where object_id = OBJECT_ID('sp_MSreplraiserror') and [name] = '@param3')
Begin
set @primarykey_text = @primarykey_text + '[TargetTableID] = ' + convert(nvarchar(100),@pkc1,1)
exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[TargetTable]', @param2=@primarykey_text, @param3=13233
End
Else
exec sp_MSreplraiserror @errorid=20598
End
end
It's running an UPDATE statement which is why we can't work out why we're getting an error about column names and definitions. All the research we've done suggests that the error is related to IDENTITY columns but only in the context of INSERTs. Does anybody have any suggestions?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 26, 2018 at 3:58 am
Neil
Long time no see!
This does seem strange. Does the error message state that the error occurred in that stored procedure? Have you tried running an Extended Events session to see what commands are being executed?
John
January 26, 2018 at 12:37 pm
When you assign a substring of a binary value to a varchar variable, you may not be getting what you expect. In this case, col2 and col3 are NOT NULL. What would happen if the substring of @bitmap returns null?
January 26, 2018 at 1:41 pm
John Mitchell-245523 - Friday, January 26, 2018 3:58 AMNeilLong time no see!
This does seem strange. Does the error message state that the error occurred in that stored procedure? Have you tried running an Extended Events session to see what commands are being executed?
John
Long time no see indeed. The training budget has been refreshed so with any luck I'll be crossing the hills for a few beers informative course before much longer.
Anyway, the error message spits out the transaction sequence ID which when looked up gives that command. When I picked it up from my colleague it was the command he'd tracked down and when I double checked it came back as the same one.
We've not put an extended events session on yet but we will do. This is on the UAT environment so it's not critical. This afternoon getting Live problems sorted needed doing more quickly. I'll let you know what pans out on Monday.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 26, 2018 at 1:49 pm
RandomStream - Friday, January 26, 2018 12:37 PMWhen you assign a substring of a binary value to a varchar variable, you may not be getting what you expect. In this case, col2 and col3 are NOT NULL. What would happen if the substring of @bitmap returns null?
We've thought that, I've had a partial look at what @bitmap returns and it's blank, definitely not NULL. I appreciate that a blank value will cause its own problems but this is an MS proc so you'd hope they've considered that. We have reason to believe that special characters lurking in a binary value can cause this but we've not been able to confirm that's what's happened here. I'm also not completely sure I've converted the binary to text properly and as I said above, Live problems intruded and meant this has been sidelined for now. I'll get back to it as soon as I can though.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply