How to enable Ah Hoc Query in SQL 2000

Hi guys

I have a program which imports data into sql server from csv files. This program uses the something like this

CREATE PROCEDURE MyImport

(

@.FileName AS VARCHAR(200)

)

AS

SET NOCOUNT ON

DECLARE @.SQL VARCHAR(4000)

SET @.SQL = ' SELECT *

FROM OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\;'', ''SELECT * FROM ' + QUOTENAME(@.FileName) + '' + ''') as t

'

EXEC (@.SQL)

when i excute this i get a permission error saying the account i specified in the web config doesn't have rights to run these type of scripts. I tried using the sa account which works. But its not recommended to setup applications to access the database using the sa account.

i did some research and i found out that yon can actually enable that by change a registry key. But i cant find the MSDASQL datasource under ms sql server providers in the registry.

Any other ways i can enable this?

Have a look at http://www.asp101.com/articles/john/connstring/default.asp|||

Excellent article. Will give it a go at it on monday.

Thanks

|||

Hi TATWORTH

The link you provided was good but not what im looking for. If you read my post carefully you will know. I have a stored procedure which will select from a file passed in as a parameter using theMSDASQL provider. NOW THIS IS ALL DONE IN SQL not in my app.

I was saying that the account i specified in the web.config does not have permissions to use the MSDASQL provider inside of SQL. The account i used only have rights to run stored procedures and that is it. But my script is written inside of a stored procedure. The reason for not being able to run the script inside of the procedure is because this procedure produces a dynamic script and executes that inside of the stored procedure.

i heard you can enable a registry key which will enable the use of that provider, but i cant find theMSDASQLprovider in the registry.

i was looking for a way to grant permissions to (web_user account) execute these type of queries.

Hope this cleared things up

Cheers