Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trapping errors from OpenDataSource

Posted on 2004-08-31
3
Medium Priority
?
419 Views
Last Modified: 2008-02-01
Hello,

I'm importing excel spreadsheets into a Sql Server database.   There are two different types of spreadsheets --lets call them type A and type B -- with spreadsheets of type B having some column names which are different than those of type A.  Unfortunately, I have no way of knowing which type of spreadsheet is being read before actually issuing a SELECT call on the spreadsheet.  

Because of this, when I attempt to select a column which is not in spreadsheet, OpenDataSource errors out and I don't know how to trap the error to issue another SELECT for the other spreadsheet type.

So: 1) is there a way to trap OpenDataSource results and, 2) if not, is there a way to determine column names (say with sp_columns) on a data source opened with OpenDataSource?


Here is a working example of my OpenDataSource call:

set @SQLString = N'SELECT @foo = Due, @boo = Comments
FROM     OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + @FileName + '";User ID=Admin;Password=;Extended properties=Excel 8.0'+'''' + ')...importRange'

SET @ParmDefinition = N'@foo varchar(30) OUTPUT, @boo varchar(100) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@foo=@result OUTPUT,
@boo=@comments OUTPUT
SELECT @comments

which fails when the spreadsheet doesn't have a 'comments' column.

Thanks!
0
Comment
Question by:pennypiper
  • 2
3 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11948191
I think I would insert the rows into a temp table first and then use the COL_NAME function to determine what colums you have to work with.

Brett
0
 
LVL 12

Accepted Solution

by:
kselvia earned 2000 total points
ID: 11959711
--I don't know if I think this is any better than selecting the results into a temp table or not. I just wanted to figure out
--how to do it, so here it is. :)  If you can create a linked server.

EXEC master..sp_addlinkedserver 'MyBook1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', @filename , NULL, 'Excel 8.0', NULL

--Create temp table for spreadsheet meta-data
create table #tmp_columns
      (      TABLE_CAT sysname collate database_default NULL,
            TABLE_SCHEM sysname collate database_default NULL,
            TABLE_NAME sysname      collate database_default NOT NULL,
            COLUMN_NAME sysname collate database_default NULL,
            DATA_TYPE smallint NOT NULL,
            TYPE_NAME sysname  collate database_default NULL,
            COLUMN_SIZE int NULL,
            BUFFER_LENGTH int NULL,
            DECIMAL_DIGITS smallint NULL,
            NUM_PREC_RADIX smallint NULL,
            NULLABLE smallint NOT NULL,
            REMARKS nvarchar(255) collate database_default NULL,
              COLUMN_DEF nvarchar(128) collate database_default NULL,
              SQL_DATA_TYPE smallint null,
              SQL_DATETIME_SUB smallint NULL,
            CHAR_OCTET_LENGTH int NULL,
            ORDINAL_POSITION smallint,
            IS_NULLABLE varchar(254) collate database_default NOT NULL,
            SS_DATA_TYPE tinyint null)

--Extract info.  See sp_tables_ex for other info available
INSERT #tmp_columns
      exec sp_columns_ex  'MyBook1'

IF (select count(1) from #tmp_columns where column_name = 'Comments') = 1
BEGIN      

--Once you have a linked server you can query it like any other datasource.

      SELECT @foo = Due, @boo = Comments from [MyBook1]...[importrange]

END

EXEC master..sp_dropserver 'MyBook1'
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12013131
Thanks! You put me on the Hall of Fame.  At the rate BillAn1 goes though, it will only be for a few hours but at least I was there :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question