Go to Top

Using large parameters for 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:

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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:

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:

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…

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:

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.