Tag Archives: SQL

TaskCentre SQL Performance – One way to improve performance

When using the “ODBC” or “OLEDB” tools in TaskCentre, it may appear that a single query is executed on the database. In actual fact from trial and error, I have identified multiple queries are being called.

For example, if you run the following query (This query identifies all Item Ledger Entries, where the timestamp is odd):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

Notice, that the transaction isolation level is READ UNCOMMITTED. This means that the query will read the dirty data and not block other users. Also it is limited to 1000 rows.

However when the task is running, it appears that the following SQL queries are run against the database:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

and

SELECT COUNT(*)
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0

The first query returns the data, the 2nd query returns the number of rows.

Notice in the 2nd query, the transaction isolation level is not declared… (In this case, will cause blocking). Also the TOP statement has been removed too, meaning that all records are counted – not good especially when there is a complex filter such as the above which can be time consuming.

Solution

A solution for this is to disable the counting of the records. One way I have identified how to do this is to UNION to a table returning no rows. (As obviously you don’t want to return rows to corrupt your SQL query).

In this example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 1000 [Item No_]
	,[Posting Date]
	,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0
UNION
(SELECT NULL,NULL,NULL WHERE 1<>1)

I’ve identified that when UNION is used, Taskcentre does not count the number of records. This means that if you are using the data, the “RowCount” will return -1 instead of the actual data. If you need this for a further step (such as a decision), then you have to calculate it by looping round in VB as per this knowledgebase article.

Note and Disclaimer:
I don’t have knowledge of how the software actually works internally, so this document is only based on my experience and investigation, so is provided as-is.

Automatic T-SQL Formatting with Microsoft SQL Server Management Studio 2014

Within MySql Workbench, there’s a feature called “Clean Up SQL” which automatically tidies up your SQL and the indentation automatically.

This is a very useful feature, as your messy SQL then becomes nice and neat. Unfortunately with Microsoft SQL Server Management Studio (SSMS), this feature is not standard.

Poor Mans T-SQL Formatter - Format T-SQL CodeLuckily I came across the following open source T-SQL formatter library, complete with an add-in for SSMS which adds this functionality – Poor Mans T-SQL Formatter. This plugin automatically formats your SQL, adding tabs and linebreaks etc in order to make it easy to read.

After installing the product, the following folder needs renaming in order to work with the 2014 version of SSMS:

%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\11.0

To:

%SystemDrive%\ProgramData\Microsoft\SQL Server Management Studio\12.0

This is due to the installer. The installer puts the files in the incorrect place for the 2014 version of SQL Management Studio. Once you have renamed the folder and restarted SSMS, it will automatically add the options to the Tools menu.
Poor Mans T-SQL Formatter File Changes

Not only does this plugin work for SQL Management Studio, there are also versions for:

If you don’t wish to install the software, there’s an online version available.

Estimate Completion Time For Long Running SQL Query

With Microsoft SQL Server, sometimes there can be long running SQL Queries which you would like to get an estimated ETA from.

Tim Ford has wrote a brilliant article which will provide an ETA for the following types of queries:

  • Database Backup
    • BACKUP
  • Database Restore
    • RESTORE
  • Index Reorganisation
    • DBREINDEX
  • DBCC Operations, such as:
    • SHRINKFILE
    • SHRINKDATABASE
    • CHECKDB
    • CHECKTABLE
  • Rollback Operations
    • KILLED/ROLLBACK

Running the following SQL query will give the following information for all transactions which are awaiting completion:

  • Session ID
  • Percentage Complete
  • Time Elapsed in Seconds
  • Wait Type
  • Wait Time
  • Last Wait Type
  • Estimated Completion Time
  • SQL Transaction Text
  • SQL Statement Currently Executing
SELECT R.session_id, 
R.percent_complete, 
R.total_elapsed_time/1000 AS elapsed_secs, 
R.wait_type,
R.wait_time,
R.last_wait_type,
DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time,
ST.text, 
SUBSTRING(ST.text, R.statement_start_offset / 2, 
 (
 CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
 ELSE R.statement_end_offset
 END - R.statement_start_offset 
 ) / 2
) AS statement_executing
FROM sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST
WHERE R.percent_complete > 0
AND R.session_id <> @@spid
OPTION(RECOMPILE);

Microsoft SQL Server – Database Recovery After Restart – ETA for Recovery

When restarting SQL Server during the middle of a large transaction (say committing 100k rows), the database needs to recover by rolling back the transactions. The may take a while and removes all access to the database.

SQL Management Studio is not very helpful in giving an time for recovery, however the following commands come in useful in estimating the ETA:

SQL Server 2008

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
	 [LogDate]
	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
	,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC

SQL Server 2012/2014

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
	 [LogDate]
	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
	,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC

Thanks Tim Lagua for original post.

XML timestamptype using VBScript or SQL

The XML “timestamptype” date/time format is as follows:

2015-11-19T11:02:02.000000

Recently during a project of integrating systems together and creating XML files, I’ve had to create this in VBScript and SQL.

Here’s the code:

SQL

convert(varchar(50),getdate(),127)

VBScript:

Year(Now) & "-" & Right("0" & Month(Now),2) & "-" & Right("0" & Day(Now),2) & "T" & Right("0" & Hour(Now),2) & ":" & Right("0" & Minute(Now),2) & ":" & Right("0" & Second(Now),2) & ".000000"

As you can see SQL is a lot cleaner than VBScript!

Drop All Triggers from MS SQL Database

Recently I’ve had the scenario where I’ve had to drop all SQL Triggers from a Microsoft SQL database.

This could be done manually through the SQL Management Studio, but with a database with lots of triggers can be time consuming to do this.

The following SQL when ran against the database will drop all triggers from the said database automatically:

 USE [{database Name}]
DECLARE @SQLCmd nvarchar(1000)
DECLARE @Trig sysname
DECLARE @owner sysname
DECLARE @uid int

DECLARE TGCursor CURSOR FOR
SELECT name, uid FROM sysobjects WHERE type = 'TR'
OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @Trig, @uid
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + user_name(@uid) + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @Trig, @uid
END

CLOSE TGCursor
DEALLOCATE TGCursor

Orphaned Windows Logins on SQL Server

Recently I’ve had to restore a SQL 2012 backup from a production machine to a test machine. Unfortunately as part of that restore process, logins to the database where not created.

As these users where “Orphaned” i.e. existed in the database, but not in the server, users where not able to login. These can be created manually, however is slow and time consuming where there are multiple users.

The following SQL (from Ted Krueger’s article) automatically created all the logins. The code works in SQL 2005, 2008, 2008 R2 and 2012. It may work in 2014 and 2016, however this is not tested.

SET NOCOUNT ON
USE AdventureWorks
GO
DECLARE @loop INT
DECLARE @USER sysname
DECLARE @sqlcmd NVARCHAR(500) = ''
 
IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL 
 BEGIN
  DROP TABLE #orphaned
 END
 
CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1))
 
INSERT INTO #Orphaned (UserName)
SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA')
 
IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
 SET @loop = 1
 WHILE @loop < = (SELECT MAX(IDENT) FROM #Orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
     BEGIN
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS'
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
         BEGIN
            SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password'''
            Exec(@sqlcmd)
            PRINT @sqlcmd
         END
     END
     
    SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']'
    Exec(@sqlcmd)
    PRINT @USER + ' link to DB user reset';
    SET @loop = @loop + 1
  END
END
SET NOCOUNT OFF

Automating Reports from Microsoft Dynamics Nav using ETL Tools

Recently I’ve been looking into a piece of software called ETL-Tools – Advanced ETL Processor Enterprise.

This software is designed to perform ETL operations, similar in which to Taskcentre does.

The software is quite well priced, with a single user licence being between $340 and $690 (£216 and £440). This is a purchase cost and includes a years worth of support. Annual maintenance is 20%, but is optional. A site licence is also available which allows unlimited users.

In this blog post, I’m going to cover creating an automated task to generate a PDF document with the sales totals for company, split by salesperson code. The report is designed to run daily and is sent to the sales manager in order to view the progress.

This is using the Nav demonstration database – Cronus, with Nav 2015, running on Server 2012.

Creating Connections
ETL Tools Nav ConnectionFor this task, we’re going to need 2 connections as follows:

  • MS SQL Database – Connection to the Nav Database
  • SMTP – In order to send the email

After opening Advanced ETL Processor, it’s a simple task of right clicking on the blank connections and choosing New. In the screenshot on the right, I’ve shown the Nav connection.

Creating The Report
ETL Tools Report WizardOn right clicking to create the report a wizard is shown. This wizard takes you through a few steps in order to create the basis of the report:

  • Source Connection (In this case, it’s the connection we’ve created earlier)
  • SQL Query
  • Fields to add to the report
  • Groupings
  • Report Orientation
  • Report Layout (Rows or Columns)
  • Theme

After completing the wizard, this opens up in design view for further editing and design:
ETL Tools Report Designer

For this task, I’ve used all default options, plus the following SQL:

Select
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code],
   Sum([CRONUS UK Ltd_$Sales Line].Amount) AS Sum_Amount
From
   [CRONUS UK Ltd_$Sales Header] Inner Join
   [CRONUS UK Ltd_$Sales Line] On [CRONUS UK Ltd_$Sales Header].[Document Type] =
   [CRONUS UK Ltd_$Sales Line].[Document Type] And
   [CRONUS UK Ltd_$Sales Header].No_ =
   [CRONUS UK Ltd_$Sales Line].[Document No_]
Where
   [CRONUS UK Ltd_$Sales Header].[Document Type] = 1
Group By
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code]

Create Package
After creating the report, we need to create a package to perform the following steps:

  • Run Report
  • Save PDF to Disk
  • Send Email with PDF Attachment

Creating the package is very simple, right click and choose add. We need to drag two “Blocks” onto the planner, “Report” and “Send Email” and join these up together:
ETL Tools Package Design

The screenshots below shows the setup of these two steps:
Run Report
ETL Tools Package Design - Run Report

Send Email
ETL Tools Package Design - Send Email

Running Task
The task can be run by either opening up the Package, right clicking and choosing “Run Package” (F3). Alternatively, under the schedule tab, it can be scheduled to run automatically.

In this example, I’ve set the email to be run manually and sent to my email address. Example in the screenshot below:
ETL Tools Emailed Report

In due course I will do some further investigation into ETL Tools. Watch this space!

Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Came across this website this evening while doing some research, wish I’d have found it earlier. Very useful in brushing up your skills in Microsoft products, including Server 2012, Azure, Office 365 etc. And best of all, it’s free…!

Looking for a simple, effective way to get training on Microsoft’s Cloud technologies? Microsoft Virtual Academy!

Source: Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Base64 Encoding and Decoding with SQL Server – Unicode Characters

With a SQL query I’ve had to write, I’ve had to encode some text in Base64.

The following functions where extremely useful, this is based on this solution, but has been adapted to handle Unicode characters.

Convert to Base64

CREATE FUNCTION [dbo].[fn_str_TO_BASE64]
(
    @STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'NVARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@STRING AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp
    )
END

Convert from Base64

CREATE FUNCTION [dbo].[fn_str_FROM_BASE64]
(
    @BASE64_STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT 
            CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@BASE64_STRING"))', 'VARBINARY(MAX)') 
            AS NVARCHAR(MAX)
            )   UTF8Encoding
    )
END

Base64 Encoding ExampleHere’s how you run the SQL queries:

SELECT dbo.fn_str_TO_BASE64('Hello World')

SELECT [dbo].[fn_str_from_base64](dbo.fn_str_TO_BASE64('Hello World'))