Ad Hoc Queries

[JaedenRuiner]

here is my statement:

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\db\rexam.xls', [Sheet1$])

Now, from Management Studio Express when I execute that statement, it works fine, I've set up the ad-hoc in the Surface Area Configuration tool, and tested it and then everything seemed to be fine.

Now, in my VB.Net application i use my connection to create a SqlCommand, and then I set the commandtext to the same string. using an untyped dataset and tableadapter, I set the ta.selectcommand = to my sqlcommand, and attempt to fill the dataset. If successful, I set my bindsource to the dataset and the DGV repopulates with my run-time executed sql code. (provided by my user entry in a multi-line textbox).

however, the same user account as I've used in the Managment Studio to execute the Ad-Hoc query, generates this exception error message while in my vb.net application:

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

I've checked the Server Objects\Linked Servers\Providers information for the Jet.Oledb provider and "Disallow Ad-Hoc Access" is unchecked[/u], so where would the permission for ad hoc access be denied to my application? User DB role, server role, etc? Why would it work from Management Studio but not via ADO.Net?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JaedenRuiner]

Nevermind. It appears to be a registry setting that "defaults to 1" if the setting isn't present, yet for some reason the Management Studio ignores that flaw, while ADO.Net doesn't. go figure.

Thanks
Jaeden "Sifo Dyas" al'Reac Ruiner