Today I stumbled onto an interesting problem. I am working on a small system for managing lists of emails. All of the data is stored in a Microsoft SQL Server. One of the tables in the database contains a text column. The table is as follows: 
CREATE TABLE [Web_Team].[tblDept] (
    [deptID] [int] IDENTITY (1, 1) NOT NULL ,
    [deptName] [char] (100) NOT NULL ,
    [defaultMsg] [text] NOT NULL ,
    [active] [bit] NOT NULL 
)

[download]
This is the only table in the database that contains a text type column. Any select statements that included the defaultMsg column was behaving funny. I was able to reproduce the behavior using the simplest of select statements. 
SELECT * FROM tblDept

[download]
This should return everything within the table, but it doesn't when the defaultMsg in one of the rows is larger than 81 characters. I added the following option to the connect statement to fix the problem. 
{'LongTruncOk' => 0, LongReadLen => 1000000 }

[download]
After adding this code to the connect statement my program works as I expected. Because this looked like the problem I ran a trace on the statement and found that the default long truncation length for a text column ( or LONG VARCHAR ) was set at 81. This is what puzzles me. Most of my development has been using MySQL not Microsoft SQL Server.

Is there any special reason that the max length would be set that low? Is there a better way of solving this problem other than the solution I have found already? 
my $DSN = 'driver={SQL Server};server=devcgi;tcpip=123.123.123.123:123
+4;database=testbed;uid=web;pwd=team;'; 

my $dbh = DBI->connect("dbi:ODBC:$DSN",'web','team', {'LongTruncOk' =>
+ 0, LongReadLen => 1000000 })
   || die "Couldn't open database: $DBI::errstr\n";

$dbh->trace(2, "test" );

my $sth = $dbh->prepare('SELECT * FROM tblDept')
   || die "Couldn't prepare statement: $DBI::errstr\n";

$sth->execute()
   || die "Couldn't execute statement: $DBI::errstr\n";

my $temp = $sth->fetchall_arrayref({})
   || die "Couldn't fetch: $DBI::errstr\n";

print Dumper($temp),"\n";

$dbh->disconnect();

[download]

In reply to Default Return Max Length for MS-SQL by fenonn

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.