Chip 1999 September/CHIPCD_9_99.iso/software/uaktualnienia/OptionPackPL/msmqocm.cab/CREATEDB.SQL

Text File | 1998-04-07 | 8.9 KB | 846 lines

create procedure msmq_check_existence
    (@DatabaseName  varchar(255))
as





    declare @DatabaseExists bit
    select @DatabaseExists = 0
    select @DatabaseExists = 1
        from sysdatabases
        where name = @DatabaseName
    if (@DatabaseExists = 1)
    begin
        return(184)
    end





    declare @DeviceExists bit
    select @DeviceExists = 0

    select @DeviceExists = 1
        from sysdevices
        where name = @DatabaseName + 'Data'
    if (@DeviceExists = 1)
    begin
        return(187)
    end

    select @DeviceExists = 1
        from sysdevices
        where name = @DatabaseName + 'Log'
    if (@DeviceExists = 1)
    begin
        return(191)
    end


    return(2)
go

create procedure msmq_create_device
    (@ISQLPath              varchar(255),
     @DeviceName            varchar(255),
     @DeviceDirectory       varchar(255),
     @DeviceSize            int,
     @CANNOT_CREATE_DEVICE  int,
     @DEVICE_EXISTS         int,
     @DEVICE_IN_USE         int,
     @FILE_EXISTS           int
     )
as
    declare @retcode int

    if @@microsoftversion < 0x07000000
        exec @retcode = msmq_create_device_6 @ISQLPath,
            @DeviceName,
            @DeviceDirectory,
            @DeviceSize,
            @CANNOT_CREATE_DEVICE,
                @DEVICE_EXISTS,
            @DEVICE_IN_USE,
            @FILE_EXISTS
    else
            exec @retcode = msmq_create_device_7 @ISQLPath,
            @DeviceName,
            @DeviceDirectory,
            @DeviceSize,
            @CANNOT_CREATE_DEVICE,
                @DEVICE_EXISTS,
            @DEVICE_IN_USE,
            @FILE_EXISTS

        return @retcode

go


create procedure msmq_create_device_6
    (@ISQLPath              varchar(255),
     @DeviceName            varchar(255),
     @DeviceDirectory       varchar(255),
     @DeviceSize            int,
     @CANNOT_CREATE_DEVICE  int,
     @DEVICE_EXISTS         int,
     @DEVICE_IN_USE         int,
     @FILE_EXISTS           int
     )
as





    declare @DeviceAlreadyUsed bit
    select @DeviceAlreadyUsed = 0
    select @DeviceAlreadyUsed = 1
        from sysusages
        where vstart between
            (select low from sysdevices where name = @DeviceName) and
            (select high from sysdevices where name = @DeviceName)
    if (@DeviceAlreadyUsed = 1)
    begin
        return(@DEVICE_IN_USE)
    end





    declare @DeviceExists bit
    select @DeviceExists = 0
    select @DeviceExists = 1
        from sysdevices
        where name = @DeviceName
    if (@DeviceExists = 1)
    begin
        return(@DEVICE_EXISTS)
    end





    declare @DevicePath varchar(255)
    select @DevicePath = @DeviceDirectory + '\' + @DeviceName + '.dat'
    declare @Command varchar(255)
    select @Command = 'dir ' + @DevicePath
    declare @DirectoryResult int
    execute @DirectoryResult = xp_cmdshell @Command, no_output
    if (@DirectoryResult = 0)
    begin
        return(@FILE_EXISTS)
    end





    declare @VirtualDeviceNum int
    select @VirtualDeviceNum = 0
    select @VirtualDeviceNum = (min(low)/0x01000000)+1
        from sysdevices SysDevices1
        where low/0x01000000 between 0 and 254
        and not exists
            (select * from sysdevices SysDevices2
                where SysDevices2.low/0x01000000 = (SysDevices1.low/0x01000000)+1)
    if (@VirtualDeviceNum = 0)
    begin
        return(185)
    end





    select @Command =
        @ISQLPath + ' -E -d master -Q "EXIT(' +
            'disk init' +
                ' name = ' + @DeviceName +
                ', physname = ''' + @DevicePath + '''' +
                ', size = ' + convert(varchar(8), @DeviceSize * 512) +
                ', vdevno = ' + convert(varchar(3), @VirtualDeviceNum) + ')"'
    declare @DeviceCreated int
    execute @DeviceCreated = xp_cmdshell @Command, no_output




    if (@DeviceCreated <> 0)
    begin
        select @Command = 'del ' + @DevicePath
        execute xp_cmdshell @Command, no_output
        return(@CANNOT_CREATE_DEVICE)
    end


    return(1)
go

create procedure msmq_create_device_7
    (@ISQLPath              varchar(255),
     @DeviceName            varchar(255),
     @DeviceDirectory       varchar(255),
     @DeviceSize            int,
     @CANNOT_CREATE_DEVICE  int,
     @DEVICE_EXISTS         int,
     @DEVICE_IN_USE         int,
     @FILE_EXISTS           int
     )
as


    declare @DeviceExists bit
    select @DeviceExists = 0
    select @DeviceExists = 1
        from sysdevices
        where name = @DeviceName
    if (@DeviceExists = 1)
    begin
select 'device exists'
        return(@DEVICE_EXISTS)
    end

    declare @DevicePath varchar(255)
    select @DevicePath = @DeviceDirectory + '\' + @DeviceName + '.dat'
    declare @Command varchar(255)
    select @Command = 'dir ' + @DevicePath
    declare @DirectoryResult int
    execute @DirectoryResult = xp_cmdshell @Command, no_output
    if (@DirectoryResult = 0)
    begin
        return(@FILE_EXISTS)
    end


    select @Command = 'disk init' +
                ' name = ' + @DeviceName +
                ', physname = ''' + @DevicePath + '''' +
                ', size = ' + convert(varchar(8), @DeviceSize * 512)

    exec (@Command)

    if @@error <> 0
    begin
        select @Command = 'del ' + @DevicePath
        execute xp_cmdshell @Command, no_output
        return(@CANNOT_CREATE_DEVICE)
    end

    return(1)
go

create procedure msmq_create_database
    (@ISQLPath              varchar(255),
     @DatabaseName          varchar(255),
     @DataDeviceDirectory   varchar(255),
     @DataDeviceSize        int,
     @LogDeviceDirectory    varchar(255),
     @LogDeviceSize         int)
as





    declare @DatabaseExists bit
    select @DatabaseExists = 0
    select @DatabaseExists = 1
        from sysdatabases
        where name = @DatabaseName
    if (@DatabaseExists = 1)
    begin
        return(184)
    end






    declare @DataDeviceName varchar(255)
    select @DataDeviceName = @DatabaseName + 'Data'
    declare @DeviceCreated int
    execute @DeviceCreated = msmq_create_device
                                @ISQLPath,
                                @DataDeviceName,
                                @DataDeviceDirectory,
                                @DataDeviceSize,
                                186,
                                187,
                                188,
                                189
    if (@DeviceCreated <> 1)
    begin
        return(@DeviceCreated)
    end





    declare @LogDeviceName varchar(255)
    select @LogDeviceName = @DatabaseName + 'Log'
    execute @DeviceCreated = msmq_create_device
                                @ISQLPath,
                                @LogDeviceName,
                                @LogDeviceDirectory,
                                @LogDeviceSize,
                                190,
                                191,
                                192,
                                193
    if (@DeviceCreated <> 1)
    begin
        execute sp_dropdevice @DataDeviceName, delfile
        return(@DeviceCreated)
    end





    declare @Command varchar(255)
    select @Command =
        'create database ' + @DatabaseName +
            ' on ' + @DataDeviceName + ' = ' + convert(varchar(4), @DataDeviceSize) +
            ' log on ' + @LogDeviceName + ' = ' + convert(varchar(4), @LogDeviceSize)
    execute(@Command)
    if (@@error <> 0)
    begin
        execute sp_dropdevice @DataDeviceName, delfile
        execute sp_dropdevice @LogDeviceName, delfile
        return(183)
    end





    execute sp_dboption MQIS, 'trunc. log on chkpt.', true


    return(0)
go