v7: Таблица 1SJOURN SQL

Привет. Не мое. Точнее частично. К сожалению не помню автора - давно это было.
К этому еще можно пользовательские блокировки прикрутить.

// Код для отключения блокировок
Function XOR(Val ParA, Val ParB)
    Res = 0;
    Koef = 1;
    For BitNumber = 1 To 8 Do
        BitA = ParA % 2;
        BitB = ParB % 2;
        ParA = Int(ParA / 2);
        ParB = Int(ParB / 2);
        BitC = (BitA + BitB) % 2;
        Res = Res + BitC * Koef;
        Koef = Koef * 2;
    EndDo;
    Return Res;
EndFunction

// индексы значений с списке
// server = 2, db = 4, uid = 6, pwd = 8, checksum = 10
Function ConnectionString()  export
    Var SQLKeyCode[36], ConnectCode[200];
    ConnectFile = IBDir() + "1Cv7.DBA";
    If FS.ExistFile(ConnectFile) = 0 Then
        Message("Это не SQL - база!", "!");
        Return (0);
    EndIF;
    FSO = CreateObject("Scripting.FileSystemObject");
    F = FSO.OpenTextFile(ConnectFile, 1);
    ConnectLen = 0;
    While F.AtEndOfStream = 0 Do
        ConnectLen = ConnectLen + 1;
        ConnectCode[ConnectLen] = Asc(F.Read(1));
    EndDo;
    F.Close();
    FSO = 0;
    SQLKey = "19465912879oiuxc ensdfaiuo3i73798kjl";
    For i = 1 To 36 Do
        SQLKeyCode[i] = Asc(Сред(SQLKey, i, 1));
    EndDo;
    Connect = "";
    For i = 1 To ConnectLen Do
        Connect = Connect + Chr(XOR(ConnectCode[i], SQLKeyCode[(i - 1) % 36 + 1]));
    EndDo;
    vl=createobject("ValueList");
    Connect=StrReplace(Connect,"{","");
    Connect=StrReplace(Connect,"}","");
    vl.fromSeparatedString(Connect);
    return (vl);
EndFunction

