OLE DB error

21 Oct 2005 7:34 AM

Squirrel

I have a stored procedure that consists 4 set of statements (delete rows and
then insert rows from excel files with openrowset). It returns error when
execute the 4th insert statement. The error message is:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].

It¡¦s strange, I think, that there is no file in the error message... BTW,
the stored procedure can be executed successfully if any one insert
statement is commented. Does openrowset or MS Jet database engine has any
limitation? Could anyone please tell me how can I solve this issue?

Any help would be appreciated.

P.S. I'm running SQL Server 2000

SC

21 Oct 2005 7:43 AM

Enric

hi Squirrel,
Could you please be so kind to post the aforementioned stored procedure here?

Show quote

"Squirrel" wrote:

> I have a stored procedure that consists 4 set of statements (delete rows and
> then insert rows from excel files with openrowset). It returns error when
> execute the 4th insert statement. The error message is:
>
>
>
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>
> [OLE/DB provider returned message: The Microsoft Jet database engine cannot
> open the file ''. It is already opened exclusively by another user, or you
> need permission to view its data.]
>
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
>
>
>
> ItÂ¡Â¦s strange, I think, that there is no file in the error message... BTW,
> the stored procedure can be executed successfully if any one insert
> statement is commented. Does openrowset or MS Jet database engine has any
> limitation? Could anyone please tell me how can I solve this issue?
>
>
>
> Any help would be appreciated.
>
>
>
> P.S. I'm running SQL Server 2000
>
>
>
> SC
>
>
>

21 Oct 2005 8:02 AM

Squirrel

here. thanks.

CREATE PROCEDURE convert_data
@userid varchar(8)
as

BEGIN TRANSACTION UpdateAll

DELETE table1

INSERT INTO table1
SELECT id, name, cat, getdate(), @userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table1.xls', 'select * from [sheet1$]')

DELETE table2

INSERT INTO table2
SELECT id, serial, add_1, add_2, add_3, getdate(), @userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table2.xls', 'select * from [sheet1$]')

DELETE table3

INSERT INTO table3
SELECT table1_id, table2_id, serial, type, amount, getdate(), @userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table3.xls', 'select * from [sheet1$]')

DELETE table4

INSERT INTO table4
SELECT code, num, description, getdate(), @userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table4.xls', 'select * from [sheet1$]')

COMMIT TRANSACTION UpdateAll

GO

Show quote

"Enric" <En***@discussions.microsoft.com> wrote in message
news:EC55EB71-31E0-4A15-8BAA-5A04C45E0640@microsoft.com...
> hi Squirrel,
> Could you please be so kind to post the aforementioned stored procedure
> here?
>
> "Squirrel" wrote:
>
>> I have a stored procedure that consists 4 set of statements (delete rows
>> and
>> then insert rows from excel files with openrowset). It returns error
>> when
>> execute the 4th insert statement. The error message is:
>>
>>
>>
>> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>>
>> [OLE/DB provider returned message: The Microsoft Jet database engine
>> cannot
>> open the file ''. It is already opened exclusively by another user, or
>> you
>> need permission to view its data.]
>>
>> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>> IDBInitialize::Initialize returned 0x80004005: ].
>>
>>
>>
>> It!|s strange, I think, that there is no file in the error message...
>> BTW,
>> the stored procedure can be executed successfully if any one insert
>> statement is commented. Does openrowset or MS Jet database engine has
>> any
>> limitation? Could anyone please tell me how can I solve this issue?
>>
>>
>>
>> Any help would be appreciated.
>>
>>
>>
>> P.S. I'm running SQL Server 2000
>>
>>
>>
>> SC
>>
>>
>>