Back to OIM Explorer

dbo.QBM_PDatabasePrepare

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.875 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVElementToObjectKey1 source text reference
references source dbo.QBM_FCVElementToObjectKey2 source text reference
references source dbo.QBM_FGIDBDataOrigin source text reference
references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PDatabasePrepareCheck1 source text reference
references source dbo.QBM_PDialogDatabaseInitialInfo source text reference
references source dbo.QBM_PJournal source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QBM_PDatabasePrepare(2  @ConnectionProvider nvarchar(255) = '',3  @ConnectionString nvarchar(max) = ''4)5AS6BEGIN7  DECLARE @DebugLevel char(1) = 'W'8  DECLARE @DebugMessage nvarchar(2000)9  DECLARE @UID_NewObject varchar(38)10  DECLARE @ModuleOwner varchar(3)11  DECLARE @xdate datetime = getutcdate()12  DECLARE @XUser nvarchar(64) = object_name(@@procid)13  DECLARE @server nvarchar(64)14  SET XACT_ABORT OFF15  BEGIN TRY16    SET nocount17      ON18    EXEC QBM_PDialogDatabaseInitialInfo19    IF NOT EXISTS(20      SELECT TOP 1 121      FROM dialogdatabase)22    BEGIN23      UPDATE DialogConfigParm24      SET Value = '5',25      XDateUpdated = @Xdate,26      XUserUpdated = @XUser27      WHERE28        FullPath = 'QBM\DBQueue\CountSlotsMax'29      UPDATE DialogConfigParm30      SET Value = '20',31      XDateUpdated = @Xdate,32      XUserUpdated = @XUser33      WHERE34        FullPath = 'QBM\DBQueue\KeepAlive'35    END36    UPDATE dialogdatabase37    SET DataOrigin = dbo.QBM_FGIDBDataOrigin(),38    XDateUpdated = @xdate,39    XUserUpdated = @XUser40    WHERE41      IsMainDatabase = 1 AND isnull(DataOrigin,42    0) <> dbo.QBM_FGIDBDataOrigin()43    UPDATE dialogdatabase44    SET ConnectionString = CASE45    WHEN @ConnectionString > ' ' THEN46    @ConnectionString47    ELSE 'User ID=<sql-user>;initial Catalog=<database>;Data Source=<DB-Server>;Password=<sql-user-password>;pooling= ''false'''48    END,49    XDateUpdated = @xdate,50    XUserUpdated = @XUser51    FROM DialogDatabase d52    WHERE53      d.IsMainDatabase = 1 AND(isnull(d.ConnectionString, N '') = N '' OR @ConnectionString > ' ' AND d.ConnectionString LIKE '%ID=<sql-user>%' OR54    (@ConnectionString > ' ' AND d.ConnectionString NOT LIKE '%ID=<sql-user>%' AND d.ConnectionString <> @ConnectionString AND @Connectionstring LIKE55    '%Password=[^;<]%'))56    UPDATE dialogdatabase57    SET ConnectionProvider = CASE58    WHEN @ConnectionProvider > ' ' THEN59    @ConnectionProvider60    ELSE 'VI.DB.ViSqlFactory,VI.DB'61    END,62    XDateUpdated = @xdate,63    XUserUpdated = @XUser64    WHERE65      IsMainDatabase = 1 AND isnull(ConnectionProvider,66    N '') = N ''67    UPDATE DialogDatabase68    SET ModuleOwner = 'CCC',69    XDateUpdated = @xdate,70    XUserUpdated = @XUser71    WHERE72      IsMainDatabase = 1 AND ISNULL(ModuleOwner,73    '') = ''74    UPDATE DialogDatabase75    SET UID_DialogCultureDefault = 'QBM-CULT-en-US',76    XDateUpdated = @xdate,77    XUserUpdated = @XUser78    WHERE79      ISNULL(UID_DialogCultureDefault,80    '') = '' AND IsMainDatabase = 181    SELECT @ModuleOwner = NULL82    SELECT TOP 1 @ModuleOwner = d.ModuleOwner83    FROM DialogDatabase d84      WITH(readpast)85    WHERE86      d.IsMainDatabase = 187    IF @ModuleOwner IS NULL88    BEGIN89      SELECT @ModuleOwner = 'CCC'90    END91    EXEC QBM_PDatabasePrepareCheck192    IF NOT EXISTS(93      SELECT TOP 1 194      FROM QBMServerTag t95      WHERE96        t.UID_QBMServerTag = 'QBM-ST-Is07')97    BEGIN98      INSERT INTO QBMServerTag(UID_QBMServerTag,99      Ident_QBMServerTag,100      Description,101      XObjectKey,102      XUserInserted,103      XUserUpdated,104      XDateInserted,105      XDateUpdated)106      SELECT107        'QBM-ST-Is07',108        'IsMasterSQL',109        'aka is07',110        dbo.QBM_FCVElementToObjectKey1('QBMServerTag',111        'UID_QBMServerTag',112        'QBM-ST-Is07'),113        @XUser,114        @XUser,115        @xdate,116        @xdate117    END118    IF EXISTS(119      SELECT TOP 1 1120      FROM QBMModuleDef d121      WHERE122        d.ModuleName = 'SIM')123    BEGIN124      SELECT @server = 'cloudserver-0'125      SELECT @UID_NewObject = '5DC98AAD-A7F7-4BA9-B66C-5C0B1DF641F2'126    END127    ELSE128    BEGIN129      SELECT @UID_NewObject = NEWID()130      SELECT @server =131      LEFT(trim(@@servername),132      64)133    END134    IF charindex(N '\',135    @server) > 0136    BEGIN137      SELECT138        @server = substring(@server,139        1,140        charindex(N '\', @server) -1)141    END142    IF NOT EXISTS(143      SELECT TOP 1 1144      FROM QBMServerHasServerTag ht145      WHERE146        ht.UID_QBMServerTag = 'QBM-ST-Is07')147    BEGIN148      IF EXISTS(149        SELECT TOP 1 1150        FROM QBMServer151        WHERE152          ident_server = @server)153      BEGIN154        INSERT INTO QBMServerHasServerTag(UID_QBMServer,155        UID_QBMServerTag,156        XObjectKey,157        XUserInserted,158        XUserUpdated,159        XDateInserted,160        XDateUpdated)161        SELECT162          s.uid_QBMServer,163          t.UID_QBMServerTag,164          dbo.QBM_FCVElementToObjectKey2('QBMServerHasServerTag',165          'UID_QBMServer',166          s.UID_QBMServer,167          'UID_QBMServerTag',168          t.UID_QBMServerTag),169          @XUser,170          @XUser,171          @xdate,172          @xdate173        FROM QBMServer s,174        QBMServerTag t175        WHERE176          t.UID_QBMServerTag = 'QBM-ST-Is07' AND s.Ident_Server = @server177        SELECT @DebugMessage = 'server "' + @server + '" updated as MasterSQL (Is07)'178        EXEC QBM_PJournal @DebugMessage,179          @@PROCID,180        'D',181          @DebugLevel182      END183      ELSE184      BEGIN185        INSERT INTO QBMServer(UID_QBMServer,186        Ident_Server,187        QueueName,188        XObjectKey,189        XUserInserted,190        XUserUpdated,191        XDateInserted,192        XDateUpdated)193        VALUES(@UID_NewObject,194        @server,195        N '\' + @server,196        dbo.QBM_FCVElementToObjectKey1('QBMServer', 'UID_QBMServer', @UID_NewObject),197        @XUser,198        @XUser,199        @xdate,200        @xdate)201        INSERT INTO QBMServerHasServerTag(UID_QBMServer,202        UID_QBMServerTag,203        XObjectKey,204        XUserInserted,205        XUserUpdated,206        XDateInserted,207        XDateUpdated)208        SELECT209          @UID_NewObject,210          t.UID_QBMServerTag,211          dbo.QBM_FCVElementToObjectKey2('QBMServerHasServerTag',212          'UID_QBMServer',213          @UID_NewObject,214          'UID_QBMServerTag',215          t.UID_QBMServerTag),216          @XUser,217          @XUser,218          @xdate,219          @xdate220        FROM QBMServerTag t221        WHERE222          t.UID_QBMServerTag = 'QBM-ST-Is07'223        SELECT @DebugMessage = 'server "' + @server + '" created as MasterSQL (Is07)'224        EXEC QBM_PJournal @DebugMessage,225          @@PROCID,226        'D',227          @DebugLevel228      END229      DELETE QBMServerHasServerTag230      WHERE231        UID_QBMServerTag = 'QBM-ST-Is07' AND UID_QBMServer NOT IN(232      SELECT UID_QBMServer233      FROM QBMServer234      WHERE235        ident_server = @server)236    END237    IF NOT EXISTS(238      SELECT TOP 1 1239      FROM QBMServerHasServerTag ht240      WHERE241        ht.UID_QBMServerTag = 'QBM-ST-Is12')242    BEGIN243      SELECT @server = N ''244      SELECT TOP 1 @server = ident_server245      FROM QBMServer s246      JOIN QBMServerHasServerTag sht247        ON s.UID_QBMServer = sht.UID_QBMServer248      WHERE249        sht.UID_QBMServerTag = 'QBM-ST-Is07'250      INSERT INTO QBMServerHasServerTag(UID_QBMServer,251      UID_QBMServerTag,252      XObjectKey,253      XUserInserted,254      XUserUpdated,255      XDateInserted,256      XDateUpdated)257      SELECT258        s.uid_QBMServer,259        t.UID_QBMServerTag,260        dbo.QBM_FCVElementToObjectKey2('QBMServerHasServerTag',261        'UID_QBMServer',262        s.UID_QBMServer,263        'UID_QBMServerTag',264        t.UID_QBMServerTag),265        @XUser,266        @XUser,267        @xdate,268        @xdate269      FROM QBMServer s,270      QBMServerTag t271      WHERE272        t.UID_QBMServerTag = 'QBM-ST-Is12' AND s.Ident_Server = @server273      SELECT @DebugMessage = 'server "' + @server + '" updated as Mailserver (is12)'274      EXEC QBM_PJournal @DebugMessage,275        @@PROCID,276      'D',277        @DebugLevel278    END279    INSERT INTO QBMServerHasServerTag(UID_QBMServer,280    UID_QBMServerTag,281    XUserInserted,282    XUserUpdated,283    XDateInserted,284    XDateUpdated,285    XObjectKey)286    SELECT287      sht.UID_QBMServer,288      'QBM-ST-SQLProcessingEnabled',289      'DBMigration',290      'DBMigration',291      GETUTCDATE(),292      GETUTCDATE(),293      dbo.QBM_FCVElementToObjectKey2('QBMServerHasServerTag',294      'UID_QBMServer',295      sht.UID_QBMServer,296      'UID_QBMServerTag',297      'QBM-ST-SQLProcessingEnabled')298    FROM QBMServerHasServerTag sht299    WHERE300      sht.UID_QBMServerTag = 'QBM-ST-Is07' AND NOT EXISTS(301    SELECT TOP 1 1302    FROM QBMServerHasServerTag sht2303    WHERE304      sht2.UID_QBMServer = sht.UID_QBMServer AND sht2.UID_QBMServerTag = 'QBM-ST-SQLProcessingEnabled')305  END TRY306  BEGIN CATCH307    EXEC QBM_PSessionErrorAdd DEFAULT308    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()309    RAISERROR(@Rethrow,310    18,311    1)312      WITH NOWAIT313  END CATCH314  ende:315  RETURN316END

Open raw exported source

1     create   procedure QBM_PDatabasePrepare (@ConnectionProvider nvarchar(255) = '' , @ConnectionString nvarchar(max) = '' ) as begin declare @DebugLevel2 char(1) = 'W' declare @DebugMessage nvarchar(2000) declare @UID_NewObject varchar(38) declare @ModuleOwner varchar(3)  declare @xdate datetime = getutcdate3() declare @XUser nvarchar(64) = object_name(@@procid) declare @server nvarchar(64) SET XACT_ABORT OFF BEGIN TRY set nocount on   exec QBM_PDialogDatabaseInitialInfo4 if not exists (select top 1 1 from dialogdatabase ) begin  update DialogConfigParm set Value = '5'  , XDateUpdated = @Xdate , XUserUpdated = @XUser where5 FullPath = 'QBM\DBQueue\CountSlotsMax' update DialogConfigParm set Value = '20'  , XDateUpdated = @Xdate , XUserUpdated = @XUser where FullPath = 'QBM\DBQueue\KeepAlive'6 end update dialogdatabase set DataOrigin = dbo.QBM_FGIDBDataOrigin() , XDateUpdated = @xdate , XUserUpdated = @XUser where IsMainDatabase = 1 and isnull7(DataOrigin,0) <> dbo.QBM_FGIDBDataOrigin() update dialogdatabase set ConnectionString = case  when @ConnectionString > ' ' then @ConnectionString else8 'User ID=<sql-user>;initial Catalog=<database>;Data Source=<DB-Server>;Password=<sql-user-password>;pooling= ''false''' end , XDateUpdated = @xdate , 9XUserUpdated = @XUser from DialogDatabase d where d.IsMainDatabase = 1 and (isnull(d.ConnectionString,N'') = N'' or @ConnectionString > ' ' and d.ConnectionString10 like '%ID=<sql-user>%'  or (@ConnectionString > ' ' and d.ConnectionString not like '%ID=<sql-user>%' and d.ConnectionString <> @ConnectionString  and11 @Connectionstring like '%Password=[^;<]%'  ) ) update dialogdatabase set ConnectionProvider = case  when @ConnectionProvider > ' ' then @ConnectionProvider12 else 'VI.DB.ViSqlFactory,VI.DB' end , XDateUpdated = @xdate , XUserUpdated = @XUser where IsMainDatabase = 1 and isnull(ConnectionProvider,N'') = N'' 13update DialogDatabase set ModuleOwner = 'CCC' , XDateUpdated = @xdate , XUserUpdated = @XUser where IsMainDatabase = 1 and ISNULL(ModuleOwner, '') = ''14 update DialogDatabase set UID_DialogCultureDefault = 'QBM-CULT-en-US' , XDateUpdated = @xdate , XUserUpdated = @XUser where ISNULL(UID_DialogCultureDefault15, '') = '' and IsMainDatabase = 1  select @ModuleOwner = null select top 1 @ModuleOwner = d.ModuleOwner from DialogDatabase d with (readpast) where d.IsMainDatabase16 = 1 if @ModuleOwner is null begin select @ModuleOwner = 'CCC' end exec QBM_PDatabasePrepareCheck1  if not exists ( select top 1 1 from QBMServerTag t 17where t.UID_QBMServerTag = 'QBM-ST-Is07' ) begin insert into QBMServerTag (UID_QBMServerTag, Ident_QBMServerTag, Description, XObjectKey , XUserInserted18, XUserUpdated, XDateInserted, XDateUpdated ) select 'QBM-ST-Is07', 'IsMasterSQL', 'aka is07', dbo.QBM_FCVElementToObjectKey1('QBMServerTag', 'UID_QBMServerTag'19, 'QBM-ST-Is07') , @XUser, @XUser, @xdate, @xdate end if exists (select top 1 1 from QBMModuleDef d where d.ModuleName = 'SIM' ) begin select @server =20 'cloudserver-0' select @UID_NewObject = '5DC98AAD-A7F7-4BA9-B66C-5C0B1DF641F2' end else begin select @UID_NewObject = NEWID() select @server = left(trim21(@@servername), 64)  end if charindex(N'\' , @server) > 0 begin select @server = substring(@server, 1, charindex(N'\' , @server)-1) end  if not exists 22(select top 1 1 from QBMServerHasServerTag ht where ht.UID_QBMServerTag = 'QBM-ST-Is07'  ) begin  if exists (select top 1 1 from QBMServer where ident_server23 = @server ) begin insert into QBMServerHasServerTag (UID_QBMServer, UID_QBMServerTag, XObjectKey , XUserInserted, XUserUpdated, XDateInserted, XDateUpdated24 ) select s.uid_QBMServer, t.UID_QBMServerTag, dbo.QBM_FCVElementToObjectKey2('QBMServerHasServerTag', 'UID_QBMServer', s.UID_QBMServer, 'UID_QBMServerTag'25, t.UID_QBMServerTag) , @XUser, @XUser, @xdate, @xdate from QBMServer s, QBMServerTag t where t.UID_QBMServerTag = 'QBM-ST-Is07'  and s.Ident_Server = 26@server select @DebugMessage = 'server "' + @server + '" updated as MasterSQL (Is07)' exec QBM_PJournal @DebugMessage, @@PROCID, 'D', @DebugLevel end else27 begin  insert into QBMServer (UID_QBMServer, Ident_Server, QueueName, XObjectKey , XUserInserted, XUserUpdated, XDateInserted, XDateUpdated  ) values 28(@UID_NewObject, @server , N'\' + @server , dbo.QBM_FCVElementToObjectKey1('QBMServer', 'UID_QBMServer', @UID_NewObject) , @XUser, @XUser, @xdate, @xdate29 ) insert into QBMServerHasServerTag (UID_QBMServer, UID_QBMServerTag, XObjectKey , XUserInserted, XUserUpdated, XDateInserted, XDateUpdated ) select @UID_NewObject30, t.UID_QBMServerTag, dbo.QBM_FCVElementToObjectKey2('QBMServerHasServerTag', 'UID_QBMServer', @UID_NewObject, 'UID_QBMServerTag', t.UID_QBMServerTag) 31, @XUser, @XUser, @xdate, @xdate from QBMServerTag t where t.UID_QBMServerTag = 'QBM-ST-Is07'  select @DebugMessage = 'server "' + @server + '" created as MasterSQL (Is07)'32 exec QBM_PJournal @DebugMessage, @@PROCID, 'D', @DebugLevel end   delete QBMServerHasServerTag where UID_QBMServerTag = 'QBM-ST-Is07' and UID_QBMServer33 not in (select UID_QBMServer from QBMServer where ident_server = @server ) end   if not exists (select top 1 1 from QBMServerHasServerTag ht where ht.UID_QBMServerTag34 = 'QBM-ST-Is12'  ) begin  select @server = N'' select top 1 @server = ident_server from QBMServer s join QBMServerHasServerTag sht on s.UID_QBMServer 35= sht.UID_QBMServer where sht.UID_QBMServerTag = 'QBM-ST-Is07'  insert into QBMServerHasServerTag (UID_QBMServer, UID_QBMServerTag, XObjectKey , XUserInserted36, XUserUpdated, XDateInserted, XDateUpdated ) select s.uid_QBMServer, t.UID_QBMServerTag, dbo.QBM_FCVElementToObjectKey2('QBMServerHasServerTag', 'UID_QBMServer'37, s.UID_QBMServer, 'UID_QBMServerTag', t.UID_QBMServerTag) , @XUser, @XUser, @xdate, @xdate from QBMServer s, QBMServerTag t where t.UID_QBMServerTag =38 'QBM-ST-Is12'  and s.Ident_Server = @server select @DebugMessage = 'server "' + @server + '" updated as Mailserver (is12)' exec QBM_PJournal @DebugMessage39, @@PROCID, 'D', @DebugLevel end   insert into QBMServerHasServerTag(UID_QBMServer, UID_QBMServerTag , XUserInserted, XUserUpdated, XDateInserted, XDateUpdated40 , XObjectKey ) select sht.UID_QBMServer, 'QBM-ST-SQLProcessingEnabled' , 'DBMigration', 'DBMigration', GETUTCDATE(), GETUTCDATE() , dbo.QBM_FCVElementToObjectKey241('QBMServerHasServerTag', 'UID_QBMServer', sht.UID_QBMServer, 'UID_QBMServerTag', 'QBM-ST-SQLProcessingEnabled') from QBMServerHasServerTag sht where sht.UID_QBMServerTag42 = 'QBM-ST-Is07' and not exists (select top 1 1 from QBMServerHasServerTag sht2 where sht2.UID_QBMServer = sht.UID_QBMServer and sht2.UID_QBMServerTag 43= 'QBM-ST-SQLProcessingEnabled' )  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow44() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return end 45