Re: nested cursor declarations through dynamic sql
From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 04/14/04
- Next message: Wayne Snyder: "Re: Created a computed column that references itself"
- Previous message: Sunanda: "OpenDataSource"
- In reply to: Josh White: "nested cursor declarations through dynamic sql"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 14 Apr 2004 13:21:57 +0100
Looks to me like it is something you can easier do with DTS. Have a look at
the following article http://www.sqldts.com/?246 and see if you can adjust
the code to use the list you get from the spreadsheet instead of looping
through all the files in the directory, as that example does now.
--
Jacco Schalkwijk
SQL Server MVP
"Josh White" <whitegoose@inorbit.com> wrote in message
news:aafea0a8.0404140003.3dd5482b@posting.google.com...
> OK... I need to get a list of file names from an Excel spreadsheet. As
> the file name and path etc may change I have made them variables and
> declared a cursor that uses opendatasource() to get the file names out
> of the xls.
>
> For each of the values retrieved, (in this case an example value is
> 'TBL3010'), I need to open another excel spreadsheet with the same
> name (in this case, 'TBL3010.xls') and get all the values out of this
> spreadsheet. These values will then be inserted into a table with the
> same name again, but I haven't got that far yet.
>
> When using opendatasource(), the only way to supply variables as the
> parameters is to use dynamic SQL, so I am currently building an sql
> statement that declares a cursor that selects the data from the xls
> with the same name as the value retrieved from the outer while loop
> cursor.
>
> However when I trey to exec() the statement I get the following error:
> Server: Msg 7302, Level 16, State 1, Line 3
> Could not create an instance of OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0'.
> OLE DB error trace [Non-interface error: CoCreate of DSO for
> Microsoft.Jet.OLEDB.4.0 returned 0x80040e73].
>
> I'm guessing its something to do with the scope of the cursor's
> clashing, but if anyone can offer any advice on how to correct my
> code, or maybe even a better way of achieving what i need, I would
> greatly appreciate it.
>
> I'm running SQL Server 2000 with SP3 on Windows XP.
>
> My code is below...
>
> -----------------------------------------------
> DECLARE
> @codeListExcelPath VARCHAR(254),
> @excelListName VARCHAR(100),
> @tableName VARCHAR(100),
> @code VARCHAR(100),
> @desc VARCHAR(30),
> @comments VARCHAR(254),
> @dataProvider VARCHAR(50),
> @conString VARCHAR(500),
> @sqlListDeclare VARCHAR(500),
> @sqlCodeValDeclare VARCHAR(500),
> @sqlCodeValSet VARCHAR(500)
>
>
> SET @codeListExcelPath = 'c:\Documents and Settings\joshw\Desktop\'
> SET @excelListName = 'codelist.xls'
> SET @dataProvider = 'Microsoft.Jet.OLEDB.4.0'
> SET @conString = 'Data Source=' + @codeListExcelPath + @excelListName
> + ';Extended Properties=Excel 8.0'
>
> -- this will declare the cursor for the outer loop (that gets the file
> -- names)
> SET @sqlListDeclare = '
> DECLARE tableNames_cursor CURSOR FOR
> select tablename
> from opendatasource(''' + @dataProvider + ''',
> ''Data Source="' + @codeListExcelPath + @excelListName
> + '";Extended Properties=Excel 8.0'')...Sheet1$'
>
> EXEC(@sqlListDeclare)
>
>
> OPEN tableNames_cursor
>
> FETCH NEXT FROM tableNames_cursor INTO @tableName
>
> WHILE @@Fetch_status <> -1
> BEGIN
> PRINT 'Processing code table ' + @tableName + '...'
>
> -- this declares the cursor that should retrieve data from the file
> name
> -- returned by the outer cursor. I have checked and the sql built here
> is
> -- fine.
> SET @sqlCodeValDeclare = '
> DECLARE codeValues_cursor CURSOR GLOBAL FOR
> select code, [description]
> from opendatasource(''' + @dataProvider + ''',
> ''Data Source="' + @codeListExcelPath + @tableName
> + '".xls;Extended Properties=Excel
> 8.0'')...Sheet1$'
>
> -- this exec raises the error. if removed everything works fine
> EXEC(@sqlCodeValDeclare) --cursor is now pointing to appropriate xls
>
> CLOSE tableNames_cursor
> DEALLOCATE tableNames_cursor
>
> FETCH NEXT FROM tableNames_cursor INTO @tableName
> END
>
> CLOSE tableNames_cursor
> DEALLOCATE tableNames_cursor
> ---------------------------------------------------------------
>
> Thanks heaps,
> Josh.
- Next message: Wayne Snyder: "Re: Created a computed column that references itself"
- Previous message: Sunanda: "OpenDataSource"
- In reply to: Josh White: "nested cursor declarations through dynamic sql"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
