SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE   PROCEDURE [dbo].[p_sys_fs_GetWorkingBase]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ServerName nvarchar(256) =
        CONVERT(nvarchar(256), SERVERPROPERTY('ServerName'));

    DECLARE @DatabaseName sysname = DB_NAME();

    DECLARE @Xml xml;
    DECLARE @WorkingBase nvarchar(4000);

    /*
        TODO:
        Pentru moment path-ul este cel al utilizatorului care rulează
        SQL Source Control. Ulterior îl putem face configurabil/dinamic.
    */
    SELECT @Xml = BulkColumn
    FROM OPENROWSET(
        BULK 'C:\Users\catalin\AppData\Local\Red Gate\SQL Source Control 7\LinkedDatabases.xml',
        SINGLE_NCLOB
    ) AS F;

    SELECT @WorkingBase =
        X.N.value(
            '(IWorkspaceId/RootPath/text())[1]',
            'nvarchar(4000)'
        )
    FROM @Xml.nodes(
        '/LinkedDatabaseStore/LinkedDatabaseList/value'
    ) AS X(N)
    WHERE X.N.value(
              '(DatabaseId/ServerAndInstanceName/text())[1]',
              'nvarchar(256)'
          ) = @ServerName
      AND X.N.value(
              '(DatabaseId/DatabaseName/text())[1]',
              'sysname'
          ) = @DatabaseName;

    IF @WorkingBase IS NULL
    BEGIN
        THROW 50000,
              'Database is not linked to SQL Source Control.',
              1;
    END;

    SELECT
        @ServerName AS ServerName,
        @DatabaseName AS DatabaseName,
        @WorkingBase AS WorkingBase;
END;
GO
