January 16, 2008 at 8:23 am
I'm having problems with the openrowset command in SQL Server 2005 stated below...
1. Create a new Excel file at C:\ExcelFile.xls and enter values ColumnName1, ColumnName2, ColumnName3 respectively on the first row.
2. Open up SQL Server 2005 Management studio and issue the following query
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;IMEX=1;Database=C:\ExcelFile.xls', 'SELECT * FROM [Sheet1$]')
select 1, 2, 3
I get the following error:...
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.Jet.OLEDB.4.0]".
I can select data from openrowset as expected, but I cannot insert, update or delete from it. According to MSDN's documentation you should be able to.
Does anybody have a fix for this?
Big THANKS in advance.
January 16, 2008 at 10:28 am
Check the properties of C:\ExcelFile.xls and confim that it is not marked read only.
January 16, 2008 at 10:31 am
Thanks for responding.
The file is not read-only.
January 16, 2008 at 11:05 am
try using this syntax:
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\ExcelFile.xls;',
'SELECT * FROM [Sheet1$]')
select 1, 2, 3
GO
January 16, 2008 at 11:14 am
As an aside I was recieving the same error as you until I made HDR=NO and IMEX=0
January 16, 2008 at 11:19 am
Excellent. It works now. It looks like the HDR and IMEX settings were throwing it off.
Thanks so much for your help. You've saved me a lot of time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply