Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
Как убрать сообщение об ошибке после выполненной процедуры. Надо, что бы он вообще ничего не показывал. Ни коды возврата, ни ошибки. В поле Messages в MS должно быть пусто?
19 дек 13, 16:16    [15318704]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
o-o
Guest
заключить в try/catch?

begin try
 select 1 / 0
end try 

begin catch
end catch
19 дек 13, 16:22    [15318743]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
o-o,

спасибо. Это конечно все здорово.

Только смысл в том, что у меня ошибку возвращает insert который находится внутри курсора. И при вашей конструкции, вместо select 1/0 я вставляю конструкцию с курсором. Действительно, ошибка не возвращается, а вот таблица, куда встравляются данные вообще не заполняется.
19 дек 13, 16:27    [15318785]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
wizli
Member

Откуда: Minsk
Сообщений: 270
Ёжик25
o-o,

спасибо. Это конечно все здорово.

Только смысл в том, что у меня ошибку возвращает insert который находится внутри курсора. И при вашей конструкции, вместо select 1/0 я вставляю конструкцию с курсором. Действительно, ошибка не возвращается, а вот таблица, куда встравляются данные вообще не заполняется.

Поставьте конструкцию try\catch на уровне insert, а не на уровне всего курсора. И будет вам счастье.
19 дек 13, 16:30    [15318811]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
wizli,

не работает! Тоже самое происходит, ошибки возвращаются.
19 дек 13, 17:16    [15319203]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
Вот скрипт:
DECLARE @publisher_db sysname,
@publication sysname
Declare subscr_cursor1 cursor local for select publisher_db, publication from distribution.dbo.MSPublications
OPEN subscr_cursor1
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
WHILE @@FETCH_STATUS = 0
BEGIN
set nocount on
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')
set nocount off
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
END
CLOSE subscr_cursor1
DEALLOCATE subscr_cursor1
go


Возвращает (для 2 баз публикаций):

Msg 8164, Level 16, State 1, Procedure sp_MSrepl_helpsubscription, Line 537
An INSERT EXEC statement cannot be nested.
Msg 8164, Level 16, State 1, Procedure sp_MSrepl_helpsubscription, Line 537
An INSERT EXEC statement cannot be nested.



Исправил, как вы подсказали:

DECLARE @publisher_db sysname,
@publication sysname

Declare subscr_cursor1 cursor local for select publisher_db, publication from distribution.dbo.MSPublications
OPEN subscr_cursor1
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
WHILE @@FETCH_STATUS = 0
BEGIN
begin try
set nocount on
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')
end try
begin catch
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')
set nocount off
end catch;
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
END
CLOSE subscr_cursor1
DEALLOCATE subscr_cursor1
go



Результат тот же!!
19 дек 13, 17:22    [15319245]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Hedera
Member

Откуда:
Сообщений: 7
Почитай здесь https://www.sql.ru/blogs/t-sql/1151
19 дек 13, 17:32    [15319308]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
o-o
Guest
ну так если еще и в CATCH засунуть, конечно, напечатает.
Вы ж спрашивали как НЕ ПЕЧАТАТь, мой ответ (засунуть в TRY, НЕ в CATCH) был про это,
а не "как заставить работать неработающее"
19 дек 13, 17:33    [15319319]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
o-o
ну так если еще и в CATCH засунуть, конечно, напечатает.
Вы ж спрашивали как НЕ ПЕЧАТАТь, мой ответ (засунуть в TRY, НЕ в CATCH) был про это,
а не "как заставить работать неработающее"


нет, смысл в том, что внутри TRY insert отрабатывает причем успешно (именно то, что мне надо). Все вставляется во временную таблицу, однако выводятся ошибки An INSERT EXEC statement cannot be nested.
А мне надо, что б они просто не выводились. При этом insert так же работал!
19 дек 13, 17:38    [15319348]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
Hedera
Почитай здесь https://www.sql.ru/blogs/t-sql/1151


варианты эти мне не подходят. Все должно делаться внутри MS SQL без выходов в cmdshell или создания линка на самого себя!
19 дек 13, 17:41    [15319368]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
o-o
Guest
интересно, а что же это я такое вижу в коде?
товарищи, это только мне мерещится, или все повторяется еще и в блоке CATCH?

begin catch
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')
set nocount off
end catch;
19 дек 13, 17:44    [15319383]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ёжик25
Все должно делаться внутри MS SQL без выходов в cmdshell или создания линка на самого себя!

Но с использованием недокументированных процедур.
Шикарные требования
19 дек 13, 17:53    [15319426]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ничто не мешает вам получить тексты системных процедур и на их основе создать свои
19 дек 13, 17:59    [15319462]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
wizli
Member

Откуда: Minsk
Сообщений: 270
Ёжик25
Вот скрипт:
DECLARE @publisher_db sysname,
@publication sysname
Declare subscr_cursor1 cursor local for select publisher_db, publication from distribution.dbo.MSPublications
OPEN subscr_cursor1
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
WHILE @@FETCH_STATUS = 0
BEGIN
set nocount on
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')
set nocount off
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
END
CLOSE subscr_cursor1
DEALLOCATE subscr_cursor1
go


Возвращает (для 2 баз публикаций):

Msg 8164, Level 16, State 1, Procedure sp_MSrepl_helpsubscription, Line 537
An INSERT EXEC statement cannot be nested.
Msg 8164, Level 16, State 1, Procedure sp_MSrepl_helpsubscription, Line 537
An INSERT EXEC statement cannot be nested.



Исправил, как вы подсказали:

DECLARE @publisher_db sysname,
@publication sysname

Declare subscr_cursor1 cursor local for select publisher_db, publication from distribution.dbo.MSPublications
OPEN subscr_cursor1
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
WHILE @@FETCH_STATUS = 0
BEGIN
begin try
set nocount on
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')
end try
begin catch
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')
set nocount off
end catch;
FETCH NEXT FROM subscr_cursor1 into @publisher_db, @publication
END
CLOSE subscr_cursor1
DEALLOCATE subscr_cursor1
go



Результат тот же!!



Смотрите, что у вас происходит.
Начинает работать курсор, выполняется строка
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')


В ней возникает ошибка, но так как мы заюзали try..catch, у нас управление передастся в блок catch, и будет выполнен код, который там есть. А там у нас вот такая строка
insert into ##Publication_subscription exec ('exec ['+ @publisher_db +'].dbo.sp_helpsubscription')


Т.е. тот же самый код, который только что отработал с ошибкой. Естественно будет выдана ошибка выполнения процедуры.
В качестве решения уберите код из блока catch и все будет хорошо.
А вообще, не плохо было бы вам прочесть вот это: Try\Catch
19 дек 13, 18:05    [15319483]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
o-o,
wizli,

Уважаемые, смысл в том, что мне надо, что бы этот код работал. Он и работает, складирует свои результаты в таблицу, но выдает ошибку. А мне просто не надо, что бы она отображалась. Конечно у меня в блоке Catch выполняется инсент. Потому что мне надо, что бы он выполнялся. Но без вывода ошибки. Т.к. это и ошибкой не назовешь, инсерт прекрасно отрабатывает, таблица заполняется. Смысл в том, что потом у меня выходит скрипт на генерацию подписки через PRINT на основе заполненной таблицы. И в нем отображается ошибка. Которой там быть не должно, т.к. скрипт будет применяться при крахе продакт-сервера.

PS Вариант с восстановлением msdb и distribution на резервном для восстановления всех репликаций не подходит. Резервный один, а филиальных продактов - 150 штук. И на каждом сложная бизнес-логика с задействованием около 20-30 репликаций. Исторически сложилось, что на msdb навешано еще много функций, вес msdb около 10гб на каждом филиальном сервере. Поэтому все это добро складировать на единственном резервном (1.5 тб) вообще не вариант. Я пишу скрипт генерации, который будет загоняться в обычный txt и складироваться на резервном. И если с процедурами sp_helppublication, sp_addarticle, sp_addpublication_snapshot, sp_articlecolumn, sp_articlefilter, sp_articleview,sp_grant_publication_access проблем не возникло. То с последней процедурой sp_helpsubscription возникает проблема. (проблема: ошибка An INSERT EXEC statement cannot be nested.)
20 дек 13, 09:37    [15321424]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
Glory
Ёжик25
Все должно делаться внутри MS SQL без выходов в cmdshell или создания линка на самого себя!

