Use local variable in OPENROWSET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use local variable in OPENROWSET

Author

Topic

rferreira
Starting Member

1 Post

Posted - 2009-09-24 : 14:02:57

Hi!

I'm a newbie in T-SQL therefore I'm having a doubt regarding the use of local variable as a parameter os OPENROWSET.

I'm trying to load data to SQL Server (Express 2005) from an Excel sheet. Hence I tried the following script:

DECLARE @excelDoc varchar(85)
SET @excelDoc = 'Excel 12.0;Database=C:\myDoc.xls'
INSERT INTO dbo.IdentifiersAssociations
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', @excelDoc , 'SELECT * FROM [IdentifiersAssociations$]')

which gives me the following error:
Incorrect syntax near '@excelDoc'.

I can make this work if I do:
DECLARE @statement varchar(200)
SET @statement = 'INSERT INTO dbo.IdentifiersAssociations SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', @excelDoc , ''SELECT * FROM [IdentifiersAssociations$]'') '
EXEC (@statement)

Can anyone explain me why do I need to use EXEC statement? Can't I use the local variable directly in OPENROWSET?

Thanks for your help.
Ricardo

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-25 : 03:17:50

Becuase object name comes from variable
Read this to know when you should use dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources