October 13, 2010 at 12:42 pm
i'm in the process of collecting performance data and creating reports about it. next up is i want to collect data every hour or so on how much commands are waiting at the distributor to be replicated, but i want to present it in a readable fashion since the stored procedure only returns 2 columns.
my idea is to create an SSIS package to run it for every publication and subsciber and dump the data into a database, but i'm having some trouble. i want to do something like the following:
select server_name = 'server_name, date_read = getdate(), database_name = 'db_name', publication_name = 'pub_name',
sp_replmonitorsubscriptionpendingcmds @publisher = 'sqlservername'
, @publisher_db = 'db_name'
, @publication = 'pub_name'
, @subscriber = 'sub_sql_server_name'
, @subscriber_db = 'sub_db'
, @subscription_type = 0
i'm hoping to do all this in one SQL statement to make it easier to create the package
October 14, 2010 at 9:05 am
alen teplitsky (10/13/2010)
i'm in the process of collecting performance data and creating reports about it. next up is i want to collect data every hour or so on how much commands are waiting at the distributor to be replicated, but i want to present it in a readable fashion since the stored procedure only returns 2 columns.my idea is to create an SSIS package to run it for every publication and subsciber and dump the data into a database, but i'm having some trouble. i want to do something like the following:
select server_name = 'server_name, date_read = getdate(), database_name = 'db_name', publication_name = 'pub_name',
sp_replmonitorsubscriptionpendingcmds @publisher = 'sqlservername'
, @publisher_db = 'db_name'
, @publication = 'pub_name'
, @subscriber = 'sub_sql_server_name'
, @subscriber_db = 'sub_db'
, @subscription_type = 0
i'm hoping to do all this in one SQL statement to make it easier to create the package
You can't capture Stored Procedure output with a Select.
A possible work around is to capture the output from the procedure to a Table with an Insert..Exec Statement then you can select from the Table.
October 14, 2010 at 11:12 am
OTF (10/14/2010)
alen teplitsky (10/13/2010)
i'm in the process of collecting performance data and creating reports about it. next up is i want to collect data every hour or so on how much commands are waiting at the distributor to be replicated, but i want to present it in a readable fashion since the stored procedure only returns 2 columns.my idea is to create an SSIS package to run it for every publication and subsciber and dump the data into a database, but i'm having some trouble. i want to do something like the following:
select server_name = 'server_name, date_read = getdate(), database_name = 'db_name', publication_name = 'pub_name',
sp_replmonitorsubscriptionpendingcmds @publisher = 'sqlservername'
, @publisher_db = 'db_name'
, @publication = 'pub_name'
, @subscriber = 'sub_sql_server_name'
, @subscriber_db = 'sub_db'
, @subscription_type = 0
i'm hoping to do all this in one SQL statement to make it easier to create the package
You can't capture Stored Procedure output with a Select.
A possible work around is to capture the output from the procedure to a Table with an Insert..Exec Statement then you can select from the Table.
Not totally correct. You can use OpenDataSource to select from an execute command. But it's overly complicated.
On the original question, why not have SSIS pull the data from each query separately? You can easily load them sequentially into the same target.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply