Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Error : Link two tables from two databases

Author  Topic 

Starting Member

1 Post

Posted - 2010-02-18 : 00:38:34

Dim DataConn_upd, SQL_upd
DIM STRConn_upd
Set DataConn_upd = Server.CreateObject("ADODB.Connection")
DataConn_upd.Open STRConn_upd

SQL_upd = "update OPENDATASOURCE('SQLOLEDB','Data Source=;User ID=sa;Password=sa').stars.dbo.attendance set nstatus=leave_option from leaves, OPENDATASOURCE('SQLOLEDB','Data Source=;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


Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:22:23
As suggested by error message you need to turn on Ad Hoc Distributed Queries option using sp_configure

EXEC sp_configure 'show advanced option', '1';
EXEC sp_configure 'Ad Hoc Distributed Queries', '1'

SQL Server MVP

Go to Top of Page

- Advertisement -