variable worksheet name 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

SQL Server 2005 Forums

Transact-SQL (2005)

variable worksheet name in openrowset

Author

Topic

zubinbalsara
Starting Member

2 Posts

Posted - 2007-01-30 : 12:13:38

HI Guys,
Is there a way to use a variable worksheet name in the openrowset. I need to open an excel file in my cursor, my worsheet name is not constant all the time,so i need to place a variable instead. Here is a snippet from my code

declare @mysheet varchar(200)
set @mysheet='sheet1$'
Declare getDistrict cursor for
SELECT district, healthplan
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes; IMEX=1;Database=D:\DatabaseUploads\Nexium\crestor.xls',
'SELECT * FROM ['''+@mysheet+''']')
WHERE [District] IS NOT NULL
set nocount on;
open getDistrict

Thanks for all your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 14:58:13

You have to use dynamic SQL.
See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Please note that the path to the file is relative from sql server, not the client.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources