SQL Server syntax error when using OPENROWSET?

Question

I'm trying to write this as part of stored procedure on SQL Server 2000. I keep getting a syntax error thrown on line starting Insert into OPENROWSET(.....

Help! I can't see the syntax error!

DECLARE @vDate Varchar(25) 
DECLARE @vCommand nvarchar(1000) 
DECLARE @fileName varchar(500)  

SET @vDate = LEFT(DATENAME(month, DATEADD(m, -1, GETDATE())), 3) + DATENAME(year, DATEADD(m, -1, GETDATE()))

SET @fileName = '\\SERVER\folder\subfolder\subfolder\Excel\JobRerun\JobRerun_' + @vDate + '.xls'

SET @vCommand =  'copy \\SERVER\folder\subfolder\subfolder\Excel\JobRerun\JobRerunTemplate.xls ' + @fileName

Exec master..xp_cmdshell @vCommand , NO_OUTPUT

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;Database=' + @fileName + ';', 
   'SELECT * FROM [RerunData$]')  

Answer 1

Your INSERT statement is incorrect to me - you have INSERT INTO OPENROWSET(.... That would imply that you're trying to insert into the OPENROWSET query, but you're supplying "SELECT *..." within it. It should resemble:

INSERT INTO your_table
SELECT * FROM OPENROWSET(...