CODEWITHSUNDEEP
sqlsql-servert-sqlsyntax
Marc
Marc

Reputation: 29

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!

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

Upvotes: 1

Views: 2049

Answers (2)

Per-Frode Pedersen
Per-Frode Pedersen

Reputation: 1027

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

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

Upvotes: 1

Related Questions