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$]')

I'm trying to reference a filename that will be different each time the procedure runs (once a month). — Marc
– Marc, Commented Aug 2, 2010 at 16:43

Per-Frode Pedersen · Accepted Answer · 2010-08-03 08:58:49Z

1

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$]''')

edited Aug 3, 2010 at 8:58

answered Aug 2, 2010 at 16:33

Per-Frode Pedersen

1,0271 gold badge6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marc Over a year ago

This has got to be the problem! But I tried doing it on a separate statement and I got the same error there too. How else could I correct?

Marc Over a year ago

I'm not experienced with dynamic sql, can you point me in the right direction?

Per-Frode Pedersen Over a year ago

Create your statement as string, and execute using the exec() function, like this: exec ('INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''@providerstring + ''', ''SELECT * FROM[RerunData$]''')

Marc Over a year ago

Your right, the inline concat is what confused it. A) That SHOULD be allowed. B) I got around it by copying my template to a temporary file which always has the same name, then inserting into that, and copying that file to give it the correct name with date.

OMG Ponies · Accepted Answer · 2010-08-02 16:29:07Z

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(...

answered Aug 2, 2010 at 16:29

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

3 Comments

Marc Over a year ago

I'm trying to copy data from sql into the excel spreadsheet. I copied this code from a working example and I'm trying to modify it to suit my needs.

Marc Over a year ago

The syntax I've seen working is INSERT INTO OPENROWSET(<params>) <select statement>

OMG Ponies Over a year ago

@Marc: The INSERT statement reads as trying to insert into the Excel spreadsheet, without any selection from the database table. Based on the details, I don't think this is what you're after.

Collectives™ on Stack Overflow

SQL Server syntax error when using OPENROWSET?

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related