very day a developer somewhere needs to write code to iterate through SQL Server™ system objects, query and update tables in linked servers, handle optimistic concurrency, and retrieve column and stored procedure metadata. In this month's column, I will address these and other T-SQL development scenarios based on some of the questions I most frequently receive from readers.
There are plenty of good sources of information about how to deploy SQL Server in a secure fashion. However, these resources are often targeted at database administrators tasked with securing already developed applications. In addition, there is a rich body of information that discusses writing secure .NET and ASP.NET code, including .NET code that accesses SQL Server. However, many of these resources focus on the data access code that runs on the application servers rather than the Transact-SQL (T-SQL) code that executes within SQL Server. Developing T-SQL code that runs securely on SQL Server is the primary focus of this column.
A common requirement when building a data warehouse is to be able to get all rows from a staging table where the business key is not in the dimension table. For example, I may want to get all rows from my STG_DATE table where the DateID is not in DIM_DATE.DateID.
This Function and the query will generate the script for Create / Drop/ Create Constraint based on a Naming Convention . More changes can be done on this. This is very simple
One of the new tasks in SQL Server 2005 is the For Loop Container. In this article we will demonstrate a few simple examples of how this works. Firstly it is worth mentioning that the For Loop Container follows the same logic as most other loop mechanism you may have come across, in that it will continue to iterate whilst the loop test (EvalExpression) is true. There is a known issue with the EvalExpression description in the task UI being wrong at present. (SQL Server 2005 Beta 2).
SQL Server 2005 has made it a lot easier for us to loop over a collection and with each iteration do something with the item retrieved. In this article we are going to show you how to iterate over a folder looking at the files within and doing something with those files. In this instance we will be entering the filename into a SQL Server table and we will then load the actual files we have just found into another SQL Server table. You will note here that there is still the need to load the file names into a table as an intermediate step just as we need to do in SQL Server 2000.
SQL Server 2005 gives us loads of flexibility in our workflow management to decide how and if the following task should be executed. In this article we are going to introduce you to one of those ways and that is putting an expression into the workflow constraint itself.