The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error

USE [PreMfg]
GO
/****** Object:  StoredProcedure [dbo].[procAddOrderItem]    Script Date: 08/11/2011 10:28:21     ******/
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[procExcelQuotebyItem] 

(   
      @OrderNumber INT
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)

--  New File Name to be created

SET @fn = '"D:\Pre-Manufacturing\Quote by Item.xls"'

--  FileCopy command string formation

SELECT @Cmd = 'Copy "D:\Pre-Manufacturing\Quote by Item (Excel) Template.xls" ' + @fn

--  FielCopy command execution through Shell Command

EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT

    SET @provider = 'Microsoft.Jet.OLEDB.4.0'
    SET @ExcelString = 'Excel 8.0;Database=' + @fn

--  Executing the OPENROWSET Command for copying the select contents to Excel sheet.

exec('INSERT INTO OPENROWSET(''' + @provider + ''',''' + @ExcelString + ''',''SELECT [ITEM NUMBER],[PHOTO],[DESCRIPTION],[CASE PACK],[PIECE PRICE],[CASE PRICE],[WT],[CUBE],[OUTSIDE CASE DIMENSIONS],[UPC#],[CASE UPC#] FROM [Sheet1$]'') 
SELECT [ItemNumber],''' + ' ' + ''',[Item_Description],[Casepack],[Unit Price],[Case Price],[Weight],[Cube],[Case Dims],[UPC],[Case UPC] FROM [Order Summery] WHERE [Order #] = ' + @OrderNumber + '')

SET NOCOUNT OFF

END

---

USE [PreMfg]
GO
/****** Object:  StoredProcedure [dbo].[procAddOrderItem]    Script Date: 08/11/2011 10:28:21 ******/
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procExcelQuotebyItem] 
(
    @OrderNumber INT
)
AS

BEGIN
SET NOCOUNT ON


DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)
DECLARE @SendMail varchar(100)

--  New File Name to be created
    SET @fn = 'D:\Pre-Manufacturing\QuotebyItem.xls'


--  FileCopy command string formation
    SET @Cmd = 'Copy D:\Pre-Manufacturing\QuotebyItemTemplate.xls ' + @fn

--  FileCopy command execution through Shell Command
    EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT

--  Mentioning the excel destination filename
    SET @provider = 'Microsoft.Jet.OLEDB.4.0'
    SET @ExcelString = 'Excel 8.0;Database=' + @fn

    EXEC('INSERT INTO OPENROWSET(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$A2:K2]'') 
    SELECT [ITEMNUMBER],[ORDER #],[ITEM_DESCRIPTION],[CASEPACK],[UNIT PRICE],[CASE PRICE],[WEIGHT],[CUBE],[CASE DIMS],[UPC],[CASE UPC] FROM [ORDER SUMMERY] WHERE [Order #] = ''' + @OrderNumber + '''')

    /* Attach the file to an email and send (the sp_send_dbmail won't accept variables in the arguments, so you have to build it with some double quotes first, then exec the whole string*/

    EXEC msdb.dbo.sp_send_dbmail 
    @recipients= 'user@domain.com', 
    @subject = 'Auto-Generated Quote by Item Report', 
    @body = 'Quote by It开发者_如何学Pythonem Report attached', 
    @body_format = 'HTML', 
    @file_attachments = @fn

    /*Cleanup*/

    SET @Cmd = 'DEL ' + @fn
    EXEC xp_cmdshell @Cmd, no_output

    SET NOCOUNT OFF
END

---

Try changing:

SET @ExcelString = 'Excel 8.0;Database=' + @fn

to:

SET @ExcelString = 'Excel 8.0;Data Source=' + @fn +';'

Not 100% sure on the trailing semi-colon so try it with and without

---

Sometimes SQL Server gets it's knickers in a twist over importing and it's potentially not your fault. My code was working one day but not the next with no changes. Very frustrating!

Make sure you run this against the DB you are having problems with (it's not a Master DB command):

DBCC FREEPROCCACHE

I randomly tried this and it seemed to reset something.