Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Trapping errors from OpenDataSource

Posted on 2004-08-31
3
Medium Priority
?
420 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:
Ken Selvia 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:Ken Selvia
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

580 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