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')
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
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.