Error : Link two tables from two databases

Please start any new threads on our new site at https://forums.sqlteam.com. 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

acsivaprakash
Starting Member

1 Post

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

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

Sivaprakash

visakh16
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';
GO
Reconfigure
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', '1'
GO
Reconfigure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources