SQL Server syntax error when using OPENROWSET?

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!

  • Are the elements of an XML alwas ordered if I get them from SQL Server 2012
  • SQL Server IN vs. EXISTS Performance
  • Group by Time Interval
  • partitioning by year over historical data
  • How to create multiple columns from a delimited string
  • Convert Epoch to DateTime SQL Server
  • 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$]')  
    

    2 Solutions collect form web for “SQL Server syntax error when using OPENROWSET?”

    You can’t do inline concatenation of string like you do in that line: ‘Excel 5.0;Database=’ + @fileName + ‘;’

    Try moving that to a separate statement

    You could try something like:


    exec ('INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''' + @providerstring + ''', ''SELECT * FROM [RerunData$]''')

    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(...
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.