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
