Linked text file with columns > 255 chars

I'm trying to create a linked server to a text file using Jet. The text file has columns with data larger than 255 characters. For some reason (I think its a limitation of Jet), when I attempt to query the said columns, SQL Server thinks the data type is ntext. This is causing a problem as I'm trying to insert these columns into corresponding columns in my SQL Server database (whose types are varchar(1000)). Is there a way I can link a text file without Jet? (or get around this restriction) Thanks

TQ1 I'm trying to insert these columns into corresponding columns in my SQL Server database (whose types are varchar(1000)) The text file has columns with data larger than 255 characters.


A1 If the business requirement amounts to simply inserting data rows from a text file source, you may wish to consider one or more of the following (Sql Server versions >= 7.0 for DTS bulk insert):

1 bulk insert,
2 bcp, or
3 DTS.

Q2 Is there a way I can link a text file without Jet?


A2 Yes.

For example, if the business requirement necessitates an external linked text file source (that may be accessed and modified by other applications), you may wish to consider a Microsoft OLE DB Provider for ODBC linked server connection instead. To do so:

Use a Microsoft OLE DB Provider for ODBC linked server connection (with an appropriate System DSN specifying the appropriate directory containing the text and schema files). Implement a LONGCHAR in the schema ini file for any char columns >255 chars wide. {The theoretical limit of the width of a LONGCHAR column in either a fixed-length or delimited table is 65500K. The Text ISAM is more likely to provide reliable support up to about 32K. The LONGCHAR is interpeted as a text type, but that doesn't perclude insertion into a varchar 1000 column.}

See ODBC Drivers text file support:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjettext_data_types.asp|||that should work nicely...

servers

I am using linked server for different sql servers. It is working perfectly. I don't have any problem with jet and excel also. when i am configuring the oracle to linked server it is adding the server perfectly. when i trying to see the tables in the linked server i am gettin the error number 7399. here i am attaching the error jpg file. The syntax used for linked server is as follows

EXEC sp_addlinkedserver
@.server = 'ORCL',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'ORCL'

The syntax for remote login is as follows

1. sp_addlinkedsrvlogin 'orcl',false,null,null,null

2. sp_addlinkedsrvlogin 'orcl',false,'sa','sai','sai'

3. sp_addlinkedsrvlogin 'orcl',true, 'sa','sai','sai'

Here sai is the name of the user and pasword in oracle. The service name is orcl. I tried with all the above options. but still i am getting the same error.

Please tell me the where i have done mistake in this oneDo you have the Oracle client on the machine that is running the SQL server ?|||check here:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=22264