Dynamic query to open row set required

• syamala.popuri

  Mr or Mrs. 500

  Points: 597

  More actions

  December 2, 2010 at 6:01 am

  #388353

  hi all

  use info_db

  Insert Into dbo.Server_Details_Table

  select A.*

  FROM

  OPENROWSET ('SQLNCLI10', 'server=hrad;trusted_connection=yes',

  'set fmtonly off exec [MASTER].[dbo].[SP_SQL_Server_Info_ToReport]')as A

  Howcan i make server name dynamic here

  because i nedd to pass different servers names then the above query needs to execute and i.e with windows authentication.

  Help in advance

  Sridevi

• Eralper

  SSCarpal Tunnel

  Points: 4438

  More actions

  December 2, 2010 at 6:59 am

  #1257848

  Hi Sridevi,

  I hope you are looking for a solution like below

  DECLARE @sql nvarchar(max)

  DECLARE @server nvarchar(100) = N'hrad'

  SET @sql = N'

  Insert Into dbo.Server_Details_Table

  select A.*

  FROM

  OPENROWSET (''SQLNCLI10'', ''server=' + @server + ';trusted_connection=yes'',

  ''set fmtonly off exec [MASTER].[dbo].[SP_SQL_Server_Info_ToReport]'')as A'

  exec sp_executesql @sql

  Eralper
  SQL Server and T-SQL Tutorials and Articles
  Microsoft Certification and Certification Exams