We are constantly implementing new tasks and developing web sites for about 12 customers. We have a development server at our office and our live servers are at our datacenter. Many times we have to rewrite or modify stored procedures and then after testing, we need to copy them to the live server.
My friend Pedro Claudio posted a link on my Facebook Wall about this presentation made by Bernardo D. A. Guimarães at the Black Hat Briefings Europe, Amsterdam in April 2009. I found it interesting and here is the slide presentation:
I was following the SQL list and came across a post from Jim Gotwald where he showed his solution to convert a time string into a decimal value (ex: ’10:30′ to 10.5).
So I decided to write a custom function where you can either pass a complete date as a string or just the time and it will return a float value representing the time in decimal.
You may call the function like this:
select dbo.timeToDecimal(cast(getDate()) as varchar(20))
select dbo.timeToDecimal(’2008-06-18 10:35:43′)
select dbo.timeToDecimal(’06/18/2009 10:22:00′)
select dbo.timeToDecimal(’10:42′)
In my previous post, I discussed the function used to split a string into a table of tokens. Very useful instead creating a cursor.
Now, with SQL 2005/2008, it became easier and faster to do the same job using XML parsing.
I found this function online, written by Jacob Sebastian which is much faster than my old one:
For those who are jumping into MS SQL 2008, here are two goo books to purchase. The first one, Microsoft SQL Server 2008 Management and Administration, is a complete, fast-paced, task-based reference you’ve been searching for. Ross Mistry presents proven techniques for SQL Servere 2008 installation, upgrades, backup/restore, data transfer, indexing and much more.
Another good one is the Pro SQL Server 2008 XML, where Michael Coles shows how to store XML using SQL Server’s built-in XML data type.
Everybody knows that dynamic queries take a lot more of work and time to process. I had last week a case in which I had a variable number of parameters to pass to a stored procedure and one of the parameters was the “order by” clause. Also, I needed to do some pagination on my results and show only 20 rows at a time.
I spent some time researching on the net, reading several blogs about that, and one of the solutions for the dynamic “where clause” came from a blog in Brazil which unfortunately I do not have the address anymore to credit, and the solution for the order by clause came from SQL Team.
Since I was using MS SQL 2000, I could not make use of the new ROW_NUMBER() function, so I created a temporary table with the passed criteria and selected from it within the range of rows also passed.
Last Friday we upgraded our two database servers to MS SQL 2005. Our co-worker Matt Horrell found some scripts online that helped us a lot, like the one that reads all the users and generates another script to create them back on the new server, and the other that reads all the linked servers and do the same.
The whole process took 2:30 hs and it was smoothly done.
I’m posting here the steps to help anybody else.
As I told before, it took about 2.5 hours total, dealing with around 65 databases.
Continuing my previous blog about splitting a list to insert data into a table, we could transform the stored procedure into a table-valued function that you can flaunt using your best hosting.
Using the same example list as before, let’s create the function for this list
<cfset addCartValues=”P555|2,P444|1,P333|2″ />
You may download the function here.
Usually we have to loop through lists to insert or update data, and some of us choose to use ColdFusion tag CFLOOP with an inner query to do that job. That will cause several connections to the database (one for each member of the loop). One way to avoid that, other than create a huge CFQUERY tag with several inserts, is to write a store procedure and with just one connection to the database, let the SQL take care of the list loop and do its job of inserting or updating data.
You may download the stored procedure here.
I found some interesting articles about this hacker attack using SQL injection and I’m aggregating them here. I got this custom tag from “Por que CF” (Why CF) written by Pedro Claudio (CF-Brasil), and here is its usage:
<cf_DisableSQLInjection> Block and log the results
<cf_DisableSQLInjection mailto=“e-mail,e-mail,e-mail,e-mail”>Block, log and send e-mail
<cf_DisableSQLInjection mailto=“e-mail,e-mail,e-mail,e-mail” mailfrom=“mail”>Block, log and send e-mail
<cf_DisableSQLInjection mailto=“e-mail” mailfrom=“mail” title=“Erro 404″ >Block, log, and send email with a title
<cf_DisableSQLInjection mailto=“e-mail” mailfrom=“mail” title=“Erro 404″ message=“<h3>Erro 404 – page not found</h3>“ >Block, log, send a message with title and HTML message
Download the custom tag here.
There is another excellent article with examples by Devit!.