query for linked server

• tpftpf

  SSC Veteran

  Points: 201

  More actions

  February 3, 2012 at 6:12 am

  #256181

  Hello,

  i want to retrive information( name of database and srvname) of instances from my linked server, for inventory scope.How i can exclude my local instance from result?

  Thanks and regards

  My script:

  DECLARE @srvname varchar(100)

  DECLARE @getDatabase CURSOR

  declare @sql varchar(200)

  SET @getDatabase = CURSOR FOR

  SELECT srvname

  FROM

  master.dbo.sysservers

  OPEN @getDatabase

  FETCH NEXT

  FROM @getDatabase INTO @srvname

  WHILE @@FETCH_STATUS = 0

  BEGIN

  set @sql='select a.name, b.srvname from ' + QUOTENAME(@srvname)+ '.master.dbo.sysdatabases a,'+ QUOTENAME(@srvname)+ '.master.sys.sysservers b where a.name not in (''master'', ''tempdb'',''model'',''msdb'')'

  exec (@sql)

  PRINT @srvname

  FETCH NEXT

  FROM @getDatabase INTO @srvname

  END

  CLOSE @getDatabase

  DEALLOCATE @getDatabase

• Bru Medishetty

  SSC-Insane

  Points: 23606

  More actions

  February 3, 2012 at 6:52 am

  #1442394

  Add where clause

  SELECT srvname

  FROM

  master.dbo.sysservers

  where srvid > 0

  ---

  Bru Medishetty

  Blog -- LearnSQLWithBru

  Join on Facebook Page Facebook.comLearnSQLWithBru

  Twitter -- BruMedishetty

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  February 3, 2012 at 7:24 am

  #1442423

  You could get rid of that cursor as well, if you use something like this: -

  DECLARE @sql AS NVARCHAR(MAX)

  SELECT @sql = COALESCE(@SQL,'') + ';' + CHAR(13) + CHAR(10) +

  'SELECT a.name, b.srvname' + CHAR(13) + CHAR(10) +

  'FROM ' + QUOTENAME(srvname) + '.master.dbo.sysdatabases a,' + QUOTENAME(srvname) + '.master.sys.sysservers b' + CHAR(13) + CHAR(10) +

  'WHERE a.name NOT IN (''master'', ''tempdb'',''model'',''msdb'')'

  FROM master.dbo.sysservers

  WHERE srvid > 0

  SELECT @sql = STUFF(@SQL,1,3,'')

  EXECUTE sp_executesql @sql

  ---

  --EDIT--

  Corrected dynamicSQL, no need to hit sysservers for each and every server.

  DECLARE @sql AS NVARCHAR(MAX)

  SELECT @sql = COALESCE(@SQL,'') + ';' + CHAR(13) + CHAR(10) +

  'SELECT name, ''' + srvname + ''' AS srvname' + CHAR(13) + CHAR(10) +

  'FROM ' + QUOTENAME(srvname) + '.master.dbo.sysdatabases' + CHAR(13) + CHAR(10) +

  'WHERE name NOT IN (''master'', ''tempdb'',''model'',''msdb'')'

  FROM master.dbo.sysservers

  WHERE srvid > 0

  SELECT @sql = STUFF(@SQL,1,3,'')

  EXECUTE sp_executesql @sql

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• johnitech.itech

  Hall of Fame

  Points: 3252

  More actions

  February 6, 2012 at 3:10 am

  #1442966

  – Get the current name of the SQL Server instance for later comparison.

  SELECT @@servername

  – Remove server from the list of known remote and linked servers on the local instance of SQL Server.

  EXEC master.dbo.sp_dropserver ‘[SERVER NAME]‘

  – Define the name of the local instance of SQL Server.

  EXEC master.dbo.sp_addserver ‘[NEW SERVER NAME]‘, ‘local’

  – Get the new name of the SQL Server instance for comparison.

  SELECT @@servername

  And a few notes relating to their usage:

  sp_dropserver: 1. this stored procedure can remove both remote and linked servers; 2. using the droplogins parameter indicates that related remote and linked server logins for [SERVER NAME] must also be removed. More info.

  sp_addserver: 1. to define a linked server, use sp_addlinkedserver (as this functionality will be depreciated in version above SQL Server 2005); 2. sp_addserver cannot be used inside a user-defined transaction. More info.