Ошибка 512 sql

my SQL is:

SELECT DB1.IdUtente
        ,DB2.Gruppo
        ,DB1.Username
        ,DB1.Psw

        ,CASE WHEN DB1.RagioneSociale IS NOT NULL
                    AND DB1.RagioneSociale <> ''
            THEN DB1.RagioneSociale
            ELSE DB1.Cognome + ' ' + DB1.Nome
            END AS Nominativo

        ,DB1.Indirizzo + ' - ' + DB1.Cap+ ' ' + DB1.Citta + '(' + DB1.Provincia + ')' AS IndirizzoCompleto
        ,DB1.Telefono + ' ' + DB1.Email AS Contatti

        ,(SELECT DISTINCT COUNT (*)
            FROM DB3
            WHERE DB3.IdAttivazione = DB1.IdUtente
            ) AS NumeroAccessi

        ,(SELECT DB4.NumTarga
            FROM DB4
            WHERE DB4.IdUtente = DB1.IdUtente
            ) AS NumeroTarghe

        ,DB1.DataRegistrazione
        ,DB1.DataScadenza
        ,DB1.Attivo 
FROM DB1
    INNER JOIN DB2
        ON DB1.IdGruppo = DB2.IdGruppo 
WHERE DB1.Demo = 0
ORDER BY DB1.RagioneSociale

Why i receive this error from sql server?

Error 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What am i wrong?

Sorry for bad english.

Thanks for any help

Kind Regards

M.W.

I’m getting the title’s error message when trying to determine the status of high availability databases on a particular cluster.

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Here is part of my code. I need to be able to utilize the results from both select statements and then assign @HadrRole if both are met.

IF ((SELECT ars.role
     FROM sys.dm_hadr_availability_replica_states ars
     INNER JOIN sys.databases dbs
     ON ars.replica_id = dbs.replica_id
     WHERE dbs.name = @DatabaseName) = 2 AND (SELECT secondary_role_allow_connections_desc
     FROM sys.availability_replicas) ='ALL')
SET @HadrRole = 3

Try to use EXISTS subqueries:

IF EXISTS 
    (
        SELECT *
        FROM sys.dm_hadr_availability_replica_states ars
        INNER JOIN sys.databases dbs
        ON ars.replica_id = dbs.replica_id
        WHERE dbs.name = @DatabaseName AND ars.role = 2
    ) AND 
    EXISTS 
    (
        SELECT  *
        FROM    sys.availability_replicas
        WHERE   secondary_role_allow_connections_desc = 'ALL'
    )
SET @HadrRole = 3

Note: you could use a SET statement thus:

SET @HadrRole = 
    CASE 
    WHEN EXISTS 
        (
            SELECT *
            FROM sys.dm_hadr_availability_replica_states ars
            INNER JOIN sys.databases dbs
            ON ars.replica_id = dbs.replica_id
            WHERE dbs.name = @DatabaseName AND ars.role = 2
        ) 
        AND EXISTS 
        (
            SELECT  *
            FROM    sys.availability_replicas
            WHERE   secondary_role_allow_connections_desc = 'ALL'
        )
    THEN 3 
    ELSE @HadrRole 
    END

answered Jan 28, 2014 at 20:44

I used this to identify if the High Availability database on each server in a cluster was primary, secondary, read-only secondary. I’m going to use this to modify my SQL Agent jobs so they will run on the correct server in the cluster (read only secondary or primary depending on the job. Thanks for the replies! I hope this helps others.

USE [msdb]
GO

/****** Object:  UserDefinedFunction [dbo].[RG_fn_HA_isPrimaryReplica_isSecondayReadable]    Script Date: 1/27/2014 4:31:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF object_id(N'RG_fn_HA_isPrimaryReplica_isSecondayReadable', 'FN') IS NOT NULL
    DROP FUNCTION RG_fn_HA_isPrimaryReplica_isSecondayReadable
    GO
CREATE FUNCTION [dbo].[RG_fn_HA_isPrimaryReplica_isSecondayReadable] (@DatabaseName SYSNAME)
RETURNS TINYINT
WITH EXECUTE AS CALLER
AS
/********************************************************************
  File Name:    RG_fn_HA_isPrimaryReplica_isSecondayReadable.sql
  Applies to:   SQL Server 2012
  Purpose:      To return either 0, 1, or 2 based on whether this
                @DatabaseName is a primary or secondary replica.
  Parameters:   @DatabaseName - The name of the database to check.
  Returns:      0 = Resolving
                1 = Primary
                2 = Secondary 
                3 = Readable Secondary
  Author:       Matthew Joyce
  Version:      1.0.0 - 01/28/2014
********************************************************************/
BEGIN
    DECLARE @HadrRole TINYINT
    -- Return role status from sys.dm_hadr_availability_replica_states
    IF (SELECT ars.role
        FROM sys.dm_hadr_availability_replica_states ars
        INNER JOIN sys.databases dbs
        ON ars.replica_id = dbs.replica_id
        WHERE dbs.name = @DatabaseName) = 1
        SET @HadrRole = 1
ELSE
    -- @DatabaseName exists but does not belong to an AG so return 1
    --  IF @HadrRole IS NULL
        --SET @HadrRole = 1
--ELSE 
    --Return 2 if secondary but not readable
     IF (SELECT ars.role
        FROM sys.dm_hadr_availability_replica_states ars
        INNER JOIN sys.databases dbs
        ON ars.replica_id = dbs.replica_id
        WHERE dbs.name = @DatabaseName) = 2 AND (SELECT secondary_role_allow_connections_desc
        FROM sys.availability_replicas
        where replica_server_name =(select @@ServerName)) <>'ALL'
SET @HadrRole = 2
ELSE 
     IF (SELECT ars.role
        FROM sys.dm_hadr_availability_replica_states ars
        INNER JOIN sys.databases dbs
        ON ars.replica_id = dbs.replica_id
        WHERE dbs.name = @DatabaseName) = 2 AND (SELECT secondary_role_allow_connections_desc
        FROM sys.availability_replicas
        where replica_server_name =(select @@ServerName)) ='ALL'
SET @HadrRole = 3
        RETURN @HadrRole;
END;
GO

answered Jan 29, 2014 at 12:08