July 8, 2010 at 7:28 pm
use distribution;
declare @sql varchar(1000)
set @sql='sp_replmonitorhelpsubscription NULL,NULL,NULL,0,0,0,NULL,0 '
declare @tb table(
[status]int,
warningint,
subscribervarchar(50),
subscriber_dbvarchar(51),
publisher_dbvarchar(52),
publicationvarchar(53),
publication_typeint,
subtypeint,
latencyint,
latencythresholdint,
agentnotrunningint,
agentnotrunningthresholdint,
timetoexpirationint,
expirationthresholdint,
last_distsyncdatetime,
distribution_agentnamevarchar(200),
mergeagentnamevarchar(50),
mergesubscriptionfriendlynamevarchar(50),
mergeagentlocationvarchar(50),
mergeconnectiontypeint,
mergePerformanceint,
mergerunspeedfloat,
mergerundurationint,
monitorrankingint,
distributionagentjobidbinary(16),
mergeagentjobidbinary(16),
distributionagentidint,
distributionagentprofileidint,
mergeagentidint,
mergeagentprofileidint,
logreaderagentname varchar(100)
)
insert @tb
exec(@sql)
select * from @tb
when execute this T-sql ,INSERT EXEC CAN'T nested will appear.can you help me ?
Thanks!
July 8, 2010 at 7:30 pm
You need to use a temp table instead of a table variable.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 8, 2010 at 8:03 pm
Tara Kizer (7/8/2010)
You need to use a temp table instead of a table variable.
temp table and table variable will cause the same error.
thanks for your reply!
July 8, 2010 at 9:00 pm
Yeah I see that now. I thought it was an issue with a table variable.
I think you're going to need to fake it out with OPENQUERY then. Or you'll need to roll your own version of that stored procedure to get around this limitation.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 9, 2010 at 2:37 pm
I would use a temp table for this.
You also need to get away from the dynamic SQL
INSERT INTO #table --or @table may work
EXEC sp_replmonitorhelpsubscription NULL,NULL,NULL,0,0,0,NULL,0
Scott Pletcher, SQL Server MVP 2008-2010
July 9, 2010 at 2:48 pm
I guess I assumed that the dynamic SQL was being used for something that the OP wasn't showing us.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 11, 2010 at 9:43 pm
Tara Kizer (7/9/2010)
I guess I assumed that the dynamic SQL was being used for something that the OP wasn't showing us.
dynamic SQL effected!
thanks a lot!
The detail is like this:
declare @sql varchar(8000)
set @sql =' use distribution;
declare @tb table(
[status]int,
warningint,
subscribervarchar(50),
subscriber_dbvarchar(51),
publisher_dbvarchar(52),
publicationvarchar(53),
publication_typeint,
subtypeint,
latencyint,
latencythresholdint,
agentnotrunningint,
agentnotrunningthresholdint,
timetoexpirationint,
expirationthresholdint,
last_distsyncdatetime,
distribution_agentnamevarchar(200),
mergeagentnamevarchar(50),
mergesubscriptionfriendlynamevarchar(50),
mergeagentlocationvarchar(50),
mergeconnectiontypeint,
mergePerformanceint,
mergerunspeedfloat,
mergerundurationint,
monitorrankingint,
distributionagentjobidbinary(16),
mergeagentjobidbinary(16),
distributionagentidint,
distributionagentprofileidint,
mergeagentidint,
mergeagentprofileidint,
logreaderagentname varchar(100)
);
insert @tb
exec [link_server].distribution.dbo.sp_replmonitorhelpsubscription NULL,NULL,NULL,0,0,0,NULL,0 ;
insert monitor.dbo.monitor_repl
select @@servername,[status],warning,subscriber,subscriber_db,publisher_db,publication,latency,getdate() from @tb'
exec (@sql)
--[likn_server] is the linkservername that link to the distribution server ,use these code ,we can
collect and save the replication latency value .
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply