SQL Server
Creating Insert Statements for SQL Server
July 2, 2009
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 insert statements. I thought it was a bit clever.
Note: you might need to register at SQLServerCentral.com to gain access to the site to read the above linked article.
Posted in SQL Server, T-SQL | 
Tags: David Finkelstein, Programming, quarksoft, SQL Server, sql server 2005, T-SQL
Calculating Age in T-SQL
March 13, 2009
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.
Posted in T-SQL | Tags: David Finkelstein, Math, Programming, quarksoft, SQL Server, sql server 2005, T-SQL
Using and Managing SQL Server Aliases
March 10, 2009
I know that not many people I have met in my SQL Server experience have used SQL Aliases. In many ways it is one of the most useful features of SQL Server. Roman Rehak wrote a great article about SQL Server Aliases. You have to be registered to see the full article, but some of the highlights are discussed below.
Creating an alias all depends on which client tools are installed on the computer you are using. Here are the common methods for running the SQL Client Configuration Tool:
- If SQL Server 2000 client tools are installed, use the SQL Client Network Utility and select the Alias tab.
- If SQL Server 2005 client tools are installed, use the SQL Server Configuration Manager and expand the SQL Native Client Configuration node to find the node for Aliases.
- If you do not have any client tools installed, you can still get at the Client Network Utility by executing “cliconfg” from any command prompt.
Aliases are useful in the following scenarios:
- Create an alias that is a friendlier name than the server name itself.
- In a Development/QA/Production environment. Using aliases allow you to keep the code base across your servers the same without having to change source code (sort of related to the last bullet).
- Enforce the use of a particular protocol. If you get the “Cannot generate SSPI context” error you can try creating an alias to force the use of the Named Pipes.
- A distributed environment with multiple servers that reference each other using the Linked Servers feature. You can define the linked servers with the aliases instead of the actual names. This makes future moving of databases and such much easier since you will not need to chnage the linked server names in your code.
- In a high-availability environment to allow you to quickly change the server without changing all your code (such as web applications and such).
I’m sure there are other useful reasons to use aliases, but that covers a few of them that show the power of the alias function.
If you have any other good ideas, please comment on the post.
So what do you do with all those IIS log files?
November 19, 2008
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 basic SQL syntax. The feature I like the best is the ability to convert the data into a SQL table for manipulation via T-SQL.
Here is a sample of what I did (this would all go on a single line)
c:\”program files”\”log parser 2.2″\LogParser -iCheckPoint:myCheckPoint.lpc -o:SQL -server:localhost -database:IISLogs -createtable:ON “SELECT * FROM ex0810*.log TO IISLogs”
This short command line will read in all log files that start with ex0810 and import them into a table called IISLogs. It will also create a checkpoint file so that if you run the command again it will not import records that have already been imported.
How cool is that?
Once all the importing is done you can leverage the SQL tables using whatever methods you need to.
If you need any help with stuff, send us a note at support at quarksoft.com.
ASP.NET Session State in SQL Server
June 13, 2008
There are lots of places to get information about putting the Session State into a SQL Server database instead of in-memory. The information I have found most useful is the utility aspnet_regsql.exe. It performs a bunch of different tasks for ASP.NET and interaction with SQL Server and the membership interfaces.
For example if you want to get session persisted across SQL Server reboots you can issue this command:
aspnet_regsql.exe -ssadd -sstype p -E -S <servername>
The sstype p is the option that allows for the state to be persisted. You can execute aspnet_regsql.exe -?
Resetting the IDENTITY counter in SQL Server T-SQL
May 13, 2008
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, 0)
This assumes your table is called SalesForce and you want to start the numbering at 1. If you wanted to start the number at 2000 you can substitute 0 with 1999. There has been some discussion that has said if the table never had records added to it the reseed value itself will be used instead of the reseed + 1 value. You should not need to use this statement for a “new” table.
Credit for this post is given to Pinal Dave over at http://sqlauthority.com/. The specific post this I am referring to is the one at this page.
Helping block spam with T-SQL
May 1, 2008
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
April 9, 2008
Pivots with Dynamic Columns in SQL Server 2005
January 7, 2008
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 accomplish the task and saved me a ton of time trying to create the process on my own. Great job Andras.
Posted in Programming, SQL Server, T-SQL | Tags: pivot, quarksoft, simple-talk.com, sql server 2005, T-SQL
Creating Dates in SQL Server… FAST!
September 17, 2007
Found a great tip online on how to create a date in T-SQL in a very fast way. The code looks like this:
declare @year int, @month int, @day int select @year = 2006, @month = 1, @day = 16select dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
This is especially useful when doing date comparisons in queries. Many people use the convert function, but the above code executes far faster because it does not have to do any string manipulations. Credit should be given to Michael Valentine Jones for this tip. More information about this tip can be found here. Here is some more great information on date manipulation for SQL Server.