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
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
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 For 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 On 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:
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:
The screenshots below shows the setup of these two steps: Run Report
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:
In due course I will do some further investigation into ETL Tools. Watch this space!
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!
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
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:
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.
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.
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;
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)
