update OPENROWSET('Microsoft.Jet.OLEDB.4.0',<br />'Excel 8.0;Database=CrojectsArchived.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 />
Do you have an explicit transaction inside the procedure? Roji. P. Thomas http://toponewithties.blogspot.com
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.
Try removing the BEGIN TRANSACTION-ROLLBACK/COMMIT IIRC, Access Oledb provider does not support distributed transactions. Roji. P. Thomas http://toponewithties.blogspot.com
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.
