Chennai .Net User Group

Dim DataConn_upd, SQL_upd
DIM STRConn_upd
STRConn_upd = "PROVIDER=SQLOLEDB;DATA SOURCE=intra;UID=sa;PWD=sa;DATABASE=users "
Set DataConn_upd = Server.CreateObject("ADODB.Connection")
DataConn_upd.Open STRConn_upd

SQL_upd = "update OPENDATASOURCE('SQLOLEDB','Data Source=172.17.244.7;User ID=sa;Password=sa').stars.dbo.attendance set nstatus=leave_option from leaves, OPENDATASOURCE('SQLOLEDB','Data Source=172.17.244.7;User ID=sa;Password=sa').stars.dbo.attendance where todate>=ndate and fromdate<=ndate and staffid=substring(emp_code,8,4) and approved='Approved' and nstatus='AA' and substring(emp_code,8,4)='" & Request.Cookies("UserName") & "'"

DataConn_upd.Execute (SQL_upd)

 Error :

Microsoft OLE DB Provider for SQL Server error '80040e14'

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

/idea/DepChecking.asp, line 33

Dear acsivaprakash,

Please Give more details about the error you have encountered.

Hi,

You are getting this error because in your database server the OpenRowset and the OpenDatasource statements the turned off.

to Turn on this statements follow the below steps.

1. Login as a Administrator in your sqlserver

2. Run the Procedure sp_configure

3. You can see 'show advanced options' in the list with run_value "0" in your resultset.

4. Now you have to change  'show advanced options' run_value to 1 for enabling,Run the below Query

sp_configure 'Show advanced options' , 1

reconfigure

5. After run this query sucessfully, you have to check the 'Ad Hoc Distributed Queries' run_value, if its 0 change it into 1 by run the below query

sp_configure 'Ad Hoc Distributed Queries' , 1

reconfigure

finally u can check the values changed to 1 for 'Ad Hoc Distributed Queries' and 'show advanced options'.

Now you can open the OpenRowset/OpenDatasource in your Database server.

Hope it helps you.

Thanks & Regards

Suyambu