E
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.
2004-11-23
1,686 reads
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.
2004-11-22
2,400 reads
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.
2004-11-19
4,068 reads
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
2004-11-18 (first published: 2004-11-05)
213 reads
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).
2004-11-18
3,221 reads
A better version of the :"Finding first/last day of month by a date"
2004-11-17 (first published: 2004-11-04)
141 reads
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.
2004-11-17
2,506 reads
backup log with truncate only doesn't shrink the file. Actually truncating log needs transaction to truncate. A simple way to reduce the log file size to 1KB.
2004-11-16 (first published: 2004-11-03)
553 reads
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.
2004-11-16
2,514 reads
In this article we are going to assume a few things about the reader as we want to concentrate as much as possible on the Script Component itself and not the peripheral stuff
The things we will assume are:
* You are happy with adding a DataFlow task to the designer in Workflow
* You are happy to configure an OLEDB Source adapter
* You are comfortable with configuring mappings in the Destination adapter
2004-11-15
1,354 reads
By Vinay Thakur
it has been a year since i have not written much on the blog...
By Steve Jones
on tenderhooks – adj. feeling the primal satisfaction of being needed by someone, which...
By DataOnWheels
I have been active in the data community throughout my career. I have met...
Comments posted to this topic are about the item A Quick Restore
Comments posted to this topic are about the item Guarding Against SQL Injection at...
I have a quick question on Ola Hallengren Index Optimize Maintenance . Do we...
While doing some testing of an application, I wanted to reset my environment after doing some testing with this code:
USE DNRTest BACKUP DATABASE DNRTest TO DISK = 'dnrtest.bak' GO /* Bunch of stuff tested here */RESTORE DATABASE DNRTest FROM DISK = 'dnrtest.bak' WITH REPLACEWhat happens if this runs, assuming the "bunch of stuff" isn't anything affecting the instance. See possible answers