0

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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.