CODEWITHSUNDEEP
sqlsql-server
Benoit
Benoit

Reputation: 23

Using a table as input parameter for sp_execute_remote (Azure SQL Database)

I am trying to use a table as input parameter for sp_execute_remote (Azure SQL Database).

I declared the type on the remote db:

if exists (select * from sys.types where name = 'MyType')
    drop type MyType;

create type MyType as table
(
mycol1 VARCHAR(5) NOT NULL,
mycol2 VARCHAR(5) NOT NULL
);

GRANT EXECUTE ON TYPE::dbo.MyType TO RemoteLogger;

I try to execute the script on my local db:

if exists (select * from sys.types where name = 'MyType')
    drop type MyType;

create type MyType as table
(
mycol1 VARCHAR(5) NOT NULL,
mycol2 VARCHAR(5) NOT NULL
);
go
declare @table MyType;

INSERT INTO @table (mycol1, mycol2) VALUES
('ABC', 'DEF'),
('GHI', 'JKL');

-- executing locally for a table is working fine
EXEC sp_executesql N'select * from @var', N'@var MyType readonly', @var = @table;

-- executing on remote db for a varchar type is working fine
EXEC sp_execute_remote  
    @data_source_name  = N'RemoteDatabase',  
    @stmt = N'SELECT @col1 AS ''mycol1'', @col2 AS ''mycol2''',
    @params = N'@col1 VARCHAR(5), @col2 VARCHAR(5)',
    @col1 = 'ABC', @col2 = 'DEF'; 

-- executing on remote db for a table type throws an error:
-- The underlying error message received was: 'Operand type clash: int is incompatible with MyType'.
EXEC sp_execute_remote  
    @data_source_name  = N'RemoteDatabase',  
    @stmt = N'SELECT * FROM @var',
    @params = N'@var MyType readonly',
    @var = @table;

I get an error on the 3rd EXEC statement: The underlying error message received was: 'Operand type clash: int is incompatible with MyType'.

Thanks.

Benoit

SOLVED by using JSON:

DECLARE @json NVARCHAR(max) = ( select * from @table
    FOR JSON AUTO, INCLUDE_NULL_VALUES );

EXEC sp_execute_remote
    N'RemoteDatabase',
    N'SELECT * FROM OpenJson(@var)
    WITH (
    mycol1 VARCHAR(5) ''$.mycol1'',
    mycol2 VARCHAR(5) ''$.mycol2''
    );',
    N'@var NVARCHAR(max)',
    @json;

Upvotes: 2

Views: 712

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

Even in "regular" SQL Server table types are not interoperable between databases. In technical terms table types belong to a particular database lack "duck typing" Eg this

use master 
drop database a
drop database b
go
create database a
create database b
go
use a
create type t as table(i int)
go
create proc pt @t t readonly
as
begin
  select * from @t
end
go
use b 
create type t as table(i int)

go
declare @t t
insert into @t(i) values (1)

exec a.dbo.pt @t

fails with

Msg 206, Level 16, State 2, Procedure a.dbo.pt, Line 0 [Batch Start Line 20]
Operand type clash: t is incompatible with t

So, use JSON.

Upvotes: 2

Related Questions