How to execute sp_executesql on the linked server via dynamic sql

• lindsaywang

  SSCrazy

  Points: 2569

  More actions

  April 18, 2011 at 8:51 pm

  #235805

  Hi,

  Could you please help me take a look at the sql below? I want to use dynamic to execute the sql on a linked server, but I got the following error message, don't know why. I also checked the @tblSql2, seems that all the info is right, could you please help me take a look at it? Thanks a lot:)

  Thanks

  Lindsay

  Error message:

  The name 'lktest.[master].dbo.sp_executesql N'SELECT @ResultCountOut=Count(*) FROM (select * from ADW1.dbo.DimAccount ) AS TempResultTable', N'@ResultCountOut INT OUT',@ResultCountOut=@ResultCount OUT' is not a valid identifier.

  DECLARE @tblSql2 NVARCHAR(300)

  DECLARE @Query NVARCHAR(300)

  DECLARE @LinkedServerName NVARCHAR(300)

  SET @Query = 'Select * from ADW1.dbo.DimAccount'

  SET @LinkedServerName = 'lktest'

  SET @tblSql2 = N'SELECT @ResultCountOut=Count(*) FROM (' + @Query + ') AS TempResultTable'

  SET @tblSql2 = @LinkedServerName + '.[master].dbo.sp_executesql N'''+REPLACE(@tblSql2,'''','''''') +

  +''', N''@ResultCountOut INT OUT'',@ResultCountOut=@ResultCount OUT'

  Select @tblSql2

  EXEC @tblSql2

• lindsaywang

  SSCrazy

  Points: 2569

  More actions

  April 19, 2011 at 12:50 am

  #1313189

  I have found one way to workarround it, past the sample here, in case someone hit same problem. Really appreciated if someone can share another way to fingure out it. Thanks a lot.

  Thanks

  Lindsay

  DECLARE @dynamicsql NVARCHAR(300)

  DECLARE @Query NVARCHAR(300)

  DECLARE @LinkedServerName NVARCHAR(300)

  DECLARE @ResultCount INT

  SET @Query = 'Select * from ADW1.dbo.DimAccount'

  SET @LinkedServerName = 'lktest'

  SET @dynamicsql = N'SELECT Count(*) as rownum FROM (' + @Query + ') AS TempResultTabl'

  SET @dynamicsql ='SELECT @ResultCountOut=rownum FROM OPENQUERY('+ @LinkedServerName+ ','''+ REPLACE(@dynamicsql,'''','''''') +''')'

  EXEC sp_executesql

  @dynamicsql,

  N'@ResultCountOut INT OUT',

  @ResultCountOut=@ResultCount OUT