Debugging in SQL Server

In SQL Server 2008, we can debug the procedure step by step. Place a break point by clicking on the left hand side of your query window. A dot will come, which indicates the control will stop at this point. From there, we are allowed to debug the procedure.


We have been provided with quick watch/Add watch options to monitor the value of a variable in the procedure.



Steps for debugging:


Press Alt+f5 or goto Debug option and click "Start debugging". If you are familiar with Visual studio then its too handy to debug in SSMS. F11 to go each and every step inside the function. Where as F10 will jump to the next line control.

How about in SQL Server 2005. Do we have any option to debug a procedure in SQL Server 2005?

SSMS doesn't have an option to debug the procedure but this can be achieved using Visual studio.

Goto Tools -> Connect to Database -> You will get Data connections -> right click it and select Add Connection -> Provide your server, appropriate database and credentials to connect.

Your database will be displayed. Goto ->Stored Procedure and select your stored proc.

Right click -> Step into Stored Procedure -> Your debug window will open.

Provide necessary inputs and you can start debugging your stored procedure

You can see your procedure running in the debug mode.

Cheers,

Venkatesan Prabu .J

Finding the length of the string without STRLEN function

Finding the length of the string without STRLEN function:

I have seen a request in one of the forum on String Lenght function. Finding the string length with out STRLEN built-in function. Nice question right !!!

Solution:
Here is the solution,

1. First -> Replace all the empty character in the string to * or any other special character.
2. Second -> Check for each letter in the string for empty until it reaches the end.
3. Third -> The string will have an empty character in the end(By default)
4. Fourth -> Get the Count of the string.

**********************************************************************************
ALTER PROCEDURE dbo.LEN_WITHOUT_STRLEN_FUNCTION(@String VARCHAR(8000))
AS
BEGIN
DECLARE @CNT INT, @IDX INT, @Flag INT
SELECT @String = REPLACE(@String, ' ','*'), @CNT = 0, @IDX = 1, @Flag = 1
WHILE ( @Flag = 1 )
BEGIN
IF ((SELECT SUBSTRING(@String,@IDX,1)) <> '')
BEGIN
SET @CNT = @CNT + 1
END
ELSE
BEGIN
SET @Flag = 0
END
SET @IDX = @IDX + 1
END
PRINT @CNT
END

**********************************************************************************

EXEC LEN_WITHOUT_STRLEN_FUNCTION 'VENKATESAN PRABU'

**********************************************************************************

Cheers,
Venkatesan Prabu .J

Converting Varchar to Float in SQL Server

NVARCHAR OR VARCHAR TO FLOAT IN SQL SERVER:

For converting the data from one datatype to another, you need to use CONVERT function.

The syntax is CONVERT(DATATYPE,COLUMN NAME)

SAMPLE QUERY:

CREATE TABLE VENKAT_TAB (ID INT, VAL nVARCHAR(100))

INSERT INTO VENKAT_TAB VALUES (1,'10.453425345')

SELECT ID, CONVERT(FLOAT,VAL) AS FLOATVAL FROM VENKAT_TAB

Cheers,
Venkatesan Prabu. J

Cascade delete option in SQL Server - Deleting multiple table records

CASCADE DELETE OPTION IN SQL SERVER:

I have seen lot of SQL developers were struggling to delete multiple tables simultaneously.

Scenario: I have deleted the master table and I would like delete those records in the child tables. Is it possible to delete multiple tables at the same time. SQL Server doesn't have an option to delete multiple tables at the same time. As a work around, there are some other options we can do. Below are the options,

1. Place the delete statements inside a transaction. Delete the child table first followed by master table and afterwards commit the transaction.

2. Create a trigger on the master table which will get invoked on delete operation. It will automatically delete the child tables data.

3. Third option is, Cascade delete option. This will be set during the Foreign key relation creation stage.

Below is a detailed article on Option 3

Primary Key : A column which helps us to uniquely identify a record in the table.

Foreign Key : A column which is referring a primary key on the other table and holds reference of a remote table record.

-- Am creating a table with ID as primary key.

This table holds primary key on the column ID.

CREATE TABLE [dbo].[VENKAT_TABLE](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[NAME1] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_VENKAT_TABLE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

-------------------------------------------------------------------------

--Creating second table with ID column in the previous table as reference

This table mapped to a foreign key which is a primary key in another table with option Cascade delete.

CREATE TABLE [dbo].[VENKAT_TABLE1](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_VENKAT_TABLE1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER TABLE [dbo].[VENKAT_TABLE1] WITH CHECK ADD CONSTRAINT [FK_VENKAT_TABLE1_VENKAT_TABLE] FOREIGN KEY([ID])
REFERENCES [dbo].[VENKAT_TABLE] ([ID])

ON DELETE CASCADE

-------------------------------------------------------------------------

-- Create third table with ID in the Frist table as reference

This table mapped to a foreign key which is a primary key in another table with option Cascade delete.

GO
CREATE TABLE [dbo].[VENKAT_TABLE2](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER TABLE [dbo].[VENKAT_TABLE2] WITH CHECK ADD CONSTRAINT [FK_VENKAT_TABLE2_VENKAT_TABLE] FOREIGN KEY([ID])
REFERENCES [dbo].[VENKAT_TABLE] ([ID])
ON DELETE CASCADE

-------------------------------------------------------------------------

OOPS, in the third SQL Statement, I got the below error. Why?

"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"

Reason: Third table has some data whereas First table doesn't have any data. So, we can't create any reference at this stage. I have deleted all the records and recreated the relation ship. Yes, am able to create it.

----------------------------------------------------------------------------

INSERT INTO VENKAT_TABLE VALUES(1,'VENKAT','JAYAKANTHAM')
INSERT INTO VENKAT_TABLE VALUES(2,'SUBA','SUNDARESAN')
INSERT INTO VENKAT_TABLE VALUES(3,'KRISHIV','VENKAT')

INSERT INTO VENKAT_TABLE1 VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE1 VALUES(2,'SUBA')
INSERT INTO VENKAT_TABLE1 VALUES(3,'KRISHIV')

INSERT INTO VENKAT_TABLE2 VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE2 VALUES(2,'SUBA')
INSERT INTO VENKAT_TABLE2 VALUES(3,'KRISHIV')

--- JOINING THE TABLE USING THEIR RELATIONSHIPS --------------------
SELECT * FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE1 B ON A.ID=B.ID
INNER JOIN VENKAT_TABLE2 C ON B.ID=C.ID

------------ DELETE MASTER TABLE WILL DELETE THE OTHER TABLE DUE TO CASCADE DELETE OPTION IN FOREIGN KEY CONSTRAINT CREATION.------------

DELETE VENKAT_TABLE
FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE1 B ON A.ID=B.ID
INNER JOIN VENKAT_TABLE2 C ON B.ID=C.ID


select * from VENKAT_TABLE -- Will provide empty records
select * from VENKAT_TABLE1 -- Will provide empty records
select * from VENKAT_TABLE2 -- Will provide empty records

----------------------------------------------------------------------------
Similarly, we can do this for update operations too. Updating the master table will get reflected in the child table.

Cheers,
Venkatesan Prabu .J

Order by in the Varchar column

I have seen a request in one of the popular forum to order by a varchar column (Only on the integers and not on the varchar data).

Below is the sequence of queries to do an order by on the varchar column.

-- Drop the table, If we have anything in the table

DROP TABLE VENKAT_TABLE

-- Create a table named VENKAT_TABLE

CREATE TABLE VENKAT_TABLE (ID VARCHAR(10))
INSERT INTO VENKAT_TABLE VALUES('a')
INSERT INTO VENKAT_TABLE VALUES('d')
INSERT INTO VENKAT_TABLE VALUES('e')
INSERT INTO VENKAT_TABLE VALUES('y')
INSERT INTO VENKAT_TABLE VALUES('1')
INSERT INTO VENKAT_TABLE VALUES('2')
INSERT INTO VENKAT_TABLE VALUES('3')
INSERT INTO VENKAT_TABLE VALUES('4')
INSERT INTO VENKAT_TABLE VALUES('5')
INSERT INTO VENKAT_TABLE VALUES('9')

--Below select statement will get all the records from the table.

SELECT * FROM VENKAT_TABLE

--Below select statement will get all the records from the table with order by on the column.

SELECT * FROM VENKAT_TABLE
ORDER BY ID DESC

--Below select statement will get all the records from the table with order by on the column.

SELECT * FROM VENKAT_TABLE
ORDER BY ISNUMERIC(ID) ASC

Cheers,

Venkatesan Prabu .J

Finding the n th Highest value in SQL Server

Finding the n th Highest value in SQL Server:

In most of the SQL interviews, you will face this question frequently. So, I would like to write an article on this.

Scenario: Find the 6th highest ID in a table


Possible Solutions:

Am creating a table variable @VenkatTable,

declare @VenkatTable table(ID int)
Insert into @VenkatTable values(100)
Insert into @VenkatTable values(200)
Insert into @VenkatTable values(1400)
Insert into @VenkatTable values(2500)
Insert into @VenkatTable values(300)
Insert into @VenkatTable values(900)
Insert into @VenkatTable values(906)
Insert into @VenkatTable values(30000)
Insert into @VenkatTable values(12300)
Insert into @VenkatTable values(20000)

--USING GROUP BY STATEMENT:

First Option is by using group by operator. I have used SelfJoin to and fetch the count of records received and the count is equal to 6.

SELECT t1.ID FROM @VenkatTable t1 INNER JOIN @VenkatTable t2 ON t1.ID<=t2.ID GROUP BY t1.ID HAVING COUNT(t1.ID)=6

--USING TOP STATEMENT:

Second option is to use the Top operator. Get the Top 6 by desc and Fetch the first one by desc.

SELECT TOP 1 ID FROM ( SELECT TOP 6 ID FROM @VenkatTable ORDER BY ID DESC ) T ORDER BY ID ASC

--USING ROW_NUMBER

In SQL Server 2005, you can use row_number to fetch the record.

SELECT ID FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) T ,* FROM @VenkatTable)TEMP WHERE TEMP.T=6

--USING RANK

As an alternative to the row_number, we are having rank function to fetch the record.

SELECT ID FROM ( SELECT RANK() OVER (ORDER BY ID DESC) R,* FROM @VenkatTable)TEMP WHERE TEMP.R=6

-- USING DENSERANK

If the values are too close or same. In that case, we can opt DenseRank function.

SELECT ID FROM ( SELECT DENSE_RANK() OVER (ORDER BY ID DESC) R,* FROM @VenkatTable)TEMP WHERE TEMP.R=6

-- USING WHERE CONDITION

A normal select statement in a correlated query will provide you the output.

SELECT ID FROM @VenkatTable as V WHERE (SELECT COUNT (*) FROM @VenkatTable where ID>=v.ID)=6

Cheers,

Venkatesan Prabu .J

Generate XSD from XML

I got an opportunity to read XML through SSIS. While reading the xml process, I have to create XSD. OOPs, I don't have an option to achieve the same. Even, SSIS holds a button to generate XSD. Unfortunately, this is not working for me.

Searched in Google and found the option of creating XSD. Microsoft is providing xsd tool to convert XML to XSD.

1. Once you have Visual studio installed, search for XSD.exe
2. Start ->Run -> goto the XSD path and execute the command > XSD xml_filename
3. You XSD will be created in the Command prompt specified path.

----------------------------------------------------------------------------------
XML:

XML to XSD using Command prompt:

XSD Created:

Cheers,
Venkatesan Prabu .J

Unpivot in SQL Server 2005

DROP TABLE VENKAT_SAMPLE
CREATE TABLE VENKAT_SAMPLE(PART1 VARCHAR(100),PART2 VARCHAR(100),
PART3 VARCHAR(100),PART4 VARCHAR(100),PART5 VARCHAR(100),[DATE] DATETIME)
INSERT INTO VENKAT_SAMPLE VALUES('XYZ','ABC','PQR','EFG','PQR','11/04/2010')
INSERT INTO VENKAT_SAMPLE VALUES('','XYZ','PQR','ABC','','12/04/2010')
INSERT INTO VENKAT_SAMPLE VALUES('ABC','PQR','','XYZ','EFG','4/13/2010')
INSERT INTO VENKAT_SAMPLE VALUES('ABC','PQR','','XYZ','EFG','4/14/2010')

SELECT * FROM VENKAT_SAMPLE;

WITH VENKAT_CTE AS
(
SELECT [DATE], PART,Orders
FROM
(SELECT [DATE], PART1,PART2,PART3,PART4,PART5
FROM VENKAT_SAMPLE ) p
UNPIVOT
(Orders FOR PART IN
(PART1,PART2,PART3,PART4,PART5)
)AS unpvt
)
SELECT COUNT(1) AS COUNT,ORDERS FROM VENKAT_CTE
WHERE [DATE] >= '6/14/2010' GROUP BY ORDERS

Cheers,

Venkatesan Prabu .J

Data from one database to another database

Copy data from One database to Another database:

There are various options to copy data from one database to another database.

1. First Option -> We will think about is - Using DTS wizard.
2. Second Option -> We can go for SSIS packages.
3. Thirst Option -> A very simple query as below,

Table and data created in VenkatDB will be moved to SubaDB

USE VENKATDB
CREATE TABLE VENKAT_TABLE(ID INT,NAME VARCHAR(100))
INSERT INTO VENKAT_TABLE VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE VALUES(2,'SUBA')
SELECT * FROM VENKAT_TABLE

USE SubaDB
SELECT * INTO VENKAT_TABLE1 FROM VenkatDB.dbo.VENKAT_TABLE

A new table venkat_table1 will be created in te second DB with all the records populated in the second table in the second DB.

Cheers,
Venkatesan Prabu .J

Defaults in sql server

Default:
Default is one of the nice concept in SQL Server. It enables us to assign a default value to the table. While inserting a record in the table, if we didnt specify any values to a particular column the data will be null (If the column is a nullable column). If there is any default value for this column, it will assigned to that column.

Scenario:

I am having a scenario like, Instead of a null value, empty( '') should be populated. Is it possible?

Script:

DROP TABLE VENKATTABLE
CREATE TABLE VENKATTABLE(ID INT, VAL VARCHAR(100))
ALTER TABLE VENKATTABLE
ADD CONSTRAINT VENKATDEFAULT_VAL
DEFAULT '' FOR VAL
INSERT INTO VENKATTABLE (ID) VALUES (1)
SELECT * FROM VENKATTABLE

Instead of altering the table, we can provide the default value in the table creation itself.


DROP TABLE VENKATTABLE
CREATE TABLE VENKATTABLE(ID INT, VAL VARCHAR(100) DEFAULT '')

Cheers,
Venkatesan Prabu .J

Disallowed implicit conversion from data type varchar to data type binary

Disallowed implicit conversion from data type varchar to data type binary, table name....

The above error message will be popped up while trying to store the binary object into a varchar column. For example,

CREATE TABLE mytest (ImageName varchar(100), ImagePath varchar(500))

In the above created table, If we try to push the image it will throw an error in converting the datatype. Because, the above wont do implicit conversion. The column type should be image datatype.

CREATE TABLE mytest (ImageName varchar(100), ImagePath image null)

Cheers,
Venkatesan Prabu .J

Service packs in SQL Server 2005

Microsoft have release three service packs with SQL Server 2005.

1. SP1 (SQL Server Service pack1)
2. SP2 (SQL Server Service pack2)
3. SP3 (SQL Server Service pack3)

You can get these service packs from the below link
http://support.microsoft.com/kb/913089

Cheers,
Venkatesan Prabu .J

Compare two table data using Like Operator - Avoided cursors

Compare two table data using Like Operator:

Scenario: I am having two tables and I need to compare the table data
This one is very very interesting topic, Usually we used to go for cursors to loop through the table data to identify the matching data.

Planned step for this scenario is,
Open a cursor or some loop -> get the data and put it in a dynamic sql -> use like
operator for each row and get the output.

OOPS, I got some different solution/fantastic solution for this problem. We can use inner join to achieve it.

Let's see, how can we achieve it?

drop table VenkatCity
drop table VenkatMainCity
create table VenkatCity(cities varchar(100))
insert into VenkatCity values('Sydney,Melbourne')
insert into VenkatCity values('Chennai,Delhi')
insert into VenkatCity values('Goa,Bombay')
create table VenkatMainCity(cities varchar(100))
insert into VenkatMainCity values('Sydney')
insert into VenkatMainCity values('Chennai')

select * from VenkatCity a
inner join VenkatMainCity b on a.cities like '%'+b.cities+'%'

The above query will take care of matching the records and obtain the desired output.

Cheers,
Venkatesan Prabu .J

Delete data permanently in SQL Server

Delete data permanently in SQL Server:

Scenario: I want to delete records from my table.

Query:
drop table venkattable
create table venkattable(id int identity, program_id varchar(10), subprogram_id varchar(10))
insert into venkattable(program_id,subprogram_id) values('a','a1')
insert into venkattable(program_id,subprogram_id) values('a','a2')
insert into venkattable(program_id,subprogram_id) values('a','a3')

Option 1:

delete from venkattable
-- The above statement will delete the record but maintain the identity value.
insert into venkattable(program_id,subprogram_id) values('a','a3')
select * from venkattable

Above statement will insert a record with id 4. The data is removed but the identity value retains.

To resolve this problem we can use reseed option.

DBCC CHECKIDENT (venkattable, RESEED, 0)

Option 2:

We can go for truncate option.
TRUNCATE TABLE venkattable

Cheers,
Venkatesan Prabu .J

Get Last updated record in a table group by a specific column name

Scenario: I want to fetch a last/latest record in a group.

Creating and inserting data into the table:

create table venkattable(id int identity, program_id varchar(10), subprogram_id varchar(10))

insert into venkattable(program_id,subprogram_id) values('a','a1')
insert into venkattable(program_id,subprogram_id) values('a','a2')
insert into venkattable(program_id,subprogram_id) values('a','a3')
insert into venkattable(program_id,subprogram_id) values('b','b1')
select * from venkattable

Option 1:

with cte
as
(
select max(id) as id from venkattable group by program_id
)
select * from venkattable where id in (select id from cte)

Option 2:

select a.id, a.program_id,a.subprogram_id from venkattable a inner join
(
select max(id) as id from venkattable group by program_id
) b
on a.id=b.id

Cheers,
Venkatesan Prabu .J

Cursors in SQL Server

Cursor in SQL Server:


Cursor is one of an important topic in SQL Server. This will enable us to loop through the result set.


Considering a scenario like, I need to loop through a table and based on the value from the table, I need to do some operation in the second table. In that case, we will go for cursors. Although, cursors are heavy weight objects and it is used in ages from sql server 7.0 version (may be before that too. Am not sure).


Let's go the implementation phase,


Am creating two tables one secondary table and the other is a reference table(Source table)


create table venkat (id int, id1 int)
create table venkatSecondTable (id int)
insert into venkat values(1,2)


The syntax is like,


1. Declare the cursor
2. Open the cursor.
3. Fetch the first record and store it into the cursor.
4. Do the operation until your cursor fetch status is empty.
5. Fetch the next record(A loop)
6. Close the cursor
7. Deallocate the cursor.


Let's see a small example query to check the working functionality of a cursor. Am creating a procedure venkatproc and trying into the insert the record into the secondary table by looping through the first table.

create procedure venkatproc
as
begin
declare @val int
declare cur cursor for select ID from venkat
open cur
fetch next from cur into @val
while @@FETCH_STATUS =0
begin
insert into venkatSecondTable(id) select id1 from venkat where id=@val
fetch next from cur into @val
end
close cur
deallocate cur
end


Most important points:


1. Cursor needs to be opened before using it.
2. We need to monitor memory leakages. If we didnt properly close or deallocate the cursor. It will cause serious memory issues(Holding the memory)
3. As a well known info, cursor is a heavy weight object. Instead we can opt while loop or cte's in sql server 2005.


Cheers,

Venkatesan Prabu. J

Insert records from other table using insert statement

Insert records from other table using insert statement:

Scenario: I need to insert records into the table after fetching records from other table.

Query:

-- Creating the table and inserting records.
create table venkat (id int, id1 int)
create table venkatSecondTable (id int, id1 int)
insert into venkat values(1,2)
-- Check whether data is there
insert into venkatSecondTable select ID,id1 from venkat
select * from venkatSecondTable

Cheers,

Venkatesan Prabu .J

Swapping column data in SQL Server

Swapping the column data between adjacent columns:

Scenario: I need to swap the column data in a table like column1 data to be moved to column2 and column2 data needs to be moved to column1
How can we achieve it?

Queries:

-- Creating the table and inserting records.
create table venkat (id int, id1 int)
insert into venkat values(1,2)
-- Check whether data is there
select * from venkat
-- Swapping the data
update venkat set id=id1,id1=id

In the above case, SQL Server will hold the data in memory until commit operation is commenced.
So, there is no need to hold the data in the third variable for swapping.

Cheers,
Venkatesan Prabu .J

ExceptionTemplates failed in SQL Server

Error message in SQL Server:

Sometimes, If we try to restore a database backup. We will get the below error,

Restore failed for Server 'SQLDB'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------
The backup set holds a backup of a database other than the existing 'Venkat' database.
------------------------------------

Reason for this issue:

This issue arises due to improper installation of SQL Server or improper software . We need to install SQL Server from proper installation disk or As a work around, we can execute the below query to restore your backup.

Solution for this issue:

If you face the above issue, execute the below query.

RESTORE DATABASE [Venkat]
FROM DISK = N'D:\Venkat.BAK'
WITH REPLACE

Cheers,
Venkatesan Prabu .J

SQL Server 2008 - Part 1 - Activity Monitor

Hi Readers,



How are you all? Atlast, I got some time to start my articles on SQL Server 2008. Let's see how it goes?



There are lot of fantastic features introduced in SQL Server 2008. Starting from Intellisense to advanced features like handling objects. One important change done by Microsoft in SQL Server 2008 is, storage architecture. Storing the data in this new version is entirely different to the older one. You can see all the features in my future articles. Let me start with a small part "Activity Monitor".



In SQL Server 2008, Activity Monitor is more flexible and it will give most of the details about a server. Goto Server -> Right click-> Activity Monitor, your activity monitor will be opened.







There are three major items,



1. Overview - Pictorial representation of your server details.

2. Processes - User processes and other details.

3. Resource waits - Deadlocks and other resource waits details.

4. Data file I/O - I/O details.

5. Recent Expesive queries - Long running queries.



Overview Tab in Acitivity Monitor:

This tab will provide a overall graphical view for the other tabs below + Overall nature of the datbase.

1. This tab will provide you the processor time - Time taken for the processor to respond.

2. Any waiting tasks in the server.

3. Database I/O - How the database I/O looks like?

4. Batch requests/sec - Nature and load of the batch requests.






Processes tab:



This tab will list down all the processes available in the server(SPID), the application used in accessing the server, wait time, wait type, wait resource, login information, blocked by etc..,



Resource Waits Tab:


This tab will listdown all the available resources and average time to use the resource.

DataFile I/O:

This tab will list down the available databases and their usages (Read/Write and Response time)

Most Expesive Queries:

This tab will list down the expesive queries available in the server.

This will tell you the expensive query with read/write/response time details.

Cheers,

Venkatesan Prabu .J

Failed to Access IIS Metabase in ASP.net

Failed to Access IIS Metabase error:

If ASP.net is installed before IIS, this issue will occur. In that case we need to register our IIS to the Dotnet framework. This can be achieved by the below command.

Goto Run -> type the below command and click "OK"

%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

Cheers,
Venkatesan Prabu .J

Microsoft Wiki

TechNet Wiki Beta Arrives!

Microsoft started allowing us to post our views/articles and knowledge in their site.

The TechNet Wiki is a library of information about Microsoft technologies written by the community for the community. Whether you write code, manage servers, keep mission-critical sites up and running, or just enjoy digging into details, we think you will be at home in the TechNet Wiki. For more information including how you can participate see:

http://social.technet.microsoft.com/wiki/

Happy writing !!!

Cheers,
Venkatesan Prabu .J

Malicious software removal tool from windows

Microsoft have recently released a fantastic software tool to detect the malicious softwares available in your system. This will detect the unwanted malicious software(which is harmful to your PC) and highlight all the red alert softwares.

In addition to the above said check, it will check your registry tampers, general PC checkup.

Microsoft Windows Malicious Software Removal Tool (KB890830):

This tool checks your computer for infection by specific, prevalent malicious software (including Blaster, Sasser, and Mydoom) and helps to remove the infection if it is found. Microsoft will release an updated version of this tool on the second Tuesday of each month.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ad724ae0-e72d-4f54-9ab3-75b8eb148356

Microsoft Windows Malicious Software Removal Tool (KB890830) x64:

This tool checks your computer for infection by specific, prevalent malicious software (including Blaster, Sasser, and Mydoom) and helps to remove the infection if it is found. Microsoft will release an updated version of this tool on the second Tuesday of each month.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=585d2bde-367f-495e-94e7-6349f4effc74

Last important point about this software : It's free of cost

Cheers,
Venkatesan Prabu .J

Database Backups in SQL Server

Database Backup:

Database backup is a very important activity in day to day activities. Backup's will be taken for safety purposes. Considering a server got crashed, In that case we can restore the backup taken. To take backup of the database, right click on the database and goto tasks -> backup

Backup database window will pop-ups. Two types of backups will be displayed in the drop down list.

1. Full Backup - This backup type will take the entire database backup.

2. Differential Backup - This backup type will take the last difference of data between now and the last backup taken.

In addition to that, we can take the file or file group backup too.

To avoid more backups to occupy the disk space, we can set an options like when these backup's will expire. This enable us to identify the invalid backups.
Now, our next option is to select the location of the backup + backup name.

Backup window provides an option to locate the backup path through "Browse" option.

Selecting the backup name and location.

OOps, I got a peculiar OS error,

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

TITLE: Microsoft SQL Server Management Studio Express------------------------------
Backup failed for Server 'VENKAT\SQLEXPRESS1'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
------------------------------ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\aa.bak'. Operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
------------------------------BUTTONS:
OK------------------------------
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

On analysing the above issue, the reason behind is -> Access issue. The SQL server service doesn't have proper access to write a backup file in the specified location. I have given full access to the sql server user to the path and tried the backup option. OOPS, my guess is correct and its working fine.

Backup operation completed successfully.

Happy Learning!!!

Cheers,

Venkatesan Prabu .J