A Web site that I am working on has a bunch of user data that is stored in text files. I am trying to convert it to MS SQL Server 7.0.

My script reads the data from a text file into %data. I then build the SQL in the insertData sub and execute it (raiseError has previously been set). The script processes several files until I get to one particular file. All of the data in this file is fine, except for one field which has about 23,000 characters! I am attempting to stuff it into a field defined as text, which should have no problem holding it. I get the following error message: 

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Strin
+g data, right truncation 
(SQL-22001)(DBD: st_execute/SQLExecute err=-1) at userDataConv.pl line
+ 101.

[download]
Line 101 is $sth->execute(@data). Here's the sub that's generating the problem: 
sub insertData {

    my (@columns, @data);

    # Only insert fields with values
    # @fields contains the names of the columns in the database
    # @textFields containst the names of the columns in the text files
    
    for (0 .. $#textFields) {
        if (exists $data{$textFields[$_]} && $data{$textFields[$_]} !~
+ /^\s*$/) {
            push @columns, $fields[$_];         # Database fieldnames
            push @data, $data{$textFields[$_]}; # Corresponding hash v
+alues
        }
    }
    
    my $fields = join ',', @columns;
    my $placeHolders = ('?,' x ($#columns)) . '?';
        
    my $sql = "INSERT INTO userData ($fields) VALUES ($placeHolders)";
    my $sth = $dbh->prepare($sql);
    $sth->execute(@data);
    $sth->finish;
}

[download]
I've never worked with MS SQL Server (or ODBC) before, so I am completely at a loss. Searching the documentation has so far turned up nothing. Is it SQL Server, ODBC, or DBI which is choking? I have no idea. Any suggestions would be greatly appreciated. I can supply more data if necessary.

Cheers,
Ovid

In reply to DBI Problem? by Ovid

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.