Replace tab in MS SQL

Replacing a tab with something else in MS SQL is easy peasy, following is the code

update [table name]
set [column name] = REPLACE( [column name], CHAR(9), ' ')

How to import Excel into MS SQL

First, forget about importing Excel into MS SQL, it is to time consuming to get it right. You are better of converting the Excel file to a simple CSV file, change the setting to a large string value for the columns, import it into MS SQL and then convert the field types to the proper field types.

Saving change is not permitted MS SQL 2008

Been battling with MS SQL 2008 and ran across the issue (it’s actually a feature!) of not being able to save changes made to a table in designer view. I got the following error:

Saving change is not permitted. the changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that cant be re-created or enabled the option Prevent Saving changes that require the table to be re-created.

Seems there is an easy fix (if you know where to look), go to
Tools Menu >
Options >
Designers >
And untick the option “Prevent saving changes that require a table re-creation”

MS SQL Date Time to Epoch

I know it’s been a while for you programmers out there, but here is an MS SQL function that converts Date Time to Epoch.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fn_dateTimeToEpoch] (
 @dateTime DATETIME
)
RETURNS BIGINT
AS
BEGIN

 DECLARE @epochDay BIGINT
 DECLARE @epochSecond INT

 – epoch ’01-01-1900 00:00:00′
 – one day 86400 seconds

 SET @epochDay = DATEDIFF( day, ’01-01-1900 00:00:00′, @dateTime )
 – get the remainder
 SET @epochSecond = DATEDIFF( second, ’01-01-1900 00:00:00′, DATEADD( day, -@epochDay, @dateTime ) )

 – convert to seconds
 RETURN ( @epochDay * 86400 ) + @epochSecond

END

 

Leave a message if you want the Epoch to Date Time function.

Create a Google Webmaster Sitemap (free code)

Want to know how to easily create a Google sitemap without having to use any other external tools?

It’s easypeasy if you use MS SQL and ColdFusion!

Let’s assume you have a database full of products (11,000 or more) and you need to generate a sitemap so google knows what to index. You wouldn’t want to do that by hand! 

The SQL is very simple

DECLARE @year CHAR( 4 ), @month VARCHAR( 2 ), @day VARCHAR( 2 ), @lastModified CHAR( 10 )SET @year = CONVERT( CHAR( 4 ), YEAR( GETDATE() ) )
SET @month = CONVERT( VARCHAR, MONTH( GETDATE() ) )
SET @day = CONVERT( VARCHAR, DAY( GETDATE() ) )
SET @lastModified = @year + ‘-’ + REPLICATE( ’0′, 2 - LEN( @month ) ) + @month + ‘-’ + REPLICATE( ’0′, 2 - LEN( @day ) ) + @day

SELECT 1 AS tag
, NULL AS parent
, NULL AS [url!1]
, ‘http://www.clickfind.com.au/product/listing.cfm?productIdentity=’ + CAST( identity AS VARCHAR( 10 ) ) AS [url!1!loc!element]
, @lastModified AS [url!1!lastmod!element]
, ‘monthly’ AS [url!1!changefreq!element]
, 1 AS [url!1!priority!element]
FROM product
FOR XML EXPLICITHow easy is that? Just transfer it to ColdFusion, wrap the
<urlset xmlns=”http://www.google.com/schemas/sitemap/0.84“></urlset> tag around it, safe it as a file with cffile.

Obviously you need change the date last modified variable and base that on the change frequently of each individual product.

You’re more than welcome to copy the code and use it, just be courteous and reference back to this article

Sponsored by www.clickfind.com.au

ColdFusion tutorial to create same hash as CF, but in MS SQL!, directly in the MS SQL RDMBS?

How cool would it be, being able to create the same MD5 hash as ColdFusion, directly in the MS SQL RDMBSH?
 
I know there’s been quite some demand for this function, most people create the MD5 hash in ColdFusion and then pass it the Database, this mean a couple more round trips to the db, which we prefer to avoid at anytime.
 
We’ll make some assumptions in this article to make things easier, and they are;
-          you are running MS SQL 2005
-          you are running ColdFusion
 
 
Ready to get started?
 
The first thing to do is go into your MS SQL database and create the following function that creates the hash and return a string value…..

Because our blog doesn’t maintain formatting and colour coding, we’ll just make this tutorial available in a Word document

You can download the document on the following link: ColdFusion tutorial to create same hash as CF, but in MS SQL!, directly in the MS SQL RDMBS?.

ColdFusion tutorial to export from database to RSS

Always wanted to export items from your existing database into RSS with ColdFusion and syndicate it, but don’t know how?

Well, this is your lucky day! We’re going to show you how you can easily export your database records to RSS.