2022 | SansSQL

Wednesday, September 14, 2022

Replace nulls with zeros in Power BI

This video demonstrates on how we can relace a specific value with another value. One such case explained in this video is on how we can replace nulls with zeros. 

Sunday, August 28, 2022

Connect to MySQL DB | This connector requires one or more additional components to be installed

This video describes how to connect to MySQL Database and fix an common issues with the Power BI MySQL connector  which says "This connector requires one or more additional components to be installed". 

Download link to MySQL NET Connector

Tuesday, August 9, 2022

Combine Multiple files and load to Power BI

In this video we discuss on how we can combine multiple files and load into Power BI. For the demo, we have the following 3 scenarios

  1. Data Sets with same number of Columns
  2. Data Sets with different number of Columns
  3. Data Sets with different file types

Friday, August 5, 2022

Power BI Content Sharing - Who can see what


This video explains on who can see what content when Power BI content is created or shared across the license types.

Using Custom fonts in Power BI


Custom Fonts are one of the major factors of Branding and for enterprises branding means a lot. This video demonstrates how to use custom fonts in Power BI which are currently not available by default.

Saturday, July 16, 2022

Unzip and extract file contents to Power BI

 
This video describes how to unzip and extract file contents to Power BI. Reading zip files without extracting them. 

Friday, July 15, 2022

Display Alternate text when a image URL is not present in Power BI

 
This video describes how to display alternate text when there is no data present for the image URL in Power BI.

Thursday, July 14, 2022

Display default image when there is no data present for the image URL in Power BI

 

This video describes how to display a default image when there is no data present for the image URL in Power BI.

Tuesday, July 12, 2022

Display URL's as images in Power BI

 

This video describes how to display URL's as images in Power BI.

Friday, July 8, 2022

Connect to Azure Data Lake from Power BI


This video describes how to connect to Azure Data Lake from Power BI.

Download link to Azure Open Datasets: 
https://docs.microsoft.com/en-us/azure/open-datasets/dataset-catalog

Wednesday, May 25, 2022

T-SQL to find Server and SQL restart date time

This script comes handy when you would like to find out the Server and SQL services restart times.
DECLARE @SQLServiceRestartDateTime AS VARCHAR(50),
		@ServerRestartDateTime AS VARCHAR(50)

SELECT @SQLServiceRestartDateTime = create_date FROM sys.databases WHERE database_id=2
SELECT @ServerRestartDateTime = CAST(DATEADD(SECOND, (ms_ticks/1000) * -1, GETDATE()) AS datetime) FROM sys.dm_os_sys_info

SELECT @SQLServiceRestartDateTime AS [SQL Service Start DateTime], @ServerRestartDateTime AS [Server Start DateTime]

Friday, May 20, 2022

T-SQL to find Backup or Restore Progress

Here is a script that comes handy while performing a huge database Backup or Restore. This script provides the details on the progress of the Backup or Restore operation including the estimated finish time.
SELECT 
   session_id AS SPID, command AS [Command], a.text AS Query, start_time AS [Start Time], 
   percent_complete AS [Percent Complete],
   dateadd(second,estimated_completion_time/1000, getdate()) AS [Estimated Completion Time]
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command like 'BACKUP%' OR r.command like 'RESTORE%') 

Monday, May 16, 2022

T-SQL to find Navigation path in CMS

In large enterprises there will be huge number of SQL servers which will be registered within a Central Management Server (CMS) and times it may become difficult to find out where the server is registered atleast for new team members in the Huge pile of servers and folder structure. This query give you the path where the specified servers is registered within the CMS.
DECLARE @ServerName nvarchar(50)
SET @ServerName='SQL01';

WITH CMSCTE
AS
(
--Anchor
SELECT server_group_id, name, description, parent_id, 1 AS [Level], CAST((name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS A
WHERE parent_id IS NULL
UNION ALL
--Recursive Member
SELECT B.server_group_id, B.name, B.description, B.parent_id, C.[level] + 1 AS [Level], CAST((C.CMSPath + '->' + B.Name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS B
JOIN CMSCTE AS C 
ON B.parent_id = C.server_group_id  
)

SELECT TOP 1 CMSPath AS 'Path in CMS' , B.name as 'Server Name', B.description AS 'Server Description', A.name AS 'Group Name', A.description AS 'Group Description'
FROM CMSCTE AS A
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS B
ON A.server_group_id=B.server_group_id
WHERE B.name = @ServerName
ORDER BY [Level] DESC

Friday, April 22, 2022

T-SQL to find Remote Server Name of the linked server

At times it happens that you might have named a linked server with a friendly name that the actual server name. As the time flies and people change in the team, it will become difficult to identify how or to where this is linked.  You can find the information using the sp_linkedservers stored procedure but what if the linked server configured uses a entirely different DNS name than the actual server? The below query helps to get the Remote Server Name with few other details when run against a linked server.  

DECLARE @linkedServerName varchar(100), @sql varchar(max)
SET @linkedServerName='Your Linked Server Name'
SET @sql = CONCAT('SELECT * FROM OPENQUERY([',@linkedServerName , '], 
''SELECT ''''',@linkedServerName,''''' AS LinkedServerName, 
@@SERVERNAME AS RemoteServerName, SUSER_SNAME() AS ConnectedWith, DB_NAME() AS DefaultDB'')')
EXEC (@sql)


Ads