Table-valued parameter READONLY error on SQL Server 2008 R2 Express

Question

I have user-defined table type and stored procedute that uses this type

create type dbo.ut_Type1(
        Col1 varchar(10),
        Col2 varchar(10),
        Col3 int) 
go
create procedure dbo.p_Procedure1(
        @TVP ut_Type1 READONLY,
        @Year int,
        @ID int)
as
begin

This is calling from client application

var td:TMSTableData;

......

TMSQuery1.SQL.Text:='exec dbo.p_Procedure1 @TVP = :Par1, @Year = :Par2, @ID = :Par3';
TMSQuery1.ParamByName('Par1').AsTable:=td.Table;
TMSQuery1.ParamByName('Par2').AsInteger:=2019;
TMSQuery1.ParamByName('Par3').AsInteger:=4605;

Everything is working fine except for one specific SQL Server. Here is execution from Profiler for this server. (Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Express Edition with Advanced Services (64-bit))

declare @p3 dbo.ut_Type1
insert into @p3 values('001','112',142)

exec sp_executesql N'exec dbo.p_Procedure1 @Table = @P1, @Year = @P2, @ID = @P3', N'@P1 dbo.ut_Type1,@P2 int,@P3 int',@p3,2019,4605

And getting error

"The table-valued parameter @P1 must be declared with the READONLY option"

I've tried the same call on similar SQL Server(Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) Express Edition with Advanced Services (64-bit)). Here is execution from Profiler

declare @p3 dbo.ut_Type1
insert into @p3 values('001','112',142)

exec sp_executesql N'exec dbo.p_Procedure1 @Table = @P1, @Year = @P2, @ID = @P3', N'@P1 dbo.ut_Type1 READONLY,@P2 int,@P3 int',@p3,2019,4605

And didn't get error.

The only differenece is keyword READONLY that is missing on the first server table-valued parameter.

Is there any configuration to SQL Server that I have to set?
Thanks!

Hi xyz. You need to end up with the READONLY specification on the parameter type specification in the call to sp_executesql. — TT., May 08 '19 at 13:55
@SeanLange Both stored procedures and client app are the same — xyz, May 08 '19 at 14:09

score 0 · Answer 1 · answered May 08 '19 at 13:33

According to Microsoft's documentation Table Parameters have the following restrictions:

SQL Server does not maintain statistics on columns of table-valued parameters.
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

Table-valued parameter READONLY error on SQL Server 2008 R2 Express

1 Answers1