Max Data Type in SQL2000

Anything I can use in SQL2000 in place of varchar(max) (only in SQL2005) with a 2 GB-per-instance capacity

DECLARE jkcursor CURSOR

READ_ONLY

FOR SELECT top 100 episodeid,episodedate,journalentry

FROM ccnidcdw.[Pre-AuthThin].dbo.tbljournal

WHERE journalentry like '%CoCustServ%'

DECLARE @.episodeid varchar(15), @.episodedate datetime, @.journal varchar(2gb)

OPEN jkcursor

FETCH NEXT FROM jkcursor INTO @.episodeid,@.episodedate,@.journal

WHILE (@.@.fetch_status = 0)

BEGIN

print @.journal

FETCH NEXT FROM jkcursor INTO @.episodeid,@.episodedate,@.journal

END

CLOSE jkcursor

DEALLOCATE jkcursor

thanks

Text/NText and Image comes with restrictions. Check the link below.

http://msdn2.microsoft.com/en-us/library/aa276838(SQL.80).aspx

|||

anything I can do with varchar ?

|||

Nope. Varchar(max) & NVarchar(max) are introduced very first in SQL Server 2005. You have to use Text/NText. But with lot of restrictions.

You don't have other options in SQL Server 2000.

|||

The limit for Varchar is 8000 while Nvarchar is 4000 and there is no Varchar/Nvarchar(max) in 2000 so you have to use Text/NText but you cannot do comparison and other operation with Text/NText. The restrictions are covered in the link I posted.

|||thanks for you help

MasterDB locking

Hi

I thought maybe someone would be able to throw some light
on a problem I have seen on our SQL DB.

We are running SQL2000 with the latest service packs and
have experienced occasions where the Masterdb has been
locked for about 5 mins. The total DB is about 30gb with
the main db about 16gb, the rest being temp etc, plus a
couple of other small DB's.

I would have thought this is impossible but it happened.
Was wondering if anyone had ever heard of such a thing
before and knew what we can do.

I am baffled :-)

I have looked on the MS site to no avail yet.

Any assistance or hints would be appreciated.

Thanks
OrtezAre you talking about the "Master" database or is it a database created by you ??

And do you mean some particular table has been locked ??|||I mean the MS MasterDB, the system one where the stored procs, etc are kept, not our own generated DB.|||Were you able to get to the tables which were locked ??|||Were you able to get to the tables which were locked ??|||Hi Enigma

well the whole system was locked at that point so could not get any info|||6.5 corruption ok, 2k? Never heard of it...or very rarely...

And how do you know "Master" database is locked if you can't see anything...

Can you look at the error logs?

And how about profiler did you start a trace?

Can you ping the box?|||Hi Bret

When a lot of processes were happening on the DB, at some point nothing was working ... whatever I was trying to access from the enterprise management console, was giving me lock timeout, such as viewing current activity which gets data from masterdb afaik ...|||I have seen similar symptoms when tempdb was unavailable. The next time it happens, try select * from master..sysprocesses vs sp_who2. sp_who2 depends on temp tables. The direct select does not. In sysprocesses pay attention to the waittype, lastwaittype, and waitresource columns. They may tell you something. Just not sure what, at the moment.|||also this is not happening at the moment, it is something that happened last week.

As I said I am baffled as this is strange

Thanks|||thanks I shall note these down as well|||if tempdb is heavily used due to high activity generated by work against temporary objects, - sp_who will timeout. i don't think it was your master database. when this occurs again try dbcc opentran ('your_database_name') and then either dbcc inputbuffer(offending_spid) of ::fn_get_sql(sql_handle_from_sysprocesses).|||well as we have no real idea what caused it we have been trying to analyse things further.

I will ofcourse try your suggestion as the more info we have the better.

due to the fact we could not access anything at that time we assumed it was the MasterDB causing this, but we have no hard evidence

Thats for the advice and we shall monitor and report back if we get further information

Cheers
Ortez|||Now I remember where I saw this behavior before. There was a second application installed (as a service) on the SQL box that had a memory leak. Only it was not a nice slow steady detectable one. This application would hit some condition, then eat the box in the course of a day. Our only solution (since we could not uninstall the offensive application) was to restart that application once per week. You may want to set up perfmon to look at SQL Memory counters. If you see SQL giving up lots of memory right before a hang/crash, you may have the same condition.|||Hi

Thanks for that tip, I will speak to the DBA and have him look at that possibilty. Anything that can help we will investigate :P

Cheers
Ortez