1C — 1C + MS SQL Server 2012 Ошибка sp_dboption

Ошибка при обновлении конфигурации, связанная с использованием процедуры sp_dboption для перевода базы в SINGLE USER режим на дальнейшую работу не влияет.
Её можно избежать создав эту процедуру вручную в базе master. Скрипт создания процедуры прилагается.

USE [master]
GO
 
 
/****** Object: StoredProcedure [dbo].[sp_dboption] Script Date: 21.03.2012 7:33:37 ******/
 
 
SET ANSI_NULLS ON
GO
 
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[sp_dboption] -- 1999/08/09 18:25
@dbname sysname = NULL, -- database name to change
@optname VARCHAR(35) = NULL, -- option name to turn on/off
@optvalue VARCHAR(10) = NULL -- true or false
AS
 
 
SET nocount ON
DECLARE @dbid INT -- dbid of the database
DECLARE @catvalue INT -- number of category option
DECLARE @optcount INT -- number of options like @optname
DECLARE @allstatopts INT -- bit map off all options stored in sysdatqabases.status
-- that can be set by sp_dboption.
DECLARE @alloptopts INT -- bit map off all options stored in sysdatqabases.status
-- that can be set by sp_dboption.
DECLARE @allcatopts INT -- bit map off all options stored in sysdatqabases.category
-- that can be set by sp_dboption.
DECLARE @exec_stmt nvarchar(MAX)
DECLARE @fulloptname VARCHAR(35)
DECLARE @alt_optname VARCHAR(50)
DECLARE @alt_optvalue VARCHAR(30)
DECLARE @optnameIn VARCHAR(35)
 
 
SELECT @optnameIn = @optname
,@optname = LOWER (@optname COLLATE Latin1_General_CI_AS)
-- If no @dbname given, just list the possible dboptions.
-- Only certain status bits may be set or cleared by sp_dboption.
-- Get bitmap of all options that can be set by sp_dboption.
SELECT @allstatopts=NUMBER FROM master.dbo.spt_values WHERE TYPE = 'D'
AND name = 'ALL SETTABLE OPTIONS'
SELECT @allcatopts=NUMBER FROM master.dbo.spt_values WHERE TYPE = 'DC'
AND name = 'ALL SETTABLE OPTIONS'
SELECT @alloptopts=NUMBER FROM master.dbo.spt_values WHERE TYPE = 'D2'
AND name = 'ALL SETTABLE OPTIONS'
IF @dbname IS NULL
BEGIN
SELECT 'Settable database options:' = name
FROM master.dbo.spt_values
WHERE (TYPE = 'D'
AND NUMBER & @allstatopts <> 0
AND NUMBER NOT IN (0,@allstatopts)) -- Eliminate non-option entries
OR (TYPE = 'DC'
AND NUMBER & @allcatopts <> 0
AND NUMBER NOT IN (0,@allcatopts))
OR (TYPE = 'D2'
AND NUMBER & @alloptopts <> 0
AND NUMBER NOT IN (0,@alloptopts))
ORDER BY name
RETURN (0)
END
-- Verify the database name and get info
SELECT @dbid = dbid
FROM master.dbo.sysdatabases
WHERE name = @dbname
-- If @dbname not found, say so and list the databases.
IF @dbid IS NULL
BEGIN
raiserror(15010,-1,-1,@dbname)
print ' '
SELECT 'Available databases:' = name
FROM master.dbo.sysdatabases
RETURN (1)
END
-- If no option was supplied, display current settings.
IF @optname IS NULL
BEGIN
SELECT 'The following options are set:' = v.name
FROM master.dbo.spt_values v, master.dbo.sysdatabases d
WHERE d.name=@dbname
AND ((NUMBER & @allstatopts <> 0
AND NUMBER NOT IN (-1,@allstatopts)
AND v.type = 'D'
AND (v.number & d.status)=v.number)
OR (NUMBER & @allcatopts <> 0
AND NUMBER NOT IN (-1,@allcatopts)
AND v.type = 'DC'
AND d.category & v.number <> 0)
OR (NUMBER & @alloptopts <> 0
AND NUMBER NOT IN (-1,@alloptopts)
AND v.type = 'D2'
AND d.status2 & v.number <> 0))
RETURN(0)
END
IF @optvalue IS NOT NULL AND LOWER(@optvalue) NOT IN ('true', 'false', 'on', 'off')
BEGIN
raiserror(15241,-1,-1)
RETURN (1)
END
-- Use @optname and try to find the right option.
-- If there isn't just one, print appropriate diagnostics and return.
SELECT @optcount = COUNT(*) ,@fulloptname = MIN(name)
FROM master.dbo.spt_values
WHERE LOWER(name COLLATE Latin1_General_CI_AS) LIKE '%' + @optname + '%'
AND ((TYPE = 'D'
AND NUMBER & @allstatopts <> 0
AND NUMBER NOT IN (-1,@allstatopts))
OR (TYPE = 'DC'
AND NUMBER & @allcatopts <> 0
AND NUMBER NOT IN (-1,@allcatopts))
OR (TYPE = 'D2'
AND NUMBER & @alloptopts <> 0
AND NUMBER NOT IN (-1,@alloptopts)))
-- If no option, show the user what the options are.
IF @optcount = 0
BEGIN
raiserror(15011,-1,-1,@optnameIn)
print ' '
SELECT 'Settable database options:' = name
FROM master.dbo.spt_values
WHERE (TYPE = 'D'
AND NUMBER & @allstatopts <> 0
AND NUMBER NOT IN (-1,@allstatopts)) -- Eliminate non-option entries
OR (TYPE = 'DC'
AND NUMBER & @allcatopts <> 0
AND NUMBER NOT IN (-1,@allcatopts))
OR (TYPE = 'D2'
AND NUMBER & @alloptopts <> 0
AND NUMBER NOT IN (-1,@alloptopts))
ORDER BY name
RETURN (1)
END
-- If more than one option like @optname, show the duplicates and return.
IF @optcount > 1
BEGIN
raiserror(15242,-1,-1,@optnameIn)
print ' '
SELECT duplicate_options = name
FROM master.dbo.spt_values
WHERE LOWER(name COLLATE Latin1_General_CI_AS) LIKE '%' + @optname + '%'
AND ((TYPE = 'D'
AND NUMBER & @allstatopts <> 0
AND NUMBER NOT IN (-1,@allstatopts))
OR (TYPE = 'DC'
AND NUMBER & @allcatopts <> 0
AND NUMBER NOT IN (-1,@allcatopts))
OR (TYPE = 'D2'
AND NUMBER & @alloptopts <> 0
AND NUMBER NOT IN (-1,@alloptopts))
)
RETURN (1)
END
-- Just want to see current setting of specified option.
IF @optvalue IS NULL
BEGIN
SELECT OptionName = v.name,
CurrentSetting = (CASE
WHEN ( ((v.number & d.status) = v.number
AND v.type = 'D')
OR (d.category & v.number <> 0
AND v.type = 'DC')
OR (d.status2 & v.number <> 0
AND v.type = 'D2')
)
THEN 'ON'
WHEN NOT
( ((v.number & d.status) = v.number
AND v.type = 'D')
OR (d.category & v.number <> 0
AND v.type = 'DC')
OR (d.status2 & v.number <> 0
AND v.type = 'D2')
)
THEN 'OFF'
END)
FROM master.dbo.spt_values v, master.dbo.sysdatabases d
WHERE d.name=@dbname
AND ((v.number & @allstatopts <> 0
AND v.number NOT IN (-1,@allstatopts) -- Eliminate non-option entries
AND v.type = 'D')
OR (v.number & @allcatopts <> 0
AND v.number NOT IN (-1,@allcatopts) -- Eliminate non-option entries
AND v.type = 'DC')
OR (v.number & @alloptopts <> 0
AND v.number NOT IN (-1,@alloptopts) -- Eliminate non-option entries
AND v.type = 'D2')
)
AND LOWER(v.name) = LOWER(@fulloptname)
RETURN (0)
END
SELECT @catvalue = 0
SELECT @catvalue = NUMBER
FROM master.dbo.spt_values
WHERE LOWER(name) = LOWER(@fulloptname)
AND TYPE = 'DC'
-- if setting replication option, call sp_replicationdboption directly
IF (@catvalue <> 0)
BEGIN
SELECT @alt_optvalue = (CASE LOWER(@optvalue)
WHEN 'true' THEN 'true'
WHEN 'on' THEN 'true'
ELSE 'false'
END)
SELECT @alt_optname = (CASE @catvalue
WHEN 1 THEN 'publish'
WHEN 2 THEN 'subscribe'
WHEN 4 THEN 'merge publish'
ELSE quotename(@fulloptname, '''')
END)
SELECT @exec_stmt = quotename(@dbname, '[') + '.dbo.sp_replicationdboption'
EXEC @exec_stmt @dbname, @alt_optname, @alt_optvalue
RETURN (0)
END
 
 
-- call Alter Database to set options
-- set option value in alter database
SELECT @alt_optvalue = (CASE LOWER(@optvalue)
WHEN 'true' THEN 'ON'
WHEN 'on' THEN 'ON'
ELSE 'OFF'
END)
-- set option name in alter database
SELECT @fulloptname = LOWER(@fulloptname)
SELECT @alt_optname = (CASE @fulloptname
WHEN 'auto create statistics' THEN 'AUTO_CREATE_STATISTICS'
WHEN 'auto update statistics' THEN 'AUTO_UPDATE_STATISTICS'
WHEN 'autoclose' THEN 'AUTO_CLOSE'
WHEN 'autoshrink' THEN 'AUTO_SHRINK'
WHEN 'ansi padding' THEN 'ANSI_PADDING'
WHEN 'arithabort' THEN 'ARITHABORT'
WHEN 'numeric roundabort' THEN 'NUMERIC_ROUNDABORT'
WHEN 'ansi null default' THEN 'ANSI_NULL_DEFAULT'
WHEN 'ansi nulls' THEN 'ANSI_NULLS'
WHEN 'ansi warnings' THEN 'ANSI_WARNINGS'
WHEN 'concat null yields null' THEN 'CONCAT_NULL_YIELDS_NULL'
WHEN 'cursor close on commit' THEN 'CURSOR_CLOSE_ON_COMMIT'
WHEN 'torn page detection' THEN 'TORN_PAGE_DETECTION'
WHEN 'quoted identifier' THEN 'QUOTED_IDENTIFIER'
WHEN 'recursive triggers' THEN 'RECURSIVE_TRIGGERS'
WHEN 'default to local cursor' THEN 'CURSOR_DEFAULT'
WHEN 'offline' THEN (CASE @alt_optvalue WHEN 'ON' THEN 'OFFLINE' ELSE 'ONLINE' END)
WHEN 'read only' THEN (CASE @alt_optvalue WHEN 'ON' THEN 'READ_ONLY' ELSE 'READ_WRITE' END)
WHEN 'dbo use only' THEN (CASE @alt_optvalue WHEN 'ON' THEN 'RESTRICTED_USER' ELSE 'MULTI_USER' END)
WHEN 'single user' THEN (CASE @alt_optvalue WHEN 'ON' THEN 'SINGLE_USER' ELSE 'MULTI_USER' END)
WHEN 'select into/bulkcopy' THEN 'RECOVERY'
WHEN 'trunc. log on chkpt.' THEN 'RECOVERY'
WHEN 'db chaining' THEN 'DB_CHAINING'
ELSE @alt_optname
END)
IF @fulloptname = 'dbo use only'
BEGIN
IF @alt_optvalue = 'ON'
BEGIN
IF databaseproperty(@dbname, 'IsSingleUser') = 1
BEGIN
raiserror(5066,-1,-1);
RETURN (1)
END
END
ELSE
BEGIN
IF databaseproperty(@dbname, 'IsDBOOnly') = 0
RETURN (0)
END
END
IF @fulloptname = 'single user'
BEGIN
IF @alt_optvalue = 'ON'
BEGIN
IF databaseproperty(@dbname, 'ISDBOOnly') = 1
BEGIN
raiserror(5066,-1,-1);
RETURN (1)
END
END
ELSE
BEGIN
IF databaseproperty(@dbname, 'IsSingleUser') = 0
RETURN (0)
END
END
SELECT @alt_optvalue = (CASE @fulloptname
WHEN 'default to local cursor' THEN (CASE @alt_optvalue WHEN 'ON' THEN 'LOCAL' ELSE 'GLOBAL' END)
WHEN 'offline' THEN ''
WHEN 'read only' THEN ''
WHEN 'dbo use only' THEN ''
WHEN 'single user' THEN ''
ELSE @alt_optvalue
END)
IF LOWER(@fulloptname) = 'select into/bulkcopy'
BEGIN
IF @alt_optvalue = 'ON'
BEGIN
IF databaseproperty(@dbname, 'IsTrunclog') = 1
SELECT @alt_optvalue = 'RECMODEL_70BACKCOMP'
ELSE
SELECT @alt_optvalue = 'BULK_LOGGED'
END
ELSE
BEGIN
IF databaseproperty(@dbname, 'IsTrunclog') = 1
SELECT @alt_optvalue = 'SIMPLE'
ELSE
SELECT @alt_optvalue = 'FULL'
END
END
IF LOWER(@fulloptname) = 'trunc. log on chkpt.'
BEGIN
IF @alt_optvalue = 'ON'
BEGIN
IF databaseproperty(@dbname, 'IsBulkCopy') = 1
SELECT @alt_optvalue = 'RECMODEL_70BACKCOMP'
ELSE
SELECT @alt_optvalue = 'SIMPLE'
END
ELSE
BEGIN
IF databaseproperty(@dbname, 'IsBulkCopy') = 1
SELECT @alt_optvalue = 'BULK_LOGGED'
ELSE
SELECT @alt_optvalue = 'FULL'
END
END
-- construct the ALTER DATABASE command string
SELECT @exec_stmt = 'ALTER DATABASE ' + quotename(@dbname) + ' SET ' + @alt_optname + ' ' + @alt_optvalue + ' WITH NO_WAIT'
EXEC (@exec_stmt)
IF @@error <> 0
BEGIN
raiserror(15627,-1,-1)
RETURN (1)
END
RETURN (0) -- sp_dboption
GO

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *