Product:
Microsoft SQL server 2016

Issue:

SSIS job fail with error:

Could not find database ID 42, name ’42’. The database may be offline. Wait a few minutes and try again.

Solution:
Check if the database have change ID.

select * from sys.databases

Above will list the database name and id.

When you do a detach and attach on a database file, you can if you are unlucky, get a new ID on the database. Then old SSIS job that worked yesterday, will fail.

Repeat the detach and attach process to get the database back to correct number.

Move file locations with ALTER command instead; this script will list the commands for each database

SELECT DB_NAME(database_id) AS [Database] 
, name AS [LogicalName]
, type_desc AS [FileType]
, physical_name AS [FilePath]
, 'ALTER DATABASE ' + DB_NAME(database_id)
+ ' MODIFY FILE ( NAME = ' + name + ', FILENAME = ''' + physical_name + '''); 'AS Command
FROM SYS.master_files
WHERE database_id > 4
ORDER BY database_id ASC, type_desc DESC;

You have to change the path to new location before you run the command.

Also copy the database files to the new location before you run the script.

https://blog.coeo.com/moving-sql-databases

More Information:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15

Understanding different SQL Server database states

https://www.mssqltips.com/sqlservertip/1420/sql-server-system-databases/

Product:

SAS

Microsoft SQL server 2016

Issue:

Some tables are not visible in SAS client program, even do the same user can see the tables in Microsoft SQL Server Management Studio (SSMS) or Tableau.

As the same user can see the tables inside Microsoft SQL Studio, it is not a permission issue.

Solution:

Change the tables and views in SQL to have shorter names. SAS can not show SQL tables or views that have a name longer than 32 characters.

More Information:

http://support.sas.com/documentation/installcenter/en/ikforecastwofrsr/75572/HTML/default/index.html

https://www.tableau.com/sv-se/products/desktop

Product:
Microsoft Power BI
Microsoft Windows 2016 server

Issue:
You have a working Dynamic SQL query, but when you schedule it, it stop working.

Suggested solution:
Rebuild your query to not use Dynamic SQL, to make it possible to schedule it in PowerBi.

See if you can change to use fewer different data sources.

More Information:

https://docs.microsoft.com/en-us/power-query/dataprivacyfirewall

https://medium.datadriveninvestor.com/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-bi-409ccec7337b

https://community.powerbi.com/t5/Service/Dynamic-SQL-through-ODBC-is-breaking-refresh-capability/m-p/276408

https://powerbitalks.com/2020/06/refresh-dataset-using-button-powerbi.html

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

Product:
Microsoft PowerBI report server  Version 1.10.7737.32652 (January 2021)
Microsoft Windows 2016 server

Issue:
Some of the powerbi reports does not refresh from the portal or on schedule. You get a error message like this;

2022-01-19 : |ERROR|63|Error Processing Data Model Refresh: SessionId: , Status: Error Failed to refresh the model, Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. Error returned: ‘COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource … is not found..
COM error: Microsoft.PowerBI.ReportServer.ASEngineManagedRoot, Datasource … is not found..
The current operation was cancelled because another operation in the transaction failed.

The powerbi report can be refreshed from PowerBI for Desktop. Also the report works from a different PowerBI server.

You can check error messages in folder c:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles

Suggested solution:

Restart the windows PowerBIReportServer service, to release memory or cache that is used by PowerBI server.

More information:

ERROR|19|Failed to get connection string for model | RequestID =  ClientSessionID =  Microsoft.PowerBI.ReportServer.AsServer.AsConnectionException: Failed to get connection string for model

error in the log file can be because you have 2 data sources in the model pointing to the same database, where the database name was all lower case in one source and upper case in the other. Use the advanced editor in PowerBI desktop to rename them to be unique and then there was one source and data refreshes fine. You must use the same letter format on all fields.

https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh