You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest writer is Mary Chipman. Mary is a programmer-writer for the Data Programmability team at Microsoft and co-author of the classic Microsoft Access Developer's Guide to SQL Server.
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. TVPs come in handy when you want to code explicit transactions inside of stored procedures, performing multiple data operations as a single unit of work. You can encapsulate multiple rows of data in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.
In versions of SQL Server prior to SQL Server 2008 you had to jump through a lot of hoops to pass in and process multiple rows of data. Stored procedure input parameters did not accept arrays or structured data, so developers used such techniques as bundling multiple data values into delimited strings or XML documents. Those text values then needed to be processed, which required that you validate the data structures and unbundle the values. With TVPs, you simply pass in the rows of data to be operated on. This not only frees server resources to focus on set-based operations, making your transactions more scalable and efficient, but it also simplifies your server-side Transact-SQL code.
Using TVPs from Access makes sense 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 need to be either committed or rolled back to guarantee data consistency. The default way of updating server-side data—one row at a time—is adequate in most data access scenarios. This article discusses how you can use TVPs as an alternative, as well as the drawbacks of using this technique in Access.
Table-valued parameters are based on strongly-typed table structures that are defined by using the Transact-SQL CREATE TYPE statement. In order to use a TVP, you must first create a table type and define its structure in the database. The table type functions as a container for the structured data rows that you pass to the server, so it mirrors the data structure of the underlying data you want to use it with. Once you've 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. In ADO.NET, the SqlParameter class has been extended to take a DataTable as a value. The SQL Server Native Client OLE DB API accomplishes the same thing by leveraging the COM IRowset interface along with a new implementation that allows buffering.
The downside for Access developers is that the ADO API has not been updated to 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.
Access pass-through queries, as the name implies, allow you to bypass the Jet 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's Returns Records property to No or Access will raise an error. Unfortunately, this means that you will be unable to return any information whatsoever through the pass-through query mechanism. If you need to get information back from the server, such as new identity values, you will need to execute a separate query to accomplish that.
Pass-through queries are most easily manipulated programmatically using the DAO QueryDef object. In addition to setting Returns Records to No, you will need to set the Connect property to a valid ODBC connection string and the SQL property to a string value that contains the Transact-SQL statements you are passing to the server for processing.
TVPs can be especially useful if you are working with an "unbound" application that relies on code execution and stored procedures rather than using bound controls to manipulate data. The tradeoff is that your client code in Access needs to be more complex, as it must parse row data into properly 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, not just Access.
Here are the steps for creating a simple proof-of-concept example using the Northwind database in SQL Server 2008.
The example creates a table structure that is saved in the database and used to insert multiple new rows into the Categories table. Note that the Categories base table contains an identity column. It also does not have a unique index defined on CategoryName, which would allow duplicate categories to be inserted. These are both potential problems that you'll need to anticipate and work around 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
Once 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 using the READONLY keyword. Other features of the stored procedure include:
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)
SET NOCOUNT ON
INSERT INTO dbo.Categories (CategoryName, Description)
SELECT CategoryName, CategoryDescription FROM @tvpNewCategories
IF @@ROWCOUNT = 0
RAISERROR ('No Categories inserted.', 16, 1)
SELECT @ReturnMessage='Categories inserted.';
IF @@TRANCOUNT > 0
SELECT @ReturnMessage = ERROR_MESSAGE() +
' Severity=' + CAST(ERROR_SEVERITY() AS nvarchar(2))
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 only 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
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
What these Transact-SQL statements are doing is packaging up the values you want to pass to the usp_InsertCategory stored procedure, and inserting the proper escape characters so that it can be processed without triggering a syntax error. When you execute the pass-through query, these statements will be passed directly to SQL Server to be parsed and executed. None of the statements will be processed by Jet or ODBC. Note that I've added an "Irish" category to demonstrate the correct syntax for passing apostrophes (single quotes). This is the sort of string processing you'll need 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 up any apostrophes.
Before you execute the query, open a SQL Server Profiler trace. Execute the query, and you will see that the statements are indeed 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 nothing if you execute the Access pass-through query. You can examine the Profiler trace to verify that the statements have in fact been sent from Access.
You'll probably only be interested in calling TVPs from Access pass-through queries for a limited number of scenarios that need to pass multiple rows of structured data to SQL Server to be processed on the server. There are several drawbacks to this technique: you won't get any design-time syntax checking, and run time error handling will be limited. In addition, you won't be able to retrieve new identity column values from within the pass-through query, because it can't return any data. You can work around these limitations, but it will require making specific design decisions as well as coding workarounds. The main benefit is that you are making your database transactions more scalable and efficient by performing iterative string processing in your client code, freeing up server resources to focus on set-based operations.
The documentation for table-valued parameters can be found in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/bb510489.aspx.
Mary Chipman is a programmer-writer for the Data Programmability team at Microsoft. She was recently profiled in Women in Technology for TechEd Online at http://msdn.microsoft.com/en-us/events/teched/cc510243.aspx. You can find the online panel she hosted at TechEd 2008 by going to http://msdn.microsoft.com/en-us/events/teched/cc676818.aspx and searching for: "Are we there yet? Successfully navigating the bumpy road from Access to SQL Server". Mary will also be presenting at TechEd 2009 (http://www.msteched.com) on the topic of working with SQL Server from Access.
I am using Mary's new approach in an Access 2002 "unbound" application that I'm upsizing from Jet to SQL Server 2008 and can report that it works quite well. In one situation I have a parent form with 4 subforms and send the data from these 5 entities to a single stored procedure that has input parameters for the parent entity and 4 TVPs for the children. The stored procedure handles Create and Update operations for the parent entity and Create, Update and Delete operations for each of the child entities, all within a server-side transaction. For the reasons that Mary explains, I don't use Identity columns for parent entity primary keys. I make a separate stored procedure call to a key table from Access prior to a parent Create operation and pass the new PK along with the other parameters. I call a public function and a class module from each of the form objects and leverage DAO to build the parent's parameter list and the child TVP scripts. The resulting code is much easier to write, cleaner and more compact that my previous approach using ADO and XML. It's a little more challenging to get your custom error messages back from SQL Server. I parse the Description property of DAO.Error objects returned by the DBEngine.Errors collection when the VBA.Err object returns Err.Number 3146 "ODBC Call Failed". Thank you Mary for researching how to use TVPs from Access and for sharing your results with the Access developer community. Your new technique is saving me hours of development effort in my current project.
Thank you, This is a most important feature, that may compel us to move to SQL Server 2008. Though the article would be more helpful if it had a sample working Access example. Thanks PS: Patrick can you share your approach. regards