Cursor result set limit of 1500?

Cursor result set limit of 1500?

Post by Neil » Wed, 07 Aug 2002 05:41:54



My client needs to update part of a varchar string, e.g. "change all records
containing '1234' in a column with '5678' and maintain the rest of the
string as is" for multiple tables and multiple columns, perhaps 20 cases in
all. The string '1234' is in the same position in all cases.

I tried using a cursor and fetch to iterate through a result set, parse the
varchar string and update the record, but got an error stating:

<eb1>Number of results grid generated reached the maximum
limit. The maximum limit is 1,500.</eb1>

There must be a better way. Is using a cursor not the way to do this? How
should this task be accomplished? Many TIA.

The batch file is:

DECLARE myCursor CURSOR
FOR SELECT gldebitacct FROM dbo.LABTRANS WHERE gldebitacct LIKE '%1032'
FOR UPDATE OF gldebitacct
OPEN myCursor
FETCH NEXT FROM myCursor

    BEGIN

            BEGIN
            {Statement block}
            FETCH NEXT FROM myCursor
            END
    END
DEALLOCATE myCursor

--
Neill Laney
http://home.nc.rr.com/nlaney
--

 
 
 

Cursor result set limit of 1500?

Post by BP Margoli » Wed, 07 Aug 2002 06:59:34



> My client needs to update part of a varchar string, e.g. "change all
records
> containing '1234' in a column with '5678' and maintain the rest of the
> string as is" for multiple tables and multiple columns, perhaps 20 cases
in
> all. The string '1234' is in the same position in all cases.

> I tried using a cursor and fetch to iterate through a result set, parse
the
> varchar string and update the record, but got an error stating:

> <eb1>Number of results grid generated reached the maximum
> limit. The maximum limit is 1,500.</eb1>

> There must be a better way. Is using a cursor not the way to do this? How
> should this task be accomplished? Many TIA.

> The batch file is:

> DECLARE myCursor CURSOR
> FOR SELECT gldebitacct FROM dbo.LABTRANS WHERE gldebitacct LIKE '%1032'
> FOR UPDATE OF gldebitacct
> OPEN myCursor
> FETCH NEXT FROM myCursor

>     BEGIN

>             BEGIN
>             {Statement block}
>             FETCH NEXT FROM myCursor
>             END
>     END
> DEALLOCATE myCursor

> --
> Neill Laney
> http://home.nc.rr.com/nlaney
> --


 
 
 

Cursor result set limit of 1500?

Post by BP Margoli » Wed, 07 Aug 2002 07:04:55


Neill,

Usually when one defines a cursor, one also defines local variables to hold
the values returned from the cursor. I notice that you are not defining any
local variables, nor is your FETCH assigning the data to local variables. A
FETCH without local variables just returns the data to the user, which is
usually not particularly useful.

The SQL Server Books Online section "DECLARE CURSOR" contains examples that
use cursors that you might want to review.

Aside from that, if I understand your requirement, there is no reason to use
a cursor in the first place. It sounds as if what you really what is
something closer to:

update dbo.LABTRANS
  set {column_name} = replace ( {column_name, '1234', '5678' )
where {column_name} like '%1234%'

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> My client needs to update part of a varchar string, e.g. "change all
records
> containing '1234' in a column with '5678' and maintain the rest of the
> string as is" for multiple tables and multiple columns, perhaps 20 cases
in
> all. The string '1234' is in the same position in all cases.

> I tried using a cursor and fetch to iterate through a result set, parse
the
> varchar string and update the record, but got an error stating:

> <eb1>Number of results grid generated reached the maximum
> limit. The maximum limit is 1,500.</eb1>

> There must be a better way. Is using a cursor not the way to do this? How
> should this task be accomplished? Many TIA.

> The batch file is:

> DECLARE myCursor CURSOR
> FOR SELECT gldebitacct FROM dbo.LABTRANS WHERE gldebitacct LIKE '%1032'
> FOR UPDATE OF gldebitacct
> OPEN myCursor
> FETCH NEXT FROM myCursor

>     BEGIN

>             BEGIN
>             {Statement block}
>             FETCH NEXT FROM myCursor
>             END
>     END
> DEALLOCATE myCursor

> --
> Neill Laney
> http://home.nc.rr.com/nlaney
> --

 
 
 

Cursor result set limit of 1500?

Post by Erland Sommarsko » Wed, 07 Aug 2002 07:19:22



> My client needs to update part of a varchar string, e.g. "change all
> records containing '1234' in a column with '5678' and maintain the rest
> of the string as is" for multiple tables and multiple columns, perhaps
> 20 cases in all. The string '1234' is in the same position in all cases.

> I tried using a cursor and fetch to iterate through a result set, parse
> the varchar string and update the record, but got an error stating:

><eb1>Number of results grid generated reached the maximum
> limit. The maximum limit is 1,500.</eb1>

That message comes from your client software, whatever you are using.

Quote:> There must be a better way. Is using a cursor not the way to do this? How
> should this task be accomplished? Many TIA.

The obvious way is:

   UPDATE tbl
   SET    col = replace(col, '1234', '5678')
   WHERE  col LIKE '%1234%'

Although I have a nagging feeling that there is whole lot things you
haven't told us about. For instance which version of SQL Server you
are using.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp