November 2, 2007 at 11:57 am
I am having a difficult time figuring this one out. I have a stored procedure that as part of an ASP.Net app connects to a csv file through a mapped network drive and imports the data into a table in SQL Server. Here it is.
------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_ImportComp]
-- Add the parameters for the stored procedure here
@CSV as varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin Try
Begin Tran
Declare @sql as nvarchar(max)
Set @sql = 'Insert into dlrComp (SN,
CM,
PM,
MC,
Comp_Total,
XU,
GM,
From_Date,
To_Date)
Select[Serial Number],
CM,
PM,
MC,
[Comp Total],
XU,
GM,
[From Date],
[To Date]
FROM OPENROWSET(' + char(39) + 'MICROSOFT.JET.OLEDB.4.0' + char(39) + ',' + char(39) + 'Text;Database=S:\' + char(39) + ',' + char(39) + 'SELECT * FROM ' + @csv + char(39) + ')'
--print @sql
Exec sp_executesql @stmt=@sql
Commit Tran
End Try
Begin Catch
Rollback Tran
--Do some logging and stuff here
End Catch
END
------------------------------------------------------------------------------------
If I am connected to SQL through SQL Management Studio while logged in on the server that is running SQL as Domain\User1 and execute
exec usp_ImportComp @CSV='Comp.csv'
It completes successfully
However if I open SSMS (while logged into Windows on my PC as Domain\User2) using runas to run it as Domain\User1 while logged into my PC and connect to SQL Server using WIndows Auth and run the same I get the following error message.
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'S:\' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
If I add
With Execute as 'Domain\User1' and modify the stored procedure I get the same error message above.
If I log onto the Server that is running SQL as Domain\User2 I can successfully run
exec usp_ImportComp @CSV='Comp.csv'
Both User1 and User2 have the same permissions to the Share and csv as does the Domain user under whose context SQL Server is running.
What am I doing wrong?
November 5, 2007 at 8:04 am
This is still baffling me. Here is some more info. If I add a linked server like so
EXEC sp_addlinkedserver imports, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'S:\',
NULL,
'Text'
or
EXEC sp_addlinkedserver imports, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\server\share',
NULL,
'Text'
and then attempt to query the csv file like so.
Select * from imports...File1#csv
Everything works great when I am logged into the Server Running SQL (as user1) and connected to SQL via SSMS using Windows Auth
However, when I am logged into my local PC (as User1) and connected to the SQL Server via SSMS using Windows Auth and I run the exact same line I get.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "txtsrv" returned message "'S:\' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
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 "imports".
Same thing when using the \\server\share unc path.
I am lost so any help anyone could provide will be greatly appreciated.
November 5, 2007 at 8:47 am
Does user1 have access to this share from your local PC or is it only available from the server? User1 needs to be able to see the share from wherever the code is being run.
November 5, 2007 at 8:54 am
Yes, User1 can see the share and read the file from both my PC and from the server. In fact I have explicitly granted all users full permissions to both the share and the file itself and still get the same error.
October 20, 2009 at 8:31 am
Hi, Did this ever get resolved. I am having the exact same issue?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply