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.
| Author |
Topic |
|
jjamjatra
Starting Member
13 Posts |
 Posted - 2014-06-18 : 19:21:43
|
I have a stored procedure in SQL 2008 that has been used for many years without problems. Here is a relevant snippet (part of an INSERT INTO table SELECT block): FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\RawData_MDS\Book1.xls;HDR=Yes;IMEX=1', 'SELECT [Property State] , [Property County] , [Property Zip] , [Originating Mortgagee] , [Mortgage Amount] , [Year] , [Month] FROM [Sheet1$]')The above code still works - I've been invoking this sProc from a VBA addin launched to process an Excel 2003 workbook opened on my WindowsXP machine.So, here is the problem: I've copied this VBA addin to another machine (WinServer2003) and loaded it for processing the same workbook with Excel 2010. The addin runs fine until the stored procedure is invoked. Then I get this message:Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.Searching on that message I've discovered: - I have no linked servers defined in my SQL Server 2008 instance
- The Surface Area Configuation facet for AdHocRemoteQueriesEnabled is TRUE
- On my server machine, the Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers\SQLNCLI10 has an entry called DisallowAdhocAccess which 0
So my SQL server shows me nothing I might have done long in the past to enable the addin to fire the sProc from my XP client machine.Any ideas to resolve this problem? The only thing really different is the "client" machine (SQL instance, sproc, and addin code all the same). |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-18 : 19:33:07
|
| Show us the output of SELECT @@ VERSION on both machines (remove the space between @@ and VERSION, my company blocks certain things). Is the Windows 2003 server up to date for service packs and hotfixes? I suspect you are encountering a bug in either SQL or Windows. It was a bug in SQL Server 7.0: http://support.microsoft.com/default.aspx?kbid=266008.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jjamjatra
Starting Member
13 Posts |
Posted - 2014-06-19 : 10:47:53
|
| Here is the output from SELECT @@VERSON (for my SQL server instance where the sProc resides):Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (Intel X86) Aug 22 2012 15:16:00 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) (VM) Sorry, but I don't see how to get the same from my "client" machine (which happens to be Win2003Server but has no SQL instance running that is being used). Please clarify. The Windows maintenance there is pretty up to date. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-19 : 15:20:02
|
| Oh gotcha, I misunderstood. I thought both machines had SQL Server installed and you were trying to duplicate something setup locally over to something on a server. I don't have the answer, and I think this is going to be a challenge to figure out since Windows 2003 is no longer supported. Even the SQL version is quite a bit out of date. 2008 is losing support very soon as is 2008 R2. I did see a link that suggested this could be a permissions issue. Just as a test, try an account that has sysadmin.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
jjamjatra
Starting Member
13 Posts |
Posted - 2014-06-20 : 09:59:53
|
| Thanks for your time and interest here. I posted elsewhere and got a small clue which led to a great article. Some work involved but here are the details:http://stackoverflow.com/questions/24296644/ad-hoc-access-to-oledb-provider-denied-after-trying-vba-addin-on-different-cli/24329003#24329003This problem is resolved. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Ad hoc access to OLE DB provider denied