Cannot insert/update OPENROWSET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cannot insert/update OPENROWSET

Author

Topic

iDev
Starting Member

8 Posts

Posted - 2008-01-16 : 10:34:00

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.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-16 : 11:02:56

Is the excel file set to read-only. Also make sure excel file is closed when you are performing INSERT operation.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-17 : 00:37:03

See if you find answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

iDev
Starting Member

8 Posts

Posted - 2008-01-24 : 20:48:23

Thank you all for your replies. Someone from another forum helped me with this issue. The error was due to setting IMEX=1 for inserts. IMEX=1 should only be used for selects as it's for import only. If I had set IMEX=0 or IMEX=2, the original code above would execute successfully.

Thanks again.

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-02 : 09:06:18

Thanks for posting the solution, it was a good help for me too.

quote:
Originally posted by iDev

Thank you all for your replies. Someone from another forum helped me with this issue. The error was due to setting IMEX=1 for inserts. IMEX=1 should only be used for selects as it's for import only. If I had set IMEX=0 or IMEX=2, the original code above would execute successfully.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources