SQL Server Performance

'Microsoft.Jet.OLEDB.4.0' error

Discussion in 'General DBA Questions' started by Vibhuti, Oct 4, 2006.

  1. Vibhuti New Member

    update OPENROWSET('Microsoft.Jet.OLEDB.4.0',<br />'Excel 8.0;Database=C:projectsArchived.xls;HDR=yes',<br />'SELECT * FROM [Project$] where ProjectId like 97')<br />set ProjectId=null,ProjectName=null<br /><br />I am using following query. Its executing fine on Sql Analyser. But same when I am trying to execute in Stored procedure its giving error as<br /><br /><font color="red">Server: Msg 7390, Level 16, State 1, Procedure RestoreArchivedProjects, Line 81<br />The requested operation could not be performed because the OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not support the required transaction interface.<br />OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IUnknown:<img src='/community/emoticons/emotion-41.gif' alt=':Q' />ueryInterface returned 0x80004002].</font id="red"><br /><br />I dont understand where I am wrong. Please help me..<br /><br />Thanks and Regard<br />Amit Vibhuti<br /><br />"Dream is not what you see in sleep, Dream is thing that doesn't let you sleep." So Keep Dreaming.<br />
  2. Roji. P. Thomas New Member

  3. Vibhuti New Member

    Sorry! didnt get u! When this explicit transition comes into play..

    This is the procedure I am using...

    SET NOCOUNT ON
    DECLARE @Cmd varchar(1000)
    DECLARE @provider varchar(100)
    DECLARE @ExcelString varchar(100)
    DECLARE @InsertString varchar(100)
    set @ArchivePath='\' + @ArchivePath + 'ArchiveTemplate'
    SET @Cmd='if not exist C:' + @File_Name + ' Copy ' + @ArchivePath + @File_Name + ' C:' + @File_Name
    EXEC MASTER..XP_CMDSHELL @Cmd, NO_OUTPUT
    set @provider = 'Microsoft.Jet.OLEDB.4.0'
    set @ExcelString = 'Excel 8.0;Database=C:' + @File_Name + ';HDR=yes'

    BEGIN TRANSACTION
    set @Cmd='SELECT * FROM [Project$] where ProjectId like ' + convert(varchar(25),@Id)
    exec('update OPENROWSET(''' + @provider + ''', ''' + @ExcelString + ''', ''' + @Cmd + ''') set ProjectId=null')

    IF @@ERROR != 0
    BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('There was an error here.', 11, 1)
    RETURN
    END
    ELSE
    --ROLLBACK TRANSACTION
    COMMIT TRANSACTION

    SET NOCOUNT OFF

    END
    GO

    Thanks and Regard
    Amit Vibhuti

    "Dream is not what you see in sleep, Dream is thing that doesn't let you sleep." So Keep Dreaming.
  4. Roji. P. Thomas New Member

    Try removing the BEGIN TRANSACTION-ROLLBACK/COMMIT

    IIRC, Access Oledb provider does not support distributed transactions.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  5. Vibhuti New Member

    Great... It worked. Hats off Sir. Thanks for your quick help.

    Thanks and Regard
    Amit Vibhuti

    "Dream is not what you see in sleep, Dream is thing that doesn't let you sleep." So Keep Dreaming.
  6. Roji. P. Thomas New Member

Share This Page