Error importing from Excel - SQL Server Forums

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Error importing from Excel

Author

Topic

MichelleMA
Starting Member

32 Posts

Posted - 2008-11-24 : 17:12:45

Hi, I am trying to import data from Excel, it worked on the dev server but I am getting an error when I run the script on staging, anyone has an idea why this is happening?
I tried setting the islolation level but I still get the same error(SET TRANSACTION ISOLATION LEVEL SERIALIZABLE)

Thanks.
---------------
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT *
INTO TableX FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\FileX.xls;HDR=YES',
'SELECT Column1, Column2 FROM [Sheet1$]')
COMMIT
---------------------
This is the message it returns:
---------------------------------

Server: Msg 7392, Level 16, State 2, Line 4
Could not start a transaction for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Neither the isolation level nor a strengthening of it is supported.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' ITransactionLocal::StartTransaction returned 0x8004d008: ISOLEVEL=1048576].

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-24 : 23:46:42

Why are you trying to run this query in a transaction? It's not updating anything.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-25 : 01:21:18

See what happens when you run this

SELECT *
INTO TableX FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\FileX.xls;HDR=YES',
'SELECT Column1, Column2 FROM [Sheet1$]')

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 03:39:18

You can check the connection properties to see if serializable isolation level is supported.

* Isolation Levels

A Long value (read-only) that specifies a bitmask that indicates the supported transaction isolation levels.
The value returned will be the combined value (bitwise Or) of zero or more of the following values:

Chaos                  0x10 =      16
Read Uncommitted      0x100 =     256
Browse                0x100 =     256
Cursor Stability     0x1000 =    4096
Read Committed       0x1000 =    4096
Repeatable Read     0x10000 =   65536
Serializable       0x100000 = 1048576
Isolated           0x100000 = 1048576

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources