LiteSQL2014-Release

/**********************************************************************/ /* PRE_MSDB.SQL */ /* */ /* Prepares MSDB for upgrade. */ /* */ /* ** Copyright (c) Microsoft Corporation ** All Rights Reserved. */ /**********************************************************************/ PRINT '----------------------------------' PRINT 'Starting execution of PRE_MSDB.SQL' PRINT '----------------------------------' use msdb go -- If the user sets implicit_transactions on, some specprocs and DBCC commands will fail -- Need to save the state and disable implicit_transactions DECLARE @implicit_transactions_flag INT DECLARE @is_implicit_transactions_set BIT SELECT @is_implicit_transactions_set = CONVERT(BIT, value) FROM fn_listextendedproperty(N'IMPLICIT_TRANSACTIONS', default, default, default, default, default, default); IF (@is_implicit_transactions_set IS NOT NULL) BEGIN EXEC sp_dropextendedproperty N'IMPLICIT_TRANSACTIONS' END SET @implicit_transactions_flag = 2 SET @is_implicit_transactions_set = @@options & @implicit_transactions_flag EXEC sp_addextendedproperty @name = N'IMPLICIT_TRANSACTIONS', @value = @is_implicit_transactions_set SET IMPLICIT_TRANSACTIONS OFF --set compatibily level to 100 EXEC sp_dbcmptlevel @dbname = 'msdb', @new_cmptlevel = '100' GO /**********************************************************************/ /* PRE_SQLAGENT100.SQL */ /* */ /* Upgrades 7.x, 8.x and 9.0 to 10.0 and drops all obsolete 8.x */ /* */ /* ** Copyright (c) Microsoft Corporation ** All Rights Reserved. */ /**********************************************************************/ PRINT '-----------------------------------------' PRINT 'Starting execution of PRE_SQLAGENT100.SQL' PRINT '-----------------------------------------' use msdb go -- Check that we're in msdb IF (DB_NAME() <> N'msdb') RAISERROR('A problem was encountered accessing msdb. upgrade script terminating.', 20, 127) WITH LOG go CHECKPOINT go --set compatibily level to 120 ALTER DATABASE msdb SET COMPATIBILITY_LEVEL = 120 GO -- Allow updates to system catalogs so that we can fully manipulate our system objects EXECUTE master.dbo.sp_configure N'allow updates', 1 go RECONFIGURE WITH OVERRIDE go /**************************************************************/ /* Record time of start of creates */ /**************************************************************/ SELECT start = getdate() INTO #InstMsdb go --preserve existing object permnission during upgrade --create perms table IF (NOT OBJECT_ID(N'dbo.upgrade_perms', 'U') IS NULL) BEGIN DROP TABLE dbo.upgrade_perms END -- upgrade_perms is filled with current permission of objects in MSDB -- the structure of table is: -- state_desc = GRANT|DENY -- permission_name = SELECT|EXECUTE|UPDATE ... -- object_name = grantor name -- grantee_name = grantee name CREATE TABLE dbo.upgrade_perms(state_desc nvarchar(60), permission_name sysname, object_name sysname, grantee_name sysname) CREATE INDEX indnc ON dbo.upgrade_perms(object_name) DECLARE @state_desc nvarchar(60) DECLARE @permission_name sysname DECLARE @object_name sysname DECLARE @grantee_name sysname DECLARE perms_cursor CURSOR LOCAL FOR SELECT state_desc, permission_name, OBJECT_NAME(major_id), USER_NAME(grantee_principal_id) from msdb.sys.database_permissions WHERE state_desc IS NOT NULL AND permission_name IS NOT NULL AND OBJECT_NAME(major_id) IS NOT NULL AND USER_NAME(grantee_principal_id) IS NOT NULL OPEN perms_cursor FETCH NEXT FROM perms_cursor INTO @state_desc, @permission_name, @object_name, @grantee_name WHILE (@@fetch_status = 0) BEGIN INSERT dbo.upgrade_perms(state_desc, permission_name, object_name, grantee_name) VALUES(@state_desc, @permission_name, @object_name, @grantee_name) FETCH NEXT FROM perms_cursor INTO @state_desc, @permission_name, @object_name, @grantee_name END DEALLOCATE perms_cursor go ------------------------VIEWS UPGRADE--------------------------------------- ------------------------TABLE UPGRADE--------------------------------------- --create an populate sysoriginatingservers use msdb go IF (NOT EXISTS (SELECT * --just a safe belt, this table shouldn't be in 8.x FROM msdb.dbo.sysobjects WHERE (name = N'sysoriginatingservers') AND (type = 'U'))) BEGIN PRINT '' PRINT 'Creating table sysoriginatingservers...' CREATE TABLE dbo.sysoriginatingservers ( -- There is only a single MSX server record in this table (originating_server_id = 1) -- 0 is generated by sysoriginatingservers_view and indicates the local server originating_server_id INT CONSTRAINT CK_originating_server_id_MustBe_1 CHECK (originating_server_id = 1) DEFAULT (1) UNIQUE CLUSTERED, originating_server sysname NOT NULL UNIQUE NONCLUSTERED, --Mark this record as a MSX server entry master_server bit CONSTRAINT CK_master_server_MustBe_1 CHECK (master_server = 1) DEFAULT (1) ) END go IF (NOT EXISTS (SELECT t.name FROM msdb.sys.all_columns c JOIN msdb.sys.all_objects t ON c.object_id = t.object_id WHERE c.name = 'originating_server_id' and t.name = 'sysjobs' and t.type = 'U')) BEGIN PRINT '' PRINT 'Adding column originating_server_id to table sysjobs...' --add new column 9.0 originating_server_id ALTER TABLE sysjobs WITH NOCHECK ADD originating_server_id INT NULL END go DECLARE @MSXServerName sysname DECLARE @LocalServerName sysname DECLARE @UpdateOrgServerTSQL nvarchar(MAX) SELECT @LocalServerName = UPPER(CONVERT(sysname, SERVERPROPERTY('servername'))) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'MSXServerName', @MSXServerName OUTPUT, N'no_output' SELECT @MSXServerName = LTRIM(RTRIM(UPPER(@MSXServerName))) IF (@MSXServerName = '') SELECT @MSXServerName = NULL IF (@MSXServerName IS NOT NULL) BEGIN IF (NOT EXISTS( SELECT * FROM dbo.sysoriginatingservers WHERE originating_server_id = 1 AND originating_server = @MSXServerName AND master_server = 1)) BEGIN PRINT '' PRINT 'Populate table sysoriginatingservers...' INSERT INTO sysoriginatingservers( originating_server_id, originating_server, master_server) VALUES(1, @MSXServerName, 1) END END IF (EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'sysjobs') AND (type = 'U'))) BEGIN IF (EXISTS (SELECT t.name FROM msdb.sys.all_columns c JOIN msdb.sys.all_objects t ON c.object_id = t.object_id WHERE c.name = 'originating_server' and t.name = 'sysjobs' and t.type = 'U')) BEGIN PRINT '' PRINT 'Populate new column originating_server_id of table sysjobs...' --set this column based on the value of 8.0 only column originating_server --if MSX server is NULL we come up with server name that cannot exit, a generated GUID SELECT @UpdateOrgServerTSQL = ' UPDATE sysjobs SET originating_server_id = CASE UPPER(originating_server) W