Error with OPENROWSET in SQLMI

Question

Error with OPENROWSET in SQLMI

cooldude001

Mr or Mrs. 500

Points: 525
More actions
June 23, 2023 at 11:20 am
Go to Answer
#4217547
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.
- This topic was modified 1 year, 5 months ago by cooldude001.
cooldude001

Mr or Mrs. 500

Points: 525
More actions
June 30, 2023 at 10:52 am
Answer
#4223268

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

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.

cooldude001 Mr or Mrs. 500 Points: 525 More actions · Answer 2

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.

This reply was modified 1 year, 5 months ago by cooldude001.

cooldude001 Mr or Mrs. 500 Points: 525 More actions · Answer 3

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]