Sql Server 2012 cursor not fetching all records for databases in Availability Groups by ChrisStewart999999999

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	773604
Opened:	12/5/2012 3:23:50 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When using a basic cursor to iterate through names of databases on which to perform actions (log backups in this case) the query used to generate the cursor returns all expected rows but the cursor iteration does not.
When running the SELECT statement alone there are 5 database names returned; when iterating through the cursor only the first and last are returned. If the "STATIC" keyword is added to the cursor declaration then all 5 results are returned. If the LEFT OUTER JOIN portion of the query is removed then the query returns all expected values. This isn't a viable workaround for us since we need to execute this job on all servers participating in an Availability Group setup and need to only execute the commands against databases being hosted on the server.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Windows Server 2008 R2 Enterprise

US English

Create 5 databases on a server:
LearningAnalyticsDW
LearningAnalyticsStaging
LearningAnalyticsWorkArea
SQLAdmin
SSISDB (via SSIS Catalog)

The following databases are in an Availability Group:
LearningAnalyticsDW
LearningAnalyticsStaging
LearningAnalyticsWorkArea
SSISDB

Then run the following code on the server hosting the Availability Group:
/*******/
DECLARE @DBName VARCHAR(500)
DECLARE DBCur CURSOR LOCAL
FOR
SELECT d.name
FROM    sys.databases AS d
        LEFT OUTER JOIN sys.dm_hadr_database_replica_states hdrs
            ON d.database_id = hdrs.database_id
WHERE d.state_desc = 'online'
        AND d.recovery_model_desc <> 'SIMPLE'
        AND (
                hdrs.is_local = 0
                OR hdrs.is_local IS NULL
            )
ORDER BY d.name

OPEN DBCur

WHILE 1 = 1
    BEGIN
        FETCH DBCur INTO @DBName
        IF @@FETCH_STATUS <> 0
            BREAK

        PRINT @DBName
    END

/*****************/
For comparison purposes, if the query is changed to:
SELECT d.name
FROM    sys.databases AS d
WHERE d.recovery_model_desc <> 'SIMPLE'
        AND d.database_id > 4
ORDER BY d.name
then all 5 database names are returned as expected.

LearningAnalyticsDW
SSISDB

LearningAnalyticsDW
LearningAnalyticsStaging
LearningAnalyticsWorkArea
SQLAdmin
SSISDB

X64

Other (e.g. VM Ware, specify in Description)

0 attachments

Microsoft