2

We have the following setup:

  • Source server version 12 or 15, collation Latin1_General_CI_AS (in our responsibility, we can change everything here)
  • Linked-server version 13, collation: Latin1_General_CI_AS (not in our responsibility, we cannot change anything here)
  • Linked database collation: SQL_Latin1_General_CP1_CI_AS
  • NHibernate is used as ORM to create queries (that means: less flexibility), with only few to the tables on the linked server.

  • Linked server options: sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'SQL Server'

    'collation compatible='false', 'data access='true', 'dist='false', 'pub='false', 'rpc='false', 'rpc out='false', 'sub='false', 'connect timeout='0', 'collation name=ull, 'lazy schema validation='false', 'query timeout='0', 'use remote collation='true', 'remote proc transaction promotion='true'

The target table TargetTableOnLinkedServer columns look like this:

PrimaryKey varchar(23)
OtherThing varchar(42)

We issue a query like this:

SELECT OtherThing FROM TargetTableOnLinkedServer WHERE PrimaryKey = 'Hello World'

We also try with sp_execute with the parameter as varchar(8000) - more detailled queries below.

In the SQL server profiler we see that the linked query is being translated to basically:

SELECT OtherThing FROM TargetTableOnLinkedServer WHERE PrimaryKey = N'Hello World'

Please note the "N" here. The "N" (as in Nvarchar) causes an index scan, instead of an index seek. Unfortunately, we cannot find a way to supress the N nor know the reason.

Is there a way to prevent that the "linked-server mechanism" 'converts' the parameter to an unicode string (by adding the N).

Thanks in advance!

In more detail the queries:

SELECT MyColumn FROM MyLinkedServer.MyDatabase.dbo.MyTable A WHERE A.MyColumn = 'MyValue'

This results in a profiler SP:StmtCompleted event on the linked server 

SELECT "Tbl1001"."MyColumn" "Col1003" FROM "MyDatabase"."dbo"."MyTable" "Tbl1001" WHERE "Tbl1001"."MyColumn"=N'MyValue'

And an RPC:Completed Event:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1001"."MyColumn" "Col1003" FROM "MyDatabase"."dbo"."MyTable" "Tbl1001" WHERE "Tbl1001"."MyColumn"=N''MyValue'''
select @p1

NHibernate creates queries like this:

EXEC sp_executesql N'SELECT MyColumn FROM MyLinkedServer.MyDatabase.dbo.MyTable A WHERE A.MyColumn = @p1',N'@p1 varchar(8000)', @p1='MyValue'

Please note the varchar and the lack of an "N". This causes the following query with an index scan (at least this shows the execution plan when I copy-paste the query in SMSS):

declare @p1 int
exec sp_prepexec @p1 output,N'@P1 nvarchar(4000)',N'SELECT "Tbl1001"."MyColumn" "Col1004" FROM "MyDatabase"."dbo"."MyTable" "Tbl1001" WHERE "Tbl1001"."MyColumn"=@P1',N'MyValue'
select @p1

Correct would be the following with an index seek: 

exec sp_prepexec @p1 output,N'@P1 varchar(4000)',N'SELECT "Tbl1001"."MyColumn" "Col1004" FROM "MyDatabase"."dbo"."MyTable" "Tbl1001" WHERE "Tbl1001"."MyColumn"=@P1','MyValue'
select @p1

I also tried using another provider MSOLEDBSQL (apparently the most modern one), no changes:

EXEC master.dbo.sp_addlinkedserver @server = N'MyServer', @srvproduct=N'', @provider=N'MSOLEDBSQL', @provstr=N'Server=MyServerName'
M T
  • 21
  • 2
  • How are you running the query? SQL won't convert a literal string to an `nvarchar`, so you're passing it one and hence the scan. `EXEC sp_executesql N'SELECT OtherThing FROM TargetTableOnLinkedServer WHERE PrimaryKey = ''Hello World'';';` won't convert the `varchar` (`'Hello World'`) to an `nvarchar` (`N'Hello World'`); which means what ever you are doing is the cause (which you haven't shown us). – Thom A Feb 07 '20 at 08:41
  • @larnu Thanks for your answer. This is what I assumed. Could you please check the more detailled, real-live queries? – M T Feb 07 '20 at 09:00
  • Your issue seems kind of similar to https://www.sqlservercentral.com/forums/topic/sp_prepexec-vs-stored-procedures#post-1095626 but this is from JDBC. What is the definition of the linked server? What provider does it use? – Martin Smith Feb 07 '20 at 09:17
  • @Martin Smith Thank you. I've added the options of the linked server above. – M T Feb 07 '20 at 10:59
  • Do you at any point collate the query from the linked server? I don't see it in your queries, but I just need to make sure... – Attie Wagner Feb 07 '20 at 11:06
  • Thank you @Birel, no, at the moment I did not add any collate in the query anywhere. I fiddled around with the options/parameters, collations and even manual CASTs in the query for hours but could not find a way. – M T Feb 07 '20 at 12:07
  • 1
    The samples above I created by just doing a manual query in SMSS while the profiler is running on the linked server. The options of the linked server were 100% standard. I cannot imagine any wrongdoing here. – M T Feb 07 '20 at 12:09
  • I've tested with the MSOLEDBSQL provider on the very fresh SQL Server 13, see above. The same result. :-( – M T Feb 07 '20 at 12:39
  • Could it be that the server collations are different and the linked db has the same collation as the source server? When server collations are different then varchar could be "converted" to Nvarchar, unless the source db (on the source server, from within which the query to the linked sever is executed) has the same collation as the linked server. – lptr Feb 07 '20 at 14:08
  • @lptr Thank you, it's the other way around: server collations are identical, linked database collation is different. I'm not sure what collation I could change. What makes it even harder: I have only control over the source server, and accesses to the linked server are not that frequent to change to risk big changes. – M T Feb 08 '20 at 16:21
  • I was not able to reproduce the conversion of varchar to Nvarchar when the collation of the linked servers was the same (although i used linked servers with lower collation precedence SQL_Latin..). Since you know that both servers have the same collation, then set Collation Compatible to true. The next thing to try would be to create a database with SQL_Latin1_General_CP1_CI_AS (on the source server) and perform a query to the linked db from within the context of that/the new db. – lptr Feb 08 '20 at 17:03

0 Answers0