SQL Server Nuggets

https://www.sqlshack.com/sql-server-replication-configuring-snapshot-and-transactional-replication/  

https://www.sqlshack.com/how-to-implement-error-handling-in-sql-server/  

 select * from sys.dm_server_services

 6/8/2025

 Got to https://<servername>/reportserver Scroll to the bottom of the page: Microsoft Power BI Report Server Version 15.0.1117.98 Also check the version of Power BI Desktop for Report Server https://www.microsoft.com/en-us/power-platform/products/power-bi/report-server On 6/8/2025 has download for May, 2025

  The above link says the error could be from upgrading SQL 2014 to 2019 I'm using Power BI Report Server which was recently upgraded to the January 2025 release. https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-january-2025-feature-summary/ My error was ignoring the data driven subscription configuration page where it tells you what columns the DDS needs to return.

  SELECT s.SubscriptionID, c.Name , c.Type ,s.EventType , c.Description , u.UserName AS CreatedBy , c.CreationDate , c.ModifiedDate , s.Description AS Subscription , s.DeliveryExtension AS SubscriptionDelivery , d.Name AS DataSource , s.LastStatus , s.LastRunTime , s.Parameters , sch.StartDate AS ScheduleStarted , sch.LastRunTime AS LastSubRun , sch.NextRunTime , c.Path ,s.DataSettings FROM Catalog c INNER JOIN Subscriptions s ON c.ItemID = s.Report_OID INNER JOIN DataSource d ON c.ItemID = d.ItemID LEFT OUTER JOIN Users u ON u.UserID = c.CreatedByID LEFT OUTER JOIN ReportSchedule rs ON c.ItemID = rs.ReportID LEFT OUTER JOIN Schedule sch ON rs.ScheduleID = sch.ScheduleID WHERE (c.Type = 2) and s.DataSettings is not null and s.LastRunTime > getdate()-31 ORDER BY c.Name

  https://www.sqlshack.com/ssrs-failed-subscription-alerting/

  https://www.sqlshack.com/for-xml-path-clause-in-sql-server/

https://www.youtube.com/watch?v=EfozSaBzlqk  

https://www.youtube.com/watch?v=dWicqCMvWow  

https://www.youtube.com/watch?v=OBNIoyqnRLg   Sample Query: select de.Name, stuff(( select ',', em.NationalIDNumber as [text()] from HumanResources.Employee em inner join HumanResources.EmployeeDepartmentHistory eh on em.BusinessEntityID = eh.BusinessEntityID and eh.EndDate is null where eh.DepartmentID = de.DepartmentID for xml path('') ), 1, 1, '') from HumanResources.Department de order by de.Name NOTES: as [text()] removes the XML tags in the single column. This just returns a comma-separated list in the single column. STUFF removes the leading ','. see 1,1,''

https://www.youtube.com/watch?v=EBED7tixs0s   Check query at 19:00 using FOR XML PATH Subscription 27:21 Review of stored proc w/dynamic SQL that handles the multi-vlued parameter 36:35

https://www.sqlshack.com/how-to-administer-sql-server-reporting-services-ssrs-subscriptions-using-powershell/  

https://www.mssqltips.com/sqlservertip/6616/ssrs-data-driven-subscription/