windows 7 32 bit Using SQL Server 2008 Table-Value

[artists266]

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. (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. 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. 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. TVPs Work
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. 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. 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. and Access Pass-Through Queries
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,Microsoft Office 2007 Key, this feature is not available if you want to use table-valued parameters. To use TVPs,Microsoft Office 2010, 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. 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. 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,windows 7 x64, 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,Office Standard 2010, not just Access. Walkthrough
Here are the steps for creating a simple proof-of-concept example using the Northwind database in SQL Server 2008. 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.
GO
CREATE TYPE dbo.CategoryTableType AS TABLE
( CategoryName nvarchar(15), CategoryDescription ntext )
GO 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: An output parameter returns information to the caller. Even though you can't obtain it from the pass-through query, it's possible that you might also want to call the stored procedure from a .NET application, where the output parameter value would be available. Try/Catch error handling, which was introduced in SQL Server 2005. Again, you can't use this feature from a pass-through query, but 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 with only 1 of them, then none of the rows will be inserted. If you do not have an explicit transaction, 9 out the 10 rows will be committed, but you will have no way of knowing which one was the problem row that didn't make it. In the Catch block, the code calls the @@TRANCOUNT function to see if a transaction is active. This allows you to avoid triggering another error if you attempt 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. 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. 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. @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'',windows 7 32 bit, ''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. 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. the following statements into the SQL Server Management Studio window: 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. 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. Also
The documentation for table-valued parameters can be found in SQL Server Books Online at Chipman is a programmer-writer for the Data Programmability team at Microsoft. She was recently profiled in Women in Technology for TechEd Online at can find the online panel she hosted at TechEd 2008 by going to searching for: "Are we there yet? Successfully navigating the bumpy road from Access to SQL Server". Mary will also be presenting at TechEd 2009 ( on the topic of working with SQL Server from Access. <div