Uwe Ricken

Bei Entwicklungen in größeren Umgebungen ist es häufig erforderlich, dass neben einer DEV-Umgebung noch eine TEST, UAT (User Acceptance Test) und ev. auch die Produktionsumgebung auf einem Server vorhanden ist. Eventuell ist es aber auch so, dass sie eine generische Entwicklung vorgenommen haben, die in allen Benutzerdatenbanken vorhanden sein sollte.

Mit Hilfe einer simplen Prozedur, die - wegen der allgemeinen Verfügbarkeit - in die Masterdatenbank implementiert wird, kann man relativ leicht Abhilfe schaffen. Der Prozedur übergeben Sie einfach das Objekt nebst Schema sowie den Objekttypen. Weiterhin übergeben Sie als Option, ob auch Systemdatenbanken (master, model, msdb, tempdb) durchsucht werden sollen. Als Ergebnis erhalten Sie eine Relation mit allen Datenbanken, in denen das Objekt gefunden wurde nebst Zeitstempel der Erstellung.

USE master
GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID('sp_SearchObject', 'P'))
    DROP PROC dbo.sp_SearchObject
    GO

CREATE PROC dbo.sp_SearchObject
    @ObjectName        nvarchar(255),
    @ObjectType        nvarchar(10),
    @InSystemDB        bit
