Ran across an interesting approach to generating insert statements for SQL Server 2005 (and other variants). If uses a less well known function called master.dbo.fn_varbintohexstr. The approach that was taken was to use the fn_varbintohexstr function to encode the data so you did not need to use cursors or any fancy parsing to handle unicode and quotes in the generated … Read More
Calculating Age in T-SQL
Here’s a decent article on calculating the age of a person (or anything else) written by Lynn Pettis. Discusses some pitfalls with leap years.
So what do you do with all those IIS log files?
You are responsible for one or more Windows web servers and you have all these IIS log files that you want to make some sense out of. If you have some basic SQL skills Microsoft is making available a utility called LogParser which does a great job getting in there and allowing you to query the files directly using some … Read More
Resetting the IDENTITY counter in SQL Server T-SQL
You’ve been there before. You are busily testing your import routines and running up the Identity column in your main import table. Before you know it the value is in the multi-millions and you just want to reset it back to 1. The way to accomplish this is to use the DBCC CHECKIDENT command. For example: DBCC CHECKIDENT (SalesForce, reseed, … Read More
Helping block spam with T-SQL
Found a great article on blocking spam using T-SQL code. It is especially useful for blog posts and other community related sites that accept user input. Could even be used on form comments to help block all those guys that submit endless forms on your sites.
Quick method to backup SQL Server database to workstation
Backup SQL Server database from a remote SQL Server to a local SQL Server
Pivots with Dynamic Columns in SQL Server 2005
Found a great article at www.simple-talk.com which described how to create pivot queries with dynamic column names. It seems simple at first until you try to adjust your columns manually and then discover that you really need to implement some special code to accomplish the task. The article Pivots with Dynamic Columns in SQL Server 2005 provides just the right information to … Read More