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.

IIS Log Importer – MS SQL

Looking for a way to import your IIS W3C format logs into MS SQL?

Microsoft has a utility available that makes the import very easy. The tool can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

To run the import, run the program after download and install, this will open up a command prompt, at the command prompt is where you give it the commands for the import, following is an example;

 LogParser “SELECT * INTO webLog FROM ex020111.log” -i:W3C -o:SQL -server:192.168.0.1 -database:yourDatabaseName -driver:”SQL Server” -username:yourSQLUsername -password:yourSQLPassword -createTable:ON 

Running the above command will create a new table in your database with all the entries from the log file. Once it is in your database you can move it to another table, from where you can work with the data.

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.