OPENROWSET and OPENDATASOUCE problems

SQL Server 2005

The OPENROWSET and OPENDATASOUCE features need to be enabled by Surface Area Configuration. It is called “Ad Hoc Remote Queries”. Otherwise, you may see the follwoing error.

Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ has been denied. You must access this provider through a linked server.

In case the error keep coming up, it may be some of the value are still missing after the Surface Area Configuration. Following the following link, make sure the DisallowAdhocAccess is set up correctly to 0.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/0cec4ce4-d210-4973-a888-b7810394f261

Well, the OPENROWSET and OPENDATASOUCE features use the temporary folders. The SQL users may not have rights to the folder SQL Server startup user’s temporary folder.

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

Following the link to set the SQL Server startup user’s temporary folder to a place most people have access.

http://support.microsoft.com/kb/814398

http://nathondalton.wordpress.com/2010/04/01/sql-memory-and-external-data/

http://msdn.microsoft.com/en-us/library/ms188721(v=SQL.90).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s