Tuesday, March 20, 2012

custom sp_browsereplcmds

Hi NG
I want to customise sp_browsereplcomdands.
I use an vb-Datagrid for output. The Grid can't show varbinary.
Thats why I need an additional column: cast (xact_seqno as bigint)
--CODE:
declare @.query nvarchar(4000),
@.dbname nvarchar(128)
set @.query='select xact_seqno,
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_command),command
from MSrepl_commands order by originator_id, publisher_database_id,
xact_seqno,article_id,command_id'
set @.dbname='distribution'
exec master..xp_printstatements @.query, @.dbname
--WORKS FINE
--BUT
declare @.query nvarchar(4000), @.dbname nvarchar(128)
set @.query=
'select xact_seqno,
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_command),command
from
MSrepl_commands order by originator_id, publisher_database_id,
xact_seqno,article_id,command_id,
convert(bigint, xact_seqno)' --< --
set @.dbname='distribution' exec master..xp_printstatements @.query,
@.dbname
--DOWN'T Show the additional Column, an alias is not possible
It seems, the parameter of xp_printstatements are not flexible to
parse an additonal column.
Any ideas?
Thanks Thomas
push the commands off into a separate table like this
sp_browsereplcmds @.results_table='test'
The schema looks like this
CREATE TABLE [dbo].[test] (
[xact_seqno] [varbinary] (16) NULL ,
[originator_id] [int] NULL ,
[publisher_database_id] [int] NULL ,
[article_id] [int] NULL ,
[type] [int] NULL ,
[command] [nvarchar] (1024) NULL
) ON [PRIMARY]
GO
alter table test
add transaction_sequenceno bigint
go
update test set transaction_sequenceno =convert(bigint, xact_seqno)
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:4213a36c.181072656@.news.t-online.de...
> Hi NG
> I want to customise sp_browsereplcomdands.
> I use an vb-Datagrid for output. The Grid can't show varbinary.
> Thats why I need an additional column: cast (xact_seqno as bigint)
> --CODE:
> declare @.query nvarchar(4000),
> @.dbname nvarchar(128)
> set @.query='select xact_seqno,
>
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_comm
and),command
> from MSrepl_commands order by originator_id, publisher_database_id,
> xact_seqno,article_id,command_id'
> set @.dbname='distribution'
> exec master..xp_printstatements @.query, @.dbname
> --WORKS FINE
> --BUT
> declare @.query nvarchar(4000), @.dbname nvarchar(128)
> set @.query=
> 'select xact_seqno,
>
originator_id,publisher_database_id,article_id,typ e,convert(int,partial_comm
and),command
> from
> MSrepl_commands order by originator_id, publisher_database_id,
> xact_seqno,article_id,command_id,
> convert(bigint, xact_seqno)' --< --
> set @.dbname='distribution' exec master..xp_printstatements @.query,
> @.dbname
>
> --DOWN'T Show the additional Column, an alias is not possible
>
> It seems, the parameter of xp_printstatements are not flexible to
> parse an additonal column.
>
> Any ideas?
> Thanks Thomas
|||thanks
I've seen this code inside sp_browsereplcmds.
But I hoped, there was another way.
Thomas
On Wed, 16 Feb 2005 15:38:13 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:

>push the commands off into a separate table like this
>sp_browsereplcmds @.results_table='test'
>The schema looks like this
>CREATE TABLE [dbo].[test] (
> [xact_seqno] [varbinary] (16) NULL ,
> [originator_id] [int] NULL ,
> [publisher_database_id] [int] NULL ,
> [article_id] [int] NULL ,
> [type] [int] NULL ,
> [command] [nvarchar] (1024) NULL
>) ON [PRIMARY]
>GO
>alter table test
>add transaction_sequenceno bigint
>go
>update test set transaction_sequenceno =convert(bigint, xact_seqno)
>GO
sql

No comments:

Post a Comment