The Unhealthy Argument

This post of mine is 100 percent a personal opinion and was written not to hurt anyone.

Off late, I have been hearing or knowing incidents which have involved 2 or more persons going into an argument on deciding which technology is the best is it WINDOWS or UNIX, ORACLE or SQL SERVER, .Net or JAVA, IE or Chrome, etc. There are few incidents that came to my notice which I would like to share with you all.

1. This was between friends of mine who worked in my previous company; they both work in the same team providing support to one of their clients. One is an ORACLE DBA and other is SQL Server DBA. They have argued hardly to decide which Database systems is the best and have ended up criticizing Larry Ellison and Bill Gates.

2. The next is between the web browsers, I have read a numerous articles in the blogs and other websites saying IE is the best or Chrome is the best or Firefox is the best, etc. The same has happened between my known persons where they have ended using abusive languages. I am sorry to say this, but when people lose temper that is the end result.

3. Again, between my old colleagues, about WINDOWS or UNIX, each of them have had strong points to prove that they work on the latest technology of the server side Operating systems but failed to convince others. Thank God, NO blows or words were exchanged in the incident.

Now, let me come to say this, each technology or application is designed for a purpose and has its own pros and cons with it. Thanks to my patience in the recent past that I have not had such an Unhealthy Argument like this. In my opinion, a Database or an Operating system is chosen with the needs of the application or the system to be designed. It comes as a decision on that has to be compatible with the end application to be designed. The architect who decides on what needs to be used is not a biased person, he/she knows that he has to be committed to his company's core business values and chooses one deciding on it.

So, people going forth I request each of you not to have a discussion or argument to decide which the best is. We all work on the latest technologies of what we have now and should be proud that most of us are redefining technology that has got us in the past.

Again, this is a personal opinion and was written not to hurt anyone. Thanks for taking time to read this blog. Your comments on the same are welcome.

Back to Blogging

It has been almost 40 days since my last blog. I am back to work and people can expect more SQL Server related stuff on this blog again. If you need any specific interests, Please do send me a note so that the same can be put as an article.

Many thanks for reading.

Out for Vacation

For those who are trying to mail me or looking out reasons finding why I am not online in any of the messangers. The answer is here. I will be out for vacation till mid of september. I will check mails few times a week but no more than that. If i don't write back to you, Please wait. Many thanks.

Virtual TechDays: FREE Technical Training Opportunity

Microsoft is Offering Free SQL Server and other products Techinical training in India. This is an online event. Please use the link below to register.

Click Here to Register

Chennai SQL Server User Group Meeting

We held our first SQL Server User Group meeting successfully. The deatils of what happened on the event and the related presentations can be downloaded from the link below. Please feel free to touch base with me if you are interested to be part of this wonderful user group.

http://cssug.blogspot.com/2009/07/chennai-sql-server-user-group-meeting.html

Awarded MVP Again

I am Happy to say that I have been Awarded MVP for this year too. Adding the TAG MVP to my name had always distinguished me from other technologists near me. It has been a pleasant experience and happy to be on those same lines again for another year. And it's Thanking time again for those who have made a difference in my life and for those who have been with me during my tough days this year.

Krishna Gopalan - KG, I wish to be near you to share the joy this moment. I can still hear you saying "Sugesh, I know you are the right person to do this job", whenever I heard that gave me confidence to enrich my skills as a DBA helping me to achieve near expertise.

Stephen Seth, Vidhya Sagar and Deepak - It is with these guys that I grew up enriching my SQL Server skills. Without them, I would still be a DBA not a MVP.

I would like to thank my friends Dalavai Shanmugha Sundaram, Mr&Mrs. Stephen Seth, Mr&Mrs. Arun Kamaraj, Chakravarthi Elangovan and Poornima rangaswamy for being with me during this tough days of personal and professional career.

A special mention of thanks to Rangaswamy and Rani Thilagam Rangaswamy for shouldering my responsibilities in India when I stay in USA.

And my parents and sister, I have no words to thank them. Without thier support and encouragement I would not have got this again.

Creating System Objects in SQL Server

You can create an object in SQL Server and convert it to a system object just by marking the object as given below.

sp_MS_marksystemobject 'object_name'

Are You Certifiable???

"www.areyoucertifiable.com"

Drive Details using TSQL

SET NOCOUNT ON

DECLARE @res INT, 

@objdets INT,

@drive INT, 

@getdrive VARCHAR(13), 

@totalsize VARCHAR(50), 

@freespace VARCHAR(50), 

@DriveLetter char(1)

CREATE TABLE ##Drivespacedetails

(

DriveLetter CHAR(1),

TotalSpace bigint,

FreeSpace bigint,

)

INSERT INTO ##Drivespacedetails(DriveLetter,FreeSpace) EXEC master.dbo.xp_fixeddrives

DECLARE  drivedetails CURSOR FOR SELECT DriveLetter FROM ##Drivespacedetails

OPEN drivedetails

FETCH NEXT FROM drivedetails INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

SET @getdrive = 'GetDrive("' + @DriveLetter + '")' 

         EXEC @res = sp_OACreate 'Scripting.FileSystemObject', @objdets OUTPUT 

         IF @res = 0  

         EXEC @res = sp_OAMethod @objdets, @getdrive, @drive OUTPUT 

         IF @res = 0  

         EXEC @res = sp_OAGetProperty @drive,'TotalSize', @totalsize OUTPUT 

    IF @res = 0  

         EXEC @res = sp_OAGetProperty @drive,'FreeSpace', @freespace OUTPUT 

         IF @res <> 0  

         EXEC sp_OADestroy @drive 

EXEC sp_OADestroy @objdets 

SET @totalsize = (CONVERT(BIGINT,@totalsize) / 1048576 )

SET @freespace = (CONVERT(BIGINT,@freespace) / 1048576 )

     update ##Drivespacedetails set TotalSpace=@totalsize,FreeSpace=@freespace where DriveLetter=@DriveLetter

FETCH NEXT FROM drivedetails INTO @DriveLetter

END

CLOSE drivedetails

DEALLOCATE drivedetails

SELECT @@servername as ServerName,DriveLetter,FreeSpace AS [FreeSpace MB], (TotalSpace - FreeSpace) AS [UsedSpace MB], TotalSpace AS [TotalSpace MB], ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##Drivespacedetails ORDER BY [DriveLetter] ASC

DROP TABLE ##Drivespacedetails

Determine which objects exist in a particular filegroup

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id --* New FileGroup*

GO

How to find startup procedures in sql

SELECT ROUTINE_NAMEFROM MASTER.INFORMATION_SCHEMA.ROUTINESWHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION FROM SYS.CONFIGURATIONS WHERE NAME = 'scan for startup procs'GO

SQL Server 2008 SP1 released

SQL Server 2008 SP1 has been released to user community. Please find the download details given below.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

Drop Indexes in a Database

set nocount on

go

DECLARE @sql VARCHAR(2000),@DB VARCHAR(100),@count int,@TableName varchar(200),

@Index_Name varchar(200),@ConstraintName varchar(200)

SET @DB = db_name()

if exists(select * from msdb..sysobjects where name =

'DropIndexes_AllTables')

drop table msdb.dbo.DropIndexes_AllTables

CREATE TABLE msdb.dbo.DropIndexes_AllTables(tablename

varchar(200),ConstraintName varchar(200),Index_Name varchar(200),type

varchar(10))

SELECT @SQL = 'USE '+@db+char(13)+'SELECT object_name(parent_object_id)

TableName,sobj.Name ConstraintName,sidx.name Index_Name,type FROM sys.objects

sobj

join '+@db+'.dbo.sysindexes sidx on sobj.parent_object_id = sidx.id

where indid > 0 and type in

(''u'',''PK'',''UQ'') and

sidx.name not like ''_WA_sys_%'''

print @sql

insert into msdb.dbo.DropIndexes_AllTables

exec(@sql)

create table #temp_baby(ConstraintName varchar(200),TableName varchar(150))

select ConstraintName,TableName, Index_Name,Type from

msdb.dbo.DropIndexes_AllTables

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

select @count = count(*) from #temp_baby

while(@count > 0)

begin

select top 1 @ConstraintName = ConstraintName,@tablename = tablename from

#temp_baby

select @sql = 'USE '+@db+char(13)+'alter table '+@tablename+' drop

constraint '+@ConstraintName+''

print @sql

delete from #temp_baby where ConstraintName = @ConstraintName and tablename =

@tablename

select @count = count(*) from #temp_baby

end

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

print '------'

delete from msdb.dbo.DropIndexes_AllTables where Index_Name in (select

ConstraintName from #temp_baby)

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

while(@count > 0)

Begin

select top 1 @tablename = TableName,@Index_name = Index_Name from

msdb.dbo.DropIndexes_AllTables order by tablename

select @sql = 'USE '+@DB+';drop index

'+@tablename+'.'+@Index_name+''

print @sql

--print @tablename, @Index_name

delete from msdb.dbo.DropIndexes_AllTables where TableName = @tablename and

Index_Name = @Index_name

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

end

drop table #temp_baby

Change Data Capture

This article introduces to a new feature in SQL Server 2008, Change Data Capture. Change Data Capture records insert, update and delete activity in your table and the details of these changes are available in a format that can be easily consumed. The database must be enabled to Capture Changes to it. A member of sysadmin fixed role can enable the change. Once that is done, members of db_owner can enable tables for which change needs to be monitored. When the first table in the database is enabled, a capture process gathers changed data from the transaction log and inserts the change information in the associated change table. Additional metadata about each transaction is inserted in a metadata table that allows the captured changes to be placed on a conventional timeline.

Step to Enable and Configure Change Data Capture:
1. Create a database SQL Server 2008
create database CDC_TEST
go
2. Enable CDC in the database created.
USE SQL2008CDC
GO
EXEC sp_cdc_enable_db
GO
You can verify if CDC is enabled using the query below for the database.
select [name] as DBName,is_cdc_enabled from sys.databases where db_name(database_id)=DB_NAME()
3. You will be a new schema CDC and few system tables created under the database. Change Data Capture requires exclusive use of cdc user and schema.
4. Create a table to enable CDC
USE CDC_TEST
GO
CREATE TABLE dbo.CDC_TEST(
ID int Primary Key NOT NULL,
Name nvarchar(100) NOT NULL,
Email nvarchar(100) NOT NULL)
5. SQL Server agent must be started for CDC.
6. Enable CD for the table where you need to capture the change.
EXEC sp_cdc_enable_table 'dbo', 'cdc_test',
@role_name = NULL, @supports_net_changes =1
You can verify if CDC is enabled using the query below for the table.
use SQL2008CDC
go
select [name], is_tracked_by_cdc from sys.tables
7. Once CDC is enabled for a table, you will find a _CT table created by itself.
8. Now we will verify how CDC works by running a insert into the table CDC_TEST.
use SQL2008CDC
go
INSERT INTO dbo.cdc_test
values (1, N'sugeshkumar', N'sugeshkr@gmail.com')
9. Now update the row that we have inserted in the previous step.
use SQL2008CDC
go
update dbo.cdc_test set name='sugesh' where id = 1
10. The before image and after image of the row can be found on the table that was created with the name _CT.
The column _$operation has the value for the DML operation done, 1 is for delete, 2 for insert, 3 for before image, 4 for after image

You have seen how to enable and configure CDC for a database and table. The list below gives you all available Stored Procedures, DMVs, Functions and table list that is used by Change Data Capture.

Stored Procedures
sys.sp_cdc_add_job (Transact-SQL)
sys.sp_cdc_generate_wrapper_function (Transact-SQL)
sys.sp_cdc_change_job (Transact-SQL)
sys.sp_cdc_get_captured_columns (Transact-SQL)
sys.sp_cdc_cleanup_change_table (Transact-SQL)
sys.sp_cdc_get_ddl_history (Transact-SQL)
sys.sp_cdc_disable_db (Transact-SQL)
sys.sp_cdc_help_change_data_capture (Transact-SQL)
sys.sp_cdc_disable_table (Transact-SQL)
sys.sp_cdc_help_jobs (Transact-SQL)
sys.sp_cdc_drop_job (Transact-SQL)
sys.sp_cdc_scan (Transact-SQL)
sys.sp_cdc_enable_db (Transact-SQL)
sys.sp_cdc_start_job (Transact-SQL)
sys.sp_cdc_enable_table (Transact-SQL)
sys.sp_cdc_stop_job (Transact-SQL)

Dynamic Management Views
sys.dm_cdc_log_scan_sessions (Transact-SQL)
sys.dm_cdc_errors (Transact-SQL)
sys.dm_repl_traninfo (Transact-SQL)

Tables
cdc._CT
Returns one row for each change made to a captured column in the associated source table.

cdc.captured_columns
Returns one row for each column tracked in a capture instance.

cdc.change_tables
Returns one row for each change table in the database.

cdc.ddl_history
Returns one row for each data definition language (DDL) change made to tables that are enabled for change data capture.

cdc.lsn_time_mapping
Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.

cdc.index_columns
Returns one row for each index column associated with a change table.

dbo.cdc_jobs (Transact-SQL)
Returns the configuration parameters for change data capture agent jobs

Functions
cdc.fn_cdc_get_all_changes_ (Transact-SQL)
sys.fn_cdc_has_column_changed (Transact-SQL)
cdc.fn_cdc_get_net_changes_ (Transact-SQL)
sys.fn_cdc_increment_lsn (Transact-SQL)
sys.fn_cdc_decrement_lsn (Transact-SQL)
sys.fn_cdc_is_bit_set (Transact-SQL)
sys.fn_cdc_get_column_ordinal (Transact-SQL)
sys.fn_cdc_map_lsn_to_time (Transact-SQL)
sys.fn_cdc_get_max_lsn (Transact-SQL)
sys.fn_cdc_map_time_to_lsn (Transact-SQL)
sys.fn_cdc_get_min_lsn (Transact-SQL)

Generate Create Index Scripts for a Database

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID

DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '

SET @IXSQL = @IXSQL + @IxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
PRINT @IXSQL

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX


-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ORDER BY TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY CLUSTERED ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ', '

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK

Common SQL Server Security Issues and Solutions

This article is from Paul S Randal on securing SQL Server. This is a nice article on how to secure SQL Server. 

http://technet.microsoft.com/en-us/magazine/2009.05.sql.aspx 

Show/Hide divs and swap images with SkinID using Javascript

The following example shows 2 ways of using javascript to show/hide divs (or any elements) on the click event of an image and also to swap the image that uses a skin id. Since skins are applied on the server side, you cannot change SkinID on client side using javascript. Though this is a crude way of doing it, it worked for developing my website since this functionality is used on only one page. I am sure there are more better and easier ways to do it, but these examples can be used in a simple website that uses asp.net or HTML.

Example 1: To Show only one div at a time.

ASP.Net Code

<div id="divContent">

<h2><a href="#" id="lnk1" onclick="return toggle('div1')">What is Test?</a></h2>
<div id="div1" style="display:none">
   <p>Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test</p>
   <p>Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
   Test Test Test Test Test Test Test Test Test Test Test Test Test Test</p>
</div>

<h2><a href="#" id="lnk8" onclick="return toggle('div8')">What is Sample?</a></h2>
<div id="div8" style="display:none">
   <p>Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
   Sample Sample Sample Sample Sample Sample Sample Sample Sample </p>
</div>
</div>

JavaScript Code:

function toggle(divID)
{
   var divToShow = document.getElementById(divID); 
   var divUL = document.getElementById('dvfaq');
 var divs = divUL.getElementsByTagName('DIV');
 for(var i = 0; i < divs.length; i++)
   {
     divs[i].style.display = 'none';
     if(divToShow.style.display == 'none')
     {
      divToShow.style.display = 'block';
     }
   }
   return false;
}

Example 2: To Show multiple divs at a time and click to open and close.

ASP.Net Code

<a id="lnk0" href="#" onclick="return toggle('div0', 'lnk0')">
Q: What is Test? <asp:Image ID="imgToggle" SkinID="ArrowClose"
style="display: inline" runat="server" /><asp:Image ID="Image27"
SkinID="ArrowOpen" style="display: none" runat="server" /></a>
<br />

<div id="div0" style="display:none">
<p>A: Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test Test Test Test Test Test Test Test Test Test
Test Test Test Test Test Test </p>
</div>

<a id="lnk1" href="#" onclick="return toggle('div1', 'lnk1')">
Q: What is Sample? <asp:Image ID="Image1" SkinID="ArrowClose"
style="display: inline" runat="server" /><asp:Image ID="Image28"
SkinID="ArrowOpen" style="display: none" runat="server" /></a>
<br />

<div id="div1" style="display:none">
<p> Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample Sample Sample Sample
Sample Sample Sample Sample </p>
</div>

JavaScript Code:

function toggle(divID, imgID)
{
var divToShow = document.getElementById(divID);
if(divToShow.style.display == 'none')
{
  divToShow.style.display = 'block';
}
else if(divToShow.style.display == 'block')
{
  divToShow.style.display = 'none';
}

var imgToShow = document.getElementById(imgID).getElementsByTagName('img')[0];
var imgToHide = document.getElementById(imgID).getElementsByTagName('img')[1];

if(imgToShow.style.display == 'none')
{   
imgToShow.style.display = 'inline';
imgToHide.style.display = 'none';
}
else if(imgToShow.style.display == 'inline')
{   
imgToShow.style.display = 'none';   
imgToHide.style.display = 'inline';
}
return false;
}

Skin Code:

<asp:Image runat="server" SkinID="ArrowClose" ImageUrl="images/arrowClose.gif"
width="9" height="9" />

<asp:Image runat="server" SkinID="ArrowOpen" ImageUrl="images/arrowOpen.gif"
width="9" height="9" />

Migrating Access Database to SQL Server

I recently received a call from one of my friend asking me, what is the easiest way to migrate a database from ACCESS to SQL Server? I was curious on answering his question and just went for a search in internet to find it was much simple than what I expected. This article describes the steps to migrate a Access database to SQL Server.

To Migrate Access to SQL Server database, download the tool SQL Server Migration Assistant for Access

Install SQL Server Migration Assistant for Access on local computer

You need to have the access database closed while migrating to SQL Server.

Goto Start->;Programs->;Microsoft SQL Server Migration Assistant 2008 for Access->;Microsoft SQL Server Migration Assistant 2008 for Access from the computer where it's installed. You will see the welcome screen.

Click Next, Create a new project by entering the Name of the project and it's location. Click Next, You will be taken to a screen to add the Access database. Click on Add Databases button to specify the folder where your access database resides.
Click Next, Here you will have option to migrate only data and/or the queries too. Here, we are migrating both data and the queries.

Click Next, You will see the SQL Server connection Screen. Here specify the SQL Server instance where you want to move your Access database along with the destination Database name and credentials.

Click Next to Link tables. If you want to use your existing Access applications with SQL Server, you can link your original Access tables to the migrated SQL Server tables. Linking modifies your Access database so that your queries, forms, reports, and data access pages use the data in the SQL Server database instead of the data in your Access database.

Click Next to start Migration.

You will see the objects and queries to be migrated. Here If there are any errors or discrepencies will also notified.
Click Ok to complete migration.

Now, you can open your SQL Server database to see that the Access tables and Queries are migrated. The Queries will be migrated as Views and you can use them as you did in the Access tables. The migration task is simple than expected. Now the next task that you will learn from me on this is HOW TO MIGRATE ACCESS REPORTS TO SQL SERVER?

Database Size Info

declare @dbname sysname

set nocount on

if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))

begin

raiserror('Incorrect database name. ', 16, 1)

end

create table #datafilestats

(    dbname varchar(25),

    flag bit default 0,

    Fileid tinyint,

    [FileGroup] tinyint,

    TotalExtents dec (8, 1),

    UsedExtents dec (8, 1),

    [Name] varchar(50),

    [FileName] sysname )

declare @string sysname, @dbname1 sysname

set @string = ''

if @dbname is not null

    set @dbname1 = @dbname

else

    set @dbname1 = ''

set @dbname = ''

while 1=1

    begin

     select top 1 @dbname = name from master..sysdatabases where name > @dbname --order by name asc     

     if @@rowcount = 0

     break

     set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'

    insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName]) exec (@string)

update #datafilestats set dbname = @dbname, flag = 1 where flag = 0

    update #datafilestats set TotalExtents = (select sum(TotalExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

        where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

    update #datafilestats set UsedExtents = (select sum(UsedExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

        where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

    end

create table #sizeinfo 

( db_name varchar(30) not null primary key clustered,

total dec (7, 1),

data dec (7, 1),

data_used dec (7, 1),

[data (%)] dec (7, 1),

data_free dec (7, 1),

[data_free (%)] dec (7, 1),

log dec (7, 1),

log_used dec (7, 1),

[log (%)] dec (7, 1),

log_free dec (7, 1),

[log_free (%)] dec (7, 1),

status dec (7, 1) )    

insert #sizeinfo (db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')

update #sizeinfo set

data = d.TotalExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

data_used = d.UsedExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

total = (data + log)

update #sizeinfo set

    [data (%)] = (data_used * 100.0 / data)

update #sizeinfo set

     data_free = (data - data_used)

update #sizeinfo set

     [data_free (%)] = (100 - [data (%)])

update #sizeinfo set

    log_used = (log * [log (%)] / 100.0)

update #sizeinfo set

    log_free = (log - log_used)

update #sizeinfo set

    [log_free (%)] = (log_free * 100.0 / log)

print ''

if @dbname1 = ''

begin

print 'Database size report on ' + @@servername + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

    select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo order by db_name asc compute sum(total)    

end

else

begin

print 'Database size report on ' + @@servername + '.' + @dbname1 + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

    select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo where db_name = @dbname1    

end

go

drop table #datafilestats 

drop table #sizeinfo

Job Schedule Information for SQL Server 2005

SELECT

   @@servername as [Server], msdb.dbo.sysjobs.name as [Name]

  , CASE

       WHEN msdb.dbo.sysjobs.enabled = 0 THEN 'Disabled'

       WHEN msdb.dbo.sysjobs.job_id IS NULL THEN 'Unscheduled'

       WHEN msdb.dbo.sysschedules.freq_type = 0x1 -- OneTime

           THEN

               'Once on '

             + CONVERT(

                          CHAR(10)

                        , CAST( CAST( msdb.dbo.sysschedules.active_start_date AS VARCHAR ) AS DATETIME )

                        , 102 -- yyyy.mm.dd

                      )

       WHEN msdb.dbo.sysschedules.freq_type = 0x4 -- Daily

           THEN 'Daily'

       WHEN msdb.dbo.sysschedules.freq_type = 0x8 -- weekly

           THEN

               CASE

                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1

                       THEN 'Weekly on '

                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1

                       THEN 'Every '

                          + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )

                          + ' weeks on '

               END

             + LEFT(

                         CASE WHEN msdb.dbo.sysschedules.freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END

                       + CASE WHEN msdb.dbo.sysschedules.freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END

                       + CASE WHEN msdb.dbo.sysschedules.freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END

                       + CASE WHEN msdb.dbo.sysschedules.freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END

                       + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END

                       + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END

                       + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END

                     , LEN(

                                CASE WHEN msdb.dbo.sysschedules.freq_interval &  1 =  1 THEN 'Sunday, '    ELSE '' END

                              + CASE WHEN msdb.dbo.sysschedules.freq_interval &  2 =  2 THEN 'Monday, '    ELSE '' END

                              + CASE WHEN msdb.dbo.sysschedules.freq_interval &  4 =  4 THEN 'Tuesday, '   ELSE '' END

                              + CASE WHEN msdb.dbo.sysschedules.freq_interval &  8 =  8 THEN 'Wednesday, ' ELSE '' END

                              + CASE WHEN msdb.dbo.sysschedules.freq_interval & 16 = 16 THEN 'Thursday, '  ELSE '' END

                              + CASE WHEN msdb.dbo.sysschedules.freq_interval & 32 = 32 THEN 'Friday, '    ELSE '' END

                              + CASE WHEN msdb.dbo.sysschedules.freq_interval & 64 = 64 THEN 'Saturday, '  ELSE '' END

                          ) - 1  -- LEN() ignores trailing spaces

                   )

       WHEN msdb.dbo.sysschedules.freq_type = 0x10 -- monthly

           THEN

               CASE

                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1

                       THEN 'Monthly on the '

                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1

                       THEN 'Every '

                          + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )

                          + ' months on the '

               END

             + CAST( msdb.dbo.sysschedules.freq_interval AS VARCHAR )

             + CASE

                   WHEN msdb.dbo.sysschedules.freq_interval IN ( 1, 21, 31 ) THEN 'st'

                   WHEN msdb.dbo.sysschedules.freq_interval IN ( 2, 22     ) THEN 'nd'

                   WHEN msdb.dbo.sysschedules.freq_interval IN ( 3, 23     ) THEN 'rd'

                   ELSE 'th'

               END

       WHEN msdb.dbo.sysschedules.freq_type = 0x20 -- monthly relative

           THEN

               CASE

                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor = 1

                       THEN 'Monthly on the '

                   WHEN msdb.dbo.sysschedules.freq_recurrence_factor > 1

                       THEN 'Every '

                          + CAST( msdb.dbo.sysschedules.freq_recurrence_factor AS VARCHAR )

                          + ' months on the '

               END

             + CASE msdb.dbo.sysschedules.freq_relative_interval

                   WHEN 0x01 THEN 'first '

                   WHEN 0x02 THEN 'second '

                   WHEN 0x04 THEN 'third '

                   WHEN 0x08 THEN 'fourth '

                   WHEN 0x10 THEN 'last '

               END

             + CASE msdb.dbo.sysschedules.freq_interval

                   WHEN  1 THEN 'Sunday'

                   WHEN  2 THEN 'Monday'

                   WHEN  3 THEN 'Tuesday'

                   WHEN  4 THEN 'Wednesday'

                   WHEN  5 THEN 'Thursday'

                   WHEN  6 THEN 'Friday'

                   WHEN  7 THEN 'Saturday'

                   WHEN  8 THEN 'day'

                   WHEN  9 THEN 'week day'

                   WHEN 10 THEN 'weekend day'

               END

       WHEN msdb.dbo.sysschedules.freq_type = 0x40

           THEN 'Automatically starts when SQLServerAgent starts.'

       WHEN msdb.dbo.sysschedules.freq_type = 0x80

           THEN 'Starts whenever the CPUs become idle'

       ELSE ''

   END

 + CASE

       WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''

       WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''

       WHEN msdb.dbo.sysschedules.freq_subday_type = 0x1 OR msdb.dbo.sysschedules.freq_type = 0x1

           THEN ' at '

             + CASE

                   WHEN msdb.dbo.sysschedules.active_start_time =      0 THEN '12:00'

                   WHEN msdb.dbo.sysschedules.active_start_time = 120000 THEN '12:00'

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 3 )

                                   , 2

                                   , 0

                                   , ':'

                                 )

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 4 )

                                   , 3

                                   , 0

                                   , ':'

                                 )

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 3 )

                                   , 2

                                   , 0

                                   , ':'

                                 )

                   ELSE STUFF(

                                 LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 4 )

                               , 3

                               , 0

                               , ':'

                             )

               END

             + CASE

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                   ELSE ' PM'

               END

       WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )

           THEN ' every '

             + CAST( msdb.dbo.sysschedules.freq_subday_interval AS VARCHAR )

             + CASE freq_subday_type

                   WHEN 0x2 THEN ' second'

                   WHEN 0x4 THEN ' minute'

                   WHEN 0x8 THEN ' hour'

               END

             + CASE

                   WHEN msdb.dbo.sysschedules.freq_subday_interval > 1 THEN 's'

               END

       ELSE ''

   END

 + CASE

       WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''

       WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''

       WHEN msdb.dbo.sysschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )

           THEN ' between '

             + CASE

                   WHEN msdb.dbo.sysschedules.active_start_time =      0 THEN '12:00'

                   WHEN msdb.dbo.sysschedules.active_start_time = 120000 THEN '12:00'

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 3 )

                                   , 2

                                   , 0

                                   , ':'

                                 )

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time AS VARCHAR ), 4 )

                                   , 3

                                   , 0

                                   , ':'

                                 )

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 3 )

                                   , 2

                                   , 0

                                   , ':'

                                 )

                   ELSE STUFF(

                                 LEFT( CAST ( msdb.dbo.sysschedules.active_start_time - 120000 AS VARCHAR ), 4 )

                               , 3

                               , 0

                               , ':'

                             )

               END

             + CASE

                   WHEN msdb.dbo.sysschedules.active_start_time <>

                   ELSE ' PM'

               END

             + ' and '

             + CASE

                   WHEN msdb.dbo.sysschedules.active_end_time =      0 THEN '12:00'

                   WHEN msdb.dbo.sysschedules.active_end_time = 120000 THEN '12:00'

                   WHEN msdb.dbo.sysschedules.active_end_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_end_time AS VARCHAR ), 3 )

                                   , 2

                                   , 0

                                   , ':'

                                 )

                   WHEN msdb.dbo.sysschedules.active_end_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_end_time AS VARCHAR ), 4 )

                                   , 3

                                   , 0

                                   , ':'

                                 )

                   WHEN msdb.dbo.sysschedules.active_end_time <>

                       THEN STUFF(

                                     LEFT( CAST ( msdb.dbo.sysschedules.active_end_time - 120000 AS VARCHAR ), 3 )

                                   , 2

                                   , 0

                                   , ':'

                                 )

                   ELSE STUFF(

                                 LEFT( CAST ( msdb.dbo.sysschedules.active_end_time - 120000 AS VARCHAR ), 4 )

                               , 3

                               , 0

                               , ':'

                             )

               END

             + CASE

                   WHEN msdb.dbo.sysschedules.active_end_time <>

                   ELSE ' PM'

               END

       ELSE ''

   END AS Schedule

FROM         msdb.dbo.sysjobs INNER JOIN

                      msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id INNER JOIN

                      msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id