HOW TO PASS PARAMETER IN OPENDATASOURCE FOR EXCEL

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 2000 Forums

Transact-SQL (2000)

HOW TO PASS PARAMETER IN OPENDATASOURCE FOR EXCEL

Author

Topic

kd12345
Starting Member

12 Posts

Posted - 2008-01-08 : 11:21:04

I've been trying to use the parameters passed to a stored procedure then using hard codeded value for the OPENDATASOURCE function. I keep getting Incorrect syntax near '+'

here is my code.

DECLARE cursor1 CURSOR FOR
SELECT distinct RTRIM(OPRID) FROM TEMP_TABLE
OPEN cursor1
declare @OPRID1 varchar(18)
FETCH NEXT FROM cursor1 INTO @OPRID1;
WHILE (@@FETCH_STATUS <> -1)
BEGIN

insert into OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\Book1_01-08-08.xls";Extended properties=Excel 8.0')...+ @OPRID1 + '$'
(col1, col2, col3)
select col1, col2, col3 from TEMP_TABLE WHERE OPRID = @OPRID1
end
FETCH NEXT FROM cursor1 INTO @OPRID1;
CLOSE cursor1
DEALLOCATE cursor1

it doesnot like the Opendatasource(....)...+ @OPRID1 + '$'
For example @OPRID1 is passing "zzz1' and i want to insert the value in "zzz1' excel tab. Excel file has "zzz1" tab.

Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-08 : 11:24:24

nope. that doesn't work.
you'll have to do your insert with dynamic sql
exec(' insert into ... ' + @oprid1 + '...')

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources