Microsoft SQL Server
Скрипты
T-SQL

Перенос операторов (notification recipient) между MS SQL 2000

Опубликовано: 16 мар 07
Рейтинг:

Автор:
Прислал:

1. Создаю текстовый файл GetOperators.sql

USE msdb
set nocount on;

create table #tbl (
id int not null,
name sysname not null,
enabled tinyint not null,
email_address nvarchar(100) null,
last_email_date int not null,
last_email_time int not null,
pager_address nvarchar(100) null,
last_pager_date int not null,
last_pager_time int not null,
weekday_pager_start_time int not null,
weekday_pager_end_time int not null,
Saturday_pager_start_time int not null,
Saturday_pager_end_time int not null,
Sunday_pager_start_time int not null,
Sunday_pager_end_time int not null,
pager_days tinyint not null,
netsend_address nvarchar(100) null,
last_netsend_date int not null,
last_netsend_time int not null,
category_name sysname null);

insert into #tbl
  EXEC sp_help_operator; 

select 'USE msdb;' + char(13) + char(10) + 'if exists (select * from dbo.sysoperators where name =' + quotename(name, char(39)) + ') ' + char(13) + char(10) + 
'exec sp_update_operator ' + 
'@name = ' + quotename(name, char(39)) + ', ' + 
'@enabled = ' + cast (enabled as char(1)) + ', ' + 
'@email_address = ' + quotename(email_address, char(39)) + ', ' + 
case 
when pager_address is not null then '@pager_address = ' + quotename(pager_address, char(39)) + ', '
else ''
end + 
'@weekday_pager_start_time = ' + ltrim(str(weekday_pager_start_time)) + ', ' + 
'@weekday_pager_end_time = ' + ltrim(str(weekday_pager_end_time)) + ', ' +
'@Saturday_pager_start_time = ' + ltrim(str(Saturday_pager_start_time)) + ', ' +
'@Saturday_pager_end_time = ' + ltrim(str(Saturday_pager_end_time)) + ', ' +
'@Sunday_pager_start_time = ' + ltrim(str(Sunday_pager_start_time)) + ', ' +
'@Sunday_pager_end_time = ' + ltrim(str(Sunday_pager_end_time)) + ', ' +
'@pager_days = ' + cast(pager_days as varchar(3)) +  
case
when netsend_address is not null then ', @netsend_address = ' + quotename(netsend_address, char(39)) 
else ''
end + 
case 
when category_name != '[Uncategorized]' then ', @category_name = ' + category_name  
else '' 
end +
' else ' + char(13) + char(10) + 'exec sp_add_operator ' +
'@name = ' + quotename(name, char(39)) + ', ' + 
'@enabled = ' + cast (enabled as char(1)) + ', ' + 
'@email_address = ' + quotename(email_address, char(39)) + ', ' + 
case 
when pager_address is not null then '@pager_address = ' + quotename(pager_address, char(39)) + ', '
else ''
end + 
'@weekday_pager_start_time = ' + ltrim(str(weekday_pager_start_time)) + ', ' + 
'@weekday_pager_end_time = ' + ltrim(str(weekday_pager_end_time)) + ', ' +
'@Saturday_pager_start_time = ' + ltrim(str(Saturday_pager_start_time)) + ', ' +
'@Saturday_pager_end_time = ' + ltrim(str(Saturday_pager_end_time)) + ', ' +
'@Sunday_pager_start_time = ' + ltrim(str(Sunday_pager_start_time)) + ', ' +
'@Sunday_pager_end_time = ' + ltrim(str(Sunday_pager_end_time)) + ', ' +
'@pager_days = ' + cast(pager_days as varchar(3)) +  
case
when netsend_address is not null then ', @netsend_address = ' + quotename(netsend_address, char(39)) 
else ''
end +
case 
when category_name != '[Uncategorized]' then ', @category_name = ' + category_name  
else '' 
end +
'; ' + char(13) + char(10) + 'go' 
from #tbl order by id;

drop table #tbl;
2. Создаю bat файл со следующей строкой:
osql -U user -P password -S SERVER -i E:\BACKUP\GetOperators.sql -o E:\BACKUP\InsertOperators.sql -w 2048 -n -h-1

3. После выполнения скрипта получаю файл InsertOperators.sql. Переношу его на другой сервер.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Скрипты / T-SQL / Перенос операторов (notification recipient) между MS SQL 2000