April 25, 2012

sp_getqueuedrows (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_getqueuedrows(nvarchar @tablename
, nvarchar @owner
, nvarchar @tranid)

MetaData:

 create procedure sys.sp_getqueuedrows (  
@tablename sysname
,@owner sysname = NULL
,@tranid nvarchar(70) = NULL
)
as
begin
set nocount on
declare @retcode int
,@dbname sysname
,@qualified_tabname nvarchar(1000)
,@tabid int
,@agent_id int
,@publisher sysname
,@publisher_db sysname
,@publication sysname
,@queue_id sysname
,@update_mode int
,@failover_id int
,@cmd nvarchar(4000)
,@queue_server sysname
,@indid int
,@indkey int
,@key sysname
,@colid int
,@typestring nvarchar(4000)
,@artcol int
,@xpinputstr nvarchar(4000)
,@selectcl nvarchar(4000)
,@joincl nvarchar(4000)
,@curuser sysname

--
-- prepare the fully qualified table
--
select @owner = case when (@owner IS NULL) then N'dbo' else @owner end
,@dbname = db_name()
,@curuser = user
select @qualified_tabname = quotename(@dbname) + N'.'
+ quotename(@owner) + N'.' + quotename(@tablename)
select @tabid = object_id(@qualified_tabname)
if (@tabid IS NULL) or (@tabid = 0)
begin
-- error
raiserror(21078, 16, 1, @qualified_tabname)
return 1
end

--
-- current user should have SELECT permission on the table
--
if (HAS_PERMS_BY_NAME(sys.fn_replreplacesinglequote(@qualified_tabname), 'OBJECT', 'SELECT')=0)
begin
-- error
raiserror(21752, 16, 1, @curuser, @qualified_tabname)
return 1
end

--
-- make sure the table is participating in a active queued subscription
--
select @agent_id = agent_id
from dbo.MSsubscription_articles
where dest_table = @tablename and owner = @owner

if (@agent_id IS NULL)
begin
-- error
raiserror(21753, 16, 1, @qualified_tabname)
return 1
end

--
-- get the details for the subscription
--
select @publisher = publisher
,@publisher_db = publisher_db
,@publication = publication
,@update_mode = update_mode
,@queue_server = queue_server
,@queue_id = queue_id
,@failover_id = failover_mode
from dbo.MSsubscription_agents where id = @agent_id
if (@update_mode not in (2,3,4,5))
begin
-- error
raiserror(21753, 16, 2, @qualified_tabname)
return 1
end

--
-- If we are in Immediate Failover mode - no queued messages
--
if (@update_mode in (3,5) and (@failover_id = 0))
begin
--
-- do an empty select on the source table and return
--
select @cmd = N'declare @dummy_action nvarchar(10), @dummy_tranid nvarchar(70)
select action=@dummy_action, tranid=@dummy_tranid, * from '
+
@qualified_tabname + N' where 1 = 2 '
exec (@cmd)
return 0
end

if (@update_mode in (2,3))
begin
--
-- set queue prefix for MSMQ cases
--
select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id
end
else
begin
--
-- Check the queue table for SQLQ
--
if not exists (select * from dbo.MSreplication_queue
where UPPER(publisher) = UPPER(@publisher) and
publisher_db = @publisher_db and
publication = @publication and
tranid = case when @tranid IS NULL then tranid else @tranid end)
begin
--
-- do an empty select on the source table and return
--
select @cmd = N'declare @dummy_action nvarchar(10), @dummy_tranid nvarchar(70)
select action=@dummy_action, tranid=@dummy_tranid, * from '
+
@qualified_tabname + N' where 1 = 2 '
exec (@cmd)
return 0
end
end

--
-- Now find the PK columns for this table
--
select @indkey = 1
,@artcol = 0
,@xpinputstr = N''
,@selectcl = N''
,@joincl = N''
,@retcode = 0

select @indid = i.indid
from dbo.sysindexes i
where ((i.status & 2048) != 0) and (i.id = @tabid)
if (@indid is null)
begin
raiserror(21750, 16, -1, @qualified_tabname)
return 1
end

--
-- create an enumeration of all the columns that are part of PK
--
create table #pkcoltab(pkindex int identity, keyname sysname collate database_default not null)
while (@indkey <= 16)
begin
select @key = index_col( @qualified_tabname, @indid, @indkey )
if (@key is null)
break
else
insert into #pkcoltab(keyname) values(@key)

select @indkey = @indkey + 1
end

--
-- initialize the commands that we need to build
--
if exists (select * from sys.objects where name = 'tempcrtcmd' and schema_id = schema_id())
drop table tempcrtcmd
create table tempcrtcmd (c1 int identity NOT NULL, procedure_text nvarchar(4000) NULL)

select @cmd = N'create table tempqjointab (action nvarchar(10), tranid nvarchar(70) '
insert into tempcrtcmd(procedure_text) values(@cmd)

--
-- now walk through each article col and if it is
-- a part of PK, then check find the column position of the key
-- corresponding to any article column is set
--
DECLARE #hCColid CURSOR LOCAL FAST_FORWARD FOR
select column_id, [name] from sys.columns
where object_id = @tabid order by column_id asc

OPEN #hCColid
FETCH #hCColid INTO @colid, @key
WHILE (@@fetch_status != -1)
begin
exec sys.sp_MSget_type @tabid, @colid, NULL, @typestring output
if ((@typestring IS NOT NULL) and (@typestring != N'timestamp'))
begin
--
-- this column is part of the article
--
select @artcol = @artcol + 1
if exists (select * from #pkcoltab where keyname = @key)
begin
--
-- this column is part of PK (offset and precision, scale)
-- prepare the input string for XP
-- prepare the create join table command
-- prepare the join and select clause for the result
--
select @xpinputstr = @xpinputstr + N';' + cast(@artcol as nvarchar)
if (@typestring = N'bigint')
select @xpinputstr = @xpinputstr + N'(19,0)'
else if (@typestring like N'decimal%') or (@typestring like N'numeric%')
begin
declare @startpos int
,@endpos int

select @startpos = charindex(N'(', @typestring, 1)
select @endpos = charindex(N')', @typestring, @startpos)
select @xpinputstr = @xpinputstr + substring(@typestring, @startpos, (@endpos - @startpos + 1))
end
select @cmd = N',' + quotename(@key) + N' ' + @typestring
insert into tempcrtcmd(procedure_text) values(@cmd)
select @selectcl = @selectcl + N', b.' + quotename(@key)

if (@joincl = N'')
begin
select @joincl = @joincl + N'a.' + quotename(@key) + N' = b.' + quotename(@key)
end
else
begin
select @joincl = @joincl + N'and a.' + quotename(@key) + N' = b.' + quotename(@key)
end
end
else
begin
--
-- this column is not part of PK
-- build the select clause for this column
--
select @selectcl = @selectcl + N', a.' + quotename(@key)
end
end

--
-- get the next column
--
FETCH #hCColid INTO @colid, @key
end
CLOSE #hCColid
DEALLOCATE #hCColid
drop table #pkcoltab

--
-- create the join table now
--
select @cmd = N') '
insert into tempcrtcmd(procedure_text) values(@cmd)
if exists (select * from sys.objects where name = N'tempqjointab' and schema_id = schema_id())
drop table tempqjointab
select @cmd = 'select procedure_text from dbo.tempcrtcmd order by c1'
exec @retcode = sys.xp_execresultset @cmd, @dbname
if (@retcode != 0)
goto cleanup

--
-- populate the join table now
--
if (@update_mode in (2,3))
begin
--
-- MSMQ case : one call to the xp should populate the join table
--
insert into tempqjointab
exec sys.xp_readpkfromqueue @tablename, @queue_id, @xpinputstr, @tranid
end
else
begin
--
-- SQLQ case : select the data for this subscription and call the
-- xp for each row in the cursor to populate the join table
--
declare @spancount int
,@data varbinary(8000)
,@state bit

declare #hcurQInfo cursor local FAST_FORWARD FOR
select data, cmdstate, tranid
from dbo.MSreplication_queue
where UPPER(publisher) = UPPER(@publisher) and
publisher_db = @publisher_db and
publication = @publication and
tranid = case when @tranid IS NULL then tranid else @tranid end and
commandtype = 1
order by orderkey
FOR READ ONLY

select @spancount = 0
open #hcurQInfo
fetch #hcurQInfo into @data, @state, @tranid
while (@@FETCH_STATUS = 0)
begin
declare @qbdata0 varbinary(8000)
,@qbdata1 varbinary(8000)

if (@state = 1)
begin
--
-- command spanning more than a row
-- we will allow spanning upto 2 rows
--
if (@spancount = 0)
select @qbdata0 = @data
else
begin
raiserror(21754, 16, 1)
close #hcurQInfo
deallocate #hcurQInfo
select @retcode = 1
goto cleanup
end
select @spancount = @spancount + 1
end
else
begin
--
-- final row for the command
--
if (@spancount = 0)
select @qbdata0 = @data
else if (@spancount = 1)
select @qbdata1 = @data
else
begin
raiserror(21754, 16, 2)
close #hcurQInfo
deallocate #hcurQInfo
select @retcode = 1
goto cleanup
end

--
-- call the xp to populate the join table
--
insert into tempqjointab
exec sys.xp_readpkfromvarbin @tablename, @xpinputstr, @tranid, @spancount, @qbdata0, @qbdata1

--
-- reset the span count
--
select @spancount = 0
end

--
-- fetch the next row
--
fetch #hcurQInfo into @data, @state, @tranid
end
close #hcurQInfo
deallocate #hcurQInfo
end

--
-- Now perform the join
--
select @cmd = N'select b.action, b.tranid ' + @selectcl
+ N'from ' + @qualified_tabname + N' a right join tempqjointab b on (' + @joincl + N') '
exec (@cmd)

--
-- all done
--
cleanup:
if exists (select * from sys.objects where name = N'tempqjointab' and schema_id = schema_id())
drop table tempqjointab
if exists (select * from sys.objects where name = N'tempcrtcmd' and schema_id = schema_id())
drop table tempcrtcmd
return @retcode
end

No comments:

Post a Comment

Total Pageviews