Tag Archives: tsql

How to update/replace or remove an email address from all SSRS subscriptions with T-SQL

Database Administrator, dba, SQL Server, SSRS, SSRS Reporting, T-SQL, tsql, , , , , , , , , , ,

Updating/replacing or removing an email address from SSRS subscriptions manually is far too time consuming and tedious. Use the below script instead to either update/replace an email address with a new one across all subscriptions or remove the email address from all subscriptions.

To exclude subscriptions, i.e. keep the email address active for a particular subscription, find the subscription Id for that subscription and include it in the WHERE clause. Remember to uncomment that line in order for the clause to be active.

If you’re worried about messing anything up then back up the table before running the script!

Create backup:

SELECT *
INTO [dbo].[Subscriptions_bk]
FROM [dbo].[Subscriptions]

Update/replace or remove an email address:

/*
To replace an email address with another email address:
Find and replace the following email addresses (Ctrl+H) with the new email address*/
/*
Email address to update/replace:
replaceSomeGuy@someCompany.com

Email address replacement:
newGuy@someCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'replaceSomeGuy@someCompany.com', 'newGuy@someCompany.com')
WHERE CHARINDEX('replaceSomeGuy@someCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

/*
To remove an email address:
NOTE:Run both of the following scripts as an email address may or may not end with ";"

Find and replace the following email address (Ctrl+H)
*/
/*
Email address to remove
removeSomeGuy@otherCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com;', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com;', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;


UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

How to assess T-SQL code quickly

Data, Database Administrator, dba, Development, Reporting, sql, SQL Server, T-SQL, tsql, , , , , , , , , , , , , , , , , , , , ,

I’m sure you’re an excellent SQL coder writing beautiful efficient queries, but your predecessor . . . well they might have just been lucky to have a job.

Going through someone else’s bad code is usually tiresome, tedious and often very confusing.

I’ve created the T-SQL Assessor excel file to help in this task.

DOWNLOAD (Dropbox link)

The assessor will colour code the sql to highlight the lines of importance. With the Key Word column you can then simply filter to words like INSERT, UPDATE, MERGE and EXEC to see where the data is going or filter the column by the word FROM to see where the data has come from.

To use the T-SQL Assessor file you will first have to format your code using Poor Man’s T-SQL Formatter. This excellent tool can be installed in Visual Studio, SQL Server Management Studio or Notepad++.

http://architectshack.com/PoorMansTSqlFormatter.ashx

You can also use the online option:

http://poorsql.com/

Poor Man’s T-SQL Formatter makes text that contains a SQL command a new line, so you can’t have INSERT and FROM on one line. This is what allows Excel formula’s to highlight the lines with key words as each line can only contain one key word, excluding comments.

Once the code is formatted simply paste it into the first sheet of the file, “SQL”.

That’s it, all the work is then done for you on the second sheet of the file, “SQL Assessed”

T-SQL Assessor is also great at preparing a report from a schema compare script created by Visual Studio. It’s very annoying Microsoft didn’t provide a way of exporting the comparison directly into excel the way Redgate did but this will help. Simply filter the file to only include the keywords.

DOWNLOAD (Dropbox link)

How to archive tables that contain a keyword from one database to another using T-Sql

Data, Database Administrator, dba, Development, How To, SQL Server, T-SQL, tsql, , , , , , , , , , , ,

In the previous article I wrote about how to identify and remove unwanted tables. Link

In that tutorial I suggested prefixing the tables you want to remove with _DELETE_.

I also suggested you may want to archive the tables in some manner before you delete them. This could be because there is a chance someone might come looking for the data that was in a deleted table or something might break by removing the table and you might want to put it back asap.

If you have only a few tables you could script the tables out but if you have a lot of tables that becomes a little unmanageable. Also if the tables total in size to 10 Gb the script to recreate the tables will be a lot lot larger.

An alternative method is to create an Archive database, copy the tables across to this target database and then delete the tables in the source database. You can then backup and drop the Archive database saving the .bak file somewhere cheaper.

The script below will allow you to do just that. It prints the T-Sql to do the job, it doesn’t carry out the job, so it’s completely safe to execute and review.

To use the script below create a target database.

Use the target database name for the variable value @TargetDb

Use the source database name for the variable value @SourceDb

The @KeyWord variable is used to gather all the tables that contain the string of choice, in the example below _DELETE_.

The @RemoveKeyWord variable is a flag that will remove the keyword string from the target database table name, e.g. _DELETE_Sales will become Sales.

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
	DROP TABLE #Table
GO

DECLARE @RemoveKeyWord BIT
DECLARE @Id AS INT
DECLARE @KeyWord AS VARCHAR(256)
DECLARE @SourceDb AS SYSNAME
DECLARE @TargetDb AS SYSNAME
DECLARE @TableName AS SYSNAME
DECLARE @SchemaName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @RemoveKeyWord = 1
SET @KeyWord = '_DELETE_'
SET @TargetDb = 'Archive'
SET @SourceDb = 'Source'
SET NOCOUNT ON

CREATE TABLE #Table (
	Id_Table INT IDENTITY(1, 1)
	,SchemaName SYSNAME
	,TableName SYSNAME
	);

SET @Sql = '
INSERT INTO #Table (
	SchemaName
	,TableName
	)
SELECT s.NAME
	,so.NAME
FROM ' + QUOTENAME(@SourceDb) + '.sys.tables AS so
LEFT JOIN ' + QUOTENAME(@SourceDb) + '.sys.schemas AS s ON so.schema_id = s.schema_id
WHERE so.NAME LIKE ' + '''' + '%' + @KeyWord + '%' + '''' + '
ORDER BY s.NAME ASC'

EXEC (@Sql)

SET @SchemaName = ''

WHILE @SchemaName IS NOT NULL
BEGIN
	SET @SchemaName = (
			SELECT MIN(SchemaName)
			FROM #Table
			WHERE SchemaName > @SchemaName
				AND SchemaName <> 'dbo'
			)

	PRINT 'USE ' + QUOTENAME(@TargetDb) + ';
GO
	
IF NOT EXISTS (SELECT * FROM ' + QUOTENAME(@TargetDb) + '.sys.schemas WHERE name = ' + '''' + @SchemaName + '''' + ')
  BEGIN
    EXEC (' + '''' + 'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + ';' + '''' + ');
  END;
  
'
END

SET @Id = 1

WHILE @Id IS NOT NULL
BEGIN
	SELECT @TableName = TableName
		,@SchemaName = SchemaName
	FROM #Table
	WHERE Id_Table = @Id

	IF @RemoveKeyWord = 1
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(REPLACE(@TableName, @KeyWord, '')) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END
	ELSE
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END

	SET @Id = (
			SELECT MIN(Id_Table)
			FROM #Table
			WHERE Id_Table > @Id
			)
END

How to delete all SQL Server Jobs that have no maintenance plan with T-Sql

Database Administrator, dba, Development, How To, sql, SQL Server, T-SQL, tsql, , , , , , , , , ,

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to clear out a development environment.

Running the script will output the T-Sql required to deleted all jobs on the SQL Server instance. Jobs for maintenance plans are not included as maintenance plans need to be deleted first. Just copy the text and paste into a new SSMS window and execute. You could change the PRINT to EXEC but I wouldn’t recommend it. 

SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.Name
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysmaintplan_subplans AS p ON j.job_id = p.job_id
WHERE p.subplan_id IS NULL
ORDER BY j.Name ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_delete_job @job_name = ' + '''' + @JobName + '''' + '; 
'
	PRINT @Sql
END

How to turn a month name and year into a date field in SQL

sql, SQL Server, SSRS, SSRS Reporting, T-SQL, , , , , , , , , , , ,

When would I use this?

Say you have an SSRS report which provides users with the parameter options month and year and the report returns data for that month and year. You will likely need that month name (varchar) and year (int) to be converted into a date fields representing the first and last day of that month/year. Once you have those two dates they can then be used in the WHERE clause of your SQL query to return data within that range. The logic below will allow the conversion of month name and year into start and end dates described above. You can now take this logic and input it into a stored procedure or user defined function.

There will only ever be 12 months going forward but to create an ever updating parameter option for years please see this tutorial.

DECLARE @year AS INT
DECLARE @month AS VARCHAR(9)
DECLARE @monthNumber AS CHAR(2)
DECLARE @startDate AS DATE
DECLARE @endDate AS DATE;

SET @year = 2016
SET @month = 'February'

IF @year IS NOT NULL
BEGIN
	WITH monthPicker
	AS (
		SELECT CASE 
				WHEN @month = 'January'
					THEN '01'
				WHEN @month = 'February'
					THEN '02'
				WHEN @month = 'March'
					THEN '03'
				WHEN @month = 'April'
					THEN '04'
				WHEN @month = 'May'
					THEN '05'
				WHEN @month = 'June'
					THEN '06'
				WHEN @month = 'July'
					THEN '07'
				WHEN @month = 'August'
					THEN '08'
				WHEN @month = 'September'
					THEN '09'
				WHEN @month = 'October'
					THEN '10'
				WHEN @month = 'November'
					THEN '11'
				WHEN @month = 'December'
					THEN '12'
				ELSE NULL
				END AS monthPicked
		)
	SELECT @monthNumber = (
			SELECT monthPicked
			FROM monthPicker
			)

	SET @startDate = (
			SELECT CAST(CAST(@year AS VARCHAR(4)) + @monthNumber + '01' AS DATETIME)
			)
	SET @endDate = (
			SELECT DATEADD(s, - 1, DATEADD(MM, DATEDIFF(M, 0, @startDate) + 1, 0))
			)
END
ELSE
BEGIN
	SET @startDate = (
			SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
			)
	SET @endDate = (DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, GETDATE()), - 1))
END

PRINT @startDate
PRINT @endDate

How to enable and disable xp_cmdshell

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , , , , , ,

xp_cmdshell is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code.

By default, the xp_cmdshell option is disabled on new installations. Along with other unwanted behavior malicious users can elevate their privileges with the use of xp_cmdshell. For this reason it is generally recommend to leave xp_cmdshell disabled. It can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

 

To disable xp_cmdshell use the following code example. Note: The following example also sets show advanced options server configuration option to 0. It is best practice to leave this option in the default state of 0 to stop the enabling of features.

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

How to populate a temp table with the days in a month

sql, SQL Server, T-SQL, , , , , , , , , , , , ,

As described in the title this is a dynamic sql script that will populate a temp table with the day dates of the current month as outputted below:

calendarTableTo create a temporary calendar table for a greater period of time than just the current month:

Set @startDate to any historical date and @numberOfLoops to any number you like (365 being a year from the @startdate).

To make the table persistent:

Remove the # signs from the #calendarTable temp table referenced and rename the table accordingly.

You could also remove any columns you do not need.

IF OBJECT_ID('tempdb..#calendarTable') IS NOT NULL
	DROP TABLE #calendarTable;
	GO

CREATE TABLE #calendarTable (
	"DayID" INT
	,"DayOfMonth" INT
	,"DayOfQuarter" INT
	,"DayOfYear" INT
	,"MonthOfDate" INT
	,"YearOfDate" INT
	,"DayDate" DATE
	,PRIMARY KEY (DayID)
	)

DECLARE @startDate AS DATE
DECLARE @baseDate DATE
	,@offSet INT
	,@numberOfLoops INT

/*
To create a temporary calendar table:
Set @startDate to any historical date 
and @numberOfLoops to any number you like 
(365 being a year from the @startdate)

To make the table persistent remove the # signs 
from the #calendarTable temp table referenced 
*/
SET @startDate = GETUTCDATE() /*'20150101'*/
SET @baseDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate), 0)
SET @offSet = 0
SET @numberOfLoops = DATEDIFF(DAY, @baseDate, DATEADD(MONTH, 1, @baseDate)) /*365*/

WHILE (@offSet < @numberOfLoops)
BEGIN
	INSERT INTO #calendarTable (
		"DayID"
		,"DayOfMonth"
		,"DayOfQuarter"
		,"DayOfYear"
		,"MonthOfDate"
		,"YearOfDate"
		,"DayDate"
		)
	SELECT (@offSet + 1)
		,DATEPART(DAY, DATEADD(DAY, @offSet, @baseDate))
		,DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, DATEADD(DAY, @offSet, @baseDate)), 0), DATEADD(DAY, @offSet, @baseDate)) + 1
		,DATEPART(DAYOFYEAR, DATEADD(DAY, @offSet, @baseDate))
		,DATEPART(MONTH, DATEADD(DAY, @offSet, @baseDate))
		,DATEPART(YEAR, DATEADD(DAY, @offSet, @baseDate))
		,DATEADD(DAY, @offSet, @baseDate)

	SET @offSet = @offSet + 1
END

SELECT *
FROM #calendarTable

How to pass a multi-value parameter to a stored procedure from a SSRS Report

Reporting, SQL Server, SSRS, T-SQL, , , , , , , , , , , , , , , , , , , ,

When you allow for multiple field values to be selected in a SSRS report there needs to be additional logic added to the back end to deal with this.

This is best explained with an example scenario.

