Search code examples
sql-serverconcatenationlinked-server

Concatenation does not work when linked server used SQL Server

When running a select statement in a SQL Server (server1)

select IDNo, Code + ' ' + No + ' ' + Extension as Ext, MenuNo
from [database1].[dbo].[table1]

gives result as expected:

1  |  Toranto 56 Placid 47  |  34563

But From another server(server2) the same query with server1 as linked server, gives output incompletely. ie Concatenated column(2nd Column) only return first letter of the expected column value (IDNo and MenuNo coming correctly)

Query with linked server (run on server2):

select IDNo, Code+' '+ No + ' ' + Extension, MenuNo
from [server1].[database1].[dbo].[table1]

Output:

 1  |  T  |  34563

Note:

  • server2 : SQL_Latin1_General_CP1_CI_AS
  • server1: Latin1_General_CI_AI

Please help

Solution

  • Use CAST

    select IDNo, CAST(Code+' '+ No + ' ' + Extension) as VARCHAR(8000), MenuNo from [server1].[database1].[dbo].[table1]

    or RESULT SET

    EXEC('SELECT ... ') WITH RESULT SET ...