We've recently experienced this problem and found a solution that appears to have resolved the issue.

We were running SQL Server 2008 (10.0.4000), but the database in question was in compatibility...

...in fact, I think I'm there:

SELECT LEN(column)

FROM tablename

WHERE LEN(column) > 0

ORDER BY LEN(column) DESC;

I ended up converting the TEXT datatype on that column to VARCHAR(MAX) first, and then re-trying one...

Thanks - I actually resolved the errors in the end by mapping the user (in my case, the SQL Service domain account) to the database. Doh!

I experienced this exact same issue today - did you ever find a resolution?

It's something that I've read every now...

Thanks Gianluca.

I usually steer clear of undocumented procedures, particularly as sp_MSforeachdb tends to miss databases every now and again. However, in this case, I may have to conceed.

Any other suggestions?

I've actually considered using Ola's script, given it's popularity. However, I like to make sure I understand all of the scripts that I use and I sure ain't at Ola's...

Hi All

Sorry to drag this one up from the depths again, but I seem to have run into a problem...

After this post, I set up a single test mirrored database...

ChrisM@home (4/18/2011)

---

Plan B, using Lowell's code:

declare @Last7Days CHAR(8)

SELECT @Last7Days = CAST(YEAR(CURRENT_TIMESTAMP - 7) * 10000 + MONTH(CURRENT_TIMESTAMP - 7) * 100 + DAY(CURRENT_TIMESTAMP - 7) AS CHAR(8))

SELECT ...

WHERE table-name-removed...

ChrisM@home (4/18/2011)

---

Double check the datatype of that INT date column. I reckon it's a text type, not an INT.

@ninja - good spot, mate - you saw this coming...

Just tried it with single-quotes and it completes in 1 sec.

I'm not being argumentative - I just want to understand why he suggested it for my own benefit, that's all.

Again,...

No...why's that? The query completed fine with and without them.

Just to clarify - I can get the query to complete quickly if I specify the date 7 days ago. However,...

1 second...see comments above

Thanks very much - updating the...

Hi Lowell,

Hmmm...gave your suggestion a try, but the query is still taking 2 mins to complete. To make sure I'm not going mad, I again tried:

WHERE table-name-removed >= 20110411 (date...