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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Error : Link two tables from two databases
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

acsivaprakash
Starting Member

India
1 Posts

Posted - 02/18/2010 :  00:38:34  Show Profile  Reply with Quote

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

India
52326 Posts

Posted - 02/18/2010 :  01:22:23  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000