Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

same function from sql 2000 doesn't work on sql 2008 r2 Expand / Collapse
Posted Friday, May 17, 2013 8:28 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 1, 2016 10:11 AM
Points: 8,587, Visits: 18,753
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

Please post the code for the function and the query using it. Without it, folks are just guessing.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1454037
Posted Friday, May 17, 2013 3:17 PM


Group: General Forum Members
Last Login: Tuesday, January 26, 2016 9:10 AM
Points: 2,602, Visits: 1,694
Sean Lange (5/16/2013)
One suggestion, since you are now on 2008 can you change the datatype of your text column to varchar(max)? That will likely help your query because it won't have to convert each and every row.

To add to what Sean has stated, you might want to move not to varchar(max) for text ntext and Image datatypes since they will be deprecated in future versions of SQL Server. Simply stated if you change it now while you are looking at it later when you move up to a newer version of SQL Server you will not have to change it.

You can find documentation on this on the following page -

Not all gray hairs are Dinosaurs!
Post #1454197
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse