2

I'm issuing a fairly simple update of single varchar column against a remote linked server. E.g.:

UPDATE Hydrogen.CRM.dbo.Customers
SET EyeColor = 'Blue'
WHERE CustomerID = 619

And that works fine when is written as an ad-hoc query:

enter image description here

Parameterized Queries Bad

When we do what we're supposed to do, and have our SqlCommand issue it as a parameterized-query, the SQL ends up being:

exec sp_executesql N'UPDATE [Hydrogen].[CRM].[dbo].[Customers] 
SET [EyeColor] = @P1 
WHERE [CustomerID] = @P5', 
N'@P1 varchar(4),@P5 bigint',
'Blue',619

The query ends up performing a Remote Scan against the linked server:

enter image description here

It creates a cursor on the linked server, and takes about 35 seconds to pull back 1.2M rows to the local server through a series of hundreds of sp_cursorfetch

Why, in the world, would the local SQL Server optimizer ever decide to pull back all 1.2M rows to the local server in order to update anything?

It only fails on varchar columns

If i update int fields, it works fine.

But this column is varchar - and it fails.

I tried other parameterizing the column as nvarchar, and it's still bad.

Every answer i've seen actually are questions:

  • "is the collation the same?"
  • "What if you change the column type?"
  • "Have you tried OPENQUERY?"
  • "Does the login have sysadmin role on the linked server?"

I already have my workaround: parameterized queries bad - use ad-hoc queries.

I was hoping for an explanation of the thing that makes no sense. And hopefully if we have an explanation we can fix it - rather than workaround it.

Bonus Reading

"Too localized, unlikely to help others." Gives 7 links of other people having the issue. I hate people.

3
  • It doesn't reproduce for me in other environments. So we're left trying to imagine why a database server would ever issue a "Remote Scan"
    – Ian Boyd
    Sep 16, 2021 at 18:15
  • Unfortunately I can't even reproduce it myself in a lab.
    – Ian Boyd
    Sep 16, 2021 at 19:34
  • HYDROGEN is a server name,, which is running an instance of SQL Server.
    – Ian Boyd
    Sep 17, 2021 at 19:42

1 Answer 1

2

I talk a little about the remote join hint here, people can speculate but if they can't reproduce it's pretty tough to give you an answer.

In the meantime, you can use sp_executesql in a slightly different way to take away SQL Server's choices between bringing any data between the servers:

EXEC [Hydrogen].[CRM].sys.sp_executesql 
  N'UPDATE [dbo].[Customers]  SET [EyeColor] = @P1  
    WHERE [CustomerID] = @P5',  
  N'@P1 varchar(4),@P5 bigint', 
  'Blue',
  619;
1
  • The query isn't generated by me directly; it's the SqlClient's parameterized query that generates the pseudo-call to sp_executesql. My workaround of abandoning parameters also works as a workaround.
    – Ian Boyd
    Sep 16, 2021 at 19:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.