Solved

Trapping errors from OpenDataSource

Posted on 2004-08-31
3
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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