Hi,
I'm setting up Transactional Replication between SQL MI to SQL VM. I want to setup monitoring by executing "sp_replmonitorsubscriptionpendingcmds" and store the results into a table.
When I use OPENROWSET, I keep getting getting errors with any provider name (MSDASQL or SQLOLEDB). Can someone help me with this please
select * FROM OPENROWSET('MSDASQL',
'Driver={SQL SERVER};Server=SQLMIConnectionstring,1433;UID=Loginname;PWD=Password;',
'set fmtonly off;exec [distribution].[dbo].[sp_replmonitorsubscriptionpendingcmds]
@publisher=''SQLMIConnectionstring'',
@publisher_db=''PublisherDBname'',
@publication=''Publicationname'',
@subscriber=''SubscriberIP,1433'',
@subscriber_db=''SubscriberDBname'',
@subscription_type=0')
for MSDASQL:
Msg 7357, Level 16, State 2, Line 12
Cannot process the object
The OLE DB provider "MSDASQL" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
for SQLOLEDB or SQLNCI
Msg 11528, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 11]
The metadata could not be determined because statement 'EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)' in procedure 'sp_MS_marksystemobject' does not support metadata discovery.
June 23, 2023 at 1:20 pm
did you follow the steps here https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-two-instances-and-sql-server-configure-tutorial?view=azuresql
SQL MI to onprem does not follow standard way of doing it between 2 onprem servers.
June 23, 2023 at 5:58 pm
Sorry if my post is not clear. I'm not having any issues setting up with Replication. Only with running " sp_replmonitorsubscriptionpendingcmds" in openrowset, I was getting the error. As there's no directway to insert the results of sp into a table, I was trying to use openrowset and its failing.
I'm adding this for anyone here who are stuck with same issue.
I created a linked server on SQL MI (to connect to same SQL MI) and used it in the Exec command. This is working and I can now create alerts for Replication latency.
Insert into TableName
Exec ('exec distribution..sp_replmonitorsubscriptionpendingcmds
@publisher=''SQLMI'',
@publisher_db=''PublisherDBName'',
@publication=''PublicationName'',
@subscriber=''SubscriberIP'',
@subscriber_db=''SubscriberDBName'',
@subscription_type=0') at [LinkedServerName]
June 30, 2023 at 10:52 am
I'm adding this for anyone here who are stuck with same issue.
I created a linked server on SQL MI (to connect to same SQL MI) and used it in the Exec command. This is working and I can now create alerts for Replication latency.
Insert into TableName
Exec ('exec distribution..sp_replmonitorsubscriptionpendingcmds
@publisher=''SQLMI'',
@publisher_db=''PublisherDBName'',
@publication=''PublicationName'',
@subscriber=''SubscriberIP'',
@subscriber_db=''SubscriberDBName'',
@subscription_type=0') at [LinkedServerName]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply