Bulk Insert of excel sheet

I need to bulk insert a excel sheet into a sql 2005 db datatable. I have to do this with three different excel files, inserting them into three different tables (each excel file has one sheet). This works like a charm for two of them, one excel file is causing troubles, as data types of the columns of the inserted data sheet are 'ntext'. This ntext declaration is causing problems within my app where I access that table.

So, any idea where I can set what datatype the columns of an inserted excel sheet should be within the sql datatable? I need the columns to be varchar(255) as it is by doing this with the two other excel files. The excel file causing troubles is being generated by another app.

Any help would be much appreciated!

t-sql code:

USE KOMAX
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',True
EXEC sp_dboption Komax, 'ansi_nulls',True
EXEC sp_dboption Komax, 'ansi_warnings',True
GO

-- Delete existing Table
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Messages')
DROP TABLE Messages
-- Insert Excel Sheet
SELECT * INTO Messages FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\Messages.XLS', [Messages$])
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',False
EXEC sp_dboption Komax, 'ansi_nulls',False
EXEC sp_dboption Komax, 'ansi_warnings',False
GO

Best Regards
Benjamin

[1452 byte] By [Capone] at [2008-1-2]