Script to truncate log of all the user databases

/************************************************************************************
Description: This script file will truncate the log file. When we run this script for first time this can take some time. So it is advisable that we run after working hours. Though this script can be run online, it is advisable to run the script when very minimal user activity is happening.

Warning : This should not be run on the production environmnet or databases

Credentials: User who runs this must be a member of sysadmin role. SA or any other user with same privileges can run this script

Version : sql server 2005 , sql server 2008
************************************************************************************/

declare @name nvarchar(255)
declare @recovery_model_desc nvarchar(255)
declare @recovery_model_desc_temp nvarchar(255)
declare @tsql nvarchar(355)
declare @tsql1 nvarchar(355)
declare @logfileName nvarchar(max)

declare database_cursor cursor for select name,recovery_model_desc
from sys.databases where name not in ('master','msdb','model','tempdb','adventureworks')

open database_cursor

fetch next from database_cursor into @name,@recovery_model_desc

while @@fetch_status = 0
begin

Print char(13) + 'Processing staring for ' + @name + ' at ' + cast(getdate() as nvarchar(20))

select @name = '[' + @name + ']'

select @tsql = 'use ' + @name + ';select top 1 @logfileNameOUT=name from sys.database_files where type = 1'

exec sp_executesql @tsql, N'@logfileNameOUT nvarchar(max) OUTPUT',@logfileNameOUT =@logfileName OUTPUT


print @logfileName
select @logfileName = '[' + @logfileName + ']'
select @tsql = 'use ' + @name + ';dbcc shrinkfile (' + @logfileName + ',1)'
exec sp_executesql @tsql

select @recovery_model_desc_temp= case when @recovery_model_desc = 'simple' then 'full' else 'simple' end

select @tsql1 = 'alter database ' + @name + ' set recovery ' + @recovery_model_desc_temp
exec sp_executesql @tsql1

select @tsql1 = 'alter database ' + @name + ' set recovery ' + @recovery_model_desc
exec sp_executesql @tsql1


exec sp_executesql @tsql

Print 'Processing completed for ' + @name + char(13)

fetch next from database_cursor into @name,@recovery_model_desc

end

close database_cursor

deallocate database_cursor

check values in text column for lower case or upper case

Today, I came across the situation where we need to update the text column and we want all the column text to UPPER CASE. But, we do not want to update the rows for which this text column already contains the word in upper case.


use AdventureWorks
GO

if object_id('tblCheckCase') is not null
drop table tblCheckCase
GO

Create table tblCheckCase
(
id int identity(1,1) primary key,
firstName nvarchar(10)
)
GO

insert into tblCheckCase(firstName)
select 'chintak'
union all
select 'chintan'
union all
select 'dhyey'
union all
select 'VIKAS'
union all
select 'DATTU'
GO

select firstName,charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) from tblCheckCase
GO

update tblCheckCase
set firstName = UPPER(firstName)
where charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) = 0
GO

select firstName as firstNameAfter,charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) from tblCheckCase
GO

Drop table tblCheckCase
GO

Use of TOP WITH TIES clause in SELECT queries

This caluse can be used to get additional rows from the base result set with the same value .
That is if the last of returned result set consists the same vlaue of order by item for other rows, those rows will also be returned.
- This can be only used in select statemnet.
- Order by clause is necessary for using this clause
- This can also be used with PERCENT clause.

Example:-

if object_id('test_top','U') is not NULL
drop table test_top
GO
create table test_top (a int, b int)
GO
insert into test_top
select 1,1
union all
select 2,1
union all
select 3,3
union all
select 4,3
go

Now,
select top 1 b from test_top

This will return only one row.
/*
b
----
1
*/


so,
select top 1 with ties b from test_top order by b

--this will return 2 rows as

/*
b
---
1
1
*/

But when we use top 100 percent caluse "with ties" not needed.

Pattern Matching with the ESCAPE Clause

We use % and _ in wildcard search with like clause but, some time we need to search the same chars from the string. So below example shows how to tackle this situation
For that we can use the ESCAPE caluse with like clause. we just have to define a ESCAPE char that will ESCAPE the next char from search string.


if object_id('test','U') is not null
drop table TASK_DETAIL
GO
create table task_detail
(
id int identity primary key,
name nvarchar(200),
task ntext
)
insert into task_detail(name,task)
select 'Chintak','review 100%_managemnet 20%_performance tuning 30%'
union all
select 'Chirag','coding 100%_unit testing 30%'
union all
select 'Ahish','management 100 hours'


-- To find out who is working 20% on any task
select id,name,task from task_detail where task like '%20!%%' ESCAPE '!'
--this will give the first row

--To find out employees working 100% on one task and also works on other task
select id,name,task from task_detail where task like '%100!%!_%' ESCAPE '!'
--this will give the first two rows

"Option Strict On disallows late binding" in SSIS Script task

"Option Strict" is a compiler option that's there to help you from introducing logic errors.
We can set this option to off at the beggining on the script task to remove such error.
for example,

Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime