CODEWITHSUNDEEP
sql-serverazure-sql-database
user2981411
user2981411

Reputation: 962

SQL Azure Remote Procedure

We are trying to execute a remote procedure on an Azure database :

INSERT INTO #temp (
    ConsultantId
    ,UserId
    ,Name
    ,Email
    ,Phone
    ,DefaultContact
    )
EXEC @RemoteResult = sp_execute_remote @RemoteSource
    ,@SQLString
    ,@ParmDefinition
    ,@userid = @userid;

Where #temp is a table with the same 6 columns as shown in the insert above.

However, we always get the $ShardName column returned even if we specify only the columns we need and as a result we also get the following error:

Column name or number of supplied values does not match table definition.

Any ideas would be appreciated.

Upvotes: 1

Views: 571

Answers (2)

LCJ
LCJ

Reputation: 22661

Adding relevant documentation

sp_execute_remote (Azure SQL Database)

sp_execute_remote adds an additional column to the result set named '$ShardName' that contains the name of the remote database that produced the row.

Upvotes: 0

user2981411
user2981411

Reputation: 962

If I understand correctly, the solution to this problem is to simply add another column to the table to accept the [$Shard] column. It need to be in the right position and needs to have the correct data type. It doens't need the same name.

For clarity, it's worth creating it with the same name though.

Assuming $Shard was the final column from the proc, this should work:

INSERT INTO #temp (
    ConsultantId
    ,UserId
    ,Name
    ,Email
    ,Phone
    ,DefaultContact
    ,[$Shard]
    )
EXEC @RemoteResult = sp_execute_remote @RemoteSource
    ,@SQLString
    ,@ParmDefinition
    ,@userid = @userid;

Upvotes: 2

Related Questions