Skip to main content

Connect Coldfusion to SQL Server Express

Having recently installed SQL Server Express 2005 on a client server running CFMX 7 I have had to work through the torture of attempting to make the existing SQL Server database driver and then realising it will not work with anything higher than SQL 2000!

The instructions below are based on FusionCube but have been modified for my needs.

These instructions assume you have already install SQL Server Express and Management Studio Express.

Prepare the database server
  1. Open the SQL Server Management Studio
  2. Click on the Registered Servers button in the toolbar
  3. Right-click the SQL Express server.
  4. Select “SQL Server Configuration Manager”
  5. If needed, enable the TCP/IP protocol under SQL Server Configuration Manager > SQL Server 2005 Network Configurations > Protocols for SQLEXPRESS
  6. Double-click the TCP/IP protocol to view the properties.
  7. Click on the IP Addresses tab and make sure that the TCP Port is set to 1433 for every IP type.
  8. Close the configuration manager.
  9. Right-click the SQL Express server in SQL Management Studio, select Properties.
  10. Go to the Security item and make sure that you have Mixed Mode enabled. Save the settings.
  11. Restart he SQL Server instance.
  12. Once the server restarts, create a new login to be used by ColdFusion, give it a SQL Server Authentication password, and set the default database to the one you’re setting up.

Setup the ColdFusion datasource. At this point you have a choice of the Microsoft JDBC driver or the very popular open source JDBC 3.0 drive jTDS. I have included the instructions for the Microsoft driver first, the jTDS instructions are further down the page.
  1. Download the Microsoft SQL Server 2005 JDBC Driver
  2. Install the JDBC drive as described on the Microsoft page.
  3. Locate the sqljdbc.jar file in the JDBC driver install folder. Copy the .jar file to WEB-INF\cfusion\lib directory, or wherever your cfusion\lib directory is located.
  4. Restart ColdFusion Application Server otherwise it will not pick up the new driver.
  5. Log in to your ColdFusion Administrator.
  6. Create a new datasource, set a name and make the datasource type ‘other’.
  7. Fill in the fields with the following properties
    JDBC URL: jdbc:sqlserver://{server name}\sqlexpress:1433;DatabaseName={database name}
    Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
    Driver Name: SQL Server 2005 JDBC
  8. Enter the username and password you created in step 12 of the database instructions above.
  9. Save the new datasource and check it verifies.

The jTDS driver:
  1. Download the jTDS Driver
  2. Extract the contents of the ZIP.
  3. Locate the jtds-1.2.5.jar file in the JDBC driver install folder. Copy the .jar file to WEB-INF\cfusion\lib directory, or wherever your cfusion\lib directory is located.
  4. Restart ColdFusion Application Server otherwise it will not pick up the new driver.
  5. Log in to your ColdFusion Administrator.
  6. Create a new datasource, set a name and make the datasource type ‘other’.
  7. Fill in the fields with the following properties
    JDBC URL: jdbc:jtds:sqlserver://{server name}/{database name}:1433;useLOBs=false;instance={instance name};
    Driver Class: net.sourceforge.jtds.jdbc.Driver
    Driver Name: jtds-1.2.jar
  8. Enter the username and password you created in step 12 of the database instructions above.
  9. Save the new datasource and check it verifies.

The useLOBs parameter shown above can be used to improve performance and stop the driver converting text and ntext fields to CLOB, thanks to the original post here.

Comments

Hi! Thanks for the clear description but i have one question. my question is that : why install the jdbc driver in preference to using the SQL Server driver option available in the CF Administrator DSNs? Is the jdbc driver newer/beter for SQL Express?

Popular posts from this blog

Take website screenshot using ASP.NET

Utilising a hidden web browser control it is possible to take a screenshot of any website. The code shown below is based on an article at plentyofcode.com (sorry the site now appears to be offline May 2012) but I have translated it from VB.NET to C# and will work in .NET so theoretically for any Windows or ASP.NET web project.
using System; using System.Drawing; using System.Drawing.Imaging; using System.Windows.Forms; using System.Diagnostics; namespace WebsiteScreenshot { public class GetImage { private int s_Height; private int s_Width; private int f_Height; private int f_Width; private string myURL; public int ScreenHeight { get { return s_Height; } set { s_Height = value; } } public int ScreenWidth { get { return s_Width; } set { s_Width = value; } } public int ImageWidth { get { return f_Width; } set { f_Width = value; } } public int ImageHeight { get { return f_Height; } set { f_Height = value; } } public string Website …

John Hamblin Furniture Restorer - New Website

I was asked to produce a new website for local furniture restorer and cabinet maker John Hamblin. Having struggled with another provider who failed to deliver a finished website I was asked to take the website on and produce a simple site that communicates their values of high quality workmanship and excellent customer service.

The new website is live and can be viewed here:
http://www.johnhamblinfurniturerestorer.co.uk/



Compress and de-compress strings in C#

A collection of resources on compressing and encrypting strings in C#.
Microsoft documentation on the System.IO.Compression.GZipStream class:
http://msdn.microsoft.com/en-us/library/system.io.compression.gzipstream.aspx
Helpful example class utilising GZip:
http://www.csharphelp.com/2007/09/compress-and-decompress-strings-in-c/
The GZipStream class is predominantly used for file compression but can be used efficiently for compressing strings of 300-400 characters or more. Below 300 characters there isn’t any measurable gain from the compression and for particularly short strings the compressed version may in fact be larger.
Class provided by C Sharp Help:
using System.IO.Compression;
using System.Text;
using System.IO;
public static string Compress(string text)
{
    byte[] buffer = Encoding.UTF8.GetBytes(text);
    MemoryStream ms = new MemoryStream();
    using (GZipStream zip = new GZipStream(ms, CompressionMode.Compress, true))����…