Tag Archives: SQL

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'))

Stripping all Non Alphanumeric Characters From String in SQL

With one of the projects I’m currently working on using Taskcentre, I required all non alphanumeric characters stripping from a string in a SQL query (in order to perform an inner join with another table).

With a bit of research I found a brilliant solution here, by Even Mein (Why re-invent the wheel!)

This solution involves creating a function as follows:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['[email protected]+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

The function can be called as part of the SQL query as follows:

Alphabetic only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z')

Numeric only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^0-9')

Alphanumeric only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z0-9')

Non-alphanumeric:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', 'a-z0-9')

Selective Reindexing of Indexes in SQL Server

I’ve wrote many scripts over the years to selectively reindex indexes and update statistics on Microsoft SQL Server. However more recently I’ve been using Hallengren’s SQL reindex script as it’s very powerful and pretty easy to use and schedule via SQL Agent.

Hallengren’s SQL Reindex Script

I currently use the following script run daily as it reorganises indexes with fragmentation of between 5% and 20%, and performs an online rebuild if over 20%. Fragmentation under 5% is ignored (it’s not worth doing anything with these). It also updates statistics on records which have been modified.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 20,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Monitoring Azure SQL Database CPU Usage

Sometimes there is a need to monitor Microsoft’s Azure SQL database usage, but your after the raw figures rather than an impressive looking graph.

Running the following SQL against the master database will give you the recent information:

DECLARE @DatabaseName nvarchar(50);
SET @DatabaseName = 'databasename';

SELECT getdate() AS [Current Server Time];

SELECT start_time AS [Start of Period],
end_time AS [End of Period],
active_session_count AS [Sessions At End of Period],
avg_cpu_percent AS [Average CPU Usage %],
avg_physical_data_read_percent AS [Average Log Reads %],
avg_log_write_percent AS [Average Log Writes %]
FROM sys.resource_stats

WHERE start_time > DATEADD(minute, -240, GETDATE()) AND database_name = @DatabaseName
Order By start_time DESC;

Here’s an example of the results:
Azure SQL Database Processor Usage

Common SQL Queries

Frequently I use SQL with the work I do with Orbis Taskcentre.

There’s some queries which I use quite a lot and have to always look up on the internet or work out how to do, so to save time, here’s my list:

Date Part of Date Time
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

First Day of This Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

First Day of Last Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

First Day of Next Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

Last Day of Current Month
DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))

Last Day of Last Month
DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

Last Day of Next Month
DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))),DATEADD(MONTH, 2, GETDATE()))

Select Text Before Space
This also checks to see if a space exists, if there’s no space it returns the full text in order to prevent an error
LEFT(@Column, CASE WHEN charindex(' ', @Column) = 0 THEN LEN(@Column) ELSE charindex(' ', @Column) - 1 END)

Count Workdays Between Two Dates
(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)