The subscription does not exist

-> I was trying to create a subscription using below query on an existing transactional replication publication,

Use [JBDB]
GO
EXEC sp_addsubscription
@publication = N'JBDBREPL',
@subscriber = N'JBResearch\IN2019_1',
@destination_db = N'JBDBSub',
@sync_type= N'initialize from lsn',
@backupdevicetype='DISK',
@backupdevicename=N'c:\temp\jbdb.bak',
@subscription_type = N'push',
@subscriptionlsn =0x00000027000002500001,
@update_mode = N'read only';

-> We received below error when above query was executed,

Msg 14055, Level 11, State 1, Procedure sys.sp_MSrepl_dropsubscription, Line 178 [Batch Start Line 130]
The subscription does not exist.

-> I tried creating subscription from GUI and got below message,

You must select a Publisher and publication to continue the wizard.

-> Then I realized that the publication was created using TSQL and had no articles added,

-> I then selected required articles on the Publication and tried creating the subscription using below query,

Use [JBDB]
GO
EXEC sp_addsubscription
@publication = N'JBDBREPL',
@subscriber = N'JBResearch\IN2019_1',
@destination_db = N'JBDBSub',
@sync_type= N'initialize from lsn',
@backupdevicetype='DISK',
@backupdevicename=N'c:\temp\jbdb.bak',
@subscription_type = N'push',
@subscriptionlsn =0x00000027000002500001,
@update_mode = N'read only';

-> I got below message this time,

Msg 21399, Level 16, State 1, Procedure sys.sp_MSaddautonosyncsubscription, Line 274 [Batch Start Line 130]
The transactions required for synchronizing the subscription with the specified log sequence number (LSN) are unavailable at the Distributor. Specify a higher LSN.

-> I had to take a fresh publisher database backup and then restore it on the subscriber and had to use proper LSN to correct this. Please use this article to get more details on configuring transactional replication using Initialize from LSN.

-> Above steps will not be required if snapshot is used. As soon as article are added to the publication, you will be able to create a subscription that uses snapshot agent without any issues.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission

-> I tried dropping a publication in a transactional replication setup and received below error,

TITLE: Microsoft SQL Server Management Studio

Could not delete publication ‘JBDBREP’.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18338.0&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.
Changed database context to ‘JBDBREPL’. (Microsoft SQL Server, Error: 15517)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2070&EvtSrc=MSSQLServer&EvtID=15517&LinkId=20476

BUTTONS:
OK

-> The error message points to some issue related to owner of the replicated database.

-> Right click the publisher database and click on properties. Click on “Files” tab and check for Owner.

-> In my case I see it blank and this seems to be the problem.

-> I executed below query to change the owner to SA. Instead of SA an appropriate login will also help.

USE [JBDBREPL]
GO
ALTER AUTHORIZATION ON DATABASE::[JBDBREPL] TO [sa]
GO

-> I tried removing the publication and it worked fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Power BI – Could not find file ‘C:\temp\AdventureWorks_Territories.csv’.

-> I opened a Power BI report and clicked on Refresh and got below error,

AW_Territories_Lookup – Could not find file ‘C:\temp\AdventureWorks_Territories.csv’.

-> I checked the file “C:\temp\AdventureWorks_Territories.csv”. It seems like someone have renamed the file to “C:\temp\AdventureWorks_Territories_AW.csv”.

-> One way to solve this issue is to rename the file from AdventureWorks_Territories_AW.csv to AdventureWorks_Territories.csv in location C:\temp.

-> In my case that was not possible as the team who changed it have used it on other reports.

-> Hence in my Power BI report, I clicked on “Transform data” and clicked on “Data source settings”.

-> I clicked on the file “C:\temp\AdventureWorks_Territories.csv” and clicked on “Change Source”.

-> Once the above changes are completed. Clicking refresh will just complete fine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.