SQL Server – How to Check Linked Servers Database Status

linked-serversql server

I want to find out if all SQL databases registered in the database XXX on the YYY instance are available on the respective linked server and if they are online or offline.
I have the below Code, however when i run it, it Returns NULL values. What am I doing wrong?

DECLARE @STR_DBServer NVARCHAR(128)
DECLARE @STR_DBName NVARCHAR(128)
DECLARE @state_desc NVARCHAR(10)
DECLARE @table table (Server nvarchar(128), Datenbank nvarchar(128), Status nvarchar(10))  

DECLARE CUR1 CURSOR FOR
        SELECT 
        STR_DBServer , STR_DBName  
        FROM   xxx
        where int_systemID = 1 
        order by    STR_DBServer 

OPEN CUR1
FETCH NEXT FROM CUR1 INTO @STR_DBServer, @STR_DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    begin try

        exec sys.sp_testlinkedserver @STR_DBServer  
        If Exists ( select s.name, 
        m.state_desc from master.sys.databases m, master.sys.servers s
        where is_linked= 1) 
        insert into @table (Status)
        values
        (@state_desc) 

    end try

    begin catch

          print 'Keine verbindung möglich mit '  + '' + @STR_DBName + ' auf ' + @STR_DBServer 

    end catch 

FETCH NEXT FROM CUR1 INTO @STR_DBServer, @STR_DBName
END
CLOSE CUR1
DEALLOCATE CUR1
select * from @table

Best Answer

You're getting NULL mainly because of this insert insert into @table (Status) values (@state_desc) since you declared the variable @state_desc and never set any value to it.
Try this instead:

DECLARE @STR_DBServer sysname;
DECLARE @SQLString nvarchar(500);
DECLARE @complete_catalog TABLE(server sysname, database_name sysname, state_desc nvarchar(120));
DECLARE @remote_databases TABLE(name sysname, state_desc nvarchar(120));

--Lists all linked servers on the current instance (except SQL Server 2000)
DECLARE LinkedServersCur CURSOR FOR
    SELECT name FROM master.sys.servers WHERE is_linked = 1 and provider <> N'MSDASQL';

OPEN LinkedServersCur;
FETCH NEXT FROM LinkedServersCur INTO @STR_DBServer;

WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SQLString = N'SELECT m.name, m.state_desc FROM [' + CONVERT(nvarchar(100), @STR_DBServer)+ '].master.sys.databases m';   
        INSERT INTO @remote_databases EXECUTE sp_executesql @SQLString;

        INSERT INTO @complete_catalog(server, database_name, state_desc)
            SELECT @STR_DBServer, name, state_desc FROM @remote_databases;

        DELETE FROM @remote_databases;
        FETCH NEXT FROM LinkedServersCur INTO @STR_DBServer;

    END
CLOSE LinkedServersCur;
DEALLOCATE LinkedServersCur;

SELECT server, database_name, state_desc FROM @complete_catalog;

You can compare the result of this query with your local table DBConfig.dbo.T_Datenbanken if you wish.

Related Solutions

Sql-server – Remote queries and linked servers

Several possible reasons for this.

Lack of data distribution statistics. It looks like you have this one covered in your post, but i'll leave it for completeness.

If the linked server login does not have sufficient privileges to obtain all useful statistics on CRM, a suboptimal plan will be used. *If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.* http://msdn.microsoft.com/en-us/library/ms175537%28v=sql.105%29.aspx

Mike Walsh also has a writeup on this: http://www.straightpathsql.com/archives/2010/07/linked-server-query-running-slow/

Query is too complex to perform well across the linked servers.

Replication: Replicate the minimum necessary columns from one server to the other. Use the local replicated data for joins.
Use temp tables: Insert the minimum necessary columns from one server to a table on the other. Use the local data for joins.

For both of these scenarios, all data is on the same machine. This enables the optimizer to have complete data distribution statistics. You can even index columns for further optimization. Always identify the minimum columns required to complete the necessary tasks. Sometimes it's sufficient just to replicate or insert 1 or 2 columns.

OPENQUERY is also an option, but I prefer all of the data on the same machine when possible.

If the cross server joins are INNER JOINS, you might also benefit from the use of the REMOTE hint. I've seen this work very well when the remote table has significantly more records than the local table.

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – Remote queries and linked servers

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Related Question