I have a table called Ireland with two columns, ID_Column and County. You can use the script below to create and populate this table. Run the query below to follow the working example.

CREATE DATABASE [TEST_DB];

USE [TEST_DB];
GO

/****** Object:  Table [dbo].[Ireland]    Script Date: 07/15/2015 10:49:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Ireland] (
	[ID_Column] [int] IDENTITY(1, 1) NOT NULL
	,[County] [varchar](9) NULL
	,PRIMARY KEY CLUSTERED ([ID_Column] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Ireland] ON

INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (1, N'Antrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (2, N'Armagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (3, N'Carlow')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (4, N'Cavan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (5, N'Clare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (6, N'Cork')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (7, N'Derry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (8, N'Donegal')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (9, N'Down')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (10, N'Dublin')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (11, N'Fermanagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (12, N'Galway')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (13, N'Kerry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (14, N'Kildare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (15, N'Kilkenny')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (16, N'Laois')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (17, N'Leitrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (18, N'Limerick')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (19, N'Longford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (20, N'Louth')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (21, N'Mayo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (22, N'Meath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (23, N'Monaghan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (24, N'Offaly')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (25, N'Roscommon')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (26, N'Sligo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (27, N'Tipperary')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (28, N'Tyrone')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (29, N'Waterford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (30, N'Westmeath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (31, N'Wexford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (32, N'Wicklow')
SET IDENTITY_INSERT [dbo].[Ireland] OFF

 

If I wanted to allow a user to select every county from the Ireland table in an SSRS report I would create a stored procedure that simply executes the following query.

SELECT * FROM Ireland

 

However an SSRS report which allows users to choose counties in Ireland like below means that a WHERE clause needs to be introduced and be able to respond to the field values selected.

Image showing an SSRS report parameter options

But the issue is that the SSRS report will pass the multi-valued parameter as a string with the values separated by a comma.

So instead of receiving the required: ‘Antrim’, ‘Armagh’, ‘Carlow’, ‘Cavan’ etc. for use in the WHERE clause.

SQL Server is passed: ‘Antrim, Armagh, Carlow, Cavan’ etc. which cannot be used.

So the first additional logic and code to be added to the back end to deal with the multi-value parameter is a User Defined Function (UDF) which splits the parameter. The following function and quotations are taken from the 4guysfromrolla website.

Function Scope:

“There are generally two parameters to a split function: the list to split and the character(s) to split on, the delimiter. In the following function we begin by declaring our input variables – @List, the list to split, and @SplitOn, the delimiter(s) to split on. The return value of this UDF is a table with two fields: Id, an identity column, and Value, an nvarchar(100) column.”

Function Logic:

“The main body of the function simply loops through the string finding the first occurrence of the delimiter on each pass. Once the delimiter has been found, the string is broken into two pieces. The first piece is inserted into the result table while the second piece replaces the original list. The loop continues until no more occurrences of the delimiter are found. Lastly, the remainder of the list is added to the result table. Return the table and you have a split function.”

Run the query below to follow the working example.

USE [TEST_DB];
GO

CREATE FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

 

(For a line by line break down of the function please see the webpage.)

Now that the split function exits within the database it is now possible to use a Stored Procedure to SELECT from the Ireland table pulling back specific counties by way of a WHERE clause.

Run the query below to follow the working example.

USE Test_DB;
GO

IF OBJECT_ID('[GetCounties]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[GetCounties] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[GetCounties] @County VARCHAR(MAX)
AS
	/*

NAME OF SP: GetCounties
Author:		Bloggins86
Date:		15/07/2015
Purpose:	Test multi-parameter select

*/
	------------------------------------------------------
	------------------------------------------------------
	--INSERT STORED PROCEDURE LOGIC HERE
	SELECT *
	FROM Ireland
	WHERE County IN (SELECT Value FROM dbo.Split(@County, ','))

	------------------------------------------------------
	------------------------------------------------------

 

Now that the populated table, split function and county select SP exists run the query below passing a string with multiple counties to return Dublin, Meath and Cork from the Ireland Table.

EXEC dbo.[GetCounties] 'Dublin, Meath, Cork'

 

You should now have returned the table with Dublin, Meath and Cork as separate row entries.

And that’s it, thanks for reading.

Combine and then split tsql insert statement files into batches of one thousand.

Batch Script, Reporting, sql, SQL Server, T-SQL, , , , , , , ,

Recently I was given the task of executing hundreds of prepared sql files some containing multi-row insert statements numbering in the tens of thousands.

So I encountered two problems with this:

  • MSSQL Multi-row insert statements  actually have some limits i.e. a maximum of 1000 rows can be inserted.
  • I didn’t know which files had more or less than a thousand insert statements.

Luckily all the new rows were going into the same table and I knew that each file had the same flat/unformatted tsql structure.

This meant I could write a batch script to combine the files and then split the tsql insert statements into batches of 1000.

The batch script below is a little long winded so here are the main sections.

  • Combine Files
  • Remove unwanted lines of text  i.e. blank lines or insert statement sections.
  • Ensure every parentheses is followed by a comma
  • Inject the “insert into table” statement and GO every 1000 lines.
  • Remove double quotes

This was a somewhat specific case but hopefully you’ll be able to pick the batch script below apart for yourself and get some use out of it. But if there’s one little snippet of code I’d like to draw your attention to it is this little gem.

TYPE *.sql > CombinedScript.sql

 

Type that into a text file and save it as combine.bat, place the file in the folder with all your sql scripts and the OS will combine them all for you as CombinedScript.sql.

Here’s the rest of the script followed by some sample data showing the shape and structure of the original sql files.

(I’d like to thank the hilite.me website for creating this awesome online utility for creating HTML highlighted code that can just be dropped into a blog, as shown below. No more gist for me!)

@ECHO OFF

::VARIABLES
SET FileToUse="CombinedScript.sql"
SET FileToDelete="Query.sql"
SET FirstLine="Insert into [LoadProfiles] (Profile,Type,ProfileDate,ProfileValue,Active,Created,CreatedBy) Values"
SET BatchSplit="GO "
SET TextLineToAdd=%BatchSplit%%FirstLine%
::SET "TextLineToAdd=%TextLineToAdd:"=%"
::Combine SQL FILES

ECHO Combining Files . . . 
ECHO Please Wait
ECHO.
TYPE *.sql > %FileToUse%
ECHO Files Combined
ECHO.

ECHO Removing unwanted lines of text . . . 
ECHO Please Wait
ECHO.
::REF 1
::REMOVE THE LINES WITH INSERT AND VALUES FROM FILE
findstr /v "Insert Values" %FileToUse% > Temp.sql
::REF 2
::REMOVE BLANK ROWS FROM Temp.sql
findstr /v /r /c:"^$" /c:"^\ *$" Temp.sql >> CleanedFile.sql
::DELETE Temp.sql
IF EXIST Temp.sql del /F Temp.sql
ECHO Lines Removed
ECHO.


::REF 3
::REPLACE ) WITH ), TO ENSURE EVERY PARENTHESES IS FOLLOWED BY A COMMA
ECHO Adding Comma to each Parentheses missing a Comma . . .
ECHO Please Wait
ECHO.
    setlocal enableextensions disabledelayedexpansion

    set "search=)"
    set "replace=),"

    set "textFile=CleanedFile.sql"

    for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        set "line=!line:%search%=%replace%!"
        >>PreparedFile.sql echo(!line!
        endlocal
    )
::DELETE CleanedFile.sql
IF EXIST CleanedFile.sql del /F CleanedFile.sql
::REF 3
::REPLACE ),, WITH ), TO ENSURE EVERY PARENTHESES IS FOLLOWED BY ONLY ONE COMMA
	    setlocal enableextensions disabledelayedexpansion

    set "search=),,"
    set "replace=),"

    set "textFile=PreparedFile.sql"

    for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        set "line=!line:%search%=%replace%!"
        >>CleanedFile.sql echo(!line!
        endlocal
    )
ECHO Commas Added
ECHO.

ECHO Creating Query file . . .
ECHO Please Wait
ECHO.
::DELETE PreparedFile.sql
IF EXIST PreparedFile.sql del /F PreparedFile.sql
 
::Try to delete the file only if it exists
IF EXIST %FileToDelete% del /F %FileToDelete%
 
::REM If the file wasn't deleted for some reason, stop and error
IF EXIST %FileToDelete% exit 1

TYPE NUL > %FileToDelete% 

::REF 4
SETLOCAL
SET count=0
SET injectevery=1000
FOR /f "delims=" %%Z IN ('type CleanedFile.sql^|findstr /n "^"') DO (
SET /a count+=1
SET line=%%Z
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO(!line:*:=! >> Query.sql
IF !count!==%injectevery% ECHO.%TextLineToAdd% >> Query.sql
ENDLOCAL
SET /a count=count %% %injectevery% 
) 

::REF 3
::REPLACE " WITH NOTHING 
ECHO Adding Comma to each Parentheses missing a Comma . . .
ECHO Please Wait
ECHO.
    setlocal enableextensions disabledelayedexpansion

    set "search=""
    set "replace= "

    set "textFile=Query.sql"

    for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
        set "line=%%i"
        setlocal enabledelayedexpansion
        set "line=!line:%search%=%replace%!"
        >>Script.sql echo(!line!
        endlocal
    )
::DELETE CleanedFile.sql
IF EXIST CleanedFile.sql del /F CleanedFile.sql

PAUSE

::REF 1: http://stackoverflow.com/questions/418916/delete-certain-lines-in-a-txt-file-via-a-batch-file
::CREDIT GOES TO: http://stackoverflow.com/users/14138/rick

::REF 2: http://www.computing.net/answers/programming/delete-blank-line-from-a-txt-file-using-batch/25575.html
::CREDIT GOES TO: http://www.computing.net/userinfo/150780

::REF 3: http://stackoverflow.com/questions/23075953/batch-script-to-find-and-replace-a-string-in-text-file-without-creating-an-extra
::CREDIT GOES TO: http://stackoverflow.com/users/2861476/mc-nd

::REF 4: FOR INSERT NEW TEXT LINE: http://stackoverflow.com/questions/15859128/looking-for-batch-file-to-insert-new-lines-into-text-file
::CREDIT GOES TO: http://stackoverflow.com/users/2128947/magoo


/*CHANGE DATABASE_NAME*/

USE [DATABASE_NAME]
GO

/****** Object:  Table [dbo].[LoadProfiles]    Script Date: 06/12/2015 15:34:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[LoadProfiles](
	[ProfileID] [int] IDENTITY(1,1) NOT NULL,
	[Profile] [int] NULL,
	[Type] [varchar](8) NULL,
	[ProfileDate] [date] NULL,
	[ProfileValue] [decimal](12, 10) NULL,
	[Active] [bit] NULL,
	[Created] [datetime] NULL,
	[CreatedBy] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[LoadProfileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 
Insert into [dbo].[LoadProfiles] (Profile,Type,ProfileDate,ProfileValue,Active,Created,CreatedBy)
 Values
(1,'24H','2012/01/01',0.003348,1,getutcdate(),333),
(1,'24H','2012/01/02',0.003392,1,getutcdate(),333),
(1,'24H','2012/01/03',0.003278,1,getutcdate(),333),
(1,'24H','2012/01/04',0.003252,1,getutcdate(),333),
(1,'24H','2012/01/05',0.003203,1,getutcdate(),333)

Universal Error Logging for Stored Procedures in SQL Server

Reporting, SQL Server, T-SQL, , , , , , , ,

Probably the best way to log whether your stored procedure ran correctly or threw an error during its run is to create another stored procedure to capture the events of the run.

This example although basic captures the primary data you would want to keep a record of regarding a procedures run.

  • The name of the stored procedure
  • The start time of the stored procedure
  • The time the stored procedure finished/errored
  • A user comment/error message
  • An assigned Log ID

Below is a pre-baked example requiring just a change to the DatabaseName, and if you wish SP_Name, text fields, as instructed in the comments of the SQL below, to test run the script.

This will create a SP_Log table and the stored procedure InsertRunIntoSpLog which can be reused over and over again when making stored procedures for other jobs to log the success/failure of these jobs.

Step 3 is an example stored procedure, which can be renamed as something other than SP_Name, which simply displays the date time.  Once created whenever the following is executed, EXEC dbo.SP_Name, the current date time will be returned and a record of the procedures run will be added to a row in the SP_Log table.

Although as stated above this is a basic example it is a very powerful foundation which can be built upon for very comprehensive event and transaction logging.

 


— CHANGE [DatabaseName] TO THE NAME OF THE DB REQUIRED
— CHANGE SP_NAME TO THE NAME OF THE SP REQUIRED
—————————————————
— STEP 1
— CREATE TABLE TO STORE ERRORS
—————————————————
USE [DatabaseName]
GO
— DROP LOG TABLE IF ALREADY EXISTS
IF OBJECT_ID('dbo.SP_Log', 'U') IS NOT NULL
DROP TABLE dbo.SP_Log
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
— CREATE LOG TABLE
CREATE TABLE [dbo].[SP_Log] (
[LogID] [int] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL
,[NameOfSp] [nvarchar](100) NOT NULL
,[StepCode] [varchar](1) NOT NULL
,[RunDate] [datetime] NOT NULL
,[Comment] [nvarchar](200) NULL
,PRIMARY KEY CLUSTERED ([LogID] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
—————————————————
— STEP 2
— CREATE STORED PROCEDURE TO WRITE ERRORS TO ERROR TABLE
—————————————————
USE [DatabaseName]
GO
IF OBJECT_ID('dbo.InsertRunIntoSpLog', 'p') IS NOT NULL
DROP PROCEDURE [dbo].[InsertRunIntoSpLog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— CREATE STORED PROCEDURE
CREATE PROCEDURE [dbo].[InsertRunIntoSpLog] @SpVariableName NVARCHAR(100)
,@StepCode VARCHAR(1)
,@Comment NVARCHAR(400)
AS
BEGIN
INSERT INTO SP_Log (
NameOfSp
,StepCode
,RunDate
,Comment
)
VALUES (
@SpVariableName
,@StepCode
,getutcdate()
,@Comment
)
END
GO
—————————————————
— STEP 3
— STORED PROCEDURE RUN / ERROR CATCH EXAMPLE
—————————————————
IF OBJECT_ID('dbo.SP_Name', 'p') IS NOT NULL
DROP PROCEDURE [dbo].[SP_Name]
GO
CREATE PROCEDURE [dbo].[SP_Name]
AS
BEGIN
BEGIN TRY
— INSERT SP RUN LOG FOR SP START
EXEC InsertRunIntoSpLog 'SP_NAME' — SP_NAME
,'S' — S, F OR E (START, FINNISH OR ERROR)
,'' — USER COMMENT
— QUERY HERE
— FOR EXAMPLE
SELECT GETDATE() AS CurrentDateTime
— INSERT SP RUN LOG FOR SP END
EXEC InsertRunIntoSpLog 'SP_NAME'
,'F'
,''
END TRY
BEGIN CATCH
PRINT 'error!'
— INSERT SP RUN LOG FOR SP ERROR
DECLARE @error_number AS INTEGER
DECLARE @error_message AS VARCHAR(400)
SET @error_number = error_number()
SET @error_message = left(error_message(), 400)
PRINT 'error_message: ' + @error_message
EXEC InsertRunIntoSpLog 'SP_NAME'
,'E'
,@error_message
END CATCH
END

view raw

Universal Error Logging for Stored Procedures in SQL Server

hosted with ❤ by GitHub