another blog :: Fight the fear of SQL with SQLHub.com

please visit

http://www.sqlhub.com  for further new tips and tricks of SQL-Server and .NET. I have booked this domain specially for SQL Server blogging.

Ritesh Shah
http://www.SQLHub.Com
Fight the fear of SQL

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

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:

http://www.microsoft.com/downloads/details.aspx?FamilyID=6f26fc45-f0ca-49cf-a6ee-840c7e8bb8af&displaylang=en

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

Reference: Microsoft Site

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