Bala's Development Blog

.Net,SQL Server, ASP.Net ……

Archive for the ‘T-SQL’ tag

T-SQL Debugging Tips – Catching the error information

with 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

Written by BALA SINGAM

January 14th, 2011 at 11:47 pm

Get list of view that uses certain column name

without 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'

Written by BALA SINGAM

July 19th, 2009 at 9:36 pm

Posted in SQL server Basics

Tagged with , , ,

How to shrink db log file

without 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)

Written by BALA SINGAM

July 18th, 2009 at 9:32 pm

Update top N record in SQL Server

with 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

Written by BALA SINGAM

June 9th, 2009 at 12:12 am

SQL – delete from join

with one 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

Written by BALA SINGAM

June 8th, 2009 at 12:09 am

SQL to check SQL Server version

without 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')

Written by BALA SINGAM

April 27th, 2009 at 7:20 pm

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

without 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


Written by BALA SINGAM

February 28th, 2009 at 12:39 am