AS
    SET NOCOUNT ON

    -- needed variables
    DECLARE    @database    sysname
    DECLARE    @ObjectId    int
    DECLARE    @parms       nvarchar(200)
    DECLARE    @sqlstmt     nvarchar(1000)

    -- Creation of temporary output object
    CREATE TABLE #ResultList
    (
        [database]    sysname        NOT NULL,
        [objectid]    int            NULL,
        [objectname]  nvarchar(255)  NULL,
        [objecttype]  nvarchar(5)    NULL,
        [crdate]      datetime       NULL,
        [refdate]     datetime       NULL,
    )

    -- definition of cursor for all affected databases
    DECLARE c CURSOR LOCAL FORWARD_ONLY
    FOR
        SELECT  name
        FROM    master.sys.sysdatabases
        WHERE   name NOT IN ('master', 'model', 'msdb', 'tempdb') OR
                (
                    name IN ('master', 'model', 'msdb', 'tempdb') AND
                    @InSystemDB = 1
                )

    OPEN c

    FETCH NEXT FROM c INTO @database
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        SET    @parms   =    N'@ObjectId int OUTPUT'
        SET    @sqlstmt =    N'SET    @ObjectId = OBJECT_ID(''[' + @database + '].' + @ObjectName + ''', ''' + @ObjectType + ''')'
        EXEC   sp_executeSQL @sqlstmt, @parms, @ObjectId = @ObjectId OUTPUT
       
        SET    @parms   =    N'@database sysname, @objectname nvarchar(255), @objecttype nvarchar(5), @objectid int'
        SET    @sqlstmt =    N'INSERT INTO #ResultList
        ([database], [objectid], [objectname], [objecttype], [crdate], [refdate])
        SELECT @database, id, @objectname, @objecttype, crdate, refdate
        FROM    [' + @database + '].sys.sysobjects
        WHERE    id = @objectid'

        EXEC    sp_executeSQL
                @sqlstmt,
                @parms,
                @database   = @database,
                @objectname = @objectname,
                @objecttype = @objecttype,
                @objectId   = @objectId

        FETCH NEXT FROM c INTO @database
    END

    CLOSE c
    DEALLOCATE c

ExitCode:
    SELECT * FROM #ResultList
    DROP TABLE #ResultList
    SET NOCOUNT OFF
GO

Die nachfolgenden Beispiele zeigen die Verwendung der Prozedur:

EXEC sp_SearchObject @ObjectName = 'dbo.DTSStatus', @ObjectType = 'U', @InSystemDB = 0

Das obige Beispiel sucht nach einer Relation dbo.dtsstatus wobei die Systemdatenbanken ausgeschlossen werden während das nachfolgende Beispiel nach einer Systemprozedur sucht und dabei alle Systemdatenbanken berücksichtigt.

EXEC sp_SearchObject @ObjectName = 'dbo.sp_executeSQL', @ObjectType = 'X', @InSystemDB = 1

Weiterführende Informationen:

• sp_executeSQL
• Verwendung von Cursor
• Erstellung von Stored Procedures

64390 Erzhausen, 23.08.2010
Uwe Ricken
db Berater GmbH

Die Verwendung von Linked Server ist eine tolle Sache; macht sie es mir doch leicht, ohne komplizierten erneuten Verbindungsaufbau unmittelbar von meinem Datenbankserver auf Datenbanken anderer Serversysteme zuzugreifen. Die nachfolgenden Codings beschränken sich auf den Zugriff auf SQL Server als Linked Server.

Grundsätzlich lassen sich Objekte, die sich auf einem Linked Server befinden, relativ einfach ansprechen. Sie müssen lediglich die Notation für den Zugriff auf Objekte von Linked Server einhalten. Der Zugriff erfolgt wie eine Wegbeschreibung und setzt sich aus vier Elementen zusammen:

[Server\Instanzname].[Datenbank].[Schema].[Table_Or_View]

Haben Sie zum Beispiel einen Linked Server mit dem Namen [SRV_FINANZ] und benötigen Informationen aus der Gehaltstabelle, die sich in der Datenbank [BUCHHALTUNG] befindet, wäre der Aufruf wie folgt durchzuführen:

SELECT * FROM [SRV_FINANZ].[BUCHHALTUNG].[dbo].[Gehaltstabelle]

Grundsätzlich funktioniert dieser Aufruf nur mit Tabellen / Views. Andere Objekte sind über diese Notationen nicht verfügbar.

Es ist aber nicht auszuschließen, dass z. B. eine UDF (User definied Function) aufgerufen werden muss, da die Entwickler direkten Zugriff auf Tabellen / Views untersagt haben. Dann kann obige Aufrufsyntax leider nicht verwendet werden.

Haben Sie zum Beispiel eine Funktion mit dem Namen fn_GehaltsListe(), die Ihnen eine Liste der aktuellen Gehaltsdaten zurückliefert, würden Sie bei dem Aufruf nach obigem Schema eine Fehlermeldung erhalten.

SELECT [SRV_FINANZ].[BUCHHALTUNG].[dbo].fn_GehaltsListe()
----------------------
Msg 207, Level 16, State 1, Line 1
Invalid Column name '[SRV_FINANZ]'

Der Aufruf wird also von der SQL Engine falsch interpretiert!

Abhilfe schafft jedoch OPENQUERY. Mit OPENQUERY werden Pass Through Abfragen auf Linked Server ausgeführt. Es gilt also immer die Syntax und die Konventionen des Zielservers! OPENQUERY hat jedoch einen Nachteil; sie dürfen keine Variablen als Parameter verwenden.

Für den Zugriff auf die Funktion wird OPENQUERY wie folgt verwendet:

SELECT * FROM OPENQUERY([SRV_FINANZ], 'SELECT [BUCHHALTUNG].[dbo].fn_GehaltsListe()')

Nun wird die Funktion erwartungsgemäß ausgeführt und liefert ein Ergebnis an den Client zurück!

Weiterführende Informationen:

• Erstellen eines Linked Servers:

  • sp_addlinkedserver
  • sp_dropserver

• Sicherheitsrichtlinien für Linked Server

  • sp_addlinkedsrvlogin
  • sp_droplinkedsrvlogin

64390 Erzhausen, 20.08.2010
Uwe Ricken
db Berater GmbH

Obwohl der Autor ein Gegner von Cursor ist, gibt es Situationen, in denen es einfach nicht möglich ist, eine andere Variante der Datenmanipulation zu favorisieren.

Das Cursor nicht gerade performant sind im Verhältnis zur mengenorientierten Datenmanipulation, sollte jedem erfahrenen SQL-Server Entwickler klar sein. Leider haben Cursor noch andere "Schwachstellen" an die der Autor unfreiwillig geraten ist.