Uso de parámetros grandes para el procedimiento almacenado de SQL con DAO

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:

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:

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 …

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:

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 una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*