Using Large Parameters for Microsoft SQL stored Procedure with DAO

As many of you already know, SQL Server team has announced deprecation of OLEDB for SQL Server database engine (Read: we can’t use ADO because ADO uses OLEDB). Additionally, SQL Azure does not officially support ADO, though one still can get away with it using SQL Server Native Client. However, the new 13.1 ODBC driver comes with a number of features that won’t be available in the SQL Server Native Client, and there may be more coming.

The bottom line: we need to work with pure DAO. There are already multiple user voice items touching on the subject of Access / ODBC or Access / SQL Server… for example:

Data connector SQL Server
Better integration with SQL Server
Better integration with SQL Azure
Please make Access able to handle more data types as are commonly used in Server databases
Make Access a better ODBC client

(If you’ve not voted or visited access.uservoice.com, go there and vote if you want Access team to implement your favorite feature)

But even if Microsoft does enhance DAO in the next version, we still have to deal with our customer’s existing applications. We considered using ODBC over OLEDB provider (MSDASQL) but we felt that it was akin to straddling a pony on a dying horse. It might work but it might just die a short way down.

For the most part, a passthrough query will do what we need to do and it’s easy to throw together a function to mimic ADO’s functionality using a DAO pass-through query. But there is one significant gap that is not easily remedied — large parameters for stored procedures. As I wrote earlier, we sometime use XML parameter as a way to pass large amount of data, which is much faster than having Access actually insert all the data one by one. However, a DAO query is limited to about 64K characters for the SQL command and in practice can be even less. We needed a way to pass parameters that could be larger than 64K characters, so we had to think about a workaround.

Enter tblExecuteStoredProcedure table

The approach we chosen was to use a table because when we use newer ODBC drivers or SQL Server Native Client, DAO is easily able to handle large amount of text (aka Memo) by inserting directly into the table. Therefore, to execute a large XML parameter, we will write the procedure to execute and its parameter to the table, then let the trigger pick it up. Here’s the table creation script:

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
);

Of course, we don’t actually intend to use this like a real table. We also arbitrarily set 10 parameters even though a stored procedure can have many more. However, in our experience, it’s quite rare to have much more than 10, especially when we are dealing with XML parameters. By itself, the table wouldn’t be very useful. We need a trigger:

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;

A quite mouthful, that trigger. Basically it takes a single insert, then figures out how to convert the parameters from their nvarchar(MAX) as defined on the table tblExecuteStoredProcedure to the actual type required by the stored procedure. Implicit conversions are used, and since it’s wrapped in a sys.sp_executesql works well for a variety of data types as long the parameter values themselves are valid. Note that we require that the stored procedure NOT return any result sets. Microsoft does allow triggers to return result sets but as noted, it’s non-standard and has been deprecated. So to avoid problems with future versions of SQL Server, we block that possibility. Finally, we clear the table, so it’s always empty. After all, we’re abusing the table; we aren’t storing any data.

I chose to use a trigger because it cuts the number of round trips between Access and SQL Server. Had I used a stored procedure to process the T-SQL from the trigger’s body, that would have meant I’d need to call it after I insert into the table and also deal with potential side effects such as two users inserting at same time or an error leaving a record behind and so forth.

OK, but how do we use the “table” and its trigger? That’s where we need a bit of VBA code to set up whole arrangement…

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

Note that we use ParamArray which allow us to specify as many parameters as we actually need for a stored procedure. If you wanted to go crazy and have 20 more parameters, you could just add more fields to the table and update the trigger and the VBA code would still work. You would be able to do something like this:

ExecuteWithLargeParameters "dbo", "uspMyStoredProcedure", dteStartDate, dteEndDate, strSomeBigXMLDocument

Hopefully, the workaround won’t be necessary for a long time (especially if you go to Access UserVoice and upvote various items relating to Access + SQL / ODBC), but we do hope you find it useful should you find yourself in situation we are in. We also would love to hear about improvements you might have for this solution or a better approach!