Tag Archives: MS SQL Server 2005

Milestone after 100 articles in Microsoft technology:

I am glad that I have just finished 100 articles of SQL Server and Microsoft technology. It has been really great pleasure that I have received an honor to write something about Microsoft technology and cherry on the Ice-Cream is that, community people gave me warm welcome in this direction.

When I see log of my blog I get more and more energy by seeing that MY blog is being accessed from so many countries. A person whom I don’t know and s/he doesn’t know me is reading my blog and getting little help, is really a very good fruit of my work in the community.

I am working in IT industry since last 8+ years but started writing the blog regularly few months back. The person who inspired me to write the blogs are Mr. Pinal Dave of http://sqlauthority.com/ (very great and kind guy along with outstanding knowledge of SQL-Server) and another one is Mr. Bihag Thaker (one of my very good friend and my companion in IT as well as in Music). I really want to THANKS both of them. Thank you very much for all the encouragement you gave me. They always show me right direction and criticize me as well, whenever it is needed.

As I have completed my 100 article today, I thought to find which article are very popular in my blog so I am listing top 10 article from my blog which got very good response from community.

Image store in SQL Server 2005 and retrieve it in asp.net application with C#
http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html

Where and Having clause in SQL Server
http://www.sqlhub.com/2009/03/where-and-having-clause-in-microsoft.html

DDL Trigger in SQL Server 2005
http://www.sqlhub.com/2009/03/ddl-trigger-in-sql-server-2005-for.html

Difference between Microsoft SQL Server 2005 and 2008
http://www.sqlhub.com/2009/03/difference-between-microsoft-sql-server_16.html

Complete reference of SQL Server JOIN
http://www.sqlhub.com/2009/03/complete-ref-of-sql-server-join-inner.html

Empty all table or Delete all data from all table with sp_msforeachtable
http://www.sqlhub.com/2009/03/empty-all-table-or-delete-all-data-from.html

For Linked Server
http://www.sqlhub.com/2009/03/linked-server-is-not-configured-for.html

Session variable in SQL Server
http://www.sqlhub.com/2009/03/session-variable-contextinfo-sql-server.html

Difference between SET and SELECT
http://www.sqlhub.com/2009/03/set-and-select-in-sql-server-2005.html

Convert string to propercase
http://www.sqlhub.com/2009/03/propercase-or-titlecase-users-define.html

After getting very warm welcome to the community, I have decided to help community more so I have joined http://www.experts-exchange.com forum today.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Stored procedure with SUBSTRING in WHILE loop SQL Server 2005

Below given stored procedure will count occurrence of given character in given string.

–stored procedure which will count occurence given character in @CountChar

–from @string variable

CREATE PROC CharCount(@String VARCHAR(50),@CountChar VARCHAR(1))

AS

BEGIN

DECLARE @counter INT

DECLARE @finalCount INT

SET @finalCount=0

SET @counter=LEN(@String)

WHILE @counter>=0

BEGIN

IF @CountChar=SUBSTRING(@String,@counter,1)

BEGIN

SET @finalCount=@finalCount+1

END

SET @counter=@counter1

END

SELECT @finalCount

END

GO

–check the SP

EXEC CharCount ‘ritesh shah from SQLHub.Com’,‘h’

Happy Programming!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Change identity column to non-identity column:

I seen one question in one of the forum how can we efficiently remove identity from the column. Below was my answer there.

–create one table with identity column

CREATE TABLE DemoIdentity

(

ID INT IDENTITY(1,1),

NAME VARCHAR(10)

)

–insert few records for testing

INSERT INTO DemoIdentity

SELECT ‘Ritesh’ UNION ALL

SELECT ‘Rajan’

–checking the records

SELECT * FROM DemoIdentity

–creating one table with same structure but without identity

CREATE TABLE DemoIdentity2

(

ID INT NOT NULL,

Name VARCHAR(10)

)

–using swich concept of partition

ALTER TABLE DemoIdentity SWITCH TO DemoIdentity2

–droping original table

DROP TABLE DemoIdentity

–renaming duplicate table with original table’s name

EXEC sp_rename ‘DemoIdentity2’,‘DemoIdentity’

–checking finally

SELECT * FROM DemoIdentity

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Auditing Trail with Trigger in SQL Server 2005

If you are new to this concept in database world than you might think what is Auditing Trail? The answer is pretty much easy and simple, if you want to increase the data-integrity and wants to have full details about insertion, edition and deletion of your data; Audit Trail is the concept you have to adopt.

Since this is a very huge topic and not possible to cover complete topic in one or may be in few articles, I will give some basic details here and will write some more article with live situation as and when time permits.

You would like to keep track of total update and delete in records after its insert, you may want which front-end accessed your row for modification, which user has changed which records.

There are many methods popular to keep track of your records change like you can have duplicate table for each table along with some comment field sand keep the track and old value. You may wish to create one table which will have details off all tables and about every single transaction but In that case, maintenance of that table is very crucial.

Let us create one table and try to keep trail of database.

–demo table for AuditTrail

–Note: this is just for demo purpose, in live situation we may have very big table than
— on explained here. everybody has their own need and situation.
CREATE TABLE AuditTrail(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT def_AuditTrail_Id DEFAULT(NEWID()),
AuditDate DATETIME,
TableName VARCHAR(50) NOT NULL,

ColumnName VARCHAR(50) NOT NULL,

Description VARCHAR(50) NOT NULL,

UserName VARCHAR(50) NOT NULL,

OldValue VARCHAR(50) NOT NULL,

NewValue VARCHAR(50) NOT NULL

)


–Now we will create one more table for which we will keep the trail in above table
–You can keep Audit of Insert, Delete and Update everything in above table

–and can customize it as per your need

CREATE
TABLE AuditDemo
(

ID INT IDENTITY(1,1) NOT NULL,

Name
VARCHAR(10),

Company VARCHAR(10)


— have few records in above table

INSERT INTO AuditDemo
SELECT
‘Ritesh’,‘eChem’ UNION ALL

SELECT ‘Rajan’,‘Marwadi’

–LET us create fixed audit trail trigger
create
TRIGGER AUDITonAuditDemo
ON
AuditDemo
AFTER UPDATE

AS

IF
UPDATE(Name)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),‘AuditDemo’,‘Name’,‘Update’,suser_sname(),DELETED.Name,INSERTED.Name

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END

IF UPDATE(company)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),‘AuditDemo’,‘Company’,‘Update’,suser_sname(),Deleted.Company,Inserted.Company

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END


–let us update record in AuditDemo tabel

UPDATE AuditDemo SET Name=‘R.Shah’ WHERE ID=2

–LET US NOW CHECK WHETHER WE HAVE TRAILED THE RECORDS OR NOT

SELECT * FROM AuditTrail


–let use update and check once agian

UPDATE AuditDemo SET Company=‘Testing’

SELECT * FROM AuditTrail

Hope you have enjoyed!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “TestLinkServer”.

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “ TestLinkServer” returned message “Cannot open database ”. It may not be a database that your application recognizes, or the file may be corrupt.”.

After reading my linked server’s article in SQL Server 2005 at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

http://www.sqlhub.com/2009/03/linked-server-is-not-configured-for.html

One of my subordinate wanted to use it in one of our small application. He started using the same code I gave in one of my above article about Linked Server but he was facing above described error. He tried everything and every possible solution from internet but didn’t find his luck. Finally he told me to look at the error and try to solve it. At the very first glance I found problem with his OLE DB adapter. He was using the MDB file which has been developed in ACCESS 97 but there was ACCESS 2007 in our SQL’s development server. As MDB was old, he was using “Microsoft.Jet.OLEDB.4.0” but he should use ” Microsoft.ACE.OLEDB.12.0” as our development server has ACCESS 2007 and it doesn’t understand old provider.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Archiving Data in SQL Server 2005

Archiving data is one of the very big topics with lots of challenging. I am trying to give small light on this topic for newbie. In our day to day activity we often don’t need very old data may be few year old data. Question might arise in mind, what should we do for those data? Delete it? Transfer it somewhere else? Deleting wouldn’t be solution in every company as you might need to view those data after few years so better option to move that somewhere in else.

You can archive your data to the same database or else you can create separate database with identical same structure and use it as your archived database. I would prefer second option as it removes the overhead on the same database.

Let us create one simple stored procedure which will archive your data to another table in same database and delete the data from the original table if archiving will done successfully.

–create table for demo

use adventureworks

go

CREATE TABLE ArchiveTest

(

ID INT IDENTITY(1,1) NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

–CREATE table two for archiving

CREATE TABLE ARCHIVE_ArchiveTest

(

–NOTE: I didn’t define ID column as IDENTITY as we are going

— to archive the records and we can’t enter manual value in IDENTIY column

ID INT NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

–insert records in above table

INSERT INTO ArchiveTest(Name)VALUES(‘Ritesh’)

INSERT INTO ArchiveTest(Name)VALUES(‘Rajan’)

INSERT INTO ArchiveTest(Name)VALUES(‘Dharmesh’)

–create stored procedure to Archive data

CREATE PROC ArchiveFromTest(@StartCharOfName CHAR(1))

AS

BEGIN

SET NOCOUNT ON

BEGIN TRAN

INSERT ARCHIVE_ArchiveTest

SELECT * FROM ArchiveTest WHERE Name LIKE ‘R%’

IF @@ERROR<>0

BEGIN

ROLLBACK TRAN

RETURN

END

DELETE FROM ArchiveTest WHERE Name LIKE ‘R%’

IF @@ERROR<>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRAN

RETURN

END

–CHECK whether above stored procedure works

EXEC ArchiveFromTest ‘R’

–finally check your data

SELECT * FROM ArchiveTest

SELECT * FROM aRCHIVE_ArchiveTest

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Logical Delete with Trigger in SQL Server 2005

Delete seems known command for every computer users and especially to whom, who engaged with database tasks but have you ever heard concept like “Logical Delete”? Fox pro guys may know this concept very well.

It proved itself very helpful in many organizations and has kept tight data integrity. Logical delete is nothing but the marked data as deleted in database rather than physically delete it. Many developer use delete flag bit column to mark row as deleted. You can set the flag true from your front end application, can make one stored procedure which can take care of flag or else you can fire instead of trigger which will detect DELETE statement and rather than deleting the row, it just marked the data as deleted.

By this way, you will always have all the data in your database but keeping and archiving those data is one of the overhead and should be done with care.

This will help a lot when you wish to UNDO your delete command and wants your entire deleted row back. You can simply change the flag and you are done.

Logical Delete becomes very critical in case of Cascading DELETE as it is difficult to handle DELETE action in all the child table but you can brainstorm your mind can do it, not a impossible task.

To know more about cascading delete and update, do refer my past article at:

http://www.sqlhub.com/2009/03/cascading-delete-and-cascading-update.html

Let us see one small example of logical deleting of data in SQL Server 2005:

–create table for demo

use adventureworks

go

CREATE TABLE LogicalDelete

(

ID INT IDENTITY(1,1) NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

–insert records in above table

INSERT INTO LogicalDelete(Name)VALUES(‘Ritesh’)

INSERT INTO LogicalDelete(Name)VALUES(‘Rajan’)

INSERT INTO LogicalDelete(Name)VALUES(‘Dharmesh’)

–create Instead Of Trigger for logical delete

–this is just basic example of logical delete

–you can customize it with your needs

CREATE TRIGGER triLogicalDelete ON LogicalDelete

INSTEAD OF DELETE

AS

BEGIN

–finding the records going to delete from DELETED table by ID column

–JOIN it with LogicalDelete table and update its status to mark as deleted

UPDATE LogicalDelete SET Deleted=1

FROM LogicalDelete

INNER JOIN Deleted

ON LogicalDelete.ID=Deleted.ID

PRINT ‘Logical delete is complete’

END

–run DELETE DML command

DELETE FROM LogicalDelete WHERE Name LIKE ‘R%’

–check whether flag is changed

SELECT * FROM LogicalDelete

You can easily find deleted records with “Deleted” column but beware of using this as logical delete is two sided sword, if you don’t know how to use it than it will affect performance of your server.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server is not configured for data access Error: 7411 in SQL Server 2005

I wrote one article to set Linked server at:

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

Sometime you properly set the link server and while trying to execute SELECT statement over it, it may show you following error.

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution of this error is really pretty much simple. Before we move to solution of error I would like to raise this error. Run the linked server example given in above article and see whether it is working properly. One you done with that let us change one property of Linked Server which will raise above said error.

–set data access property to ‘false’ which will generate error

–when you try to execute any SELECT statement against your linked server

exec sp_serveroption [LinkToAceess],‘Data Access’,‘false’

Now try to execute SELECT statement and greet the error:

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution is so simple; just enable your data access to linked server with following command.

exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Linked Server in SQL Server 2005 from ACCESS 2007

Linked Server is one of the cool features of Microsoft SQL Server especially for those who want to access remote database, remote database could be of any type SQL Server, Sybase, Access etc. you can access those databases with OLEDB provider. Let us see how can we do it?

Before you move further I would like to request my readers that please create one Access MDB file named “Testing.MDB” and create at least one table named “empDetails” in that MDB.

Now, we will link “Testing.MDB” database to SQL Server 2005.

Note: You can do it by wizard and by query but I prefer query so will show you that path only.

Below query will cover file steps to link the server.

1.) Add linked server

2.) Add linked server credential, if any

3.) Check sys.servers whether database is added as linked server

4.) List all available tables in MDB file

5.) Perform SELECT on “empDetails” table.

–add link server

exec sp_addlinkedserver

@server=‘LinkToAceess’,

@srvproduct=‘AccessDatabase’,

–if you have older version of access then kindly use old jet provider

–Microsoft.Jet.OLEDB.4.0

@provider=‘Microsoft.ACE.OLEDB.12.0’,

@datasrc=‘C:\Testing.mdb’

GO

–add MDB’s credental, if any

EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’

–check whether ‘LinkToAccess’ has been added

select * from sys.servers

–list all tables available in Testing.MDB

exec sp_tables_ex ‘LinkToAceess’

–perform SELECT on empDetail table.

SELECT * FROM [LinkToAceess]empDetails

So, finally you have used Linked Server from within your SQL Server

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SET IDENTITY INSERT – how to insert value in IDENTITY Column in Microsoft SQL Server

I want to share one of my experiences about IDENTITY column. When I was very small kid in SQL Server few years back and I used to do programming in PHP with MySQL back end. I have just started learning SQL Server at that time one thought pops up in my mind. There is no way to insert value in IDENTITY column (that’s what I was thinking at that time after reading IDENTITY column from book) than what happens to the table I have with just one column and that column is IDENTITY enable column?

I tried to search out the way and finally found it and got very happy at that time. I didn’t even think that I would write article on SQL Server and especially on this experience.

–CREATE DEMO TABLE

CREATE TABLE IdentityColumn

(

ID INT IDENTITY(1,1),

NAME VARCHAR(10)

)

GO

–TRYING TO INSERT VALUE

INSERT INTO IdentityColumn VALUES(1,‘Ritesh’)

GO

–above statement will show you below given error

–Msg 8101, Level 16, State 1, Line 1

–An explicit value for the identity column in table ‘IdentityOneColumn’ can only be specified

–when a column list is used and IDENTITY_INSERT is ON.

–SET IDENTITY_INSERT to ON so that we can enter value manually in IDENTITY column

SET IDENTITY_INSERT IdentityColumn ON

–INSERT record in IDENTITY column with column list in INSERT

INSERT INTO IdentityColumn(id,name) VALUES(1,‘Ritesh’)

–once you done, set value OFF to IDENTITY_INSERT

SET IDENTITY_INSERT IdentityColumn OFF

GO

–check the table

SELECT * FROM IdentityColumn

–again start inserting regularly

INSERT INTO IdentityColumn(name) VALUES(‘RAJAN’)

–AGAIN check the table

SELECT * FROM IdentityColumn

–one more time manual insert

SET IDENTITY_INSERT IdentityColumn ON

INSERT INTO IdentityColumn(id,name) VALUES(5,‘Alka’)

SET IDENTITY_INSERT IdentityColumn OFF

GO

–AGAIN check the table

SELECT * FROM IdentityColumn

–again start inserting regularly and check table

INSERT INTO IdentityColumn(name) VALUES(‘RAvi’)

SELECT * FROM IdentityColumn

After observing above behavior, you come to know that, if you insert big value manually in IDENTITY column, next regular value will follow you value.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah