SQL SERVER – SQL TIPS – SELECT the ERROLOG.

A useful tip for look into the ERRORLOG, is use a SELECT statement with ERROLOG as table.

First is necessary to create a table with the structure of the ERROLOG outcome.

CREATE TABLE TBLERRORLOG

(

LOGDATE DATETIME,

PROCESSINFO VARCHAR(30),

DETAIL VARCHAR(8000)

)

Then, full the table with the results of extended stored procedure xp_readerrolog:

INSERT INTO TBLERRORLOG exec master..xp_readerrorlog

Now is possible SELECT the table TBLERRORLOG with a certain filter:

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

If you have several error logs, you can use the following code:

truncate table TBLERRORLOG

Go

INSERT INTO TBLERRORLOG exec sp_readerrorlog 2

go

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

First, the table TBLERRORLOG is truncated, then the table is filled using the undocumented stored procedure sp_readerrolog, there is possible to define the ERROLOG number, in this case I use 2, then a SELECT statement is used. This code can be used several times depending of the ERROLOG allowing us a quickly way to find some issue.

SQL SERVER – TSQL – Handling Errors

For SQL Server 2000 and earlier version, the most common way to working error as using @@ERROR function. SQL Server 2005 introduces the construct TRY…CATCH, it improves handling errors. The construct TRY…CATCH is the principal tool for handling error on SQL Server, specifically with exceptions errors. This capability allows you to manage exception errors in similar way as .NET programming languages.

A TRY…CATCH consist in two parts: these are a TRY block followed immediately by a CATCH block. All the TSQL Code must to be on TRY block and the handling error must to be on CATCH block. When in the TRY block does not exist error, the CATCH block is simply skipped. If the TRY block has an error, then the control is transferred to the next CATCH block.

Both, TRY and CATCH block starts with BEGIN TRY and BEGIN CATCH respectively, and ends with END TRY and END CATCH respectively.

You can test the following code in order to see a case without error on TRY block.

-- TSQL WORKING FINE

BEGIN TRY

PRINT 100 / 10

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The code on TRY block was executed successfully; that’s way the CATCH block was not executed. The result is the following:

10

Working on BLOCK TRY. WITHOUT ERRORS!!!!!!

Now, execute the following code with error on the TRY block:

-- TSQL WITH ERROR - DIVIDE BY ZERO

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The divide by zero causes an ERROR on the TRY block, it cause that the control was passed immediately to CATCH BLOCK and also cause that the second PRINT on TRY block was not executed. The result is the following.

Working on BLOCK CATCH WITH ERRORS!!!!!!

To obtain information about the error that cause the CATCH block to be executed, SQL Server provides the following system functions:

ERROR_NUMBER() function returns an integer with the number of error

ERROR_SEVERITY() function returns the severity number.

ERROR_STATE() function returns the error state number.

ERROR_LINE() function returns the line number inside the routine that caused the error.

ERROR_MESSAGE() function returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

ERROR_PROCEDURE() function returns the name of the stored procedure or trigger where the error occurred.

You can execute the following code in order to create a Stored Procedure that encapsulates the system functions mentioned above. Also you can customize the error messages. The following code is customized when the error number 208 (Invalid Object Name) happens, this customized message error will be used later. :

create procedure dbo.sp_err_msg

as

if ERROR_NUMBER() = 208

print 'Oops, the table queried does not exists. '

else

select

ERROR_NUMBER() as ErrorNumber,

ERROR_SEVERITY() as Severity,

ERROR_STATE() as State,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage,

ERROR_PROCEDURE() as ErrorOnProcedure

GO

You can modify the second code used in this article in order to see how works the error system functions.

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The divide by zero cause an error on TRY block, then the control was passed to CATCH block and the stored procedure is executed. The result is the following.

Some errors are not handled by construct TRY…CATCH :

• Compilation errors. Ex syntax error.
• Recompilation errors. Ex object name resolution error.

If an compilation or recompilation errors occurs when they are at TRY … CATCH level, those errors are not handled by construct TRY …CATCH. On the other hand, if the error occurs at a lower execution level (ex. Sp_executesql or a stored procedure) the errors are handled by the construct TRY…CATCH.

Run the following code to demonstrate an event that is not handle by TRY…CATCH. The code, execute a SELECT statement to a non existing table, it cause an error:

BEGIN TRY

SELECT * FROM TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

Result:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'TABLENOTDEFINED'.

However, if the SELECT statement is executed by a stored procedure the error will occur at a level lower than TRY block. Then, the error will be handled by the construct TRY … CATCH.

create procedure dbo.sp_exec_TABLENOTDEFINED

as

SELECT * FROM TABLENOTDEFINED

GO

BEGIN TRY

exec dbo.sp_exec_TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The table non defined on the Stored procedure cause an error 208, it is handled by TRY ... CATCH, and a customized error message is shown.

Result.

Oops, the table queried does not exists.

Conclusion

TRY..CATCH is the main tool provided by SQL Server to handling errors. To obtain more information about the errors is possible to use a system functions as: ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), ERROR_PROCEDURE().

SQL SERVER – TIPS – Reclaim space for a table after drop column.

In a table, after a column is dropped using alter table drop column statement, is necessary to reclaim the space, just when a fixed-length column or text column were dropped.

To see the behavior you can create 3 tables with 3 fields, this 3 fields must to be different datatypes

-- create a db test create database db_test go use db_test go -- create 3 table with the same fields create table test1 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test2 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) go create table test3 ( a char(20), --fixed-lenght column b varchar(20), --variable-lenght column c text --text column ) Go

Now, you can add some information and see the space of each table.

-- insert some information into the 3 tables. insert into test1 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test2 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') insert into test3 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ') --checking the space sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 32 KB 24 KB 8 KB 0 KB */

Then, drop a column for each table, after drop a column the space for each table is the same than before. To reclaim the space is necessary to execute the dbcc cleantable on each table. This dbcc utility must to be executed when a variable-length column or text column were dropped.

-- drop one column in each table in order to -- see the behavior after drop a column alter table test1 drop column a alter table test2 drop column b alter table test3 drop column c --reclaim space dbcc cleantable ('db_test','test1') dbcc cleantable ('db_test','test2') dbcc cleantable ('db_test','test3')

MSDN says “DBCC CLEANTABLE reclaims space after a variable-length column or a text column is dropped by using the ALTER TABLE DROP COLUMN statement. The command does not reclaim space after a fixed-length column is dropped.”

After execute DBCC CLEANTABLE, you can see that some space was reclaimed for the table test3 where the TEXT column was dropped, for the other two tables the space is the same, even for the table 2 where a variable-length column was dropped, in this case some space must to be reclaimed, perhaps does not happening anything because the amount of data is not relevant.

sp_spaceused test1 Go sp_spaceused test2 Go sp_spaceused test3 GO /* --Result test1 1 32 KB 24 KB 8 KB 0 KB test2 1 32 KB 24 KB 8 KB 0 KB test3 1 24 KB 16 KB 8 KB 0 KB */

SQL SERVER – TIPS – Transfer Objects between Schemas

In this tip, I show you, how you can transfer an object between schemas using ALTER SCHEMA statement.

-- create a db test create database db_test go use db_test go --Create a schema original create schema original go -- create a schema final create schema final go -- create a table with the schema original create table original.test1 (a int) go -- checking the schema for the test1 table select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1' go /* -- Result schema_name name ------------ --------------- original test1 */ -- transfering object test1 from schema original to final -- ORIGINAL -> FINAL alter schema final transfer original.test1 go /* -- Result Command(s) completed successfully. */ -- checking the schema for the test1 table after the changes select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1' go /* -- Result schema_name name ------------ --------------- final test1 */