¿Cómo ejecutar un Stored Procedure de SQL Server desde MS Access que retorne un valor STRING?

Anteriormente explicamos cómo ejecutar un procedimiento de SQL Server. Pueden consultarlo en este enlace:

http://notasaccess.blogspot.com.ar/2016/05/como-ejecutar-un-stored-procedure-de.html

En esta oportunidad explicaremos cómo ejecutar un procedimiento que retorne un valor como el siguiente:

CREATE PROCEDURE dbo.CerrarProceso 

@proceso INT

AS

    SELECT 'resultado'

GO

La forma de ejecutar el procedimiento desde SQL sería:

EXECUTE CerrarProceso 286

La forma de ejecutarlo desde MS Access sería:

    Dim qdef As DAO.QueryDef

    Dim rs As Recordset

    Set qdef = CurrentDb.CreateQueryDef("")

    qdef.ReturnsRecords = True

    qdef.Connect = CurrentDb.TableDefs("[dbo_Estados]").Connect

    Dim sql As String

    sql = "EXEC CerrarProceso " & Proceso

    qdef.sql = sql

    Set rs = qdef.OpenRecordset()

    MsgBox (rs.Fields(0))

SQL Server - Como copiar datos entre tablas manteniendo el ID con Identitty

Escenario:

• Queremos copiar datos de una tabla de una base de datos hacia otra tabla similar en otra base de datos
• La tabla posea una columna que utiliza IDENTITY
• Queremos usar el mismo ID que en la base origen

Para ello utilizamos la opción identity_insert :

set identity_insert basedestino.dbo.Propuestas_Opciones on

insert into basedestino.dbo.Propuestas_Opciones (ID,Campo,Valor) select ID,Campo,Valor from baseorigen.dbo.Propuestas_Opciones

set identity_insert basedestino.dbo.Propuestas_Opciones off

SQL Server: eliminar clave foránea si existe

IF EXISTS (SELECT * 

  FROM sys.foreign_keys 

   WHERE object_id = OBJECT_ID(N'dbo.FK_Vigilancias_Normas_Adicionales_Normas')

   AND parent_object_id = OBJECT_ID(N'dbo.Vigilancias_Normas_Adicionales')

)

  ALTER TABLE Vigilancias_Normas_Adicionales DROP CONSTRAINT FK_Vigilancias_Normas_Adicionales_Normas

IF EXISTS (

SELECT * 

FROM sys.columns 

WHERE  object_id = OBJECT_ID(N'[dbo].[Vigilancias_Normas_Adicionales]') 

AND name = 'ID Norma'

)

BEGIN

ALTER TABLE [dbo].[Vigilancias_Normas_Adicionales] DROP COLUMN [ID Norma]

END

SQL Server: inhabilitar trigger temporalmente

ALTER TABLE Certificados DISABLE TRIGGER Certificados_Trigger_Eventos

GO

UPDATE Certificados

SET Resolución = 'blabla'

GO

ALTER TABLE Certificados ENABLE TRIGGER Certificados_Trigger_Eventos

GO

SQL Server: leer contenido de una carpeta de Windows

Escenario

Se requiere que un stored procedure acceda a una carpeta de windows y lea los nombres de los archivos dentro de esa carpeta

Enfoque xp_dirtree

La siguiente línea de código funciona:

EXEC xp_dirtree @carpeta, 1, 1 

Pero posee algunas desventajas

• Estos procedimientos no están documentados
• Microsoft recomienda no usarlos
• Microsoft aclara que pueden desaparecer en versiones futuras de SQL Server
• Requiere permisos de sysadmin para ver el contenido de la carpeta

Enfoque CLR

Todas las recomendaciones indican no utilizar los procedimientos XP y en su lugar crear código C# que pueda ser utilizado desde SQL Server. Para ello se requiere crear un proyecto especial en Visual Studio para SQL Server.

El código para leer los archivos es el publicado en http://stackoverflow.com/a/11560496/2144424 por Eoin Campbell:

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetFiles_FillRow", TableDefinition = "FilePath nvarchar(4000)")]
    public static IEnumerable GetFiles(SqlString path)
    {
        return System.IO.Directory.GetFiles(path.ToString()).Select(s => new SqlString(s));
    }

    public static void GetFiles_FillRow(object obj,out SqlString filePath)
    {
        filePath = (SqlString)obj;
    }
};

Una vez desplegada esta solución, veremos lo siguiente en SQL Server:

Y funciona simplemente haciendo lo siguiente

select * From GetFiles('C:\Temp\');

En mi caso, como he incluido este SELECT dentro de un sotored procedure, he necesitado darle más privilegios al SP usando esta línea de código:

CREATE PROCEDURE dbo.EnviarMails

@auditoria INT,

@modo NVARCHAR(10)

WITH EXECUTE AS OWNER

AS

DECLARE @para VARCHAR(MAX)

Por último muestro cómo hacer el despliegue de esta solución en ambientes productivos. Pueden ayudarse siguiendo este camino:

• Despliegan desde Visual Studio en el ambiente de desarrollo
• Utilizan en SQL Management Studio la función de generar scripts para crear el script de instalación en producción

De todas formas, acá dejo un ejemplo:

USE [mibase]

GO

EXEC sp_configure 'clr enabled', '1'

GO

RECONFIGURE

GO

EXEC sp_changedbowner 'sa'

GO

ALTER DATABASE mibase SET TRUSTWORTHY ON;

GO

IF EXISTS (SELECT *

           FROM   sys.objects

           WHERE  object_id = OBJECT_ID(N'[dbo].[GetFiles]')

                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))

DROP FUNCTION [GetFiles]

GO

IF EXISTS (

SELECT * 

FROM sys.assemblies 

WHERE name='BD_Extended'

)

BEGIN

DROP ASSEMBLY BD_Extended

END

GO

CREATE ASSEMBLY _Extended from 'C:\BD_Extended\BD.Extended.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

CREATE FUNCTION [dbo].[GetFiles](@path [nvarchar](4000))

RETURNS  TABLE (

[FilePath] [nvarchar](4000) NULL

) WITH EXECUTE AS CALLER

AS 

EXTERNAL NAME [BD_Extended].[UserDefinedFunctions].[GetFiles]

GO

@@IDENTITY con MS Access y SQL Server

Problema

Supongamos un escenario en donde tenemos a SQL Server como base de datos y MS Access como aplicación.

Insertamos un registro en una tabla y queremos ver el ID asignado, para lo cual utilizamos esta línea de código:

Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY", dbOpenForwardOnly)

¿Cuál es el problema?

Si existe un trigger detrás de esta tabla que inserta en otra tabla, es posible que el IDENTITY sea el del trigger y no el que estamos buscando

Solución

Una posible solución es usar la función nativa de SQL Server para obtener el último IDENTITY de una tabla:

Function Retornar_Identity_Tabla()

    Dim qdef As DAO.QueryDef

    Dim VarRecords As Variant

    Set qdef = CurrentDb.CreateQueryDef("")

    qdef.ReturnsRecords = True

    qdef.Connect = CurrentDb.TableDefs("dbo_Certificados").Connect

    qdef.sql = "SELECT IDENT_CURRENT('certificados')"

    qdef.OpenRecordset

    VarRecords = qdef.OpenRecordset.GetRows(1)

    Retornar_Scope_Identity = VarRecords(0, 0)

End Function

SQL SERVER: crear o reemplazar tabla

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mitabla')

DROP TABLE mitabla

GO

CREATE TABLE [dbo].[mitabla](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Fecha] [datetime] NOT NULL,

[Aplicacion] [nvarchar](200) NOT NULL,

[Version] [nvarchar](50) NOT NULL,

[Titulo] [nvarchar](200) NOT NULL,

[Modulo] [nvarchar](200) NOT NULL,

[Usuario] [nvarchar](200) NULL,

[Detalle] [nvarchar](max) NULL,

[Severidad] [nvarchar](50) NULL,

CONSTRAINT [PK_mitabla] PRIMARY KEY

(

[ID]

) 

)