PowerBI SQLWATCH Dashboard Refresh

DataAnalyst · 27 October 2020 14:17

Are there more people experiencing data refresh problems with the SQLWATCH dataset?
Out of the blue it is failing many times, some times it is working but 9 out of 10 times it is failing with the error: Unable to connect to the data source undefined.

I already tried removing and setting up the gateway again.
Deleting report and dataset, clear cache and publishing again. but nothing seems to help.
The strange thing as said before is that it is not always failing…

Marcin · 27 October 2020 14:58

Can you correlate this with server load, or with the amount of data you’re pulling? Maybe you’re trying too pull too much and it times out or gives up etc.

DataAnalyst · 8 November 2020 09:01

It seems to be a Power BI Issue, some times it works some times it isn’t
Tried changing refresh times, and that seems to help a little bit.

Another question I have: on the page “Servers” you can find the SQL Version.
In my power bi report it still says 2017 version CU13 while I already updated all my servers a week ago to CU22 version. looing at the checks etc. my reports is refreshing…

Marcin · 10 November 2020 20:11

Which version are you running? I seem to recall there was a problem with the version reporting some time ago.

DataAnalyst · 10 November 2020 21:29

Version 2.6 released July 22

Marcin · 10 November 2020 21:57

Looks like this is only populated during the installation. There is nothing that will refresh the version once installed. I will have to look at fixing this in the next version.

DataAnalyst · 11 November 2020 08:04

Thanks that would be great, do you think reinstalling would help? just to have it up to date?

Marcin · 11 November 2020 10:40

Yeah it would but its an overkill. The value is in the [dbo].[sqlwatch_meta_server] table and comes from the @@VERSION variable. You can update it yourself.

Here’s the script:

github.com

marcingminski/sqlwatch/blob/3.x/SqlWatch.Monitor/Project.SqlWatch.Database/Scripts/Post-Deployment/Reference-Data/Script.PostDeployment-LoadMeta-Server.sql

/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
begin transaction
	/* add local instance to server config so we can satify relations */
	merge dbo.sqlwatch_config_sql_instance as target
	using (select [servername] = @@SERVERNAME, [repo_collector_is_active] = 0) as source
	on target.sql_instance = source.[servername]
	when not matched then
		insert (sql_instance, [repo_collector_is_active])
		values (source.[servername], source.[repo_collector_is_active]);

This file has been truncated. show original

To clarify, this is the script that runs during the installation, you’d need to turn it into an update statement or add an update clause to the merge.