Tag Archives: Ritesh Shah

Enable “Ad Hoc Distributed queries” by sp_configure in SQL-Server 2005

If you want to use OpenDataSource or OpenRowSet query than you must have to enable “Ad Hoc Distributed queries”. It is disabled by default as a part of security configuration as per Microsoft.
You can set this option by two ways.
1.) sp_configure command
2.) Surface area configuration.

Since I am a script bee, I will explain steps for sp_configure.
You can run sp_configure to see current status of “run_value” field. If you don’t able to see “Ad Hoc Distributed queries” in the results set. You have to enable “Show advanced option” by following command.
sp_configure ‘show advanced options’, 1
reconfigure
GO
You may get message
Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.


Once you are done with that, you can see “Ad Hoc Distributed queries” in list of sp_configure. You have to set “Run_value” to “1”. If it is “0” than run following command.

sp_configure ‘Ad Hoc Distributed Queries’, 1
reconfigure
GO
Which will show you following message.
Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Once you will go through the procedure described here, you will be able to run OpenRowSet and OpenDataSource query.

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

MS-SQL Server 2008 Service Pack 1 – CTP

MS-SQL Server 2008 Service Pack 1 – CTP

Microsoft has introduced SP1 for Microsoft SQL Server 2008 CTP version on 23rd FEB 09. You can download it from:

Do read instructions and system requirement documentation from setup documentation before applying SP1.

Reference: Microsoft Site

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

Create your own system stored procedure – SQL-SERVER 2005

Create your own system stored procedure – SQL-SERVER 2005
Sometime, its great help if we can create our own stored procedure like any other system stored procedure. It will help us to access that stored procedure from any of the database within one sql-server instance though it have been created only once.
If you create any stored procedure in MASTER database with “sp_” prefix and mark it as system stored procedure than you can take an advantage of name resolution algorithm of engine. Don’t create any stored procedure with “sp_” prefix in your own database as it incur burden on engine, because when you try to execute stored procedure with “sp_” prefix, engine will first try to search it in MASTER database.
We will create one stored procedure in MASTER database.


CREATE PROC sp_list_of_Employees
AS
SELECT * FROM Employee
RETURN
I am assuming that you are having one table, name “Employee” in “AdventureWorks” database but not in “MASTER” database, though we have created above stored procedure in MASTER database.
Now, try to run above stored procedure.


USE MASTER
GO
exec sp_list_of_Employees

As soon as you will run this, you will be greeted with an error:


Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4 Invalid object name ‘Employee’.


If you have “Employee” table in “AdventureWorks” database then try to run procedure in AdventureWorks database.
USE AdventureWorks
GO
exec sp_list_of_Employees

Again you will be greeted with the same error:
Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4 Invalid object name ‘Employee’.

Though you ran the procedure from AdventureWorks database, but it is still looking at “MASTER” database for “Employee” table, because we have not marked it as system routines.
We can mark stored procedure with below given command:
USE MASTER


EXECUTE sp_ms_marksystemobject ‘sp_list_of_Employees’
Now, try to run SP in AdventureWorks database and you will get results or you can run this stored procedure in any of the database in your SQL instance, which have Employee table.
USE AdventureWorks
GO
exec sp_list_of_Employees

Hope you have enjoyed reading.
Happy SQLing!!!!
Reference: Ritesh Shah

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

Session Variable – Context_Info -SQL-Server 2005

Session Variable – Context_Info:
Session is a powerful tool in any of the programming language. SQL-Server is not a full fledge programming language but it do supports session variable for current session or connection. It stores value of session in 128 byte of binary information.
You can set or retrieve its value from CONTEXT_INFO column of the following system views.
sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses

While storing the value in CONTEXT_INFO is aware that you are having 128 bit, whatever you will assign, will be stored in that 128 bit space only. Let’s have a look at it.
I am going to store two VARCHAR variable in CONTEXT_INFO. First variable will cost 6 bit and another variable cost 4 bit. If the datatype you use, is not able to implicitly convert itself to binary than you will have to do that task manually. Conversion table of datatype is given at the end of article.
Now let’s have a look at example.

USE AdventureWorks


DECLARE @Fname VARCHAR(10), @Lname VARCHAR(10), @bVar binary(128)
SET @Fname=‘Ritesh’
SET @Lname=‘Shah’
SELECT @bVar=CONVERT(binary(6),@Fname)+CONVERT(binary(4),@Lname)
SET CONTEXT_INFO @bVar
GO
We have declare two VARCHAR variable and one binary variable. Set the values in both VARCHAR variable, convert it to binary and stored it in binary variable. Finally set the binary variable to CONTEXT_INFO column. You can query system views to get value of CONTEXT_INFO like below.
SELECT Fname = convert(varchar(10), substring(context_info, 1, 6))
FROM master..sysprocesses
WHERE spid = @@spid


SELECT Lname = convert(varchar(10), substring(context_info, 7, 4))
FROM master..sysprocesses
WHERE spid = @@spid
@@spid is used to get the exact session ID. As I explained previously also, you can get the CONTEXT_INFO column in three system views so that you can query any of the views like:
SELECT context_info FROM sys.dm_exec_sessions WHERE session_id = @@SPID;


SELECT context_info FROM sys.dm_exec_requests WHERE session_id = @@SPID;


SELECT context_info FROM sys.sysprocesses WHERE spid = @@SPID;

Below is the conversion table I got from the SQL Server documentation.

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

UPDATE Query with JOIN in SQL-Server

I know all of you are aware with UPDATE statement of SQL-Server but may be some of you don’t know the power of JOIN in UPDATE statement. Herewith, I am going to describe UPDATE with JOIN. Sometime in real world, we have requirement to update some fields of one table depend upon another table. To cater this need we may use iteration tool like “WHILE”, sometime which is the cause of low performance so in that case you can go for UPDATE with JOIN

Well, let start our practical session by creating one table.

CREATE TABLE CustomerMaster

(

ID INT NOT NULL CONSTRAINT PK_custID PRIMARY KEY,

CompanyName VARCHAR(20) NOT NULL,

Email VARCHAR(50) NOT NULL

)

And now we will enter some records in above table.

INSERT INTO CustomerMaster VALUES (1,‘PCI INC.’,‘info@pci.com’)

INSERT INTO CustomerMaster VALUES (2,‘GW INC.’,‘info@gw.com’)

Now, we will create another table and along with some INSERT script to enter the records.

CREATE TABLE CustomerContact

(

CustomerContactName VARCHAR(15) NOT NULL,

Email VARCHAR(50) NULL,

ParentID INT CONSTRAINT FK_CustID REFERENCES CustomerMaster(ID)

)

INSERT INTO CustomerContact VALUES(‘Ritesh’,‘R@pci.com’,1)

INSERT INTO CustomerContact VALUES(‘Alka’,NULL,1)

INSERT INTO CustomerContact VALUES(‘Avi’,NULL,2)

Now, you can observe that our record number 2 and three doesn’t have their own email address so now we may wish to update those email addresses from its company’s email addresses (parent’s). below will be the query for same.

UPDATE CustomerContact SET EMAIL=

(SELECT Email FROM CustomerMaster cm where cm.ID=CustomerContact.parentID) where email is null

In above case, our NULL value in email field of customerContact table will be populated by its corresponding records from parent table.

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

Memory configuration of SQL-Server 2005:

Memory configuration of SQL-Server 2005:

All of you knew that, now a day, hardware cost is going down and down, expectation of performance is going up and up. Today, even personal computers are having more than 2GB memory, SQL-Server by default uses 2GB memory but there is a way you can use more memory for SQL-Server and release it whenever you wish and re-allocate it to OS,

Cause memory management is set to dynamic in SQL-Server 2005.

Thanks to AWE application which is a heart of memory management. First of all you will have to make this application enable by following setting.

sp_configure ‘awe enabled’, 1

RECONFIGURE

GO

Value “1” is for enabling AWE and “0” is for disabling. BTW, “0” is default value.

Once, you enable AWE option. You can set minimum and maximum memory for SQL-Server by following script.

SP_CONFIGURE ‘min server memory’, 256;

RECONFIGURE;

GO

sp_configure ‘max server memory’, 9144

RECONFIGURE

GO

Please be informed that, you have to give value in MBs in above commands. You can get more information about AWE from latest SQL-Server book online, you can download it from Microsoft website, topic is “MIN AND MAX SERVER MEMORY”.

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

Correlated Subquery – SQL-Server

Correlated Subquery in MS-SQL Server:

I am writing this article by assuming that you are very well aware with sub-query concept as this is little bit ahead than sub-query. Before we start technically, if you will observe the name “CO-Related subquery”, you will get the concept that this is something highly related to each other and yes, you are absolutely right. Correlated sub-query (also known as repeating subquery) is depend on the main outer query, if you will run it without outer query, you will be greeted with error, while in subquery, if you will run sub-query without outer query, it will give you results set.

MICROSOFT’s definition for Correlated sub-query in its books online:

Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

Well, enough theory, right????? Now let’s move towards some practical stuff.

We will see this with two table related to cricket match. J

Create one table with cricketer’s personal details with below given query.

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

Now, we will insert few records in it.

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Sachin Tendulkar’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Steve Waugh’,‘Australia’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Saurav Ganguly’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Jaisurya’,‘Sri Lanka’)

Once you are done with this, please, create one table for score board as follow.

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

Now, it is time to insert few records in score board table.

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-1998’,190,1)

Now, If you want to see the tournament (match name) with cricketers name, who made run below than average run with correlated subquery.

SELECT C.Name,p.MatchName,p.Run

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

WHERE

run<(SELECT AVG(Run) from PersonalScore ps where p.matchname=PS.matchname)

First subquery will calculate the Average of runs and than it will execute every time outer query get new “MatchName”. Every single “MatchName” from outer query will be passed to subquery and it return the results based on the situation.

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

Delete-Select-Duplicate-Records-by-CTE-in-SQL-SERVER 2005

Delete Duplicate records from table with CTE. As I explain in my previous article that selecting and deleting duplicate records, could be done by several ways so this is another way to do the same.
You can refer earlier article by following link.
http://blog.extreme-advice.com/2009/02/28/select-delete-duplicate-records-sql-server/
As this is going to be done by CTE, You can get more information about CTE by following link.
Once you create table given in above link, you can use the below given query for selecting or deleting records.
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
Select * from DeleteDuplicateRecords where RowNum>1
Or
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
DELETE from DeleteDuplicateRecords where RowNum>1
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

CTE (Common Table Expression) SQL-Server 2005

One of the beautiful features of MS SQL-Server 2005 is CTE and it is mainly used for recursive query. In recursive query, query executes itself; it is the same concept we used to use in C or C# etc programming language for recursive function. In real world we often need recursive hierarchical data for list of category and subcategory resides in one single table. Let’s have a look at it.

Step 1:

Simply create one table.

USE AdventureWorks

GO

Create Table VehicleCategory

(

ID Int Constraint PK_VehicleCategoryID Primary Key,

BikeCategory VarChar(100),

ParentID Int Constraint FK_VehicleCategory_ParentID References VehicleCategory(ID),

Description varchar(50)

)

GO

Step 2:

Insert few records in above created table:

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(1,‘Bike’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(2,‘Scooter’,Null,‘Main Category’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(3,‘Yamaha RX 100’,1,‘125 CC bike of Yamaha’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(4,‘Hero Honda CBZ’,1,‘150 CC bike of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(5,‘Honda Activa’,2,‘125 CC Scooter of hero honda’)

Insert Into VehicleCategory(ID,BikeCategory,ParentID,Description) Values(6,‘TVS Scooty’,2,’75 CC Scooter of TVS’)

Step 3:

Now, if I want all the category and subcategory of “Bike” than what I suppose to do? There may be different ways to fulfill this requirement but I would like to go for new feature introduce in SQL-Server 2005, which is CTE (Common Table Expression)

WITH cte_VehicleCategory AS

(

SELECT ID,BikeCategory,ParentID,Description FROM VehicleCategory Where ID=1

UNION ALL

SELECT v.ID,v.BikeCategory,v.ParentID,v.Description FROM VehicleCategory AS v

INNER JOIN

cte_VehicleCategory AS cv ON v.ParentID=cv.ID

)

Select * from cte_VehicleCategory

Let’s study little bit more about how this magical code worked???

Our CTE name is cte_VehicleCategory, which will get its base records from the first query above the UNION ALL. It will iterate to seek ID’s value in parentid of query below the UNION ALL. Now, as you know that this is recursive query so if you want to create infinite query ;) than change statement after “INNER JOIN” to “cte_VehicleCategory AS cv ON v.ID=cv.ID”

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