Category Archives: Reporting

How to assess T-SQL code quickly

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 identify and remove unused tables in SQL Server with T-Sql

In a perfect world an organisation should never get itself into the situation where tables need to be identified as functionally obsolete and removed in bulk. Schema changes should be stepped through the cycle of development, test, staging and live with developers cleaning as they go, but we don’t live in a perfect world as you may have noticed.

What do I mean by functionally obsolete? This means the tables are no longer being interacted with by user generated objects like stored procedures, functions or views. There may also be tables that could be classified as business redundant. That is they are being referenced by user generated objects frequently but they no longer have a use to the business, i.e. one job may have been replaced by another without the former being disabled or dropped. Business redundant objects are more difficult to determine and finding them may require input from multiple stakeholders.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no impact on the overall environment script out the object then drop it. (Obviously do this in a test environment first if possible)

The script below is mostly a light weight SELECT statement that can be run on any environment. It does not execute any of the code it generates. It uses the sys.dm_db_index_usage_stats dynamic management view to determine when the tables were last interacted with. Interaction being defined as the following actions being applied against the object, update, seek, scan, lookup.

Caveat: Entries in this view reset to NULL after a Server reboot. Also the DMV has been known to be a bit unreliable with earlier versions of SQL Server with cases of the view being reset when a full index rebuild is carried out against a table. So don’t execute any code without first reviewing it.

Tables with NULL values for the fields below should be tables that have not been referenced at all or at least since the last time the server was rebooted.

LastUserUpdate
LastUserSeek
LastUserScan
LastUserLookup

Removing these objects is the low hanging fruit of cleaning up an environment. The script also provides stats on how many times these interactions happened and the size of the object. All these stats together should help you determine if an object is functionally redundant or business redundant and can be removed.

To aid further in the cleanup the script also creates the fields Action, Comments, Renamed, RenamedDate, RenameForDeletion, RestoreOriginalName, DropTable, DroppedDate.

The script output can then be copied and pasted into an Excel spread sheet and used to coordinate and track the cleanup progress.

SET NOCOUNT ON

DECLARE @Database TABLE ([DbName] [sysname])
DECLARE @DbName AS [sysname]
DECLARE @Sql AS [varchar] (max)

IF OBJECT_ID('tempdb..#TableStats', 'U') IS NOT NULL
	DROP TABLE #TableStats

IF OBJECT_ID('tempdb..#IndexStats', 'U') IS NOT NULL
	DROP TABLE #IndexStats

IF OBJECT_ID('tempdb..#TableUsageStats', 'U') IS NOT NULL
	DROP TABLE #TableUsageStats

IF OBJECT_ID('tempdb..#TableSizeStats', 'U') IS NOT NULL
	DROP TABLE #TableSizeStats

CREATE TABLE #TableStats (
	[DbName] [sysname]
	,[SchemaName] [sysname]
	,[ObjectId] [bigint]
	,[TableName] [sysname]
	,[ModifiedDate] [datetime]
	);

CREATE TABLE #IndexStats (
	[DbName] [sysname]
	,[ObjectId] [bigint]
	,[HasIndex] [bit]
	);

CREATE TABLE #TableSizeStats (
	[DbName] [varchar](255) NULL
	,[SchemaName] [varchar](255) NULL
	,[ObjectId] [bigint]
	,[TableName] [varchar](255) NULL
	,[RowCount] [bigint] NULL
	,[AvailableSpacePercentage] [numeric](6, 2) NULL
	,[UnusedSpaceGb] [numeric](10, 3) NULL
	,[UsedSpaceGb] [numeric](10, 3) NULL
	,[TotalSpaceGb] [numeric](10, 3) NULL
	,[UnusedSpaceMb] [numeric](13, 3) NULL
	,[UsedSpaceMb] [numeric](13, 3) NULL
	,[TotalSpaceMb] [numeric](13, 3) NULL
	,[UnusedSpaceKb] [bigint] NULL
	,[UsedSpaceKb] [bigint] NULL
	,[TotalSpaceKb] [bigint] NULL
	)

CREATE TABLE #TableUsageStats (
	[DbName] [sysname]
	,[ObjectId] [bigint]
	,[TableName] [nvarchar](128) NULL
	,[LastUserUpdate] [datetime] NULL
	,[LastUserSeek] [datetime] NULL
	,[LastUserScan] [datetime] NULL
	,[LastUserLookup] [datetime] NULL
	,[UserUpdateCount] [bigint] NOT NULL
	,[UserSeekCount] [bigint] NOT NULL
	,[UserScanCount] [bigint] NOT NULL
	,[UserLookupCount] [bigint] NOT NULL
	);

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME NOT IN (
		'tempdb'
		,'master'
		,'mode'
		,'model'
		)
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			);
	SET @Sql = '
INSERT INTO #TableStats (
	DbName
	,schemaName
	,ObjectId
	,TableName
	,ModifiedDate
	)
SELECT DbName
	,SchemaName
	,ObjectId
	,TableName
	,ModifiedDate
FROM (
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
	,s.NAME AS SchemaName
	,t.object_id AS ObjectId
	,t.NAME AS TableName
	,t.modify_date AS ModifiedDate
FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s ON t.schema_id = s.schema_id
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.extended_properties AS ep ON ep.major_id = t.[object_id] /*Exclude System Tables*/
WHERE t.NAME IS NOT NULL
	AND s.NAME IS NOT NULL
	AND (ep.[name] IS NULL OR ep.[name] <> ''microsoft_database_tools_support'')
	) AS rd
WHERE rd.SchemaName IS NOT NULL
ORDER BY DbName ASC
	,TableName ASC;
'

	EXEC (@Sql)

	SELECT @Sql = '
INSERT INTO #IndexStats (
	DbName
	,ObjectId
	,HasIndex
	)
SELECT ' + '''' + @DbName + '''' + ' AS DbName
	,OBJECT_ID AS ObjectId
	,IndexCheck AS HasIndex
FROM (
	SELECT DISTINCT OBJECT_ID
		,CASE 
			WHEN (
					[TYPE] > 0
					AND is_disabled = 0
					)
				THEN 1
			ELSE 0
			END AS IndexCheck
	FROM ' + QUOTENAME(@DbName) + '.sys.indexes
	) AS rd
WHERE rd.IndexCheck = 1
'

	EXEC (@Sql)

	SET @Sql = 
		'
INSERT INTO #TableSizeStats (
	[DbName]
	,[SchemaName]
	,[ObjectId]
	,[TableName]
	,[RowCount]
	,[AvailableSpacePercentage]
	,[UnusedSpaceGb]
	,[UsedSpaceGb]
	,[TotalSpaceGb]
	,[UnusedSpaceMb]
	,[UsedSpaceMb]
	,[TotalSpaceMb]
	,[UnusedSpaceKb]
	,[UsedSpaceKb]
	,[TotalSpaceKb]
	)
SELECT DISTINCT rd.[DbName]
	,rd.[SchemaName]
	,rd.[ObjectId]
	,rd.[TableName]
	,rd.[RowCount]
	,CASE 
		WHEN TotalSpaceKb > 0
			THEN ((UnusedSpaceKb / TotalSpaceKb) * 100)
		ELSE 0
		END AS AvailableSpacePercentage
	,CONVERT(NUMERIC(10, 3), (rd.[UnusedSpaceKb] / 1024.) / 1024.) AS UnusedSpaceGb
	,CONVERT(NUMERIC(10, 3), (rd.[UsedSpaceKb] / 1024.) / 1024.) AS UsedSpaceGb
	,CONVERT(NUMERIC(10, 3), (rd.[TotalSpaceKb] / 1024.) / 1024.) AS TotalSpaceGb
	,CONVERT(NUMERIC(13, 3), (rd.[UnusedSpaceKb] / 1024.)) AS UnusedSpaceMb
	,CONVERT(NUMERIC(13, 3), (rd.[UsedSpaceKb] / 1024.)) AS UsedSpaceMb
	,CONVERT(NUMERIC(13, 3), (rd.[TotalSpaceKb] / 1024.)) AS TotalSpaceMb
	,rd.[UnusedSpaceKb]
	,rd.[UsedSpaceKb]
	,rd.[TotalSpaceKb]
FROM (
	SELECT ' 
		+ '''' + @DbName + '''' + ' AS DbName
		,t.Object_id AS ObjectId
		,s.[name] AS [SchemaName]
		,t.[name] AS [TableName]
		,p.[rows] AS [RowCount]
		,SUM(a.[used_pages]) * 8 AS [UsedSpaceKb]
		,(SUM(a.[total_pages]) - SUM(a.[used_pages])) * 8 AS [UnusedSpaceKb]
		,SUM(a.[total_pages]) * 8 AS [TotalSpaceKb]
	FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s ON t.schema_id = s.schema_id
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.indexes AS i ON t.OBJECT_ID = i.object_id
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.partitions AS p ON i.object_id = p.OBJECT_ID
		AND i.[index_id] = p.[index_id]
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.allocation_units a ON p.[partition_id] = a.[container_id]
	WHERE t.[is_ms_shipped] = 0
		AND i.OBJECT_ID > 255
	GROUP BY s.[name]
		,t.[name]
		,t.[object_id]
		,p.[rows]
	) AS rd
ORDER BY DbName ASC
	,SchemaName ASC
	,TableName ASC;
'

	EXEC (@Sql)

	SET @Sql = '
INSERT INTO #TableUsageStats (
	[DbName]
	,[ObjectId]
	,[TableName]
	,[LastUserUpdate]
	,[LastUserSeek]
	,[LastUserScan]
	,[LastUserLookup]
	,[UserUpdateCount]
	,[UserSeekCount]
	,[UserScanCount]
	,[UserLookupCount]
	)
SELECT DbName
	,ObjectId
	,TableName
	,LastUserUpdate
	,LastUserSeek
	,LastUserScan
	,LastUserLookup
	,UserUpdateCount
	,UserSeekCount
	,UserScanCount
	,UserLookupCount
FROM (
	SELECT DISTINCT ROW_NUMBER() OVER (
			PARTITION BY ius.Object_Id ORDER BY last_user_update DESC
			) AS RN
		,' + '''' + @DbName + '''' + ' AS DbName
		,ius.OBJECT_ID AS ObjectId
		,o.NAME AS TableName
		,ius.last_user_update AS LastUserUpdate
		,ius.last_user_seek AS LastUserSeek
		,ius.last_user_scan AS LastUserScan
		,ius.last_user_lookup AS LastUserLookup
		,ius.user_updates AS UserUpdateCount
		,ius.user_seeks AS UserSeekCount
		,ius.user_scans AS UserScanCount
		,ius.user_lookups AS UserLookupCount
	FROM ' + QUOTENAME(@DbName) + 
		'.sys.dm_db_index_usage_stats AS ius
	INNER JOIN ' + QUOTENAME(@DbName) + '.sys.objects AS o ON ius.OBJECT_ID = o.OBJECT_ID
		AND o.NAME IS NOT NULL
	) AS rd
WHERE rd.RN = 1
ORDER BY rd.DbName ASC
	,rd.TableName ASC
	,rd.LastUserUpdate DESC
	,rd.LastUserSeek DESC
	,rd.LastUserScan DESC
	,rd.LastUserLookup DESC;
'

	EXEC (@Sql)
END;
GO

SELECT DISTINCT ROW_NUMBER() OVER (
		ORDER BY ts.[DbName] ASC
			,ts.[SchemaName] ASC
			,ts.[TableName] ASC
		) AS Row
	,ts.[DbName]
	,ts.[SchemaName]
	,ts.[TableName]
	,'' AS Action --Rename, keep etc.
	,'' AS Comments
	,'' AS Renamed --boolean flag
	,'' AS RenamedDate 
	,i.[HasIndex] --Tables without an index are heaps
	,tss.[TotalSpaceMb] AS TableSizeInMb
	,ts.[ModifiedDate]
	,tus.[LastUserUpdate]
	,tus.[LastUserSeek]
	,tus.[LastUserScan]
	,tus.[LastUserLookup]
	,tus.[UserUpdateCount]
	,tus.[UserSeekCount]
	,tus.[UserScanCount]
	,tus.[UserLookupCount]
	,tss.[AvailableSpacePercentage]
	,tss.[UnusedSpaceGb]
	,tss.[UsedSpaceGb]
	,tss.[TotalSpaceGb]
	,tss.[UnusedSpaceMb]
	,tss.[UsedSpaceMb]
	,tss.[TotalSpaceMb]
	,tss.[UnusedSpaceKb]
	,tss.[UsedSpaceKb]
	,tss.[TotalSpaceKb]
	,'USE ' + QUOTENAME(ts.[DbName]) + '; EXEC sp_rename ' + '''' + ts.[SchemaName] + '.' + ts.[TableName] + '''' + ', ' + '''' + '_DELETE_' + ts.[TableName] + '''' + ';' AS RenameForDeletion
	,'USE ' + QUOTENAME(ts.[DbName]) + '; EXEC sp_rename ' + '''' + ts.[SchemaName] + '.' + '_DELETE_' + ts.[TableName] + '''' + ', ' + '''' + ts.[TableName] + '''' + ';' AS RestoreOriginalName
	,'USE ' + QUOTENAME(ts.[DbName]) + '; DROP TABLE ' + QUOTENAME(ts.[SchemaName]) + '.' + '[' + '_DELETE_' + ts.[TableName] + ']' + ';' AS 'DropTable'
	,'' AS DroppedDate
FROM #TableStats AS ts
LEFT JOIN #TableSizeStats AS tss ON ts.ObjectId = tss.ObjectId
	AND tss.DbName = ts.DbName
LEFT JOIN #IndexStats AS i ON ts.ObjectId = i.ObjectId
	AND i.DbName = ts.DbName
LEFT JOIN #TableUsageStats AS tus ON ts.ObjectId = tus.ObjectId
	AND tus.DbName = ts.DbName
ORDER BY ts.[DbName] ASC
	,ts.[SchemaName] ASC
	,ts.[TableName] ASC

	-- REF: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

How to get MS SQL Server job history using a stored procedure

In a previous article (see article) I provided simple queries to return the server’s job history. It’s only natural that when you find yourself running the same script over and over again eventually you’ll get around to turning it into a stored procedure.

Well today was the day.

You can use the script below to create the Stored Procedure usp_GetJobHistory.

To deploy the SP to your instance you’ll need to replace the text PLACEHOLDER with the name of the database you’ll be deploying to. Ideally you should have a utility or administration database rather than deploying to Master.

The SP has 5 parameters.

Parameter Datatype Options Definition
@DaysToPull INT Any Number How many days of history you want
working back from today
@Status VARCHAR One of the following: All’, ‘Succeeded’, ‘Failed’,
‘Retried’, ‘Cancelled’, NULL
Will limited the rows returned
based on the status of the entry
@SortOrder VARCHAR ASC or DESC The occurrence order i.e. oldest to newest
@JobName VARCHAR Any Text Used in a LIKE operator to find jobs containing that text
@ReturnSql BIT 0, 1 or NULL Returns the SQL you can modify or
run to return the history

Example command:

Create the SQL code to return the job history for jobs containing the work backup that failed in the past day sorted by newest to oldest.

EXEC dbo.GetJobHistory @DaysToPull = 1
,@SortOrder = ‘DESC’
,@Status = ‘Failed’
,@JobName = ‘Backup’
,@ReturnSql = 1

Tip: you could use this to create a SSRS report that could be published daily to notify stakeholders of any job failures.

-- REPLACE PLACEHOLDER
USE [PLACEHOLDER];
GO

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

ALTER PROCEDURE [dbo].[usp_GetJobHistory] @DaysToPull INT
	,@Status VARCHAR(9) = NULL
	,@SortOrder VARCHAR(4) = NULL
	,@JobName VARCHAR(256) = NULL
	,@ReturnSql BIT = NULL
AS
BEGIN
	-- =============================================
	-- Author:      Bloggins
	-- Create date: 20170420
	-- Description: <Query to retrieve job history bypassing SSMS inbuilt viewer>
	-- Website: https://techtidbytes.wordpress.com/
	-- =============================================
	SET NOCOUNT ON

	BEGIN TRY
		-- Variable Declarations 
		DECLARE @PreviousDate DATETIME
		DECLARE @Year VARCHAR(4)
		DECLARE @Month VARCHAR(2)
		DECLARE @MonthPre VARCHAR(2)
		DECLARE @Day VARCHAR(2)
		DECLARE @DayPre VARCHAR(2)
		DECLARE @FinalDate INT
		DECLARE @StatusClause AS VARCHAR(255)
		DECLARE @Sql AS VARCHAR(MAX)

		IF @SortOrder IS NULL
			OR (
				@SortOrder <> 'ASC'
				AND @SortOrder <> 'DESC'
				)
		BEGIN
			SET @SortOrder = 'ASC'
		END

		IF @Status = 'All'
			OR @Status IS NULL
			OR (
				@Status <> 'All'
				AND @Status <> 'Failed'
				AND @Status <> 'Succeeded'
				AND @Status <> 'Retried'
				AND @Status <> 'Cancelled'
				)
		BEGIN
			SET @StatusClause = '0, 1, 2, 3'
		END
		ELSE IF @Status = 'Failed'
		BEGIN
			SET @StatusClause = '0'
		END
		ELSE IF @Status = 'Succeeded'
		BEGIN
			SET @StatusClause = '1'
		END
		ELSE IF @Status = 'Retried'
		BEGIN
			SET @StatusClause = '2'
		END
		ELSE IF @Status = 'Cancelled'
		BEGIN
			SET @StatusClause = '3'
		END

		SET @PreviousDate = DATEADD(dd, - @DaysToPull, GETDATE())
		SET @Year = DATEPART(yyyy, @PreviousDate)

		SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

		SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

		SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

		SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

		SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
		/*RunDuration FORMAT (DD:HH:MM:SS)*/
		SET @Sql = '
SELECT ROW_NUMBER() OVER(ORDER BY h.instance_id ' + @SortOrder + ' ) AS Row
	,h.Server AS ServerName
	,s.database_name AS DbName
	,j.name AS JobName
	,s.step_name AS StepName
	,h.step_id AS StepId
	,CASE 
		WHEN h.run_status = 0
			THEN ''Failed''
		WHEN h.run_status = 1
			THEN ''Succeeded''
		WHEN h.run_status = 2
			THEN ''Retried''
		WHEN h.run_status = 3
			THEN ''Cancelled''
		END AS RunStatus
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS RunTime
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE(''0'', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, '':''), 6, 0, '':''), 9, 0, '':'') AS RunDuration
	,h.sql_severity AS SqlSeverity
	,h.message AS MessageReturned
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_status IN (' + @StatusClause + ')
	AND h.run_date > ' + CONVERT(VARCHAR(255), @FinalDate)

		IF @JobName IS NOT NULL
		BEGIN
			SET @Sql = @Sql + '
AND j.name LIKE ' + '''' + '%' + @JobName + '%' + '''' + ''
		END

		SET @Sql = @Sql + '
ORDER BY h.instance_id ' + @SortOrder

		IF @ReturnSql = 1
		BEGIN
			PRINT (@Sql)
		END
		ELSE
		BEGIN
			EXEC (@Sql)
		END
	END TRY

	BEGIN CATCH
		PRINT '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
	END CATCH
END

How to capture command prompt output and write it to text file

Say you have a batch script that you run that throws back information you want to keep. Well maybe the best way to do that is to have the output written to a text file. This technique can then be combined with a task scheduler so you have an  automated task that creates a log file.

The below batch script below has two main parts. The first part creates a file to capture the command output. The second part is the command prompt command to run.

Creating the file takes the following 2 parameters:

  • filePath: Where you want the file written, defaulted to your desktop
  • baseName: What the file will be called exclusive of a time stamp, defaulted to Output

The script will then take these parameters to create the parameter fileName, which combines the directory location, with what you want the file to be called along with a time stamp, e.g. User\Desktop\Output20170213111157

The command prompt command goes between the parenthesis (). The results of this command will be written to the file.

@ECHO off
REM SET FILE OUTPUT DIRECTORY
SET "filePath=%USERPROFILE%\Desktop\"
REM SET THE BASE OF THE NAME FOR THE OUTPUT FILE 
SET "baseName=Output"
REM CREATE TIMESTAMP
SET timeStamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%
REM CREATE VARIABLE TO HOLD FILENAME WITH A TIMESTAMP
SET "fileNameStamp=%baseName%%Timestamp%"
SET "fileName=%filePath%%fileNameStamp%"
REM CAPTURE COMMAND OUTPUT TO TEXT FILE
> %fileName%.txt (
REM This is where the commands go!
ECHO Text Captured
)

The following working example captures your computer network information and writes it to the file NetworkInfo(time stamp) on your desktop.

@ECHO off
REM SET FILE OUTPUT DIRECTORY
SET "filePath=%USERPROFILE%\Desktop\"
REM SET THE BASE OF THE NAME FOR THE OUTPUT FILE 
SET "baseName=NetworkInfo"
REM CREATE TIMESTAMP
SET timeStamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%
REM CREATE VARIABLE TO HOLD FILENAME WITH A TIMESTAMP
SET "fileNameStamp=%baseName%%Timestamp%"
SET "fileName=%filePath%%fileNameStamp%"
REM CAPTURE COMMAND OUTPUT TO TEXT FILE
> %fileName%.txt (
REM This is where the commands go!
echo Checking your network information, Please wait...

systeminfo | findstr /c:"Host Name" 
systeminfo | findstr /c:"Domain"

ipconfig /all | find "Physical Address" 

ipconfig | find "IPv4" 
ipconfig | find "Default Gateway"
)

How to get tableau data to refresh automatically within the internet browser

This tutorial describes how to embed a Tableau view, i.e. a published sheet or dashboard etc., into your web page and set the view to refresh every 30 seconds.

Step 1. Open note pad and save the code below into it calling the file template.html

Step 2. Copy the URL

  • Publish the Tableau view and copy the URL from the share button

Or

  • Copy the URL if you have already published the report

Note: If a hash symbol (#) and number, or a “:iid=<n>” appear at the end of the URL, do not include those characters. For example, in the following URL, you would not copy the #3 characters:

http://server-name.com/views/AutoRefreshExample/AutoRefreshExample#3

Step 3. Replace the following in the template file with your URL and change the title to something more appropriate than Auto Refresh Example.

http://server-name.com/views/AutoRefreshExample/AutoRefreshExample

Note: You can change the rate of refresh by changing the content=”30″ value to however many seconds you need. Also you probably don’t want purple as your background color unless you’re Prince so go ahead and change that too. background-color: #452775

<!--Template-->
<!DOCTYPE html PUBLIC "-//W3C//DTDXHTML 1.0 Transitional//EN" <html lang="en-US" xml:lang="en-US"> 
<head> 
<title>Auto Refresh Example</title> 
<meta http-equiv="refresh" content="30">
</head> 
<style>
body {background-color: #452775}
</style>
<body> 
 src="http://server-name.com/views/AutoRefreshExample/AutoRefreshExample?:embed=yes&:refresh=yes" 
width="100%" 
height="100%"
align="middle"
frameborder="0"  
marginwidth="0" 
marginheight="0"
scrolling="no"
> 

 

Your browser does not support iframes.

 

 
 
</body> 
</html>

How to lookup SQL Server Jobs and get the job history with a query

Sometimes job history just won’t load in SQL Server Management Studio for one reason or another. One of the main reasons is that there are too many entries in the sysjobhistory table. The article here will help you resolve that problem. For a more immediate answer to the data you are looking for, like most things with SSMS, you can query the tables that contain this data directly.

For a permanent solution to bypassing SSMS I recommend using this stored procedure. If you just want a quick query see below.

If you want to get a job history for everything that has run over the last 7 days you can run the query below. Simply change the 7 to another number to go further back in time by days.

-- Variable Declarations 
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT


-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, - 7, GETDATE()) -- Last 7 days  
SET @Year = DATEPART(yyyy, @PreviousDate)

SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Pull Job History 
SELECT j.[name]
	,s.step_name
	,h.step_id
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS run_time
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)  '
	,h.run_status
	,h.sql_severity
	,h.message
	,h.SERVER
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_date > @FinalDate
ORDER BY h.instance_id DESC

 

To get a job history for everything that has succeeded or failed over the last 7 days run the query below. Simply change the @RunStatus variable to either 0 (failed) or 1 (succeeded).

-- Variable Declarations 
DECLARE @RunStatus AS BIT
DECLARE @PreviousDate DATETIME
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

/*Succeeded Jobs*/
--SET @RunStatus = 1
/*Failed Jobs*/
SET @RunStatus = 0

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, - 7, GETDATE()) -- Last 7 days  
SET @Year = DATEPART(yyyy, @PreviousDate)

SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Pull Job History 
SELECT j.[name]
	,s.step_name
	,h.step_id
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS run_time
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)  '
	,h.run_status
	,h.sql_severity
	,h.message
	,h.SERVER
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_status = @RunStatus
	AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC

 

If you want to generate a list of all the:

  1. jobs and their owners
  2. SSIS packages and their owners

you can do so by running the queries below. (If you don’t already know the precise name or ID of a job)

--Jobs
select s.name,l.name
 from  msdb..sysjobs s 
 left join master.sys.syslogins l on s.owner_sid = l.sid

--Packages
select s.name,l.name 
from msdb..sysssispackages s 
 left join master.sys.syslogins l on s.ownersid = l.sid

 

Once you have retrieved either the name (command) or the ID of the job you are looking for you can plug that info into either one of the queries below also.

use msdb

select *
from dbo.sysjobsteps with (nolock)
where command like '%YourJobName%'

select *
from dbo.sysjobs sj with (nolock)
where sj.job_id = '1234-1234-1234-1234-1234'
Image with the text 3000 years later in giant letters

How to provide a dynamic parameter drop down of year options in SSRS

Say you’ve developed a report which returns data based on a inputted year parameter value, e.g. “give me all the sales in 2014”.

How do you provide the year options for the SSRS report?

Well there’s three ways that come to mind.

Select distinct from a date field, e.g. SELECT DISTINCT YEAR(SalesDate) FROM Sales

This would certainly provide you with all the available years but the database could have millions of sales. So it’s not too efficient.

You could manually populate years in the parameter settings in the SSRS report, kind of primitive but it would work.

But for me the robust and efficient way is the solution provided below.

The following SQL query dynamically populates an integer field in a temporary table with years. The query uses a base year variable, which can be set to as far back as when the required data fields and values existed in the database. A loop then provides the years up to and including the current year. This query can be used to generate a dataset for an SSRS report and then this dataset can then be used to provide parameter values for the report. The report will then always create a list of years between the base year and the current year. Meaning the years parameter will never need to be adjusted again.

/*Create temp table populated with the years from a base year to the present year*/
IF OBJECT_ID('tempdb..#availableYear') IS NOT NULL
    DROP TABLE #availableYear

CREATE TABLE #availableYear ([Year] INT)

DECLARE @baseYear AS INT
DECLARE @i AS INT

/*Change the base year to the earliest year the database has the required data available*/
SET @baseYear = 2013
SET @i = 0

WHILE @i <= YEAR(GETDATE()) - @baseYear
BEGIN
    INSERT INTO #availableYear
    SELECT @baseYear + @i

    SET @i = @i + 1
END

SELECT * FROM #availableYear

Within the Stored Procedure that populates the report you can then do something like below to make sure the date range matches the year chosen.

DECLARE @yearChosen AS INT

SET @yearChosen = 2013

DECLARE @startDate date
DECLARE @endDate date

SET @startDate = CONVERT(CHAR(4), @yearChosen) + '0101'
SET @endDate = CONVERT(CHAR(4), @yearChosen) + '1231'

PRINT @startDate
PRINT @endDate    

--OR For example

YEAR(SaleDate) = @yearChosen

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

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.

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)