Como muchos de ustedes ya saben, el equipo de SQL Server ha anunciado el desuso de OLEDB para el motor de base de datos de SQL Server (Lea: no podemos usar ADO porque ADO usa OLEDB). Además, SQL Azure no es oficialmente compatible con ADO, aunque uno puede salirse con la suya utilizando SQL Server Native Client. Sin embargo, el controlador 13.1 ODBC viene con una serie de características que no estarán disponibles en el SQL Server Native Client, y es posible que haya más.
El resultado final: necesitamos trabajar con DAO puro. Ya hay varios elementos de voz del usuario tocando el tema de Access / ODBC o Access / SQL Server … por ejemplo:
Conector de datos SQL Server
Mejor integración con SQL Server
Mejor integración con SQL Azure
Haga que Access pueda manejar más tipos de datos tal como se usan comúnmente en las bases de datos del servidor
Haga de Access un mejor cliente ODBC
(Si no ha votado o visitado access.uservoice.com, vaya allí y vote si desea que el equipo de Access implemente su función favorita)
Pero incluso si Microsoft mejora el DAO en la próxima versión, todavía tenemos que lidiar con las aplicaciones existentes de nuestros clientes. Consideramos usar ODBC sobre el proveedor OLEDB (MSDASQL) pero sentimos que era similar a montar en un caballo moribundo. Podría funcionar, pero podría morir un poco más adelante.
En su mayor parte, una consulta de paso hará lo que tenemos que hacer y es fácil combinar una función para imitar la funcionalidad de ADO utilizando una consulta de transferencia DAO. Pero hay una brecha importante que no se soluciona fácilmente: grandes parámetros para los procedimientos almacenados. Como escribí anteriormente, en algún momento usamos el parámetro XML como una forma de pasar gran cantidad de datos, que es mucho más rápido que hacer que Access realmente inserte todos los datos uno por uno. Sin embargo, una consulta DAO está limitada a unos 64K caracteres para el comando SQL y en la práctica puede ser aún menor. Necesitábamos una forma de pasar parámetros que pudieran tener más de 64K caracteres, por lo que tuvimos que pensar en una solución alternativa.
Ingrese la tabla tblExecuteStoredProcedure
El enfoque que elegimos fue usar una tabla porque cuando usamos controladores ODBC más nuevos o SQL Server Native Client, DAO puede manejar fácilmente una gran cantidad de texto (también conocido como Memo) al insertarlo directamente en la tabla. Por lo tanto, para ejecutar un parámetro XML grande, escribiremos el procedimiento a ejecutar y su parámetro en la tabla, luego dejaremos que el disparador lo recoja. Aquí está el script de creación de la tabla:
CREATE TABLE dbo.tblExecuteStoredProcedure ( ExecuteID int NOT NULL IDENTITY CONSTRAINT PK_tblExecuteStoredProcedure PRIMARY KEY CLUSTERED, ProcedureSchema sysname NOT NULL CONSTRAINT DF_tblExecuteStoredProcedure DEFAULT 'dbo', ProcedureName sysname NOT NULL, Parameter1 nvarchar(MAX) NULL, Parameter2 nvarchar(MAX) NULL, Parameter3 nvarchar(MAX) NULL, Parameter4 nvarchar(MAX) NULL, Parameter5 nvarchar(MAX) NULL, Parameter6 nvarchar(MAX) NULL, Parameter7 nvarchar(MAX) NULL, Parameter8 nvarchar(MAX) NULL, Parameter9 nvarchar(MAX) NULL, Parameter10 nvarchar(MAX) NULL, RV rowversion NOT NULL );
Por supuesto, en realidad no tenemos la intención de usar esto como una tabla real. También establecemos arbitrariamente 10 parámetros a pesar de que un procedimiento almacenado puede tener muchos más. Sin embargo, en nuestra experiencia, es bastante raro tener mucho más de 10, especialmente cuando se trata de parámetros XML. Por sí solo, la tabla no sería muy útil. Necesitamos un disparador:
CREATE TRIGGER dbo.tblExecuteStoredProcedureAfterInsert ON dbo.tblExecuteStoredProcedure AFTER INSERT AS BEGIN --Throw if multiple inserts were performed IF 1 < ( SELECT COUNT(*) FROM inserted ) BEGIN ROLLBACK TRANSACTION; THROW 50000, N'Cannot perform multiple-row inserts on the table `tblExecuteStoredProcedure`.', 1; RETURN; END; --Process only single record which should be the last inserted DECLARE @ProcedureSchema sysname, @ProcedureName sysname, @FullyQualifiedProcedureName nvarchar(MAX), @Parameter1 nvarchar(MAX), @Parameter2 nvarchar(MAX), @Parameter3 nvarchar(MAX), @Parameter4 nvarchar(MAX), @Parameter5 nvarchar(MAX), @Parameter6 nvarchar(MAX), @Parameter7 nvarchar(MAX), @Parameter8 nvarchar(MAX), @Parameter9 nvarchar(MAX), @Parameter10 nvarchar(MAX), @Params nvarchar(MAX), @ParamCount int, @ParamList nvarchar(MAX), @Sql nvarchar(MAX); SELECT @ProcedureSchema = p.ProcedureSchema, @ProcedureName = p.ProcedureName, @FullyQualifiedProcedureName = CONCAT(QUOTENAME(p.ProcedureSchema), N'.', QUOTENAME(p.ProcedureName)), @Parameter1 = p.Parameter1, @Parameter2 = p.Parameter2 FROM inserted AS p WHERE p.RV = ( SELECT MAX(x.RV) FROM inserted AS x ); SET @Params = STUFF(( SELECT CONCAT( N',', p.name, N' = ', p.name ) FROM sys.parameters AS p INNER JOIN sys.types AS t ON p.user_type_id = t.user_type_id WHERE p.object_id = OBJECT_ID(@FullyQualifiedProcedureName) FOR XML PATH(N'') ), 1, 1, N''); SET @ParamList = STUFF(( SELECT CONCAT( N',', p.name, N' ', t.name, CASE WHEN t.name LIKE N'%char%' OR t.name LIKE '%binary%' THEN CONCAT(N'(', IIF(p.max_length = -1, N'MAX', CAST(p.max_length AS nvarchar(11))), N')') WHEN t.name = 'decimal' OR t.name = 'numeric' THEN CONCAT(N'(', p.precision, N',', p.scale, N')') ELSE N'' END ) FROM sys.parameters AS p INNER JOIN sys.types AS t ON p.user_type_id = t.user_type_id WHERE p.object_id = OBJECT_ID(@FullyQualifiedProcedureName) FOR XML PATH(N'') ), 1, 1, N''); SET @ParamCount = ( SELECT COUNT(*) FROM sys.parameters AS p WHERE p.object_id = OBJECT_ID(@FullyQualifiedProcedureName) ); SET @ParamList += (( SELECT CONCAT(N',', p.ParameterName, N' nvarchar(1)') FROM (VALUES (1, N'@Parameter1'), (2, N'@Parameter2'), (3, N'@Parameter3'), (4, N'@Parameter4'), (5, N'@Parameter5'), (6, N'@Parameter6'), (7, N'@Parameter7'), (8, N'@Parameter8'), (9, N'@Parameter9'), (10, N'@Parameter10') ) AS p(ParameterID, ParameterName) WHERE p.ParameterID > @ParamCount FOR XML PATH(N'') )); SET @Sql = CONCAT(N'EXEC ', @FullyQualifiedProcedureName, N' ', @Params, N';'); --Prevent any result sets from being returned from a trigger (which is deprecated) --If a stored procedure returns any, the trigger will end in an error EXECUTE sys.sp_executesql @Sql, @ParamList, @Parameter1, @Parameter2, @Parameter3, @Parameter4, @Parameter5, @Parameter6, @Parameter7, @Parameter8, @Parameter9, @Parameter10 WITH RESULT SETS NONE; DELETE FROM dbo.tblExecuteStoredProcedure WHERE EXISTS ( SELECT NULL FROM inserted WHERE inserted.ExecuteID = tblExecuteStoredProcedure.ExecuteID ); END;
Un absoluto bocado, este Trigger. Básicamente, se necesita una sola inserción, luego se descubre cómo convertir los parámetros de su nvarchar (MAX) como se define en la tabla tblExecuteStoredProcedure al tipo real requerido por el procedimiento almacenado. Se utilizan conversiones implícitas, y dado que está envuelto en un sys.sp_executesql funciona bien para una variedad de tipos de datos siempre que los valores de los parámetros sean válidos. Tenga en cuenta que requerimos que el procedimiento almacenado NO devuelva ningún conjunto de resultados. Microsoft permite que los triggers devuelvan conjuntos de resultados, pero como se señaló, no es estándar y ha quedado en desuso. Entonces, para evitar problemas con futuras versiones de SQL Server, bloqueamos esa posibilidad. Finalmente, limpiamos la tabla, por lo que siempre está vacía. Después de todo, estamos abusando de la tabla; No estamos almacenando ningún dato.
OK, pero ¿cómo usamos la «tabla» y su trigger ? Ahí es donde necesitamos un poco de código VBA para configurar todo el arreglo …
Public Sub ExecuteWithLargeParameters( _ ProcedureSchema As String, _ ProcedureName As String, _ ParamArray Parameters() _ ) Dim db As DAO.Database Dim rs As DAO.Recordset Dim i As Long Dim l As Long Dim u As Long Set db = CurrentDb Set rs = db.OpenRecordset("SELECT * FROM tblExecuteStoredProcedure;", dbOpenDynaset, dbAppendOnly Or dbSeeChanges) rs.AddNew rs.Fields("ProcedureSchema").Value = ProcedureSchema rs.Fields("ProcedureName").Value = ProcedureName l = LBound(Parameters) u = UBound(Parameters) For i = l To u rs.Fields("Parameter" & i).Value = Parameters(i) Next rs.Update End Sub
Tenga en cuenta que utilizamos ParamArray que nos permite especificar tantos parámetros como realmente necesitamos para un procedimiento almacenado. Si desea volverse loco y tener 20 parámetros más, simplemente podría agregar más campos a la tabla y actualizar el trigger y el código VBA aún funcionaría. Podrías hacer algo como esto:
ExecuteWithLargeParameters "dbo", "uspMyStoredProcedure", dteStartDate, dteEndDate, strSomeBigXMLDocument
Con suerte, la solución temporal no será necesaria durante mucho tiempo (especialmente si va a Access UserVoice y vota varios elementos relacionados con Access + SQL / ODBC), pero esperamos que lo encuentre útil si se encuentra en situación. ¡También nos encantaría saber acerca de las mejoras que pueda tener para esta solución o un mejor enfoque!
Deja tu comentario