T-SQL Debugging Tips – Catching the error information

January 14, 2011

(2) Comments

During recent debugging of T-SQL stored procedures, I found adding following
code in your catch block will help to point the actual cause of error and make your programming life much easy!

 Begin Try
    Print 'Do Something'
 End Try
 BEGIN CATCH
         ROLLBACK TRANSACTION
         print 'Error Msg: ' + ERROR_MESSAGE()
         print 'Error Procedure: ' + ERROR_PROCEDURE()
         print 'Error Number: ' + convert(varchar(50),ERROR_NUMBER())
         print 'Error Line: ' + convert(varchar(50),ERROR_Line()
   END CATCH

Source : http://msdn.microsoft.com/en-us/library/ms178600.aspx

Get list of view that uses certain column name

July 19, 2009

(0) Comments

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

I was looking a query that tell me a list of view using certain view from a table, and found the answer here.

See the code below. Hope this could help someone somewhere.

select view_name from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
 where column_name='ColumnNameHere'
 and table_name='TableNameHere'

BALA SINGAM

, , ,


How to shrink db log file

July 18, 2009

(0) Comments

I got this code snippet from my collegue , hope this can be helpful for others too

declare @dbname nvarchar(255)
set @dbname = 'databasename'
backup log @dbname with truncate_only
DBCC SHRINKDATABASE (@dbname, 0)

Update top N record in SQL Server

June 9, 2009

(4) Comments

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

SQL statement to update top N records

update daily_sales
set is_calc='Y'
from daily_sales
inner join (select top 100 * from daily_sales where product_category='CA') a
on a.unique_record_id=daily_sales.unique_record_id
and a.milestone_code=daily_sales.milestone_code

Source is SQLServerCentral

SQL – delete from join

June 8, 2009

(1) Comment

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

Simple statement to delete record a table with condition from other related table.

delete prod
from excp_amended_daily_sales_deposits prod
inner join excp_amended_daily_sales ds
on ds.id=prod.id
where  ds.status <> 'Active'
and ds.reason_code='51'

Another example

delete  hist_tbl_ASNHeaders 
from    hist_ShipmentHeader,hist_tbl_ASNHeaders
where hist_ShipmentHeader.ShipmentHeaderID=hist_tbl_ASNHeaders.fkShipmentHeaderID

SQL to check SQL Server version

April 27, 2009

(0) Comments

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.
SELECT  SERVERPROPERTY('productversion'), 
            SERVERPROPERTY ('productlevel'), 
             SERVERPROPERTY ('edition')

SQL Basic #3 : T-SQL Database back up script

February 28, 2009

(0) Comments

Disclaimer : This tutorial is meant for beginners and for anyone who find it useful. The main purpose of this post to keep everything i find useful for future reference for myself and for others.

Yesterday I been requested by one of our client to write a database back up script so that they can schedule it as SQL Server job. As usual i google it and found plenty of solutions but I was looking for something simple and workable. Found it here

This is one line T-SQL code to back up any given database however I added 2 extra line code to have the date on the back up file name.

declare @filename as varchar(100)
SET @fileName = 'd:\' + 'Skanda' + '_' + CONVERT(VARCHAR(20),GETDATE(),112)</code> + '.BAK'
BACKUP DATABASE Skanda TO DISK = @fileName

The a backup file with following format will created Skanda_20100214.BAK


|| Older Entries »