Home Forums SQL Server 2008 SQL Server 2008 - General same function from sql 2000 doesn't work on sql 2008 r2 RE: same function from sql 2000 doesn't work on sql 2008 r2

  • May 16, 2013 at 8:39 am

    #1615830

    Linda_web (5/16/2013)

    We have very strange situation I hope someone can help, we copied a database from sql 2000 sp4 to sql 2008 R2 SP2 , runs with compatibility level 2000, it has table valued function basically returns text column, the statement has select * from mytable where len(convert(varchar(4000), TextCol)) > 25

    Same function works in sql 2000, but doesn’t work in sql 2008, simply no result just runs hours and hangs and we have to kill it. Any idea why? And what I need to do in sql 2008,

    BTW: this new server has more RAM and CPU than old server, old server on 2003, but this sql on win 2008 r2

    Thank you

    I know this is no help on the current problem but I have to ask. If you convert the column to varchar(4000) on the fly for this, why not just convert the column in the table to varchar(4000) so you don't have to bother with the annoyances of the Text datatype?

    My other question would be, why not just use DATALENGTH instead of doing the conversion to varchar(4000)?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)