simple query with null

Question

Post reply

simple query with null

goher2000

SSCertifiable

Points: 5479

November 24, 2021 at 4:17 am

#3956322

Hi guys,

what I am getting null , I should be getting 'NOTPRIMARY'

select ' if cast(serverproperty(''machinename'') as nvarchar(20)) in ('+    stuff((
SELECT ', N''' +
 CASE WHEN (CS.replica_server_name IS NULL OR CS.replica_server_name ='' OR CS.replica_server_name=NULL) THEN N'NOTPRIMARY' ELSE CS.replica_server_name END
 
 + '''' --,  CS.join_state_desc, RS.role_desc, RS.operational_state_desc, RS.connected_state_desc, RS.synchronization_health_desc
    FROM sys.availability_groups_cluster AS C
        INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CS
            ON CS.group_id = C.group_id
        INNER JOIN sys.dm_hadr_availability_replica_states AS RS
            ON RS.replica_id = CS.replica_id
where  role_desc= 'SECONDARY' AND C.name ='x'
for xml path('')
),1,1,'') +')  BEGIN ' + char(13) as '--chksvrs'

November 26, 2021 at 5:30 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

goher2000 SSCertifiable Points: 5479 More actions · Answer 1

goher2000

SSCertifiable

Points: 5479

November 24, 2021 at 4:18 am

#3956323

oh and run it on secondary node, thanks in advance

Phil Parkin SSC Guru Points: 246963 More actions · Answer 2

If you run just the inside part of the query, as follows, what result do you get:

SELECT CASE
           WHEN
           (
               CS.replica_server_name IS NULL
               OR CS.replica_server_name = ''
           ) THEN
               N'NOTPRIMARY'
           ELSE
               CS.replica_server_name
       END
FROM sys.availability_groups_cluster C
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states CS
        ON CS.group_id = C.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states RS
        ON RS.replica_id = CS.replica_id
WHERE RS.role_desc = 'SECONDARY'
      AND C.name = 'x';

I don't have HA configured, so cannot properly test.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

goher2000 SSCertifiable Points: 5479 More actions · Answer 3

goher2000

SSCertifiable

Points: 5479

November 24, 2021 at 2:22 pm

#3956511

the result is nothing, thnx

Capture

Mike01 SSChampion Points: 11395 More actions · Answer 4

is c.name really = to 'x'?? what does this return?

SELECT CASE

WHEN

(

CS.replica_server_name IS NULL

OR CS.replica_server_name = ''

) THEN

N'NOTPRIMARY'

ELSE

CS.replica_server_name

END,

c.name

FROM sys.availability_groups_cluster C

INNER JOIN sys.dm_hadr_availability_replica_cluster_states CS

ON CS.group_id = C.group_id

INNER JOIN sys.dm_hadr_availability_replica_states RS

ON RS.replica_id = CS.replica_id

WHERE RS.role_desc = 'SECONDARY'

--AND C.name = 'x'

;

This reply was modified 3 years ago by Mike01.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 246963 More actions · Answer 5

OK, so now you understand why you did not get 'NOTPRIMARY', I assume?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

goher2000 SSCertifiable Points: 5479 More actions · Answer 6

goher2000

SSCertifiable

Points: 5479

November 24, 2021 at 2:37 pm

#3956517

i got it, thnx

goher2000 SSCertifiable Points: 5479 More actions · Answer 7

x= alwayson group name.

the query is to return list of Secondary replicas for specific ag , if you run on secondary it should return none for the said AG

Phil Parkin SSC Guru Points: 246963 More actions · Answer 8

As an aside, as you're using SS 2019, I suggest you start using STRING_AGG() rather than the legacy FOR XML PATH method.

Makes your code much easier to read.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

goher2000 SSCertifiable Points: 5479 More actions · Answer 9

goher2000

SSCertifiable

Points: 5479

November 24, 2021 at 4:25 pm

#3956561

Thanks for your help and advise.