martes, 17 de mayo de 2016

SQL SERVER: crear usuario y login si no existen

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'ff')
BEGIN
    CREATE LOGIN ff WITH PASSWORD = N'ff', CHECK_POLICY = OFF
END
GO
IF NOT EXISTS 
    (SELECT 1   
     FROM DBO.SYSUSERS
     WHERE name = 'ff')
BEGIN
CREATE USER ff FROM LOGIN ff
EXEC SP_ADDROLEMEMBER 'db_datawriter', 'ff'
EXEC SP_ADDROLEMEMBER 'db_datareader', 'ff'
END
GO

lunes, 16 de mayo de 2016

SQL SERVER: crear o reemplazar stored procedure

IF object_id(N'[dbo].[EnviarMails_Auditoria]', 'p') IS NULL
    EXEC ('create procedure EnviarMails_Auditoria as select 1')
GO
ALTER PROCEDURE EnviarMails_Auditoria
AS
SELECT 2
GO

viernes, 13 de mayo de 2016

¿Cómo ejecutar un Stored Procedure de SQL Server desde MS Access?

Function Ejecutar_Procedimiento()
    Dim qdef As DAO.QueryDef
    Set qdef = CurrentDb.CreateQueryDef("")
    qdef.ReturnsRecords = False
    qdef.Connect = CurrentDb.TableDefs("[cualquier tabla linkeada de SQL]").Connect
    qdef.sql = "EXEC mi_store"
    qdef.Execute
End Function