[Aporte] Solucion Cuentas Pegadas (SQL)

por **Wiz Khalifa** 2011-12-31, 19:12

Solucion Cuentas Pegadas (SQL)

Les paso a dejar el codigo, que lo debemos pegar/agregar en el Analizador de Consultas.
[Tienes que estar registrado y conectado para ver esa imagen] Paso uno a ejecutar

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MEMB_STAT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MEMB_STAT]
GO
CREATE TABLE [dbo].[MEMB_STAT] (
[memb___id] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ConnectStat] [tinyint] NULL ,
[ServerName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[IP] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[ConnectTM] [smalldatetime] NULL ,
[DisConnectTM] [smalldatetime] NULL
) ON [PRIMARY]
GO

[Tienes que estar registrado y conectado para ver esa imagen] Luego de colocar ese codigo, presionamos F5 y apretamos Ctrl + N y pegamos el siguiente codigo:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WZ_CONNECT_MEMB]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[WZ_CONNECT_MEMB]
GO
CREATE PROCEDURE WZ_CONNECT_MEMB

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

Begin

BEGIN TRANSACTION

SET NOCOUNT ON

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) valueS (
@uid,
@uip,
1,
@server
)
End

IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFF
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

Apretamos nuevamente F5 y luego Ctrl + N y pegamos el ultimo codigo

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WZ_DISCONNECT_MEMB]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[WZ_DISCONNECT_MEMB]
GO
CREATE PROCEDURE WZ_DISCONNECT_MEMB

@uid varchar(20)
AS
Begin

BEGIN TRANSACTION

SET NOCOUNT ON

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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Luego de aver pegado el ultimo codigo presionamos por ultima vez F5 y guardamos donde mas comodo nos quede.

Nota: Con este codigo podremos solucionar el BUG que se nos genera a la hora de tener una PC de bajo recurso y no se ponga BUG el servidor.

Creditos: United

Saludos