Phytagoras hypotenuse with PowerShell

To get the hypotenuse of a right angle triangle with Pythagoras formular using the two Methods Sqrt and Pow of the .NET System.Net class with PowerShell it is possible in just one line:

[System.Math]::Sqrt( [System.Math]::Pow(11.68, 2) + [System.Math]::Pow(7.24, 2) )

Which will give the implicit formatted result

13.7419067090415

Actually you do not have to spell out the namespace System as it is implicit to PowerShell. But I like to spell it out as a kind of documentation.

This is a example on using .NET features direct with PowerShell. Also I think it shows that you have to know .NET as a PowerShell user.

The numbers are from the dimensions in mm of a RJ45 plug. So now you know the inner size of the hole to drill to make it fit.

Add SQL Agent job step with tokens in output file name

 If you create a new SQL Agent job step with tokens in output file name using the stored procedure sp_add_jobstep or want to add a filename with tokens to an existing job step using the stored procedure sp_update_jobstep you will get a error like

A fatal scripting error occurred.
Variable SQLLOGDIR is not defined.

In this case I was trying to use the token SQLLOGDIR, but it does not matter which token.

Many users of Ola Hallengren (LinkedIn) Maintenance Solution (link) had similar errors. Actually Ola is aware of the challenge - he just forgot to promote his solution ;-)

When you use the stored procedure sp_add_jobstep you with that call the internal stored procedure sp_add_jobstep_internal. You can see the definition of both procedures in the system database msdb with SQL Server Management Studio (SSMS).

The root-challenge is that somewhere along the path from calling sp_add_jobstep to setting the output file name on the job step the content of the parameter is interpreted. But at that point the execution is outside SQL Agent, and the tokens are not available.

The trick - that Ola forgot to promote - is to build a string with each token element seperated.

DECLARE @_token_log_directory NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(SQLLOGDIR))';
DECLARE @_token_job_name NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(JOBNAME))';
DECLARE @_token_date NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(DATE))';
DECLARE @_token_time NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(TIME))';
DECLARE @_output_file_name NVARCHAR(MAX) = @_token_log_directory + N'\' + @_token_job_name +  N'_' + @_token_date + N'_' + @_token_time + N'.txt';

This string can then be used as parameter value to the parameter @output_file_name in the stored procedures to a SQL Agent job step.

EXECUTE msdb.dbo.sp_update_jobstep @job_name=N'IndexOptimize - Db1.Schema1.Tbl1',
  @step_id=1,
  @output_file_name= @_output_file_name;

If you dig into Olas code to install the maintenance solution and search for the string „@TokenLogDirectory“ you will see that Ola build this string element by element. With careful seperation of $-sign and token names.

UDL-file connection to SQL Server

UDL is a component in Windows named Universal Data Link. To be more specific it is a OLE DB tool that is a connection defined in a UDL-file a with a GUI to edit the connection.

The file is usually crated as a empty file with the type ".udl".

The GUI has three tabs as follows:

Provider

A list of local available OLE DB providers for various sources like SQL Server components. The default provider is "Microsoft OLE DB Provider for SQL Server". But the provider "Microsoft OLE DB Driver for SQL Server" is the latest SQL Server provider which I will continue with here as I would recommend using it.

The provider "SQL Server Native Client" should be avoided as it is deprecated.

Connection

Basic connection configuration points are available here for different types of connection on the given provider. Some typical configuration points are:

• Server name: The server name can be short or long (FQDN) and can also be with protocol and port information. If you try to select a server the UDL GUI will try to scan the network for a server.
• Log on: The default is unfortunately "SQL Server Authentication" but I would recommend "Windows Authentication" in general. This also has a field for Server SPN if you want Kerberos authentication.
  There are other Active Directory log on that could be relevant to more special situations. These are not available with other providers.
• Database: A drop-down filed where a database can be entered or selected. If you want to select a database the UDL GUI will try to connect to the server.
  A more special situation is the possibility to connect to a database file as a attached database. This is sometimes used in more dynamic development situations.

Also there is the button "Test Connection" where the text explains quite well what the button does.

Advanced

This tab will only show some other configuration points. The three tabs together will not show all configuration points.

All

This is the access to all configuration points on the given provider. Some point for timeout, encryption or Application Name are only available in UDL GUI here.

Edit file

After entering the values and saving them by clicking OK you can edit the UDL-file with a text editor. This way you can see a working example of a connection string with the given provider and configuration values. This can be quite handy when working on a effective connection string or a string with specific features.

Reference

Microsoft Docs: "Universal Data Link (UDL) configuration"

Microsoft Docs: "Microsoft OLE DB Driver for SQL Server"

Top-ten posts 2022

2022 was a year with far fewer posts than expected. But there still were some activity on this blog.

The top-ten 2022 is a little different from top-ten 2021.

Rank	Move	Rank 2021	Title	Views	Created
1	0	1	SqlBulkCopy with PowerShell	1530	2017-07-30
2	>9	>10	Move table to filegroup	1390	2015-09-22
3	+3	6	Audit Log for Analysis Services	476	2014-01-08
4	-2	2	ISO 8601 date formatting using PowerShell	439	2008-08-14
5		N/A	Windows Server 2022 preview on VMware Workstation	391	2021-03-10
6	-3	3	DBCC CHECKDB with PowerShell	384	2014-10-09
7	-3	4	xp_instance_regwrite syntax	150	2013-09-10
8	0	8	SQL Server Agent schedule owner	140	2010-02-08
9	>2	>10	Generate sqlcmd statements	93	2010-02-10
10	>1	>10	Sandbox Active Directory in VMware Workstation	82	2018-12-28

Some posts I have updated since the creation, and then the history is described in the post itself.
The oldest post is more than ten years old, and that I personally find quite satisfying.
There is more than ten times views difference between some posts which is significant.

The blog is more a personal collection of refined notes than public presentations. This is one of the reasons that I am fine with the limited amount of views.

MSDTC Operations

Microsoft Distributed Transaction Coordinator, abbreviated MSDTC or just DTC, is a somewhat aged but still very essential technology from Microsoft.

As a SQL Server DBA I have primarily met MSDTC under the Linked Server functionality. Here MSDTC serves the technical transactions between the SQL Server installation where the linked server is defined and the target of the linked server on another Windows Server. The MSDTC usage is implicit when using a linked server, primarily as the linked server is based on the OLEDB technology.

As a side note this is why you will often see OLEDB waits in SQL Server when working over a linked server.

Graphical User Interface

The primary GUI for MSDTC is the – also – elder Microsoft Management Console (MMC) component named "Component Services". This is a COM+ snap-in for MMC. If you are using a Danish Windows then the component is named "Komponenttjenester". It is not easy to find in-depth documentation on COM+ or MMC, and the voluminous (five-volume) "COM+ Developers´s Reference Library" is unfortunately no longer available in the book stores, that is either as new or used.

Usually you are working on the local MSDTC. You might find the path in Component Services long, but here it is:

1. Console Root
2. Component Services
3. Computers
4. My Computer
5. Distributed Transaction Coordinator
6. Local DTC

When you right-click Local DTC you have access to the properties of the Local DTC with three configuration areas in separate tabs. The first tab is on MSDTC tracing configuration.

The second tab is on MSDTC logging configuration.

The third and last tab is on MSDTC security configuration.

All the examples are with default values.

When expanding Local DTC you see two items:

• Transaction List
• Transaction Statistics

The first item Transaction List will give you a simple list of active transactions. But there is not much more you can see on each transaction or the transaction history.

The second item Transaction Statistics give you a rather simple graphical presentation of a few measures. There are no possibilities to drill-down or get other other details.

I think that the limited monitoring shows the age of the MSDTC component.

MSDTC Log Files

Is a standard Windows installation the MSDTC log files are in

C:\Windows\System32\MsDtc

The log files are in a proprietary binary Microsoft format, with no local tools to read the log files.

You can open the MSDTC log files with the command line tool tracefmt.exe. But you will have to download Windows SDK to get this tool. With Windows SDK you will get tracefmt.exe in both 32- and 64-bit editions in these (default) locations:

• C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x64
• C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x86

The paths are examples from the current Windows SDK version.

Using tracefmt.exe is not complicated but also not just point-and-click. Read the documentation and spend some time with the syntax to get a output you can use.

MSDTC Application Error

Some errors will surface in Windows Application Event Log like this example.

Log Name: Application
Source: Microsoft-Windows-MSDTC Client 2
Date: 28-11-2022 07:34:19
Event ID: 4879
Task Category: CM
Level: Warning
Keywords: Classic
User: N/A
Computer: SQLDB42.sqladmin.lan
Description:
MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system SQLDB666.

MSDTC in AlwaysOn Availability Groups

As SQL Server AlwaysOn Availability Groups are based on Windows Failover Cluster, among others, you will have to go through some configuration details to get a robust transaction even during a failover. There are some basic documentation like Configure distributed transactions for an AlwaysOn availability group, but I really think you will have to do some more reading, build a sandbox and try several solutions thoroughly before going live.

This could be important if you are working wrestling with an application build with a high-level framework like .NET Entity Framework or Java Hibernate, as such frameworks tend to use client-side transactions and then rely on distributed transactions.