Posts filed under ‘MS SQL’

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), ' ')

July 14, 2010 at 4:53 am Leave a comment

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.

September 17, 2009 at 11:45 pm 1 comment

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”

May 4, 2009 at 5:38 am 10 comments

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.

August 25, 2008 at 4:14 am 6 comments

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 EXPLICIT
How 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

January 21, 2008 at 6:09 am 2 comments

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?.

January 6, 2008 at 5:54 am Leave a comment

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.

Continue Reading January 5, 2008 at 5:09 am Leave a comment


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 19 other followers

Archives

Top Rated

Top Clicks

  • None

Blog Stats

  • 168,294 hits

%d bloggers like this: