First published on MSDN on Apr 18, 2013
Somebody approached me with this error, asking whether this is by design or caused by certain conditions.
Msg 25023, Level 16, State 1, Server <removed name>, Procedure sp_MSaddexecarticle, Line 192
Stored procedures containing table-value parameters cannot be published as '[serializable] proc exec' articles.
Step 1: The first thing I wanted to do was to attempt to reproduce the issue and see if this happens “out of the box”. The idea is to create a TVP scenario and then use transactional replication to replicate stored procedure execution.
DROP TABLE tbl_tvptest
CREATE TABLE tbl_tvptest (c1 int primary key nonclustered, c2 varchar(2000))
INSERT INTO tbl_tvptest VALUE (1, 'abc')
INSERT INTO tbl_tvptest VALUES (2, 'def')
GO
CREATE TYPE TVP AS TABLE
( c1 int
, c2 varchar (2000));
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo.usp_TestTVP
@TVP TVP READONLY
AS
SET NOCOUNT ON
INSERT INTO dbo.TVPTest
(c1, c2)
SELECT *
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @TVPvar AS TVP;
/* Add data to the table variable. */
INSERT INTO @TVPvar
SELECT 3, 'xzy'
/* Pass the table variable data to a stored procedure to test it. */
EXEC usp_TestTVP @TVPvar
GO
Now, create the publication and articles
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'DB1', @optname = N'publish', @value = N'true'
GO
exec [DB1].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
-- Adding the transactional publication
use [DB1]
exec sp_addpublication @publication = N'TVPTest', @description = N'Transactional publication of database ''DB1'' from Publisher ''myServer\SQL2008R2''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'TVPTest', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'TVPTest', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'TVPTest', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'TVPTest', @login = N'AD\Joseph'
GO
exec sp_grant_publication_access @publication = N'TVPTest', @login = N'NT SERVICE\MSSQL$SQL2008R2'
GO
exec sp_grant_publication_access @publication = N'TVPTest', @login = N'NT SERVICE\SQLAgent$SQL2008R2'
GO
exec sp_grant_publication_access @publication = N'TVPTest', @login = N'distributor_admin'
GO
-- Adding the transactional articles
use [DB1]
exec sp_addarticle @publication = N'TVPTest', @article = N'TVPTest', @source_owner = N'dbo', @source_object = N'TVPTest', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'TVPTest', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboTVPTest]', @del_cmd = N'CALL [sp_MSdel_dboTVPTest]', @upd_cmd = N'SCALL [sp_MSupd_dboTVPTest]'
GO
use [DB1]
GO
exec sp_addarticle
@publication = N'TVPTest',
@article = N'usp_TestTVP',
@source_owner = N'dbo',
@source_object = N'usp_TestTVP',
@type = N'serializable proc exec', –– tried 'proc exec' as well
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x0000000008000001,
@destination_table = N'usp_TestTVP',
@destination_owner = N'dbo',
@status = 16
GO
Sure enough, I received the error. So it appears that this is reproducible at will with the simplest of situations.
Step 2: Next, I took a look at the source code of sp_MSaddexecarticle. I found the following, which pretty much confirmed my findings.
IF EXISTS (SELECT *
FROM sys.parameters
WHERE object_id = @procid
AND system_type_id = 243)
BEGIN
-- Stored procedures containing table-value parameters cannot be published as '[serializable] proc exec' articles.
RAISERROR (25023, 16, -1)
RETURN (1)
END
Essentially the code is checking if one of the stored procedure parameters is a TVP (type = 243) and if so, raises the error.
Conclusion: TVPs are not supported as parameters for stored procedures the execution of which you want to replicate.
Namaste!
Joseph
