MS Access: Cannot perform join, group, sort, or indexed restriction. A value being searched or sorted on is too long

Me he encontrado con este problema en Access:

Cannot perform join, group, sort, or indexed restriction. A value being searched or sorted on is too long.

Si bien las causas pueden ser varias, en mi caso el problema fue vincular una tabla de SQL Server y seleccionar como clave primaria un campo que poseía valores mayores a 255 caracteres.

¿Cómo pasar un argumento al abrir un formulario en MSAccess?

El último argumento al abrir un formulario permite pasar parámetros:

    DoCmd.OpenForm "Certificados_Revision", acNormal, , , , , "M"

Luego en la apertura del formulario:

Private Sub Form_Open(Cancel As Integer)

    If Me.OpenArgs = "M" Then

        MsgBox Me.OpenArgs

    Else

Ver en MSDN: https://msdn.microsoft.com/en-us/library/office/ff820845.aspx

SQL Server - Cannot resolve the collation conflict between

Al intentar crear una vista, se obtiene el siguiente error:

Msg 468, Level 16, State 9, Procedure V_Monitor_Emails_Detalle, Line 3

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Modern_Spanish_CI_AS" in the UNION operation.

Contexto:

• La vista es UNION entre una tabla y una vista
• La tabla ha sido creada por el usuario
• La vista es una vista sobre otra vista nativa de SQL Server

El problema es que algunas columnas tienen distintas "collation" que otras y esto es verificado por SQL Server, por cada columna al momento de hacer la UNION. En este caso la causa del problema es que una es una vista nativa y la otra es una tabla creada por el usuario.

Accediendo a las propiedades de cada columna, se puede ver el detalle:

La forma de resolverlo es agregando COLLATE DATABASE_DEFAULT en las columnas que correspondan, por ejemplo:

(...)

      ,[Enviado]

      ,[Error]

      ,[Cuenta]

FROM 

Correos C

UNION

SELECT Year(Fecha) As Anio, Month(Fecha) AS Mes, Day(Fecha) AS Dia, DATEPART(HOUR,Fecha) AS Hora,

      null AS [ID]

      ,null AS [Estado]

      ,null AS [Reintentos]

      ,null AS [Último reintento]

      ,[Fecha]

      ,'ND' AS [Para]

      ,null AS [CC]

      ,null AS [CO]

      ,Titulo COLLATE DATABASE_DEFAULT AS [Asunto]

      ,Adicional COLLATE DATABASE_DEFAULT AS [Cuerpo] 

      ,null AS [Adjuntos]

      ,null AS [Formato]

¿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]

) 

)

MS Access error ODBC 4405

Quizá se hayan encontrado con este problema:

SQL Server tiene una limitación. Genera un error 4405 cuando se modifican campos en una vista que afectan a más de una tabla base.
Se puede resolver haciendo que cada vez que cambias un campo en esa pantalla, se grabe.

Private Sub Combo53_AfterUpdate()

    RunCommand acCmdSaveRecord

    Me.Requery

    Combo53.SetFocus

End Sub

Private Sub ComboInspector_AfterUpdate()

    RunCommand acCmdSaveRecord

    Me.Requery

    ComboInspector.SetFocus

End Sub

Fuente: http://www.upsizing.co.uk/Art50_ViewUpdates.aspx

MSAccess: A problem occured while DB was communicating with the OLE server or Active X Control

Me he encontrado con este problema en una base de datos existente:

Luego de probar muchas soluciones encontré una muy sencilla y efectiva:

• Duplicar el formulario con problemas (copiar y pegar)
• Borrar el formulario con problemas y reemplazarlo por el duplicado

Esta solución funcionó en mi caso. Más información en: http://stackoverflow.com/a/29355405/2144424

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

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

¿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

Fuente: http://stackoverflow.com/questions/18800727/ms-access-call-sql-server-stored-procedure

SQL SERVER: error desde trigger

ALTER TRIGGER [dbo].[Web_Usuarios_Trigger_Controles]

ON [dbo].[Web_Usuarios]

AFTER INSERT, UPDATE

AS 

BEGIN

IF EXISTS (

 SELECT 1

 FROM Web_Usuarios 

 WHERE 

Estado = 'Activa' 

AND [ID] NOT IN (SELECT id FROM INSERTED) /* No es el insertado */

AND [ID Solicitante] IN (SELECT [ID Solicitante] FROM INSERTED) /* Mismo solicitante */

AND 'Activa' IN (SELECT Estado FROM INSERTED) /* El registro insertado está activo */

  )

  BEGIN

 RAISERROR ('Una empresa no puede estar autorizada para más de un cliente.' ,10,1)

 ROLLBACK TRANSACTION

  END 

ELSE

BEGIN

UPDATE [Web_Usuarios] SET [Fecha modificación] = GETDATE() WHERE [ID] IN (SELECT id FROM INSERTED)

END

END

GO

SQL SERVER: crear clave foránea si no existe

IF NOT EXISTS (

SELECT * 

FROM sys.foreign_keys 

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

)

BEGIN

ALTER TABLE Web_Usuarios 

WITH CHECK ADD CONSTRAINT [FK_Web_Usuarios_Web_Usuarios_Maestro]

FOREIGN KEY([ID Usuario Web])

REFERENCES [Web_Usuarios_Maestro] ([ID Usuario Web])

ALTER TABLE Web_Usuarios

CHECK CONSTRAINT [FK_Web_Usuarios_Web_Usuarios_Maestro]

END

GO

SQL SERVER: eliminar unique constraint si existe

IF EXISTS (

SELECT * 

FROM sys.indexes 

WHERE name='AK_Web_Usuarios' AND object_id = OBJECT_ID(N'[dbo].[Web_Usuarios]') 

)

BEGIN

ALTER TABLE [dbo].[Web_Usuarios] DROP CONSTRAINT AK_Web_Usuarios

END

SQL SERVER: elminar índice si existe

IF EXISTS (

SELECT * 

FROM sys.indexes 

WHERE name='I_Web_Usuarios' AND object_id = OBJECT_ID(N'[dbo].[Web_Usuarios]') 

)

BEGIN

DROP INDEX I_Web_Usuarios ON [dbo].[Web_Usuarios]

END

GO

SQL Server: eliminar restricción de clave foránea

ALTER TABLE dbo.Certificados_Eventos DROP CONSTRAINT [FK_Certificados_Eventos_Certificados]

GO

SQL Server - Detectar acción en trigger

ALTER TRIGGER dbo.Vigilancias_Trigger_Eventos

ON dbo.Vigilancias

AFTER INSERT, UPDATE, DELETE

AS 

BEGIN

DECLARE @accion AS NVARCHAR(20);

    SET @accion = 'Creación'

    IF EXISTS(SELECT * FROM DELETED)

    BEGIN

        SET @accion = 

            CASE

                WHEN EXISTS(SELECT * FROM INSERTED) THEN 'Modificación'

                ELSE 'Eliminación'

            END

    END

END

SQL Server - Crear o reemplazar trigger

IF OBJECT_ID(N'[dbo].[Certificados_Eventos_Insert]') IS NULL 

    exec ('CREATE TRIGGER dbo.Certificados_Eventos_Insert ON  [dbo].[Certificados] AFTER INSERT AS BEGIN SELECT 1 END')

GO

ALTER TRIGGER dbo.Certificados_Eventos_Insert

ON dbo.Certificados

AFTER INSERT

AS 

BEGIN

INSERT INTO [Certificados_Eventos] ([ID Certificado], [Fecha], [Tipo Documento], [Acción], [Usuario], [ID Documento])

    VALUES (@@IDENTITY , GETDATE(), 'CRT', 'Certificado - Creación', CURRENT_USER, @@IDENTITY )

END

GO

SQL Server - Crear o reemplazar stored procedure

IF object_id('dbo.Instalar_Version', 'p') is null

    exec ('create procedure Instalar_Version as select 1')

go

ALTER PROCEDURE Instalar_Version

@producto VARCHAR, @version VARCHAR, @estado VARCHAR

AS

BEGIN

END

GO

Formato de fecha en SQL Server DD/MM/YYYY

SELECT

CONVERT(varchar(11),[Fecha de emisión del informe de ensayos],103) 

FROM Certificados C

SQL Server - Crear o reemplazar columna

IF NOT EXISTS (

SELECT * 

FROM sys.columns 

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

AND name = 'Requiere Validación Cierre'

)

BEGIN

ALTER TABLE Procesos

ADD [Requiere Validación Cierre] bit NOT NULL 

CONSTRAINT DF_Procesos_Requiere_Validacion_Cierre DEFAULT ((0))

END

GO

SQL Server - Insertar fila si no existe

IF NOT EXISTS (SELECT '1' FROM Opciones C WHERE C.Opcion = 'Carpeta Contratos')

BEGIN

INSERT INTO Opciones (Opcion, Valor, Tipo, [Sección], Notas)

VALUES ('Carpeta Contratos','blabla','Texto','Ubicaciones','Carpeta en donde se almacenan los documentos de contratos.')

END

GO

SQL Server - Crear o reemplazar vista

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'V_Certificados_Rutas_Contratos')

DROP VIEW V_Certificados_Rutas_Contratos

GO

CREATE VIEW V_Certificados_Rutas_Contratos

AS

SELECT 

...

Ver y modificar la configuración de SQL Server DataBase Mail

Para consultar la configuración:

select * from msdb.dbo.sysmail_configuration

Para modificar la configuración:

EXECUTE msdb.dbo.sysmail_configure_sp 'AccountRetryAttempts', '4';

Posicionarse en un registro de MS Access por clave primaria (sin DoCmd.GoToRecord)

Supongamos que queremos movernos a otro registro de otro formulario usando clave primaria donde DoCmd.GoToRecord no puede ayudarnos. Esta es una posible solución:

Dim idExtension As Integer

idExtension = Me("Id Extensión")

[Form_Certificados_Editor].Certificados_Editor_Extensiones.SetFocus

[Form_Certificados_Editor].Certificados_Editor_Extensiones.Form.Recordset.FindFirst "ID=" & idExtension

Cómo Generar un reporte en PDF (con filtro)

Primero definir una variable en el módulo para almacenar el filtro:

Public filtro_pdf_aesel As String

Utilizar el siguiente código para generar el reporte

filtro_pdf_aesel = "[ID]=" & Me("ID") & " AND [Tipo]='V'"

DoCmd.OutputTo acOutputReport, "Extraccion_Ensayo", acFormatPDF, archivo, True

En el evento OPEN y CLOSE del reporte

Private Sub Report_Open(Cancel As Integer)

    If Len(filtro_pdf_aesel) <> 0 Then

         Me.Filter = filtro_pdf_aesel

         Me.FilterOn = True

    End If

End Sub

Private Sub Report_Close()

    filtro_pdf_aesel = vbNullString

End Sub

Crear una función escalar en SQL Server y utilizarla desdea MS Access

CREATE FUNCTION Retornar_Opcion (@opcionId NVARCHAR(50)) RETURNS NVARCHAR(250)

AS

BEGIN

DECLARE @valor NVARCHAR(250)

SELECT @valor = Valor

FROM Certi_Opciones

WHERE Opcion = @opcionId

RETURN @valor

END

GO

Ahora bien, en principio hay dos formas de usarla en MS Access:

• Usar DAO Querydef y acceder directamente a la base para que la función definida por el usuario sea reconocida
• Crear una vista en SQL Server que utilice la función y llamar a la vista normalmente desde MS Access. Ejemplo:

CREATE VIEW V_Snippets_Parametrizados

AS

SELECT S.ID, dbo.Retornar_Opcion(S.ID) AS Snippet

FROM Snippets S

GO

Con este último método se accede sin problemas vía ODBC porque MS Access no utiliza la función en forma directa.

Cómo Generar un reporte en PDF

DoCmd.OutputTo acOutputReport, "Mi reporte", acFormatPDF, "C:\Users\jp\Desktop\test1.pdf", True