April 25, 2012

sp_getsqlqueueversion (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_getsqlqueueversion(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

 create procedure sys.sp_getsqlqueueversion   
(
@publisher sysname -- pubisher - cannot be null
,@publisher_db sysname -- publisher_db - cannot be null
,@publication sysname -- publication - cannot be null
,@version int output -- version of the queue > 0
)
as
begin
set nocount on
declare @retcode int
,@queueidstring sysname

--
-- Security check
--
exec @retcode = sp_MSreplcheck_subscribe
if @@error != 0 or @retcode != 0
return 1
--
-- the subscription must be initialized
--
if exists (select * from sys.objects where name = 'MSsubscription_agents')
begin
--
-- do the select for sql queues
--
select @queueidstring = substring(queue_id, 1, 10)
,@version = cast(substring(queue_id, 12, 10) as int)
from dbo.MSsubscription_agents
where publisher = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and update_mode in (4,5)
if (@queueidstring = N'mssqlqueue')
begin
--
-- found the entry
--
if (@version = 0)
select @version = 1
end
else
begin
--
-- No subscription exists
--
select @version = 0
return 0
end
end
else
begin
--
-- No subscription exists
--
select @version = 0
return 0
end
--
-- all done
--
return 0
end

No comments:

Post a Comment

Total Pageviews