procedure patch_xp(check_error=0)
    if ExclusiveMode()=0 then
        if FS.ExistFile(IbDir()+"1cv7.dds")=1 then
            constr=ConnectionString();
            if ConStr<>0 then
                server=constr.getValue(2);database=constr.getValue(4);uid=constr.getValue(6); pwd=constr.getValue(8);
                con=createobject("ADODB.Connection"); con.cursorlocation=3; err="";
                con.open("provider=SQLOLEDB;data source="+trimall(server)+";DataBase="+trimall(database)+";uid="+trimall(uid)+";pwd="+""+trimall(pwd));

                qwerty="
                |alter procedure _1sp__1SUIDCTL_GetMaxID(@tid INTEGER, id CHAR(9) OUTPUT) AS
                |select id=MAXID from _1SUIDCTL(UPDLOCK)  where TYPEID=@tid
                |if id is null select id='         '";
                try
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не выполнена модификация счетчика идентификаторов документов и справочников..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;

                qwerty="
                |alter procedure _1sp__1SENTRY_MaxRowID(i INTEGER OUTPUT) AS
                |set nocount on
                |select i=@@IDENTITY
                |if i is null select i=0";
                try
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не выполнена модификация счетчика идентификаторов проводок..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;

                qwerty="
                |alter procedure _1sp__1SOPER_MaxRowID(i INTEGER OUTPUT) AS
                |set nocount on
                |select i=@@IDENTITY
                |if i is null select i=0";
                try
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не выполнена модификация счетчика идентификаторов операций..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;



                qwerty="
                |alter procedure _1sp__1SCONST_MaxRowID(i INTEGER OUTPUT) AS
                |set nocount on
                |select i=@@IDENTITY
                |if i is null select i=0";
                try
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не выполнена модификация счетчика идентификаторов констант..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;

                try
                    qwerty="
                    |ALTER  procedure _1sp__1SCONST_TLockX AS
                    |return";
                    con.execute(qwerty);

                    qwerty="
                    |ALTER  procedure _1sp__1SCONST_TLock AS
                    |return";
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не отключена блокировка таблицы констант..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;




                try
                    qwerty="
                    |ALTER  procedure _1sp__1SENTRY_TLockX AS
                    |return";
                    con.execute(qwerty);

                    qwerty="
                    |ALTER  procedure _1sp__1SENTRY_TLock AS
                    |return";
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не отключена блокировка таблицы проводок..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;


                try
                    qwerty="
                    |ALTER  procedure _1sp__1SOPER_TLockX AS
                    |return";
                    con.execute(qwerty);

                    qwerty="
                    |ALTER  procedure _1sp__1SOPER_TLock AS
                    |return";
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не отключена блокировка таблицы операций..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;


                try
                    qwerty="select name from sysobjects where name='u_1ssystem'";
                    rs= con.execute(qwerty);
                    if rs.recordcount()=0 then
                        qwerty="
                        |create trigger u_1ssystem on _1ssystem for update as
                        |set nocount on
                        |if update(eventidta)
                        |begin
                        |    declare @UserAccess as char(16)

                        |    select @UserAccess =
                        |cast((SELECT DATABASEPROPERTYEX(db_name(), 'UserAccess')) as char(16))

                        |    if  (@UserAccess='MULTI_USER') Or  @UserAccess='RESTRICTED_USER'
                        |    begin
                        |         IF EXISTS(select * from inserted i, deleted d where
                        |              d.curdate > i.curdate
                        |              or (d.curdate = i.curdate and d.curtime > i.curtime)
                        |              or (d.curdate = i.curdate and d.curtime = i.curtime and d.eventidta > i.eventidta)
                        |              )
                        |          BEGIN
                        |              update _1ssystem with (updlock)
                        |set curdate = d.curdate, curtime = d.curtime, eventidta = d.eventidta
                        |from deleted d
                        |          END
                        |    end
                        |end";
                        con.execute(qwerty);
                    endif;

                    qwerty="
                    |alter procedure _1sp__1SSYSTEM_TLock AS
                    |return";
                    con.execute(qwerty);

                    qwerty="
                    |alter procedure _1sp__1SSYSTEM_TLockX AS
                    |return";
                    con.execute(qwerty);



                except
                    err=err+?(err<>"",linebreak,"")+"Не изменена блокировка таблицы актуальности..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;

                //изменение блокировок справочников с монопольных на построчные
                qwerty="declare sc_cur cursor for
                |select name
                |from sysobjects
                |where (name like 'SC%') and (name not like '%NEW%')
                |and type = 'U' and OBJECTPROPERTY(id,'IsMSShipped') = 0
                |open sc_cur
                |declare @sc_name varchar(50)
                |declare @sql varchar(1000)
                |fetch next from sc_cur into @sc_name
                |while @@FETCH_STATUS = 0
                |begin
                |select @sql = 'alter procedure _1sp_'+@sc_name+'_TLock AS set nocount on declare i integer select i=1 from '+@sc_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |select @sql = 'alter procedure _1sp_'+@sc_name+'_TLockX AS set nocount on declare i integer select i=1 from '+@sc_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |fetch next from sc_cur into @sc_name
                |end
                |close sc_cur
                |DEALLOCATE sc_cur";
                con.execute(qwerty);
                //конец модуля изменения блокировок справочников


                //изменение блокировок движений регистров с монопольных на построчные
                qwerty="declare ra_cur cursor for
                |select name
                |from sysobjects
                |where (name like 'RA%')  and (name <> 'RAG_1sUSERS') and (name not like '%NEW%')
                |and type = 'U' and OBJECTPROPERTY(id,'IsMSShipped') = 0
                |open ra_cur
                |declare @ra_name varchar(50)
                |declare @sql varchar(1000)
                |fetch next from ra_cur into @ra_name
                |while @@FETCH_STATUS = 0
                |begin
                |select @sql = 'alter procedure _1sp_'+@ra_name+'_TLock AS set nocount on declare i integer select i=1 from '+@ra_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |select @sql = 'alter procedure _1sp_'+@ra_name+'_TLockX AS set nocount on declare i integer select i=1 from '+@ra_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |fetch next from ra_cur into @ra_name
                |end
                |close ra_cur
                |DEALLOCATE ra_cur";
                con.execute(qwerty);

                //изменение блокировок итогов регистров с монопольных на построчные
                qwerty="declare rg_cur cursor for
                |select name
                |from sysobjects
                |where (name like 'RG%') and (name not like '%NEW%')
                |and type = 'U' and OBJECTPROPERTY(id,'IsMSShipped') = 0
                |open rg_cur
                |declare @rg_name varchar(50)
                |declare @sql varchar(1000)
                |fetch next from rg_cur into @rg_name
                |while @@FETCH_STATUS = 0
                |begin
                |select @sql = 'alter procedure _1sp_'+@rg_name+'_TLock AS set nocount on declare i integer select i=1 from '+@rg_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |select @sql = 'alter procedure _1sp_'+@rg_name+'_TLockX AS set nocount on declare i integer select i=1 from '+@rg_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |fetch next from rg_cur into @rg_name
                |end
                |close rg_cur
                |DEALLOCATE rg_cur";
                con.execute(qwerty);



                //конец модуля изменения блокировок регистров

                //удаление блокировок журанала документов
                try
                    qwerty="
                    |alter procedure _1sp__1SJOURN_TLockX AS
                    |return";
                    con.execute(qwerty);

                    qwerty="
                    |alter procedure _1sp__1SJOURN_TLock AS
                    |return";
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не отключена блокировка журнала документов..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;




                //изменение блокировок журналов документов с монопольных на построчные
                qwerty="declare dt_cur cursor for
                |select name
                |from sysobjects
                |where (name like 'DT%') and (name not like '%NEW%')
                |and type = 'U' and OBJECTPROPERTY(id,'IsMSShipped') = 0
                |open dt_cur
                |declare @dt_name varchar(50)
                |declare @sql varchar(1000)
                |fetch next from dt_cur into @dt_name
                |while @@FETCH_STATUS = 0
                |begin
                |select @sql = 'alter procedure _1sp_'+@dt_name+'_TLock AS set nocount on declare i integer select i=1 from '+@dt_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |select @sql = 'alter procedure _1sp_'+@dt_name+'_TLockX AS set nocount on declare i integer select i=1 from '+@dt_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |fetch next from dt_cur into @dt_name
                |end
                |close dt_cur
                |DEALLOCATE dt_cur";
                con.execute(qwerty);

                qwerty="declare dh_cur cursor for
                |select name
                |from sysobjects
                |where (name like 'DH%') and (name not like '%NEW%')
                |and type = 'U' and OBJECTPROPERTY(id,'IsMSShipped') = 0
                |open dh_cur
                |declare @dh_name varchar(50)
                |declare @sql varchar(1000)
                |fetch next from dh_cur into @dh_name
                |while @@FETCH_STATUS = 0
                |begin
                |select @sql = 'alter procedure _1sp_'+@dh_name+'_TLock AS set nocount on declare i integer select i=1 from '+@dh_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |select @sql = 'alter procedure _1sp_'+@dh_name+'_TLockX AS set nocount on declare i integer select i=1 from '+@dh_name+' (ROWLOCK HOLDLOCK XLOCK) where 0=1'
                |execute(@sql)
                |fetch next from dh_cur into @dh_name
                |end
                |close dh_cur
                |DEALLOCATE dh_cur";
                con.execute(qwerty);




                //конец модуля изменения блокировок документов





                qwerty="
                |Delete from RAG_1sUSERS where usrscnt=0";
                try
                    con.execute(qwerty);
                except
                    err=err+?(err<>"",linebreak,"")+"Не выполнена очистка таблицы соединений..."+linebreak+
                    "Описание ошибки: "+geterrordescription()+linebreak+
                    "---------------------------------------------------------------------------------------";
                endtry;




                if check_error=1 then //выполним проверку-исправление возможных ошибок в БД.
                    qwerty="
                    |DELETE FROM _1sentry
                    |WHERE docid IN (SELECT iddoc FROM _1sjourn (nolock)
                    |WHERE closed=0)";

                    try
                        con.execute(qwerty);
                    except
                        err=err+?(err<>"",linebreak,"")+"Не выполнено удаление проводок по непроведенным документам..."+linebreak+
                        "Описание ошибки: "+geterrordescription()+linebreak+
                        "---------------------------------------------------------------------------------------";
                    endtry;

                    qwerty="
                    |DELETE FROM _1sentry
                    |WHERE docid NOT IN (SELECT iddoc FROM _1sjourn (nolock))";

                    try
                        con.execute(qwerty);
                    except
                        err=err+?(err<>"",linebreak,"")+"Не выполнено удаление проводок по несуществующим документам..."+linebreak+
                        "Описание ошибки: "+geterrordescription()+linebreak+
                        "---------------------------------------------------------------------------------------";
                    endtry;

                    qwerty="
                    |DELETE FROM _1sentry
                    |WHERE docid NOT IN (SELECT docid FROM _1soper (nolock))";
                    try
                        con.execute(qwerty);
                    except
                        err=err+?(err<>"",linebreak,"")+"Не выполнено удаление проводок по несуществующим операциям..."+linebreak+
                        "Описание ошибки: "+geterrordescription()+linebreak+
                        "---------------------------------------------------------------------------------------";
                    endtry;

                    qwerty="
                    |DELETE FROM _1soper
                    |WHERE docid NOT IN (SELECT iddoc FROM _1sjourn (nolock))";
                    try
                        con.execute(qwerty);
                    except
                        err=err+?(err<>"",linebreak,"")+"Не выполнено удаление операций по несуществующим документам..."+linebreak+
                        "Описание ошибки: "+geterrordescription()+linebreak+
                        "---------------------------------------------------------------------------------------";
                    endtry;
                endif;

                con.close();
            else
                err=err+?(err<>"",linebreak,"")+"Неверные параметры соединения с базой данных SQL сервера..."+linebreak+
                "---------------------------------------------------------------------------------------";
            endif;
            if err<>"" then
                err="Ошибки верификации механизма блокировок:"+linebreak+err;
                message (err);
            endif
        endif;
    endif;
endprocedure

Процедура ПриНачалеРаботыСистемы()
    patch_xp();
...