Problemas que este procedimento pode corrigir

1. Remover a tabela MEMB_STAT antiga

dbo.MEMB_STAT

2. Excluir as procedures antigas

Agora remova as procedures responsáveis por registrar conexão e desconexão.

WZ_CONNECT_MEMB

USE [MuOnline]
GO

IF EXISTS (
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[WZ_CONNECT_MEMB]')
    AND type IN (N'P', N'PC')
)
DROP PROCEDURE [dbo].[WZ_CONNECT_MEMB]
GO

WZ_DISCONNECT_MEMB

USE [MuOnline]
GO

IF EXISTS (
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[WZ_DISCONNECT_MEMB]')
    AND type IN (N'P', N'PC')
)
DROP PROCEDURE [dbo].[WZ_DISCONNECT_MEMB]
GO

3. Criar novamente a tabela MEMB_STAT

Antes de recriar as procedures, crie a tabela responsável por armazenar o status de conexão das contas.

USE [MuOnline]
GO

CREATE TABLE [dbo].[MEMB_STAT] (
    [memb___id] [nvarchar](10) NULL,
    [ConnectStat] [tinyint] NULL,
    [serverName] [nvarchar](20) NULL,
    [iP] [nvarchar](15) NULL,
    [ConnectTM] [smalldatetime] NULL,
    [DisConnectTM] [smalldatetime] NULL
) ON [PRIMARY]
GO

4. Criar a procedure WZ_CONNECT_MEMB

Esta procedure registra o jogador como conectado e atualiza IP, servidor e horário da conexão.

USE [MuOnline]
GO

CREATE PROCEDURE [dbo].[WZ_CONNECT_MEMB]

    @uid varchar(20),
    @server varchar(20),
    @uip varchar(20)

AS

BEGIN

    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    IF EXISTS (
        SELECT memb___id
        FROM MEMB_STAT WITH (READUNCOMMITTED)
        WHERE memb___id = @uid
    )

    BEGIN

        UPDATE MEMB_STAT

        SET
            ip = @uip,
            connectstat = 1,
            servername = @server,
            ConnectTM = GETDATE()

        WHERE memb___id = @uid;

    END

    ELSE

    BEGIN

        INSERT INTO MEMB_STAT
        (
            memb___id,
            ip,
            connectstat,
            servername,
            ConnectTM
        )

        VALUES
        (
            @uid,
            @uip,
            1,
            @server,
            GETDATE()
        );

    END


    IF(@@ERROR <> 0)

        ROLLBACK TRANSACTION;

    ELSE

        COMMIT TRANSACTION;


    SET NOCOUNT OFF;

END
GO

5. Criar a procedure WZ_DISCONNECT_MEMB

Esta procedure registra o horário da desconexão e altera ConnectStat para 0.

USE [MuOnline]
GO

CREATE PROCEDURE [dbo].[WZ_DISCONNECT_MEMB]

    @uid varchar(20)

AS

BEGIN

    SET NOCOUNT ON;

    BEGIN TRANSACTION;


    IF EXISTS (
        SELECT memb___id
        FROM MEMB_STAT WITH (READUNCOMMITTED)
        WHERE memb___id = @uid
    )

    BEGIN

        UPDATE MEMB_STAT

        SET
            DisConnectTM = GETDATE(),
            connectstat = 0

        WHERE memb___id = @uid;

    END

    ELSE

    BEGIN

        INSERT INTO MEMB_STAT
        (
            memb___id,
            DisConnectTM,
            connectstat
        )

        VALUES
        (
            @uid,
            GETDATE(),
            0
        );

    END


    IF(@@ERROR <> 0)

        ROLLBACK TRANSACTION;

    ELSE

        COMMIT TRANSACTION;


    SET NOCOUNT OFF;

END
GO

6. Testando o sistema

Depois de executar todas as queries:

• Reinicie os componentes do servidor MU Online.
• Entre com uma conta no jogo.
• Abra a tabela MEMB_STAT.
• Confira se ConnectStat mudou para 1.
• Desconecte a conta.
• Confira se ConnectStat voltou para 0.

Para visualizar os registros:

USE [MuOnline]
GO

SELECT *
FROM MEMB_STAT
ORDER BY ConnectTM DESC
GO

Como identificar usuários online no site

Em grande parte dos websites de MU Online, o número de usuários conectados é obtido através da coluna:

ConnectStat

Normalmente:

0 = Offline
1 = Online

Uma consulta simples para contar usuários conectados seria:

SELECT COUNT(*) AS Online
FROM MEMB_STAT
WHERE ConnectStat = 1