Exception handling and nested transactions

This template below is very useful if you decide to add Transactions into your Store Procedure. Your SP could be called by other scripts and rollback may be issued inside the script. You need to use Safepoint if in this case. However, if there is no transaction exists, using savepoint will cause errors.

Another good article abuot Transaction Savepoints;  http://www.blackwasp.co.uk/SQLSavepoints.aspx

Template for error handling and nested transactions in Store Procedure

Original URL: http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/

create procedure [usp_my_procedure_name]
as
begin
	set nocount on;
	declare @trancount int;
	set @trancount = @@trancount;
	begin try
		if @trancount = 0
			begin transaction
		else
			save transaction usp_my_procedure_name;

		-- Do the actual work here
	
lbexit:
		if @trancount = 0	
			commit;
	end try
	begin catch
		declare @error int, @message varchar(4000), @xstate int;
		select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
		if @xstate = -1
			rollback;
		if @xstate = 1 and @trancount = 0
			rollback
		if @xstate = 1 and @trancount > 0
			rollback transaction usp_my_procedure_name;

		raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
	end catch	
end
go

@@Trancount and XACT_STATE()

Recently, I’ve encountered code that includes both @@Trancount and XACT_STATE().

 

XACT_STATE() is a scalar function that gives the user transaction state of a current running request. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not.

XACT_STATE returns the following three values

• 1: The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

• 0: There is no active user transaction for the current request.

• -1: The current request has an active user transaction, but an error has occurred that has
  caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

So before commit or rollback always test XACT_STATE for 0, 1, or -1.

• If 1, the transaction is committable.
• If -1, the transaction is uncommittable and should be rolled back.
• If 0, there is no transaction and a commit or rollback operation will generate an error.

 

Below is the difference between them

both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction.

@@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.

XACT_STATE cannot be used to determine whether there are nested transactions.

URL:  http://www.advancesharp.com/blog/1017/sql-transaction-status-and-xact-state

Using hashbyte()

 

Reference

URL: http://www.bidn.com/blogs/TomLannen/bidn-blog/2265/using-hashbytes-to-compare-columns

Using HASHBYTES() to compare columns

change text size: A A A

• Comments (2)

posted 10/17/2011  by TomLannen -  Views: [11671]

Recently, while at a client engagement, I was building some SSIS packages an issue came up where they didn?t want to use the CHECKSUM() function in TSQL to do column comparisons because the results can be inaccurate on some rare occasions.  I personally have never come across this but others here at Pragmatic Works have.  So we have two options freely available to work around this issue.  The first is the third party component plugin that you can get free at codeplex called Multiple Hash.  The client wasn?t comfortable with having to install this component on multiple servers throughout the environment so that option wasn?t available to me.  Instead I had to use the HASHBYTES() function in TSQL.

HASHBYTES() is much more reliable than checksum when it comes to producing accurate results, but it comes at a slight cost.

The first thing to note is how to construct the HASHBYTES() function.  In the first part you tell the function which algorithm you are going to use.  I?m using SHA1, but be aware that they single tics ? ? are required followed by a comma.  Then you must concatenate the columns you wish to use together as seen below.

There you can see its already a bit more arduous than using CHECKSUM(), but not that big of a deal to concatenate a bunch of columns.  Lets look at the results.

Uh-Oh here is our first problem.  HASHBYTES() doesn?t work with NULL values inside any columns.  So we?ve got to handle the Nulls in our query using the ISNULL() function.

Now the results look like this:

Then next thing that you have to look at is how HASHBYTES() handles(or more accurately doesn?t handle) Data types.  Here the ID column is an INT data type, but the same holds true for any non-string data type.

We get an error saying that the data type is wrong for the HASHBYTES() function

So now we have to CAST every column that is a non-string data type.

Now after this fix our results look better.

So as you can see already there will be a good deal more T-SQL coding involved with using HASHBYTES then with CHECKSUM(). But this isn?t all.  The last little gotcha isn?t quite as obvious as the first two.  Lets go back to our Null handling query.

I?ve gone and edited the data some for this example. Please also note that HASHBYTES() is case sensitive meaning that if you have the same spelling but different casing at an individual character level the hash value returned will be different.

The rows are different from one another but when concatenated together for the HASBYTES() function they produce the same exact value. So to handle this we have to update our code again.  We are going to add a rarely used character to the concatenation so ensure that they results will return correctly.

Here I chose to use a pipe to basically delimit the columns thereby making them different from each other. The result is much better.

If we take a quick look at results of the two concatenations we can see why we get the different results

The delimited column is obviously different from one row to the next, and the Non-Delimited column is exactly the same for each row.

While HASHBYTES() is definitely more accurate and reliable than using CHECKSUM(), there are some hoops to jump through to insure you get the results you need.  I hope this helps you guys out.

How to search one specific object among databases

-- Run on the SVANWARSQL02

DECLARE @SearchString VARCHAR(255)

DECLARE @tblName    VARCHAR(300)      -- each table from  select * from StagingData.dbo.stgETL0Cleanup  delete from StagingData.dbo.stgETL0Cleanup where searchstr ='ch_ivr_audit'
                           
DECLARE @db_name1    VARCHAR(300)
DECLARE @db_name2    VARCHAR(300)
DECLARE @sql        VARCHAR(500)
DECLARE @sql1       VARCHAR(500)
DECLARE @counter    INT
DECLARE @error_num  INT

DECLARE @name VARCHAR(50)

SET @counter = 0
--set @name = 'blabla'
--select searchstr,@name from StagingData.dbo.stgETL0Cleanup

--TRUNCATE TABLE StagingData.dbo.ETL0_SearchObjectResults

--IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_SearchObjectResults'))
--   BEGIN
--    CREATE TABLE StagingData.dbo.ETL0_SearchObjectResults
--        (   
--        sp_name                        VARCHAR(300),
--        dbName                        VARCHAR(300),
--        tblName                        VARCHAR(300),
--        ID                            VARCHAR(1000)
--        )
--    END
   
--TRUNCATE TABLE StagingData.dbo.ETL0_lkpResults   
   
--IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_lkpResults'))
--   BEGIN
--    CREATE TABLE StagingData.dbo.ETL0_lkpResults
--        (   
--        dbName                        VARCHAR(300),
--        tblName                        VARCHAR(300),
--        ID                            VARCHAR(1000)
--        )     
--   END 
                   
--DECLARE tbl_cursor CURSOR FOR
--SELECT searchstr
--FROM StagingData.dbo.stgETL0Cleanup                   

                                                       
DECLARE db_cursor CURSOR FOR 
SELECT name                 -- get the list of all databases on this server
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerExternal','ReportServerExternalTempDB','ReportServerTempDB',
'Strata','TelcoReconciliation','TestForReplication','VanBMCProduction_Backup','MapInfo','RepMD','CommonEvent','CommonEvent_2011Q1',
'CommonEvent_2011Q2','CommonEvent_2011Q3','QuestSoftware','QuestWorkDatabase','ProdAdminDB_Backup','EventData_Detail_cw','Chr_Utils_WrkDB',
'VanReplicaOld'
) 
ORDER by name
   
   
    OPEN db_cursor

 
FETCH NEXT FROM db_cursor INTO @name

While @@FETCH_STATUS = 0
BEGIN
    --OPEN tbl_cursor
    --FETCH NEXT FROM tbl_cursor INTO @tblname
   
    --    While @@FETCH_STATUS = 0
    --        BEGIN   
           
                --SET @counter = @counter + 1
                       
                SET @db_name1 =@name+'.dbo.'+'syscomments'
                SET @db_name2 =@name+'.dbo.'+'sysobjects'      
               

                SELECT @sql =                                        
                'SELECT DISTINCT '''+@name+''',o.name,'+CONVERT(varchar(1000),@counter)+' From '+@db_name1+' s JOIN'+' '+@db_name2+' o ON s.id = o.id '
               
                --SELECT @sql = @sql+' WHERE  s.text like '+'''%'+@tblname+'%'''
                SELECT @sql = @sql+' WHERE  s.text like '+'''%'+'cc_optimal_settlement_toberemoved'+'%'''   --->>>>> individual search, change the name between % %
                --SELECT @sql ='INSERT INTO StagingData.dbo.ETL0_SearchObjectResults (sp_name,ID)  '+@sql   
                EXEC(@sql)
               
                Select @error_num = @@ERROR               
                IF @error_num <> 0  Print @sql
               
                --SELECT @sql1 = 'SELECT '''+@name+''','''+@tblName+''','+CONVERT(varchar(1000),@counter)
                --SELECT @sql1 = 'INSERT INTO StagingData.dbo.ETL0_lkpResults(dbName,tblName,ID) '+@sql1
       
                --EXEC(@sql1)
               
                --Select @error_num = @@ERROR
                --IF @error_num <> 0  Print @sql
               
               
                --FETCH NEXT FROM tbl_cursor INTO @tblname
               
        --    END
        --Close tbl_cursor

       
    FETCH NEXT FROM db_cursor INTO @name
END   

--DEALLOCATE tbl_cursor
Close db_cursor
DEALLOCATE db_cursor

--select distinct l.tblName,l.dbName,r.sp_name from StagingData.dbo.ETL0_SearchObjectResults r join StagingData.dbo.ETL0_lkpResults l on r.ID = l.ID order by l.tblName,l.dbName,r.sp_name

 

--SELECT DISTINCT o.name, 'Chr_Utils','ch_ivr_audit'
--From Chr_Utils.dbo.syscomments s JOIN Chr_Utils.dbo.sysobjects o ON s.id = o.id 
--WHERE  s.text like '%ch_ivr_audit%'

--, '+['@name']+','+['@tblname']+

Code for searching objects among databases and jobs

 

1. Among databases

-- Run on the SVANWARSQL02

DECLARE @SearchString VARCHAR(255)

DECLARE @tblName    VARCHAR(300)      -- each table from  select * from StagingData.dbo.stgETL0Cleanup  delete from StagingData.dbo.stgETL0Cleanup where searchstr ='ch_ivr_audit'
                           
DECLARE @db_name1    VARCHAR(300)
DECLARE @db_name2    VARCHAR(300)
DECLARE @sql        VARCHAR(500)
DECLARE @sql1       VARCHAR(500)
DECLARE @counter    INT
DECLARE @error_num  INT

DECLARE @name VARCHAR(50)

SET @counter = 0
--set @name = 'blabla'
--select searchstr,@name from StagingData.dbo.stgETL0Cleanup

TRUNCATE TABLE StagingData.dbo.ETL0_SearchObjectResults

IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_SearchObjectResults'))
   BEGIN
    CREATE TABLE StagingData.dbo.ETL0_SearchObjectResults
        (   
        sp_name                        VARCHAR(300),
        dbName                        VARCHAR(300),
        tblName                        VARCHAR(300),
        ID                            VARCHAR(1000)
        )
    END
   
TRUNCATE TABLE StagingData.dbo.ETL0_lkpResults   
   
IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_lkpResults'))
   BEGIN
    CREATE TABLE StagingData.dbo.ETL0_lkpResults
        (   
        dbName                        VARCHAR(300),
        tblName                        VARCHAR(300),
        ID                            VARCHAR(1000)
        )     
   END 
                   
DECLARE tbl_cursor CURSOR FOR
SELECT searchstr
FROM StagingData.dbo.stgETL0Cleanup                   

                                                       
DECLARE db_cursor CURSOR FOR 
SELECT name                 -- get the list of all databases on this server
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerExternal','ReportServerExternalTempDB','ReportServerTempDB',
'Strata','TelcoReconciliation','TestForReplication','VanBMCProduction_Backup','MapInfo','RepMD','CommonEvent','CommonEvent_2011Q1',
'CommonEvent_2011Q2','CommonEvent_2011Q3','QuestSoftware','QuestWorkDatabase','ProdAdminDB_Backup','EventData_Detail_cw','Chr_Utils_WrkDB',
'VanReplicaOld'
) 
ORDER by name
   
   
    OPEN db_cursor

 
FETCH NEXT FROM db_cursor INTO @name

While @@FETCH_STATUS = 0
BEGIN
    OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @tblname
   
        While @@FETCH_STATUS = 0
            BEGIN   
           
                SET @counter = @counter + 1
                       
                SET @db_name1 =@name+'.dbo.'+'syscomments'
                SET @db_name2 =@name+'.dbo.'+'sysobjects'      
               

                SELECT @sql =                                        
                'SELECT DISTINCT '+'o.name,'+CONVERT(varchar(1000),@counter)+' From '+@db_name1+' s JOIN'+' '+@db_name2+' o ON s.id = o.id '
               
                SELECT @sql = @sql+' WHERE  s.text like '+'''%'+@tblname+'%'''
                --SELECT @sql = @sql+' WHERE  s.text like '+'''%'+@name+'%'''   --->>>>> individual search, change the name between % %
                SELECT @sql ='INSERT INTO StagingData.dbo.ETL0_SearchObjectResults (sp_name,ID)  '+@sql   
                EXEC(@sql)
               
                Select @error_num = @@ERROR               
                IF @error_num <> 0  Print @sql
               
                SELECT @sql1 = 'SELECT '''+@name+''','''+@tblName+''','+CONVERT(varchar(1000),@counter)
                SELECT @sql1 = 'INSERT INTO StagingData.dbo.ETL0_lkpResults(dbName,tblName,ID) '+@sql1
       
                EXEC(@sql1)
               
                Select @error_num = @@ERROR
                IF @error_num <> 0  Print @sql
               
               
                FETCH NEXT FROM tbl_cursor INTO @tblname
               
            END
        Close tbl_cursor

       
    FETCH NEXT FROM db_cursor INTO @name
END   

DEALLOCATE tbl_cursor
Close db_cursor
DEALLOCATE db_cursor

--select distinct l.tblName,l.dbName,r.sp_name from StagingData.dbo.ETL0_SearchObjectResults r join StagingData.dbo.ETL0_lkpResults l on r.ID = l.ID order by l.tblName,l.dbName,r.sp_name

 

--SELECT DISTINCT o.name, 'Chr_Utils','ch_ivr_audit'
--From Chr_Utils.dbo.syscomments s JOIN Chr_Utils.dbo.sysobjects o ON s.id = o.id 
--WHERE  s.text like '%ch_ivr_audit%'

--, '+['@name']+','+['@tblname']+

 

2. Among jobs

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  -- Instead of use nolock everytime

DECLARE              @Str VARCHAR(100)

DECLARE tbl_cursor CURSOR FOR   
SELECT searchstr
FROM StagingData.dbo.stgETL0Cleanup        stc
WHERE  stc.SearchStr not like 'ch%'

    OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @Str
   
        While @@FETCH_STATUS = 0
            BEGIN

                SELECT  @Str = '%' + @Str + '%'

                SELECT                            @str as SearchingName,
                                                CONVERT(VARCHAR(50),jo.name) AS JobName,
                                                jo.enabled,
                                                js.last_run_date,
                                                CONVERT(VARCHAR(30),js.database_name) AS dbname,
                                                CONVERT(VARCHAR(50), js.step_name) AS step_name,
                                                REPLACE(REPLACE(js.command, CHAR(10), ''), CHAR(13), '') AS Cmd,
                                                js.step_id
                FROM    msdb.dbo.sysjobs           jo
                JOIN      msdb.dbo.sysjobsteps js
                ON     jo.job_id     = js.job_id
                WHERE
                    (  jo.name    LIKE @Str
                        OR          js.step_name   LIKE @Str
                        OR          js.command       LIKE @Str
                      )
                                               
                ORDER BY jo.job_id, js.step_id
               
                FETCH NEXT FROM tbl_cursor INTO @Str
       
            END

        Close tbl_cursor
        DEALLOCATE tbl_cursor

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Find all stored procedures where a column is being used/referenced

-- compliments from SQLUSA

   1:  USE AdventureWorks2008; 

   2:  DECLARE @SchemaName sysname = N'Production'; 

   3:  DECLARE @TableName sysname = N'Product'; 

   4:  DECLARE @ColumnName sysname = N'ProductID'; 

   5:  SELECT QUOTENAME(refing.referencing_schema_name) + 

   6:  N'.' + QUOTENAME(refing.referencing_entity_name) As SprocName 

   7:  FROM sys.dm_sql_referencing_entities(QUOTENAME(ISNULL(@SchemaName,N'dbo')) + 

   8:  N'.' + QUOTENAME(@TableName),'object') refing 

   9:  CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(refing.referencing_schema_name) + 

  10:  N'.' + QUOTENAME(refing.referencing_entity_name), 'object') refed 

  11:  WHERE EXISTS(SELECT * FROM sys.objects 

  12:  WHERE refing.referencing_id = object_id and type ='P') 

  13:  AND refed.referenced_schema_name = @SchemaName 

  14:  AND refed.referenced_entity_name = @TableName 

  15:  AND refed.referenced_minor_name  = @ColumnName 

  16:  ORDER BY SprocName;

  17:   

DATETIME in Store Procedure and Date/Time in Reporting Service

In SP, if you specify only YYYYMMDD and no timestamp, the default one is 0000000. However, in Reporting service parameter section, if the datatype is Date/Time and no time stamp specified, the default one is 125959am. (to be confirmed?)

Report Optimization/Tuning

I have been assigned a task to speed up a slow report. This report actually has 6 different datasets and each calls a different store procedure. My job is to tune it to speed it up. The first step I did is to find out how long it may take to execute the report and associated store procedure to produce the results.

SSMS 2008 can not save comments for SQL Server 2000 tables

I am trying to put some comments for a view in SQL SERVER 2000 but seems not able to do so. However, when I use Enterprise Analyzer to perform the same task, it seems fine. I think SSMS 2008 is not quite compatible with SQL SERVER 2000. Just a small tip to remember.

Closure of the MS BI project

will write some experience through this project
Several items that I’d like to touch are listed below:

1. SSIS tips
Development
We have 3 types of packages: daily, weekly and monthly.
Daily pkgs will pick up new records everyday and aggregate into the summary table.
For daily changed reference data, we do not pick up untill the monthly pkgs kick in.
That’s our version to Slowly Changing Dimension (ideally, SCD should be used)

weekly pkgs will truncate the fact table and bring 3 month new records

monthly pkgs will truncate dimension tables and bring all records

Deployment
So far, the deployment is using file system and import pkgs via SSMS. That’s not the best way though but DBA feels more comfortable in this way

Debugging
We have created a sysssislog table which contains all log information. If data error or pkg error occurs , dba or am will get an email immediately.

When we direct error output to another table in OLE DB destination, make sure it’s using table/view instead of fast load. If fast load is used, if the stream hits any bad data, ssis will direct all data after the bad row into the error table no matter if it’s bad or not. Be careful!

2. SSAS tips
Development
Deployment
Debugging

3. Version Control (SVN)

how to handle the Numeric datatype = datetime in the where clause

In a where clause, I need to put billing_period = effective_date. The billing_period is numeric(6,0) while the effective_date is datetime. I’ve come up with the following code.

Step 1 is to convert billing_period into varchcar

Step 2 is to convert effective_date into varchar. This step requires peeling each part from the date type and padding the month and day.

cast(rut.BILLING_PERIOD as varchar(6))+'01' =
cast(datepart(yy,b.effective_date) as varchar(4))+
REPLICATE('0',2-len(cast(DATepart(mm,b.effective_date) as varchar(2))))+cast(DATepart(mm,b.effective_date) as varchar(2))+
REPLICATE('0',2-len(cast(DATepart(dd,b.effective_date) as varchar(2))))+cast(DATEPART(dd,b.effective_date) as varchar(2))

How to create a time dimension in a table?

  with mycte as

(
select cast('2009-01-01' as datetime) DateValue
union all
select DateValue + 1
from    mycte  
where   DateValue + 1 < = GetDate() + 10

  )

select DateValue

from    mycte

OPTION (MAXRECURSION 0)

This script will generate date starting from 2009-01-01 to 10 days after current date.

Oracle Numeric type to SQL SERVER Numeric

In DDL, one column is defined as CDR_ID                NUMERIC,

when I run the script against Sql server, the output is CDR_ID (numeric(18,0), null).

Seems SQL SERVER automatically sets the default as (18,0)

What’s is database collation?

Here is a good article

http://help.godaddy.com/article/4203

Collation controls the way string values are sorted

This is another good article on the topic of collation

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/12/883.aspx

Drop multiple tables with schema having \

This one is based on the following thread:

http://dbaspot.com/forums/sqlserver-faq/228429-problem-alter-schema-dbo-transfer-owrd-julainih-authorlist.html

I need to modify a bit to accommodate my scenario.

 

DECLARE @id varchar(255)
DECLARE @dropCommand varchar(255)

DECLARE tableCursor CURSOR FOR
    select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'STRATOS\donso'

OPEN tableCursor
FETCH next FROM tableCursor INTO @id

WHILE @@fetch_status=0
BEGIN
    SET @dropcommand = 'drop table ' + @id
    EXECUTE(@dropcommand)
    FETCH next FROM tableCursor INTO @id
    print 'drop table'+@id
END

CLOSE tableCursor
DEALLOCATE tableCursor

Tricky part:

1. Use information_schema to get the list of tables that belong to user STRATOS\donso

2. Since the schema contains ‘ \ ‘, we must use [] to escape. Otherwise, SSMS will keep popping error msgs. The solution is below

    select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'STRATOS\donso'