Using SQL Server 2008 Table-valued Parameters in Access 2007

  • Article

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

**Summary: **Learn how to use table-valued parameters (TVPs) in SQL Server 2008 to marshal multiple rows of data from a client application to SQL Server from a Microsoft Office Access 2007 application. (4 printed pages)

Mary Chipman, Microsoft Corporation

April 2009

**Applies to: **Microsoft SQL Server 2008, Microsoft Office Access 2007

Contents

  • Overview

  • How Type-valued Parameters Work

  • Type-valued Parameters and Access Pass-Through Queries

  • Type-valued Parameter Walkthrough

  • Conclusion

  • Additional Resources

Overview

Table-valued parameters (TVPs) in SQL Server 2008 provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. Use TVPs to code explicit transactions in a stored procedure or perform multiple data operations as a single unit of work. You can encapsulate multiple rows of data in a single parameterized command. The incoming rows are stored in a table variable that can be manipulated using Transact-SQL. In SQL Server 2008 and earlier, you had to perform multiple operations to pass in and process multiple rows of data. Stored procedure input parameters did not accept arrays or structured data. Therefore, developers used techniques such as bundling multiple data values into delimited strings or XML documents. Those text values then needed to be processed, which required you to validate the data structures and unbundle the values. Using TVPs, you pass in the rows of data to be manipulated. This not only frees server resources to focus on set-based operations that make your transactions more scalable and efficient, but also simplifies your server-side Transact-SQL code.

Using TVPs from Access 2007 is recommended only for a limited set of scenarios, such as a store-and-forward application that operates on data locally in Access and then posts changes to SQL Server, "unbound" applications that interoperate with stored procedures, and transactional applications such as orders-line items in which all rows must be either committed or rolled back to guarantee data consistency. The default way to update server-side data—one row at a time—is sufficient in most data access scenarios. This article discusses how you can use TVPs alternatively in addition to the challenges to using this technique in Access 2007.

How Type-valued Parameters Work

Table-valued parameters are based on strongly-typed table structures that are defined by using the Transact-SQL CREATE TYPE statement. To use a TVP, first create a table type and define its structure in the database. The table type functions as a container for the structured rows that you pass to the server so that it mirrors the data structure of the underlying data you want to use it with. When you have defined the table type, you can then use it to define an input parameter for a stored procedure or Transact-SQL statement.

You can work with TVPs directly in Transact-SQL code, or by using either ADO.NET or SQL Server Native Client objects. ADO.NET extends the SqlParameter class to take a DataTable as a value. The SQL Server Native Client OLE DB API achieves the same functionality by using the COM IRowset interface together with a new implementation that enables buffering.

The disadvantage for Access developers is that the ADO API does not support TVPs directly through ADO Parameter objects. Unless you want to program directly against the SQL Server Native Client API by declaring and calling API functions, that leaves you with a single option: pass-through queries running Transact-SQL, which you can modify programmatically using a DAO QueryDef object.

Type-valued Parameters and Access Pass-Through Queries

Access pass-through queries, as the name implies, enable you to bypass the Access database engine and to send Transact-SQL code directly to SQL Server for execution. Although pass-through queries can be used to return records, this feature is not available if you want to use table-valued parameters. To use TVPs, you must set the query Returns Records property to No or Access will raise an error. Unfortunately, this means that you will be unable to return any information through the pass-through query mechanism. To get information back from the server, such as new identity values, execute a separate query.

Pass-through queries are more easily manipulated programmatically using the DAO QueryDef object. In addition to setting Returns Records to No, you must set the Connect property to a valid ODBC connection string and the SQL property to a string value that contains the Transact-SQL statements that you are passing to the server.

TVPs can be especially useful if you are working with an "unbound" application that relies on code execution and stored procedures instead of using bound controls to manipulate data. The tradeoff is that the client code in Access must be more complex because it must parse row data into correctly delimited Transact-SQL statements. The benefits include a simpler server-side programming model with more maintainable stored procedure code, faster performance from set-based operations, and better scalability for larger data volumes. Another consideration is that TVPs are server-side objects. Therefore, they can be used from multiple client applications including Access.

Type-valued Parameter Walkthrough

Here are the steps for creating a simple proof-of-concept example using the Northwind database in SQL Server 2008.

The code example creates a table structure that is saved in the database and used to insert multiple new rows into the Categories table. Notice that the Categories base table contains an identity column. It also does not have a unique index defined on CategoryName, which would enable inserting duplicate categories. These are both potential problems that you should expect and be prepared to handle in a real-world situation. Execute the following Transact-SQL statements in SQL Server Management Studio to create the CategoryTableType.

Use Northwind 
GO 
CREATE TYPE dbo.CategoryTableType AS TABLE 
     ( CategoryName nvarchar(15), CategoryDescription ntext ) 
GO

When the CategoryTableType is created, you can use it to define a stored procedure input parameter. The following usp_InsertCategory stored procedure takes the CategoryTableType as an input parameter, which is defined by using the READONLY keyword.

Other features of the stored procedure include:

  • An output parameter returns information to the caller. Even though you cannot obtain it from the pass-through query, you might also want to call the stored procedure from a .NET Framework application, where the output parameter value would be available.

  • Try/Catch error handling, which was introduced in SQL Server 2005. Again, you cannot use this feature from a pass-through query. However, you could conceivably call the stored procedure from another client.

  • An explicit transaction using BEGIN TRAN, COMMIT, and ROLLBACK statements. This ensures that all rows inserted into the Categories table will commit in a single transaction, or all will be rolled back. For example, if you have 10 rows, and there is an error that has only 1 of them, no rows will be inserted. If you do not have an explicit transaction, 9 of the 10 rows will be committed. However, you will have no way to know which row contained the error and was not committed.

  • In the Catch block, the code calls the **@@TRANCOUNT** function to verify that a transaction is active. This helps you to avoid triggering another error if you try a rollback without an active transaction.

Execute the following Transact-SQL statements in the SQL Server Management Studio window to create the usp_InsertCategory stored procedure.

CREATE PROCEDURE dbo.usp_InsertCategory 
(@tvpNewCategories dbo.CategoryTableType READONLY,
@ReturnMessage nvarchar(255) = NULL OUTPUT)

AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.Categories (CategoryName, Description)
SELECT CategoryName, CategoryDescription FROM @tvpNewCategories 
IF @@ROWCOUNT = 0
RAISERROR ('No Categories inserted.', 16, 1)
COMMIT
SELECT @ReturnMessage='Categories inserted.';
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
SELECT @ReturnMessage = ERROR_MESSAGE() +
' Severity=' + CAST(ERROR_SEVERITY() AS nvarchar(2))
END CATCH
GO

In Access, create a pass-through query and set the Returns Records property to No. Set the ODBC Connect Str property to a DSN or valid ODBC connection string.

Paste the following Transact-SQL statements into the SQL pane of your pass-through query. You can also test these statements from SQL Server Management Studio query window—the result will be the same. The difference is that in Management Studio you will be able to see the return message.

DECLARE @ReturnMessage nvarchar(255)
DECLARE @catType CategoryTableType
INSERT INTO @catType
EXEC ('
SELECT ''Organic'', ''Organically grown produce and grains''
SELECT ''Conventional'', ''Non-organically grown produce and grains''
SELECT ''Irish'', ''Mrs. O''''Leary''''s creamery products''
')
EXEC dbo.usp_InsertCategory @catType, @ReturnMessage OUTPUT;
SELECT @ReturnMessage as ReturnMessage

These Transact-SQL statements package the values to pass to the usp_InsertCategory stored procedure, and insert the required escape characters so that it can be processed without triggering a syntax error. When you execute the pass-through query, these statements pass directly to SQL Server to be parsed and executed. None of the statements will be processed by the Access database engine or ODBC. Notice that an "Irish" category was added to demonstrate the correct syntax for passing apostrophes ('). This is the kind of string processing you will require to handle in your VBA code as you concatenate row values with Transact-SQL statement syntax. For example, you could use the VBA Replace function to find and double any apostrophes.

Before you execute the query, open a SQL Profiler trace. Execute the query, and you will see that the statements are passed through directly to SQL Server.

Enter the following statements into the SQL Server Management Studio window.

SELECT CategoryID, CategoryName, Description from dbo.Categories

You will see that the new rows have been inserted. If you define a unique index on the CategoryName column and then attempt to execute the InsertCategory stored procedure again, you will see the error message only if you execute the statements from SQL Server Management Studio, but you will see nothing if you execute the Access pass-through query. You can examine the Profiler trace to verify that the statements have been sent from Access.

Conclusion

You will probably only be interested in calling TVPs from Access pass-through queries for a limited number of scenarios that must pass multiple rows of structured data to SQL Server to be processed on the server. There are several limitations to this technique: you will not get any design-time syntax checking and run time error handling will be limited. In addition, you will be unable to retrieve new identity column values from the pass-through query, because it cannot return any data. You can work around these limitations, but it will require making specific design decisions in addition to code workarounds. The main benefit is that you make your database transactions more scalable and efficient by performing iterative string processing in the client code. This frees server resources to focus on set-based operations.

Additional Resources

For more information about table-valued parameters, see SQL Server Books Online.

About the Author

Mary Chipman is a programmer-writer for the Data Programmability team at Microsoft. She has written and spoken extensively about Microsoft data platforms and technologies. Mary will present a session entitled "Solve Problems without Spending Money: Microsoft Office Access and Microsoft SQL Server" at Microsoft Tech·Ed North America 2009. To view the online panel she hosted at Tech Ed 2008, go to Microsoft Tech Ed Online: Tech Talks and search for "Are we there yet? Successfully navigating the bumpy road from Access to SQL Server".