MSSQL does not quote table name in UPDATE sql for linked server

Question

Hello,

I have already described this problem with UPDATE statement 13 years ago.

It seems to me this problem has not been fixed yet :)

https://social.msdn.microsoft.com/Forums/en-US/4ed06833-d426-4856-aae8-26ba8d9cd238/linked-server-and-sensitive-to-register-name-of-table-problem-with-update?forum=sqldatabaseengine

In short - MSSQL does not quote table name in generated UPDATE statement for linked server.

See attached image.

Server generates statement UPDATE TableWithMixName set "Col" = 'cOl_333'

Column name was quoted. Table name - no.

The correct statement is UPDATE "TableWithMixName" set "Col" = 'cOl_333'

This problem has been existing in server since MSSQL 2000

This screenshot was created with MSSQL 2012

I know exactly that this problem exists in MSSQL 2016+.

Regards,

Dmitry Kovalenko.

Answer 1

For what it's worth, when I run (on SQL 2008):

UPDATE AGGE2.Northgale_utf8.dbo.Orders
SET    CustomerID = 'ALFKI'
WHERE  OrderID = 11100

And then use Profiler to see what happens on the other side, I see:

declare @p1 int
set @p1=6
exec sp_prepexec @p1 output,NULL,N'UPDATE "Northgale_utf8"."dbo"."Orders" set "CustomerID" = N''ALFKI''  WHERE "OrderID"=(11100)'
select @p1

That is, quotes are applied.

If this does not happen in your case, it may be that the OLE DB provider you are using says that it does not support quoted identifiers with UPDATE.