Но с использованием недокументированных процедур.
Шикарные требования


sp_helpsubscription - это недокументированная? Или вы имеете ввиду sp_MSrepl_helpsubscriberinfo.


Glory
Ничто не мешает вам получить тексты системных процедур и на их основе создать свои


Так как же мне получить тектст sp_MSrepl_helpsubscriberinfo (насколько я понял, именно ей не нравится insert into), если, как я понимаю, она в DDLке где то зашита?
20 дек 13, 09:50    [15321485]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
sp_helptext
20 дек 13, 09:53    [15321498]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
Ёжик25,

Это как заклеить скотчем мигающую лампочку на панельной доске машины: "машина же едет, фигли она мигает"


http://www.t-sql.ru/post/An_INSERT_EXEC_statement_cannot_be_nested.aspx
"А теперь несколько способов обойти эту ошибку:

1) Первый и самый правильный
По возможности не использовать вложенных инструкций INSERT EXEC, ...
2) Используем OPENQUERY или OPENROWSET ..."
20 дек 13, 09:54    [15321502]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
Сергей Викт.
sp_helptext


sp_helptext 'sp_helpsubscription' - выдает текст процедуры, внутри которой использованна процедура sp_MSrepl_helpsubscriberinfo.

sp_helptext 'sp_MSrepl_helpsubscriberinfo' выдает

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 91
There is no text for object 'sp_MSrepl_helpsubscriberinfo'.
20 дек 13, 09:56    [15321511]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Ёжик25
sp_helptext 'sp_MSrepl_helpsubscriberinfo' выдает

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 91
There is no text for object 'sp_MSrepl_helpsubscriberinfo'.

Посмотрите внимательно в sp_helpsubscription, в какой базе и сервере находится sp_MSrepl_helpsubscriberinfo, и выполняйте sp_helptext там
20 дек 13, 10:00    [15321530]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Ёжик25
Member

Откуда: оттуда
Сообщений: 360
alexeyvg
Ёжик25
sp_helptext 'sp_MSrepl_helpsubscriberinfo' выдает

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 91
There is no text for object 'sp_MSrepl_helpsubscriberinfo'.

Посмотрите внимательно в sp_helpsubscription, в какой базе и сервере находится sp_MSrepl_helpsubscriberinfo, и выполняйте sp_helptext там



Привожу текст


--
-- Name:
-- sp_helpsubscription
--
-- Description:
-- Lists subscription information associated with a particular publication, article,
-- Subscriber, or set of subscriptions. This stored procedure is executed at a Publisher
-- on the publication database.
--
-- Security:
-- Public
--
-- Returns:
-- Result set of subscription properties
--
-- Owner:
-- <current owner>

create procedure sys.sp_helpsubscription
(
@publication sysname = '%',
@article sysname = '%',
@subscriber sysname = N'%',
@destination_db sysname = '%',
@found int = 23456 OUTPUT,
@publisher sysname = NULL
)
AS
BEGIN
DECLARE @cmd nvarchar(4000)
DECLARE @retcode int
DECLARE @publisher_type sysname

SET @retcode = 0

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@publisher_type = @publisher_type OUTPUT,
@rpcheader = @cmd OUTPUT,
@skipSecurityCheck = 1

IF @retcode <> 0
RETURN (@retcode)

SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
set @cmd = @cmd + N'sys.sp_MSrepl_helpsubscription'

EXEC @retcode = @cmd
@publication,
@article,
@subscriber,
@destination_db,
@found OUTPUT,
@publisher,
@publisher_type

RETURN (@retcode)
END


На какой же базе, черт возьми, выполняется sp_MSrepl_helpsubscription??? Удивительно! Ни на какой! Ни в одной базе на сервере нет такой процедуры! Во всяком случае найти ее в явном виде нельзя.
20 дек 13, 10:06    [15321557]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Ёжик25,
+

001	-- Name:
002	--          sp_MSrepl_helpsubscription
003	 
004	-- Description:
005	--          Lists subscription information associated with a particular publication, article,
006	--          Subscriber, or set of subscriptions. This stored procedure is executed at a Publisher
007	--          on the publication database.
008	 
009	-- Security:
010	--          Internal
011	-- Requires Certificate signature for catalog access
012	 
013	-- Returns:
014	--          Result set of subscription properties
015	 
016	-- Owner:
017	--          <current owner="">
018	 
019	create procedure sys.sp_MSrepl_helpsubscription
020	(
021	    @publication    sysname,
022	    @article        sysname,
023	    @subscriber     sysname,
024	    @destination_db sysname,
025	    @found          int OUTPUT,
026	    @publisher      sysname,
027	    @publisher_type sysname
028	)
029	AS
030	BEGIN
031	    SET NOCOUNT ON
032	 
033	    /*
034	    ** Declarations.
035	    */
036	 
037	    DECLARE @retcode                int
038	    DECLARE @subscriber_bit         smallint
039	    DECLARE @no_row                 bit
040	    DECLARE @srvid                  smallint
041	    DECLARE @pubid                  int
042	    DECLARE @artid                  int
043	    DECLARE @immediate_sync         bit
044	    DECLARE @subscription_type_id   int
045	    DECLARE @sync_typeid            int
046	    DECLARE @publish_bit            int
047	    DECLARE @orig_publication       sysname
048	    DECLARE @full_subscription      bit
049	    DECLARE @distributor            sysname
050	    DECLARE @distributiondb         sysname
051	    DECLARE @distproc               NVARCHAR(255)
052	    DECLARE @dbname                 sysname
053	 
054	    DECLARE @publication_ids TABLE
055	    (
056	        pubid   int
057	    )
058	     
059	    SELECT @publish_bit = 1
060	    SELECT @distributor = NULL
061	    SELECT @distributiondb = NULL
062	    SELECT @distproc = NULL
063	    SELECT @dbname = NULL
064	    SELECT @orig_publication = @publication
065	    SELECT @publisher = ISNULL(@publisher, publishingservername())
066	 
067	    SET @retcode = 0
068	 
069	    /* Security check. To public. */
070	    /* NOTE: Security check is part of the output after the cursor has been run */
071	 
072	    /*
073	    ** Check if the database is published.
074	    */
075	    IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
076	    BEGIN
077	        RAISERROR (14013, 16, -1)
078	        RETURN (1)
079	    END
080	 
081	    /*
082	    ** Initializations of @now_row.
083	    */
084	    IF @found = 23456
085	    BEGIN
086	        SELECT @no_row=0
087	    END
088	    ELSE
089	    BEGIN
090	        SELECT @no_row=1
091	    END
092	 
093	    /*
094	    ** Initializations.
095	    */
096	    SELECT @subscriber_bit = 4
097	 
098	    /*
099	    ** Parameter Check:  @subscriber.
100	    */
101	    IF @subscriber IS NULL
102	    BEGIN
103	        RAISERROR (14043, 16, -1, '@subscriber', 'sp_MSrepl_helpsubscription')
104	        RETURN (1)
105	    END
106	 
107	    /*
108	    ** Parameter Check:  @subscriber.
109	    ** Check if remote server is defined as a subscription server, and
110	    ** that the name conforms to the rules for identifiers.
111	    */
112	 
113	    IF @subscriber <> '%'
114	    BEGIN
115	 
116	        EXECUTE @retcode = sys.sp_validname @subscriber
117	        select @subscriber = UPPER(@subscriber)
118	 
119	        IF @retcode <> 0
120	            RETURN (1)
121	 
122	    END
123	 
124	    /*
125	    ** Parameter Check:  @publication.
126	    ** If the publication name is specified, check to make sure that it
127	    ** conforms to the rules for identifiers and that the publication
128	    ** actually exists.  Disallow NULL.
129	    */
130	 
131	    IF @publication IS NULL
132	    BEGIN
133	        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_helpsubscription')
134	        RETURN (1)
135	    END
136	 
137	    -- Get list of matching publication id's for
138	    -- this publisher/type combo
139	    INSERT INTO @publication_ids
140	    SELECT pubid
141	    FROM   sys.fn_IHgetpubid(@publication, @publisher, @publisher_type)
142	     
143	    IF @publication <> '%'
144	    BEGIN
145	        EXECUTE @retcode = sys.sp_validname @publication
146	 
147	        IF @retcode <> 0
148	        BEGIN
149	            RETURN (1)
150	        END
151	 
152	        IF NOT EXISTS
153	        (
154	            SELECT  *
155	            FROM    @publication_ids
156	        )
157	        BEGIN
158	           RAISERROR (20026, 11, -1, @publication)
159	           RETURN (1)
160	        END
161	    END
162	 
163	    /*
164	    ** Parameter Check:  @article.
165	    ** If the article name is specified, check to make sure that it
166	    ** conforms to the rules for identifiers and that the article
167	    ** actually exists.  Disallow NULL.
168	    **
169	    ** If @article is 'all', only return one entry for the whole publication
170	    ** for full subscriptions (subscriptions inlcluding all the articles in a
171	    ** publication).
172	    **
173	    */
174	 
175	    IF @article IS NULL
176	    BEGIN
177	        RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_helpsubscription')
178	        RETURN (1)
179	    END
180	 
181	    IF LOWER(@article) <> 'all'
182	    BEGIN
183	        IF @article <> '%'
184	        BEGIN
185	            IF NOT EXISTS
186	            (
187	                SELECT  *
188	                FROM    sysextendedarticlesview sv,
189	                        @publication_ids pi
190	                WHERE   sv.name  = @article
191	                  AND   sv.pubid = pi.pubid
192	            )
193	            BEGIN
194	                RAISERROR (20027, 11, -1, @article)
195	                RETURN (1)
196	            END
197	        END
198	 
199	 
200	        IF EXISTS
201	        (
202	            SELECT  *
203	              FROM  syssubscriptions sub,
204	                    syspublications pub,
205	                    sysextendedarticlesview art,
206	                    @publication_ids pi
207	             WHERE  ((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
208	                and (sub.srvname is not null and len(sub.srvname)> 0)
209	                AND ((@publication = N'%') or (pub.name = @publication))
210	               AND   ((@article = N'%') or (art.name = @article))
211	               AND  art.pubid = pub.pubid
212	               AND  sub.artid = art.artid
213	               AND  ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
214	               AND  (sub.login_name = suser_sname(suser_sid()) OR
215	                    is_srvrolemember('sysadmin') = 1 OR
216	                    is_member ('db_owner') = 1)
217	               AND  pub.pubid = pi.pubid
218	        )
219	        BEGIN
220	            SELECT @found = 1
221	            IF @no_row <> 0 RETURN (0)
222	        END
223	        ELSE
224	        BEGIN
225	            SELECT @found = 0
226	            RETURN(0)
227	        END
228	    END
229	 
230	    CREATE TABLE #helpsubscription
231	    (
232	        subscriber          sysname collate database_default not null,
233	        publication         sysname collate database_default not null,
234	        article             sysname collate database_default not null,
235	        destination_db      sysname collate database_default not null,
236	        status              tinyint NOT NULL,
237	        sync_type           tinyint NOT NULL,
238	        subscription_type   int NOT NULL,
239	        full_subscription   bit NOT NULL,
240	        distribution_jobid  binary(16) NULL,
241	        subscription_name   nvarchar(386) collate database_default not null,
242	        -- SyncTran
243	        update_mode         int NOT NULL,
244	        loopback_detection  bit not null
245	    )
246	 
247	    /* Open a CURSOR LOCAL FOR subscriber/destination_db and publication pair
248	    **
249	    ** Get subscriptions
250	    ** sa or dbo can see every subscriptions while
251	    ** others only see their own.
252	    */
253	 
254	    /*
255	    ** Performance Optimization: Eliminate the 'LIKE' clause for publication name.
256	    **                           Empirical evidence shows almost 50% speed improvement when
257	    **                           opening the cursor if publication name is provided.
258	    */
259	    IF (@publication <> '%')
260	        DECLARE hChelpsubscription_pub CURSOR LOCAL FAST_FORWARD FOR
261	            SELECT  DISTINCT sub.srvname,
262	                    pub.name,
263	                    sub.dest_db,
264	                    pub.pubid,
265	                    sub.srvid,
266	                    pub.immediate_sync
267	              FROM  syssubscriptions sub,
268	                    syspublications pub,
269	                    sysextendedarticlesview art,
270	                    @publication_ids pi
271	             WHERE  ((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
272	                and (sub.srvname is not null and len(sub.srvname)> 0)
273	               AND  pub.name = @publication collate database_default
274	               AND  art.pubid = pub.pubid
275	               AND  sub.artid = art.artid
276	               AND  ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
277	               AND  (sub.login_name = suser_sname(suser_sid()) OR
278	                        is_srvrolemember('sysadmin') = 1 OR
279	                        is_member ('db_owner') = 1)
280	               AND  pub.pubid = pi.pubid
281	               FOR READ ONLY
282	    ELSE
283	        DECLARE hChelpsubscription_pub CURSOR LOCAL FAST_FORWARD FOR
284	            SELECT  DISTINCT sub.srvname,
285	                    pub.name,
286	                    sub.dest_db,
287	                    pub.pubid,
288	                    sub.srvid,
289	                    pub.immediate_sync
290	              FROM  syssubscriptions sub,
291	                    syspublications pub,
292	                    sysextendedarticlesview art,
293	                    @publication_ids pi
294	             WHERE  ((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
295	                and (sub.srvname is not null and len(sub.srvname)> 0)
296	               AND  art.pubid = pub.pubid
297	               AND  sub.artid = art.artid
298	               AND  ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
299	               AND  (sub.login_name = suser_sname(suser_sid()) OR
300	                        is_srvrolemember('sysadmin') = 1 OR
301	                        is_member ('db_owner') = 1)
302	               AND  pub.pubid = pi.pubid
303	               FOR READ ONLY
304	 
305	    OPEN    hChelpsubscription_pub
306	    FETCH   hChelpsubscription_pub
307	    INTO    @subscriber,
308	            @publication,
309	            @destination_db,
310	            @pubid,
311	            @srvid,
312	            @immediate_sync
313	 
314	    WHILE (@@fetch_status <> -1)
315	    BEGIN
316	 
317	        /*
318	        ** Is it a full subscription ? i.e. Does it include all the articles?
319	        **
320	        */
321	        IF NOT EXISTS
322	        (
323	            SELECT  *
324	            FROM    sysextendedarticlesview art
325	            WHERE   art.pubid = @pubid
326	              AND   NOT EXISTS
327	                    (
328	                        SELECT  *
329	                        FROM    syssubscriptions sub
330	                        WHERE   sub.artid = art.artid
331	                          AND   sub.srvid = @srvid
332	                          AND   ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
333	                    )
334	        )
335	        BEGIN
336	            /* Do all the subscriptions on the publication have same
337	            ** sync_type and subscription_type ?
338	            */
339	 
340	            /*
341	            ** Get subscription type on the publication
342	            */
343	            SELECT  @subscription_type_id = subs.subscription_type,
344	                    @sync_typeid = subs.sync_type
345	            FROM    sysextendedarticlesview art,
346	                    syssubscriptions subs
347	            WHERE   art.pubid = @pubid
348	              AND   subs.srvid = @srvid
349	              AND   ((@destination_db = N'%') OR (subs.dest_db = @destination_db))
350	              AND   subs.artid = art.artid
351	 
352	 
353	            /*
354	            ** if the subscription all have the same subscription type
355	            ** and sync_type
356	            */
357	            IF NOT EXISTS
358	            (
359	                SELECT  *
360	                FROM    sysextendedarticlesview art,
361	                        syssubscriptions subs
362	                WHERE   art.pubid = @pubid
363	                  AND   subs.srvid = @srvid
364	                  AND   ((@destination_db = N'%') OR (subs.dest_db = @destination_db))
365	                  AND   subs.artid = art.artid
366	                  AND   (subscription_type <> @subscription_type_id
367	                            OR sync_type <> @sync_typeid)
368	            )
369	            BEGIN
370	                SELECT @full_subscription = 1
371	            END
372	            ELSE
373	            BEGIN
374	                SELECT @full_subscription = 0
375	            END
376	        END
377	        ELSE
378	        BEGIN
379	            SELECT @full_subscription = 0
380	        END
381	 
382	        /*
383	        ** If it is a full subscription and the @article is 'all',
384	        ** only return one entry for the whole publication.
385	        ** Always return one row per publication if @article is 'ALL'
386	        */
387	        IF LOWER(@article) = 'all'
388	        BEGIN
389	            INSERT INTO #helpsubscription
390	            SELECT  TOP 1
391	                    @subscriber,
392	                    @publication,
393	                    @article,
394	                    @destination_db,
395	                    sub.status,
396	                    case sub.sync_type
397	                        when 2 then
398	                            case sub.nosync_type
399	                                when 3 then 5
400	                                when 2 then 4
401	                                when 1 then 3
402	                                else 2
403	                            end
404	                        else sub.sync_type
405	                    end,
406	                    sub.subscription_type,
407	                    @full_subscription,
408	                    sub.distribution_jobid,
409	                    @subscriber + ':' + @destination_db  ,
410	                    -- NOTE: For Queued case: we will always the following
411	                    -- enumeration for update mode as the initial failover
412	                    -- state is contained within the value of the update mode
413	                     
414	                    -- For update mode = 2,4 return 2 (Queued only)
415	                    -- For update mode = 3,5 return 3 (Immediate with Queued as failover)
416	                    -- For update mode = 6,7 return 4 (Queued with Immediate as failover)
417	                     
418	                    case
419	                        when sub.update_mode = 4 then 2
420	                        when sub.update_mode = 5 then 3
421	                        when sub.update_mode in (6,7) then 4
422	                        else sub.update_mode
423	                    end,
424	                    sub.loopback_detection
425	                    -- end SyncTran
426	            FROM    syssubscriptions sub, sysextendedarticlesview art
427	            WHERE   sub.srvid = @srvid
428	              AND   ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
429	              AND   sub.artid = art.artid
430	              AND   art.pubid = @pubid
431	        END
432	        ELSE
433	        BEGIN
434	            /*
435	            ** Get subscriptions
436	            ** sa or dbo can see every subscriptions while
437	            ** others only see their own.
438	            */
439	 
440	            INSERT INTO #helpsubscription
441	            SELECT  @subscriber,
442	                    @publication,
443	                    art.name,
444	                    @destination_db,
445	                    sub.status, 
446	                    case sub.sync_type
447	                        when 2 then
448	                            case sub.nosync_type
449	                                when 3 then 5
450	                                when 2 then 4
451	                                when 1 then 3
452	                                else 2
453	                            end
454	                        else sub.sync_type
455	                    end,
456	                    sub.subscription_type,
457	                    @full_subscription,
458	                    sub.distribution_jobid,
459	                    @subscriber + ':' + @destination_db + ':' + art.name,
460	                    -- NOTE: For Queued case: we will always the following
461	                    -- enumeration for update mode as the initial failover
462	                    -- state is contained within the value of the update mode
463	                     
464	                    -- For update mode = 2,4 return 2 (Queued only)
465	                    -- For update mode = 3,5 return 3 (Immediate with Queued as failover)
466	                    -- For update mode = 6,7 return 4 (Queued with Immediate as failover)
467	                     
468	                    case
469	                        when sub.update_mode = 4 then 2
470	                        when sub.update_mode = 5 then 3
471	                        when sub.update_mode in (6,7) then 4
472	                        else sub.update_mode
473	                    end,
474	                    sub.loopback_detection
475	                    -- end SyncTran
476	            FROM    syssubscriptions sub,
477	                    sysextendedarticlesview art
478	            WHERE   sub.srvid = @srvid
479	              AND   ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
480	              AND   art.pubid = @pubid
481	              AND    ((@article = N'%') or (art.name = @article))
482	              AND   sub.artid = art.artid
483	              AND   (sub.login_name = suser_sname(suser_sid())
484	                        OR is_srvrolemember('sysadmin') = 1
485	                        OR is_member ('db_owner') = 1)
486	        END
487	        FETCH   hChelpsubscription_pub
488	        INTO    @subscriber,
489	                @publication,
490	                @destination_db,
491	                @pubid,
492	                @srvid,
493	                @immediate_sync
494	    END
495	 
496	    CLOSE hChelpsubscription_pub
497	    DEALLOCATE hChelpsubscription_pub
498	 
499	    -- Include 9.0 subscriber types in the table
500	    CREATE TABLE #dist_agent_properties
501	    (
502	        job_id                      VARBINARY(16) NULL,
503	        offload_enabled             bit NULL,
504	        offload_server              sysname collate database_default null,
505	        dts_package_name            sysname collate database_default null,
506	        dts_package_location        int NULL,
507	        status                      int NULL,
508	        subscriber_security_mode    smallint NULL,
509	        subscriber_login            sysname NULL,
510	        job_login                   sysname NULL,
511	        distrib_agent_name          nvarchar(100) NULL,
512	        subscriber_type             tinyint NULL,
513	        subscriber_provider         sysname NULL,
514	        subscriber_datasource       nvarchar(4000) NULL,
515	        subscriber_providerstring   nvarchar(4000) NULL,
516	        subscriber_location         nvarchar(4000) NULL,
517	        subscriber_catalog          sysname NULL
518	    )
519	 
520	    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo    @publisher  = @publisher,
521	                                                        @rpcsrvname = @distributor OUTPUT,
522	                                                        @distribdb  = @distributiondb OUTPUT
523	    IF @retcode <> 0
524	        RETURN @retcode
525	 
526	    SELECT @distributor = RTRIM(@distributor)
527	 
528	    -- Get distribution agent properties
529	    SELECT @distproc =  QUOTENAME(@distributor) + '.' +
530	                        QUOTENAME(@distributiondb) +
531	                        '.dbo.sp_MSenumdistributionagentproperties'
532	 
533	    SELECT @dbname = db_name()
534	 
535	    INSERT INTO #dist_agent_properties
536	    EXEC @retcode = @distproc   @publisher      = @publisher,
537	                                @publisher_db   = @dbname,
538	                                @publication    = @orig_publication,
539	                                @show_security  = 1
540	     
541	    /*
542	    ** Get subscriptions
543	    */
544	    SELECT  hs.subscriber as [subscriber],
545	            hs.publication as [publication],
546	            hs.article as [article],
547	            hs.destination_db as [destination database],
548	            -- distributionstatus   = 0 means that the subscription has been deactivated.
549	            case
550	                when hs.status = 2 and ap.status = 0 then 0
551	                else hs.status
552	            end as [subscription status],
553	            hs.sync_type as [synchronization type],
554	            hs.subscription_type as [subscription type],
555	            hs.full_subscription as [full subscription],
556	            hs.subscription_name as [subscription name],
557	            -- SyncTran
558	            hs.update_mode as [update mode],
559	            ap.job_id as [distribution job id],
560	            hs.loopback_detection as [loopback_detection],
561	            ap.offload_enabled as [offload_enabled],
562	            ap.offload_server as [offload_server],
563	            ap.dts_package_name as [dts_package_name],
564	            ap.dts_package_location as [dts_package_location],
565	            ap.subscriber_security_mode as [subscriber_security_mode],
566	            ap.subscriber_login as [subscriber_login],
567	            '**********' as [subscriber_password],
568	            ap.job_login as [job_login],
569	            '**********' as [job_password],
570	            ap.distrib_agent_name,
571	            ap.subscriber_type,
572	            ap.subscriber_provider,
573	            ap.subscriber_datasource,
574	            ap.subscriber_providerstring,
575	            ap.subscriber_location,
576	            ap.subscriber_catalog
577	    FROM    #helpsubscription hs
578	        LEFT OUTER JOIN #dist_agent_properties ap
579	            ON  hs.distribution_jobid = ap.job_id
580	    ORDER BY subscriber, publication, article
581	     
582	    DROP TABLE #dist_agent_properties
583	END


Сообщение было отредактировано: 20 дек 13, 10:16
20 дек 13, 10:15    [15321609]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ёжик25
На какой же базе, черт возьми, выполняется sp_MSrepl_helpsubscription??? Удивительно! Ни на какой! Ни в одной базе на сервере нет такой процедуры!

Есть. И база эта называется mssqlsystemresource
А для тех, кого забанили в гугле даю ссылку http://www.g-productions.nl/index.php?name=sp_MSrepl_helpsubscription
20 дек 13, 10:15    [15321611]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
и да, нафига создавать ещё одну тему?
20 дек 13, 10:19    [15321640]     Ответить | Цитировать Сообщить модератору
 Re: сообщение об ошибке  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Ёжик25
Ни в одной базе на сервере нет такой процедуры!
Есть. Только живет она в ресурсной БД. Вот ее текст - sp_MSrepl_helpsubscription

Может лучше озвучите цель ваших приседаний вокруг sp_helpsubscription?
20 дек 13, 10:20    [15321642]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить