Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Monthly Archives: March 2011

SQL Server offers an OUTPUT command to capture data that is changed, either through updating, deletion or insertion. The ‘before’ and ‘after’ images of the data are stored in system tables named DELETED and INSERTED. It is possible to query the applicable table to retrieve data that has been deleted or inserted by your sql statement. In the case of updates, the old data can be found in DELETED and the newly written data in INSERTED.

The following example demonstrates the process. An Audit table is created to store the results. An UPDATE is then issued against a number of columns. OUTPUT writes the old and new versions of the data (along with the id of the row changed and the date and time) to the audit table. Adding the output clause to stored procedures will allow you to audit all data changes in your database, although there will obviously be some overhead due to additional writes.

CREATE TABLE Audit_Table (CustomerID varchar(12),
                         OldAddress1 varchar(50),
                         OldAddress2 varchar(50),
                         OldAddress3 varchar(50),
                         OldAddress4 varchar(50),
                         OldAddress5 varchar(50),
                         OldPostCode varchar(12),
                         NewAddress1 varchar(50),
                         NewAddress2 varchar(50),
                         NewAddress3 varchar(50),
                         NewAddress4 varchar(50),
                         NewAddress5 varchar(50),
                         NewPostCode varchar(12),
                         DateChanged datetime);

UPDATE CustomerTable
SET Address1 = '99 My Road',
      Address2 = 'My Area',
      Address3 = 'My City',
      Address4 = 'My County',
      Address5 = 'My Country',
      PostCode = 'AA1 1AA'
OUTPUT INSERTED.CustomerID,
	    DELETED.Address1 AS 'OldAddress1',
	    INSERTED.Address1 AS 'NewAddress1',
	    DELETED.Address2 AS 'OldAddress2',
	    INSERTED.Address2 AS 'NewAddress2',
	    DELETED.Address3 AS 'OldAddress3',
	    INSERTED.Address3 AS 'NewAddress3',
	    DELETED.Address4 AS 'OldAddress4',
	    INSERTED.Address4 AS 'NewAddress4',
	    DELETED.Address5 AS 'OldAddress5',
	    INSERTED.Address5 AS 'NewAddress5',
	    DELETED.stu_capc AS 'OldPostCode',
	    INSERTED.stu_capc AS 'NewPostCode',
	    GETDATE() AS 'DateChanged'
INTO AuditTable
WHERE CustomerTable.CustomerID = 'AB123456';

Tags: , , , , ,

When running a trace in SQL Server Profiler, it is bad practice to save the trace information directly into a SQL Server table. Writing trace data directly to a table uses a lot or resources and this can greatly errode server performance. However, it is possible to load the contents of a file once a trace has been captured.

Find the path of any existing traces by executing the following:-

select path 
from sys.traces

Once the path and file name are known, run the following SQL:-

-- Load contents of trace into a SQL temporary table
-- Do not specify log numbers to load all log files for a trace
SELECT * INTO ##temp_trc
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default);

SELECT * FROM ##temp_trc

The above example loads all trace files like log%.trc [log1.trc, log2.trc etc] into a temporary table. You can of course load into a physically stored table by removing the hashes. If you want to load data from just one trace file from a group of rolled over trace files then specify the number on the end [e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log7.trc].

Tags: , , ,

SQL Server errors with a severity level of 19 or above are considered fatal errors. The following is taken from Books online:-

Severity Level 19: SQL Server Error in Resource

These messages indicate that some nonconfigurable internal limit has been exceeded and the current batch process is terminated. Severity level 19 errors occur rarely; however, they must be corrected by the system administrator or your primary support provider. The administrator should be informed every time a severity level 19 message occurs.

Severity Levels 20 through 25

Severity levels from 20 through 25 indicate system problems. These are fatal errors, which means that the process (the program code that accomplishes the task specified in your statement) is no longer running. The process freezes before it stops, records information about what occurred, and then terminates. The client connection to SQL Server closes, and depending on the problem, the client might not be able to reconnect.

Error messages with a severity level of 19 or higher stop the current batch. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection. Errors messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.

Severity Level 20: SQL Server Fatal Error in Current Process

These messages indicate that a statement has encountered a problem. Because the problem has affected only the current process, it is unlikely that the database itself has been damaged.

Severity Level 21: SQL Server Fatal Error in Database (dbid) Processes

These messages indicate that you have encountered a problem that affects all processes in the current database; however, it is unlikely that the database itself has been damaged.

Severity Level 22: SQL Server Fatal Error Table Integrity Suspect

These messages indicate that the table or index specified in the message has been damaged by a software or hardware problem.

Severity level 22 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine if other objects in the database are also damaged. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.

If restarting does not help, the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem. For example, if the message tells you that SQL Server has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.

Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect

These messages indicate that the integrity of the entire database is in question because of a hardware or software problem.

Severity level 23 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine the extent of the damage. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.

Severity Level 24: Hardware Error

These messages indicate some type of media failure. The system administrator might have to reload the database. It might also be necessary to call your hardware vendor.

Setting up Alerts

You should set up alerts in SQL Server Agent for any errors that have a severity level of 19 or above. Use the following script to generate the alerts, ensuring you replace “Operator Name” with the appropriate name of the operator to notify by email:-

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'019 - Fatal Error In Resource',
		@message_id=0,
		@severity=19,
		@enabled=1,
		@delay_between_responses=1800,
		@include_event_description_in=1,
		@category_name=N'[Uncategorized]',
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'020 - Fatal Error in Current Process',
		@message_id=0,
		@severity=20,
		@enabled=1,
		@delay_between_responses=1800,
		@include_event_description_in=1,
		@category_name=N'[Uncategorized]',
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'021 - Fatal Error in Database Processes',
		@message_id=0,
		@severity=21,
		@enabled=1,
		@delay_between_responses=1800,
		@include_event_description_in=1,
		@category_name=N'[Uncategorized]',
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'022 - Fatal Error: Table Integrity Suspect',
		@message_id=0,
		@severity=22,
		@enabled=1,
		@delay_between_responses=1800,
		@include_event_description_in=1,
		@category_name=N'[Uncategorized]',
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'023 - Fatal Error: Database Integrity Suspect',
		@message_id=0,
		@severity=23,
		@enabled=1,
		@delay_between_responses=1800,
		@include_event_description_in=1,
		@category_name=N'[Uncategorized]',
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'024 - Fatal Error: Hardware Error',
		@message_id=0,
		@severity=24,
		@enabled=1,
		@delay_between_responses=1800,
		@include_event_description_in=1,
		@category_name=N'[Uncategorized]',
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'025 - Fatal Error',
		@message_id=0,
		@severity=25,
		@enabled=1,
		@delay_between_responses=1800,
		@include_event_description_in=1,
		@category_name=N'[Uncategorized]',
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'019 - Fatal Error In Resource', @operator_name=N'Operator Name', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'020 - Fatal Error in Current Process', @operator_name=N'Operator Name', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'021 - Fatal Error in Database Processes', @operator_name=N'Operator Name', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'022 - Fatal Error: Table Integrity Suspect', @operator_name=N'Operator Name', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'023 - Fatal Error: Database Integrity Suspect', @operator_name=N'Operator Name', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'024 - Fatal Error: Hardware Error', @operator_name=N'Operator Name', @notification_method = 1
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'025 - Fatal Error', @operator_name=N'Operator Name', @notification_method = 1
GO

Tags: , , ,

The BULK INSERT command is used widely for importing data into SQL Server tables. It is very handy when there is a requirement to import large amounts of data from a CSV or Text file. An example follows:-

BULK INSERT MyTable
FROM 'e:\mytextfile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

However, there are certain scenarios when I do not want to import vast amounts of data into a table, but instead need to be able query the file directly. This can be achieved using OPENROWSET.

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]')

It is then possible to join data from the file to tables within your database:-

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]') x
LEFT JOIN CustomerTable c
	on c.customerid = x.custid

Tags: , , , , , ,

%d bloggers like this: