Using ANSI SQL style DECLARE CURSOR statement in UDF…

Today I encountered a post in the Microsoft Technical forums where a user was trying to use the ANSI SQL style DECLARE CURSOR statement which will raise error # 1049 in SQL Server. This behavior is true in SQL Server 2000. Here is a code sample that demonstrates the problem:   create function cur_test (@id int)returns intasbegin   …

5

OUTPUT clause in INSERT/UPDATE/DELETE statements

SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results…

3

Test linked server connection settings…

Have you ever encountered a scenario in SQL Server 2000 where you want to test the connectivity of a linked server configuration via TSQL? This was not possible easily and best handled from client side. But we have now a system stored procedure “sp_testlinkedserver” in SQL Server 2005 that allows you to test linked server…

12

Performing batched updates…

Updating a large table is a common scenario. This is often encountered in cases of applications that performs series of tasks in the background or data warehousing loading scenarios. To reduce locking and logging resources, such update statements are broken down into smaller batches or units of work. This has been traditionally done using SET…

0

Welcome !

Welcome to the SQL Server Engine Tips blog – hosted by the engine program management team. The purpose of the blog is to share guidelines, best practices, tips and answer questions related to implementing and deploying solutions using SQL Server. We will cover topics related to the SQL Server Engine including new features in SQL…

4