Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Post Unanswered: 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


    T

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb Re: Linked text file with columns > 255 chars


    Q1 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/de...data_types.asp
    Last edited by DBA; 01-11-03 at 21:10.

  3. #3
    Join Date
    Jan 2003
    Posts
    5

    Thanks...

    that should work nicely...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •