Category Archives: Tech Notes

AI-900: Microsoft Azure AI Fundamentals

2022-01-20 Jon Culp

I got my first certification. I’ve been in IT for more than a decade and have always been fearful to attempt getting certifications. I had a fear of failure. I realize now there is no shame in failing a certification test, it just means you need to keep studying. Luckily, I passed! Define your goals and do the work to achieve them.

Business Intelligence, Tech Notes

Gartner vs Forrester 2021

2022-01-13 Jon Culp

I’ve been following Gartner’s magic quadrant and the Forrester Wave for a while now and decided to compare them for BI platforms, Integrations, and DB/DW. There are differences in what kind of platform and the attributes for scoring by each, but I wanted to try to line up the closest kinds of platform analysis that I had seen by each. I think it would be interesting to show how these have changed year-over-year to get a feel for momentum.

I didn’t pay for redistribution licensing for these, so I hope they don’t come after me.

Business Intelligence Platforms:

Integration Platforms:

Database / Data Warehouse:

Databases, MS SQL Server 2016, Tech Notes

Check Transactional Replication Status

2019-07-18 Jon Culp

select [distributionHistory].comments [Comments]
     , isnull([Last Error].error_text,'') as [Last Error]
     , convert(datetimeoffset, [distributionHistory].[time]) [Last Synchronized Date]
     , [distributionAgent].publication [Publication Name]
     , [distributionAgent].publisher_db as [Publisher Database]
     , (case
            when [distributionHistory].runstatus = '1' then 'Start'
            when [distributionHistory].runstatus = '2' then 'Succeed'
            when [distributionHistory].runstatus = '3' then 'InProgress'
            when [distributionHistory].runstatus = '4' then 'Idle'
            when [distributionHistory].runstatus = '5' then 'Retry'
            when [distributionHistory].runstatus = '6' then 'Fail'
            else cast([distributionHistory].runstatus as varchar)
        end
       ) [Run Status]
     , sub.name [Subscriber]
     , [distributionAgent].subscriber_db [Subscriber Database]
     , (case
            when [distributionAgent].subscription_type = '0' then 'Push'
            when [distributionAgent].subscription_type = '1' then 'Pull'
            when [distributionAgent].subscription_type = '2' then 'Anonymous'
            else cast([distributionAgent].subscription_type as varchar)
        end
       ) [Subscription Type]
     , und.UndelivCmdsInDistDB [Undelivered Commands]
from
    [distribution].[dbo].[MSdistribution_agents] as [distributionAgent] with (nolock)
    left join [distribution].[dbo].[MSdistribution_history] as [distributionHistory] with (nolock)
        on [distributionHistory].agent_id = [distributionAgent].id
    join
    (
        select s.agent_id
             , MaxAgentValue.[time]
             , sum(case when xact_seqno > MaxAgentValue.maxseq then 1 else 0 end) as UndelivCmdsInDistDB
        from
            [distribution].[dbo].[MSrepl_commands] t with (nolock)
            join [distribution].[dbo].MSsubscriptions as s with (nolock)
                on (
                       t.article_id = s.article_id
                       and t.publisher_database_id = s.publisher_database_id
                   )
            join
            (
                select hist.agent_id
                     , max(hist.[time]) as [time]
                     , h.maxseq
                from
                   [distribution].[dbo].MSdistribution_history hist with (nolock)
                    join
                    (
                        select agent_id
                             , isnull(max(xact_seqno), 0x0) as maxseq
                        from [distribution].[dbo].MSdistribution_history with (nolock)
                        group by agent_id
                    ) as h
                        on (
                               hist.agent_id = h.agent_id
                               and h.maxseq = hist.xact_seqno
                           )
                group by
                    hist.agent_id
                  , h.maxseq
            ) as MaxAgentValue
                on MaxAgentValue.agent_id = s.agent_id
        group by
            s.agent_id
          , MaxAgentValue.[time]
    ) und
        on [distributionAgent].id = und.agent_id
           and und.[time] = [distributionHistory].[time]
    join [master].[sys].[servers] as sub with (nolock)
        on [distributionAgent].subscriber_id = sub.server_id
	outer apply (
           select top 1
                  error_text
           from [distribution].[dbo].MSrepl_errors with (nolock)
           where id = [distributionHistory].error_id
           order by time desc
       ) as  [Last Error];

select [distributionHistory].comments [Comments]

, isnull([Last Error].error_text,'') as [Last Error]

, convert(datetimeoffset, [distributionHistory].[time]) [Last Synchronized Date]

, [distributionAgent].publication [Publication Name]

, [distributionAgent].publisher_db as [Publisher Database]

, (case

when [distributionHistory].runstatus = '1' then 'Start'

when [distributionHistory].runstatus = '2' then 'Succeed'

when [distributionHistory].runstatus = '3' then 'InProgress'

when [distributionHistory].runstatus = '4' then 'Idle'

when [distributionHistory].runstatus = '5' then 'Retry'

when [distributionHistory].runstatus = '6' then 'Fail'

else cast([distributionHistory].runstatus as varchar)

end

) [Run Status]

, sub.name [Subscriber]

, [distributionAgent].subscriber_db [Subscriber Database]

, (case

when [distributionAgent].subscription_type = '0' then 'Push'

when [distributionAgent].subscription_type = '1' then 'Pull'

when [distributionAgent].subscription_type = '2' then 'Anonymous'

else cast([distributionAgent].subscription_type as varchar)

end

) [Subscription Type]

, und.UndelivCmdsInDistDB [Undelivered Commands]

from

[distribution].[dbo].[MSdistribution_agents] as [distributionAgent] with (nolock)

left join [distribution].[dbo].[MSdistribution_history] as [distributionHistory] with (nolock)

on [distributionHistory].agent_id = [distributionAgent].id

join

(

select s.agent_id

, MaxAgentValue.[time]

, sum(case when xact_seqno > MaxAgentValue.maxseq then 1 else 0 end) as UndelivCmdsInDistDB

from

[distribution].[dbo].[MSrepl_commands] t with (nolock)

join [distribution].[dbo].MSsubscriptions as s with (nolock)

on (

t.article_id = s.article_id

and t.publisher_database_id = s.publisher_database_id

)

join

(

select hist.agent_id

, max(hist.[time]) as [time]

, h.maxseq

from

[distribution].[dbo].MSdistribution_history hist with (nolock)

join

(

select agent_id

, isnull(max(xact_seqno), 0x0) as maxseq

from [distribution].[dbo].MSdistribution_history with (nolock)

group by agent_id

) as h

on (

hist.agent_id = h.agent_id

and h.maxseq = hist.xact_seqno

)

group by

hist.agent_id

, h.maxseq

) as MaxAgentValue

on MaxAgentValue.agent_id = s.agent_id

group by

s.agent_id

, MaxAgentValue.[time]

) und

on [distributionAgent].id = und.agent_id

and und.[time] = [distributionHistory].[time]

join [master].[sys].[servers] as sub with (nolock)

on [distributionAgent].subscriber_id = sub.server_id

outer apply (

select top 1

error_text

from [distribution].[dbo].MSrepl_errors with (nolock)

where id = [distributionHistory].error_id

order by time desc

) as [Last Error];

Databases, MS SQL Server 2008 R2, MS SQL Server 2016, Tech Notes

T-SQL Rename Logical and Physical Database Files

2018-08-10 Jon Culp

I used to get really nervous about renaming database files, which I think is an appropriate response. Changing the attributes of the database’s physical objects does sound a bit scary on the surface, but once you’ve done it a few times you’ll realize it’s easy to do, and easy to fix if you make a mistake. This simple script template shows the basic steps of the process.

-- USE [master];
GO
--Change logical file names.
ALTER DATABASE [My_Database] 
  MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);
ALTER DATABASE [My_Database] 
  MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);
GO
--Disconnect all existing sessions.
ALTER DATABASE [My_Database] SET SINGLE_-- USER WITH ROLLBACK IMMEDIATE;
GO
--Change database to OFFLINE mode.
ALTER DATABASE [My_Database] SET OFFLINE;
GO

/******************************************
STOP! NOW RENAME THE PHYSICAL FILES ON DISK
******************************************/

--Point databases at new phyisical files.
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data', 
  FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log', 
  FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');
GO
--Change database to ONLINE mode.
ALTER DATABASE [My_Database] SET ONLINE;
GO
--Reopen connections to all users
ALTER DATABASE [My_Database] SET MULTI_-- USER;
GO

-- USE [master];

--Change logical file names.

ALTER DATABASE [My_Database]

MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);

ALTER DATABASE [My_Database]

MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);

--Disconnect all existing sessions.

ALTER DATABASE [My_Database] SET SINGLE_-- USER WITH ROLLBACK IMMEDIATE;

--Change database to OFFLINE mode.

ALTER DATABASE [My_Database] SET OFFLINE;

/******************************************

STOP! NOW RENAME THE PHYSICAL FILES ON DISK

******************************************/

--Point databases at new phyisical files.

ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data',

FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');

ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log',

FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');

--Change database to ONLINE mode.

ALTER DATABASE [My_Database] SET ONLINE;

--Reopen connections to all users

ALTER DATABASE [My_Database] SET MULTI_-- USER;

Business Intelligence, Tech Notes

Example Manufacturing Dashboard in PowerBI

2018-05-23 Jon Culp 4 Comments

Tech Notes, Windows

Can’t RDP to Server after Windows Patch (CredSSP)

2018-05-15 Jon Culp 2 Comments

I was preparing to remote into a virtual machine to do some maintenance when I encountered an error.

“An authentication error has occurred.
The function requested is not supported

Remote computer: [Computer Name]
This could be due to CredSSP encryption oracle remediation.
For more information, see https://go.microsoft.com/fwlink/?linkid=866660”

Information from the link in the error:

Credential Security Support Provider protocol (CredSSP) is an authentication provider that processes authentication requests for other applications.

A remote code execution vulnerability exists in unpatched versions of CredSSP. An attacker who successfully exploits this vulnerability could relay user credentials to execute code on the target system. Any application that depends on CredSSP for authentication may be vulnerable to this type of attack.

This security update addresses the vulnerability by correcting how CredSSP validates requests during the authentication process.

Options:

The bottom line is that in order to RDP into the target server both computers need to have the update installed. In my case, my local machine was updated, but the target machine was not yet updated. This target machine did not have automatic updates turned on and was an Azure VM. I could have contacted support, or turned on automatic updates from the portal, but the reality was I needed to get into the machine quickly to handle an emergency fix. Due to the simple architecture (stand-alone VM) I could not push the patch via SCCM or GPO.

Work-around 1:

Continue reading Can’t RDP to Server after Windows Patch (CredSSP) →

Business Intelligence

Power BI On-Premises Data Gateway Service Failures

2018-05-11 Jon Culp

Power BI On-Premises Data Gateway is a windows service (PBIEgwService) that moves data from on-premises to the cloud, and also is used by Power BI Report Server. The data gateway has been failing nearly daily. I’ve changed the service settings to restart upon failure with a 3 minute buffer. If this does not resolve the issue I might set up a heart-beat process that checks the health of the service and if it’s ever not running it will start it.

BIML, Links & Lists

Some BIML References

2018-05-09 Jon Culp

Strengthening my BIML skills for SSIS automation.

Biml Syntax Basics and Rules

Biml Resources and References

Generate Multiple SSIS Packages Using BIML and Metadata

BIML I – Getting Started with BIML

Databases, Links & Lists, Tech Notes

Microsoft Released SQL Operations Studio (preview)

2017-11-20 Jon Culp

Microsoft recently announced at the 2017 SQL PASS event that they would be previewing a new tool for sql server that works across multiple operating systems.

“SQL Operations Studio (preview) is a free, light-weight tool, that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they’re running.”

It’s now available for preview.

Read and Download:
https://docs.microsoft.com/en-us/sql/sql-operations-studio/what-is

More info (video) at TechNet:
https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/

Databases, MS SQL Server 2008 R2, MS SQL Server 2016, Tech Notes

Remove Numbers from Text SVF, Nested vs CTE

2017-10-11 Jon Culp

I need to remove numbers from a string while processing some data as part of an ETL process in a data warehouse. I have to do this for many columns and potentially many tables so I decided to put the logic into a Scalar-Valued Function (SVF), understanding there are performance detriments when using SVFs. Generally this will be used on relatively small data sets for cleaning “codes” that will be insert into a slowly changing dimension. I was curious about how to do this. My instinct was to do it using nested replace functions. I did a quick google search to see what options the collective would recommend. I came across a post on Stack Overflow that mentioned the nested replace functions, and then someone also mentioned using a recursive Common Table Expression (CTE), which I thought was a creative suggestion.

https://stackoverflow.com/questions/13240298/remove-numbers-from-string-sql-server/

I have a soft spot in my heart for recursive CTEs so I thought I’d try both options and see which solution performs better. Let’s start with building the functions:

--create Nested function
create function udf_RemoveNumbers_Nested
	(
		-- Add the parameters for the function here
		@String varchar(200)
	)
returns varchar(200)
as
begin
	-- Declare the return variable here
	declare @Result varchar(200);

	-- Add the T-SQL statements to compute the return value here
	select
		@Result = 
		replace(
		 replace(
		  replace(
		   replace(
		    replace(
			 replace(
			  replace(
			   replace(
			    replace(
				 replace( @String, '0', '' )
				 , '1', '' )
				, '2', '' )
			   , '3', '' )
			  , '4', '' )
			 , '5', '' )
			, '6', '' )
		   , '7', '' )
		  , '8', '' )
		 , '9', '' );

	-- Return the result of the function
	return @Result;

end;
go

--creat CTE function
alter function udf_RemoveNumbers_CTE
	(
		-- Add the parameters for the function here
		@String varchar(200)
	)
returns varchar(200)
as
begin
	-- Declare the return variable here
	declare @Result varchar(200);

	-- Add the T-SQL statements to compute the return value here
	;
	with cte
	as ( select
				@String as string
			  , 0 as n
		 union all
		 select
				convert( varchar(200), replace( string, n, '' ))
			  , n + 1
		 from	cte
		 where	n <= 9
	   )
	select
		@Result = (
					  select top 1	string from cte order by n desc
				  );

	-- Return the result of the function
	return @Result;

end;
go

--create Nested function

create function udf_RemoveNumbers_Nested

(

-- Add the parameters for the function here

@String varchar(200)

)

returns varchar(200)

begin

-- Declare the return variable here

declare @Result varchar(200);

-- Add the T-SQL statements to compute the return value here

select

@Result =

replace(

replace( @String, '0', '' )

, '1', '' )

, '2', '' )

, '3', '' )

, '4', '' )

, '5', '' )

, '6', '' )

, '7', '' )

, '8', '' )

, '9', '' );

-- Return the result of the function

return @Result;

end;

--creat CTE function

alter function udf_RemoveNumbers_CTE

(

-- Add the parameters for the function here

@String varchar(200)

)

returns varchar(200)

begin

-- Declare the return variable here

declare @Result varchar(200);

-- Add the T-SQL statements to compute the return value here

;

with cte

as ( select

@String as string

, 0 as n

union all

select

convert( varchar(200), replace( string, n, '' ))

, n + 1

from cte

where n <= 9

)

select

@Result = (

select top 1 string from cte order by n desc

);

-- Return the result of the function

return @Result;

end;

Now that we have some functions we need some test data so let’s create a simple table and populate it with simi-random data by leveraging the newid() function.

Continue reading Remove Numbers from Text SVF, Nested vs CTE →

Jon Culp [.com]

Category Archives: Tech Notes

AI-900: Microsoft Azure AI Fundamentals

Gartner vs Forrester 2021

Check Transactional Replication Status

T-SQL Rename Logical and Physical Database Files

Example Manufacturing Dashboard in PowerBI

Can’t RDP to Server after Windows Patch (CredSSP)

Information from the link in the error:

Options:

Work-around 1:

Power BI On-Premises Data Gateway Service Failures

Some BIML References

Microsoft Released SQL Operations Studio (preview)

Remove Numbers from Text SVF, Nested vs CTE

An Information Archive for a Frivolous Human