OpenDataSource and Dynamic SQL Question

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)

OpenDataSource and Dynamic SQL Question

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2004-06-22 : 03:09:58

Hi.
I am trying to read from an excel file in an SP. I want to insert the result set into a temporary table. The file name must be paramterized. The problem i have now is that the temp tables is not in the scope of the dynamic execution. How can I do that?..
Thanks In Advance
The code is as follows:


DECLARE @Qry VARCHAR(1000)
CREATE TABLE #Temp(...)
SET @Qry = 'INSERT INTO #Temp
SELECT T.*
FROM	OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + @FileName+ '";User ID=Admin;Password=;Extended properties=Excel 8.0'+'''' + ')...sheet1$'

EXEC(@Qry)
SELECT * FROM #TempZipCode
DROP TABLE #TempZipCode

chadmat
The Chadinator

1974 Posts

Posted - 2004-06-22 : 15:21:51

You can either put it all in the Dynamic string (The Temp table creation too). Or you can use a global temp table ##Temp.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-22 : 23:33:08

In your example #Temp should be in the scope of the EXEC. You should be able to do what you are doing. What error are you getting?

SELECT T.* will cause an error unless you alias the query like: ...sheet1$ T'

Also, you create #Temp and select from #TempZipCode. Aside from those typos, it should work.

And even if it did not work, changing it to:

SET @Qry = 'SELECT T.* ....'

INSERT INTO #Temp
EXEC(@Qry)

should also work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources