Just ran the first Live to Test refresh since converting to v11. Do I have to re-run TIM against Test also? I’m getting those nasty services errors logging in to Tessitura which I believe are TIM related.
David - there's new SQL stuff you have to do in a v11 copy from live to test. I think it is in the v10 to v11 copy documentation and I swear I saw it somewhere else... But this is what I've been using just after the securecheck step.
Hope this makes sense. If I can find the official doc I'll put a link later!
use impresario
GO
declare @schema_name varchar(100)
select @schema_name = 'SqlDependencySchema'
declare @objects table (
id int identity(1,1) not null,
name varchar(255) not null,
obj_id int not null,
xtype varchar(10) not null)
insert @objects
select b.name, b.id, b.xtype
from sys.schemas a
join sysobjects b on a.schema_id = b.uid
where a.name = @schema_name
and b.name like 'SqlQueryNotification%'
declare @sql varchar(255)
declare @obj_name varchar(255)
declare @obj_id int
declare @obj_type varchar(10)
declare @svc_name varchar(255)
declare @ct int
select @ct = 1
while @ct <= (select MAX(id) from @objects)
begin
select @obj_name = name,
@obj_id = obj_id,
@obj_type = xtype
from @objects
where id = @ct
if @obj_type = 'SQ'
select @svc_name = name
from sys.services
where service_queue_id = @obj_id
if coalesce(@svc_name,'') <> ''
select @sql = 'IF EXISTS (SELECT * FROM sys.services WHERE name = ''' + @svc_name + ''')'
select @sql = @sql + ' BEGIN '
select @sql = @sql + 'DROP SERVICE [' + @svc_name + ']'
select @sql = @sql + ' END'
exec (@sql)
end
if coalesce(@obj_name,'') <> ''
select @sql = 'DROP QUEUE [' + @schema_name + '].[' + @obj_name + ']'
if @obj_type = 'P'
select @sql = 'DROP PROCEDURE [' + @schema_name + '].[' + @obj_name + ']'
select @ct = @ct + 1
-- Drop Services User
USE [impresario]
/****** Object: Schema [tessApi] Script Date: 12/18/2011 21:38:09 ******/
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'tessApi')
DROP SCHEMA [tessApi]
/****** Object: Schema [SqlDependencySchema] Script Date: 12/18/2011 21:38:21 ******/
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SqlDependencySchema')
DROP SCHEMA [SqlDependencySchema]
/****** Object: User [tessApi] Script Date: 12/18/2011 21:38:43 ******/
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')
DROP USER [tessApi]
USE [impresario_cci]
--/****** Object: Login [tessApi] Script Date: 12/18/2011 21:40:07 ******/
--IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'tessApi')
--DROP LOGIN [tessApi]
--GO
-- Create Services User
USE IMPRESARIO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tessApi')
CREATE USER [tessApi] FOR LOGIN [tessApi]
WITH DEFAULT_SCHEMA = [SqlDependencySchema]
ALTER USER [tessApi] WITH DEFAULT_SCHEMA=[SqlDependencySchema]
CREATE SCHEMA [SqlDependencySchema] AUTHORIZATION [tessApi]
EXEC sp_addrolemember 'tessitura_sql_dependency_role', 'tessApi'
EXEC sp_addrolemember 'impusers', 'tessApi'
USE impresario_cci
-- Enable Service Broker
declare @query nvarchar(255)
If NOT EXISTS (select * From sys.databases Where is_broker_enabled = 1 and name = 'impresario')
Begin
SET @query = N'ALTER DATABASE impresario SET ENABLE_BROKER with rollback immediate'
Exec sp_executesql @query
End