SQL Server stored procedure not able concat varchar at input parameter [duplicate]

Question

I trying to insert data from a database into multiple reports. I would like to made the destination file name to be dynamic, which was a variable. But when I replace the file name with variable, I get a syntax error.

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=D:\Report\PeopleCounter\myDynamicFileName.xlsx;',
'SELECT * FROM [Sheet1$]')
    select * from myresult;

What would be the easy way to fix this?

Be aware the T-SQL is a very crufty language. If the syntax diagram only shows a literal string as a possibility, that usually means that only a literal string is allowed. Not variables. Not expressions. Indeed, the remarks lower on that page state it doesn't accept variables. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 8, 2017 at 6:50

marc_s · Accepted Answer · 2017-12-08 08:57:22Z

1

Use a dynamic query

DECLARE @value NVARCHAR(MAX)
SET @value = 'Excel 12.0;Database=D:\Report\PeopleCounter\myDynamicFileName.xlsx;'

DECLARE @a NVARCHAR(MAX)
SET @a = 'insert into OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', 
''' + @value + ''',
''SELECT * FROM [Sheet1$]'')
    select * from myresult;'

PRINT @a
EXEC @a

Print @a result:

    insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=D:\Report\PeopleCounter\myDynamicFileName.xlsx;',
    'SELECT * FROM [Sheet1$]')
     select * from myresult;

edited Dec 8, 2017 at 8:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 8, 2017 at 7:03

I A Khan

8,92716 gold badges57 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

SQL Server stored procedure not able concat varchar at input parameter [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related