Getting ColdFusion To Work With MS SQL Server Express 2005 Databases

Posted December 10, 2008 at 9:38 AM

Tags: Work, SQL

Yesterday, after debating about whether or not to convert my blog to MySQL so that I could have a version of it running locally, I was persuaded by my esteemed Twitter colleagues to try using MS SQL Server Express since I do have SQL Server 2005 running in production. Downloading and installing SQL Server Express took the better part yesterday what with the .NET 3.5 updates and the additional software prerequisites that were needed (not to mention that I was doing client work in parallel); but, all in all that went smoothly.

This morning, I then tried to get my local ColdFusion server to play nicely with my new shiny SQL Server Express instance. I was stumped almost immediately. When it comes to this kind of server setup, I am truly a fish out of water; but thankfully, James Buckingham was there to help me with all the right answers. He pointed me in the direction of two crucial blog posts by Matt Woodward and Ben Forta.

Matt Woodward: ColdFusion + SQL Server Express 2005. The take away from this article was that by default the TCP/IP connection is not enabled for SQL Server Express and must be activated manually using the SQL Server Configuration manager. Also, the default port is different and can be found under the TCP/IP connection properties.

Ben Forta: ColdFusion And SQL Server 2005. The take away from this article was that SQL Server Express does not allow SQL Authentication by default. In order to activate SQL Server Authentication (which is how ColdFusion talks to MS SQL Server Databases), it must be turned on in the server's security properties.

Anyway, all seems to be up and running. A big thanks to James Buckingham and the rest of my Twitter friends.

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

You're welcome mate. Your blog has help me out numerous times in the past so I'm more than happy to return the favor.

Cheers,
James

@James,

This is pretty exciting. I just got my 404 handler working locally as well! Now I am unstoppable :) Time to start actually working on my blog again. Got some great ideas.

@Ben,

Sorry you had so much trouble getting going with this. Did you install 2005 or 2008? The reason I ask is because if you installed 2008, you'll probably want to also download and install Visual Web Developer 2008 because you'll need this to publish any MS SQL Server 2008 databases.

For example, I'll create a db on my local server, then when I'm ready to replicate everything for the live site, I would typically use the MS SQL Server Database Publishing Wizard to connect to my db and essentially "publish" to either a local .sql file (which is what I usually do so that I have a flat file to archive with my project) or directly to the sql server where the live site will have access to.

Unfortunately, the old Database Publishing Wizard won't connect with SQL Server 2008. Microsoft has decided to embed an updated database publishing wizard into their Visual Studio lineup, so now you have to download the entire program just to use a portion of the software.

Here's a link to the old Database Publishing Wizard which works just fine with SQL 2005: http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Here's a link to Visual Web Developer 2008: http://www.microsoft.com/express/vwd/

Also, as for the Authentication Mode ... if you select "Mixed Mode" for the Authentication Mode during installation, you shouldn't have any problems, but most people probably select "Windows Authentication Mode" which can cause some issues, such as you've experienced.

Here's a pretty good link I've personally used and should have given you yesterday as well (sorry) which also covers the TCP/IP Enabling, etc. http://semmle.com/documentation/semmlecode/installation/database-installation/mssql/

@Steve,

I installed 2005 w/ Tools. I was able to export data down from the live site to the development site with no problem, so I am not sure if my "publishing wizard" is all good. Going UP will be a different story, but not there yet :)

I would suggest installing MS JDBC driver for SQL Server 2005 as well and use it instead of built-in one. Some SQL statements won't work with built-in driver.

@Radekg,

Any ideas which types of SQL statements won't work?

Can't really remember exact statements that were causing it to crach but remember error message I was receiving. But the error message was similar to this one:

http://www.bennadel.com/blog/1112-Incoming-Tabular-Data-Stream-Remote-Procedure-Call-Is-Incorrect.htm

I remember using MS JDBC driver instead of built-in one I was able to resolve my issues.

This is perfect. Thanks for adding the takeaways instead of just a link. Now I can get this setup running on my laptop for faster development when I can't find a wifi hotspot.

ColdFusion can use SQL Server Authentication to connect to a SQL Server 2005 database (and needs to if you use the provided driver), but it doesn't have to. One of the things the JDBC driver provides is the ability to connect through integrated authentication.

If you're headed down that road (as we are), you may find the following links useful:

http://groups.google.com/group/macromedia.coldfusion.database_access/browse_thread/thread/a2b928cc703dfc24?pli=1

http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=6&threadid=1206110&highlight_key=y&keyword1=integrated

It's not particularly easy to set up, especially if SQL Server isn't your first language, but we did manage to get it working.

@Connor,

Glad to pass on the knowledge that was passed on to me :)

@Dave,

Thanks for posting the resources for that path.

And everytime I ask myself - why? Why would anyone want MS SQL Server ANY VERSION for a blog or any other mid-sized website or app? MySQL is so much better, so much easier and has such fine GUI-tools - and it's free! And it's installed in less than 5 minutes ;-)

Ben,

We too use SQL Server 2005 Express on our dev laptops, based on having the full version on our production servers, so we've been down this road a couple of teams each within our team over the past couple years.. I've ended up pulling together a pretty detailed set of steps that go through each install to avoid repeatedly hitting these same potholes...

A couple of other items I'd share along these lines:

1) I wanted the default directory for the db server's data files in a different location (e.g., a different drive) primarily for simplicity of backups. The only way I could find to specify that was to run the start the SQL Server installer from a command line with the INSTALLSQLDATADIR option like

sqlexpr.exe INSTALLSQLDATADIR="E:\DATA"

There are gobs of other command line options that could probably be used to address most of this for an unattended (and repeatable!) install, but that's the only one I ever /really/ wanted to use.

2) I also found a decent document on the MS site on how to manually uninstall in situations if you hork up the install process and want to start over, but don't uninstall all the various pieces in the right order. Trust me, I've been there and it is pretty easy to end up in a situation where you can't uninstall nor can you reinstall cuz stuff gets left around if you don't uninstall in the right sequence. Look for help at http://support.microsoft.com/kb/909967 in those situations.

Hopefully, those will be of use to people, too -- and thanks for pulling all of this together on this post. This would have been a great resource the first time we started down this path and ran into the same kinds of problems.

--
/ron

@Sebastiaan,

I'll give you the fact that MySQL is much easier to install and setup. However, once it is installed and running, they are both just SQL engines and are quite easy to use. There's nothing about MS SQL Server that is inherently worse about functioning. In fact, there are things about it that I like more, like having the ability to run conditional logic inbetween queries such as:

IF (@id = 0) BEGIN

SET @id = ( SELECT ... );

END

MySQL does not allow us to do that.

Once they are both up an running, can you think of any reason to use one and not the other?

@Ron,

Thanks for posting tips from the field.

@Sebastiaan,

Your question sounds similar to "Why not use PHP (it's free!) instead of ColdFusion?"

My response is that it's mostly personal preference, but as long as you can get the job done, it really doesn't matter what database/application technology you use ... so long as it's maintainable, dependable and scalable. I too use MySQL at times, but am most comfortable with and prefer MS SQL Server.

I have to admit, working with MS SQL Server this morning was giving me a huge headache. I was having trouble creating indexes. Management Studio needed to restarted a few times before that would work. The data export was not copying my primary keys or my indexes and I couldn't find a toggle to include those. Even my SCRIPT TO didn't seem to want to script my indexes. Plus, my connections didn't want to stick between management studio reboots.

I never liked Management Studio. I always preferred SQL Server Admin from 2000 or whatever it was called. That's the sad thing here - I don't think its actually SQL server that is giving me a headache - it's that Mangement Studio 2005 is a really really difficult GUI. It makes Navicat look like a little slice of heaven.

So the question becomes, does the poorness of the interface alone want me to switch database engines? Would I be willing to switch databases (potentially having to re-write MANY queries) just to not have to use Management Studio 2005?

That is a tough question!

@Ben: I suggest installing Management Studio 2008. Works fine with MSSQL 2005 and it is much faster but some Adobe apps (photoshop, livecycle designer) may stop working due to some changes in some DLLs.

@Radekg,

Hmmm, I'll think about it. Thanks.

No Photoshop? That would be a dealbreaker for me, I've got CS4 installed, so I do buttons and things in Photoshop, as well as the occasional fun thing. (Lucky me, it's the Master Collection. Not sure I need the whole thing, but who am I to protest?)

I haven't yet run into problems with SSMS 2005, but it's good to know that I might have to steer clear of 2008 for the time being. There should be freeware (or inexpensive) UIs out there, right?

@Dave: reinstalling photoshop helps but there is no solution for livecycle designer yet.

@radekg: thanks, I'll keep that in mind. I am still getting artifacts on my main monitor from Photoshop anyway, so it's not like that would be the only issue I'd have ...

Also, somewhat to answer my own question, there is a version of Toad for SQL Server. At my last job, we used Toad for Oracle (the shareware version) until we were told we needed to use a free tool, so we switched to SQL Developer, which none of us really liked.

http://www.toadsoft.com/

@Ben,

for one MySQL is free, it runs on any OS (Linux or Windows hosting), the Management Studio is a lot easier to work with (thank you WebYog), backup is a lot easier (scripting instead of dump-files) and performance and functionality wise it's getting closer to MS SQL.

@Steve,

I'll pay for Adobe ColdFusion any day - no PHP for me! But seeing what an excellent job Railo is doing lately (especially the Admin section per website) I'm wondering if I should go "open source/free" all the way and make the switch from Adobe ColdFusion to Railo ColdFusion ;-)

I am very interested in discussing SQL Server with you.

Please tell me WHEN.

Ben,

You may not be aware, but Express allows you to setup external apps to handle certain tasks. Well you can configure it to use SQL Servers's DTS Wizard.

Don't have the link handy but I am sure you can find.

Also consider eclipse with one of the various free Db management tools , many of them are quite nice and surprisingly powerful.

@Ben

I'm not trying to be a poster child for MySQL or anything but when you said:

"In fact, there are things about it that I like more, like having the ability to run conditional logic inbetween queries such as:

IF (@id = 0) BEGIN

SET @id = ( SELECT ... );

END

MySQL does not allow us to do that."

Are you referring to something like this:

DELIMITER $$
CREATE PROCEDURE `con_test`()

BEGIN

DECLARE s1 VARCHAR(50);
DECLARE s2 VARCHAR(50);
DECLARE id int(11);

SET id = 1;

IF id = 1 THEN SELECT user FROM temp.user AS s1 WHERE userID = id;

END IF;

SET id = (SELECT userID FROM temp.user WHERE user = 'Matt');

SELECT user FROM temp.user AS s2 WHERE userID = id;

END
$$

My inquisitive mind went to work. :)

I created a temp database named "temp" on my local MySQL install, a table named "user" with an auto-increment ID field (userID) as the primary key and a VARCHAR(50) field named "user". I then dropped 3 names (anthony, Matt, Tony) in and whipped up this procedure to test with. It returns anthony & Matt as expected.

I may be off the mark on what you were referring to, but it gave me an excuse to play around a bit which is always fun. :)

@Anthony,

Yeah, for some reason MySQL *does* allow us to use conditional logic inside of stored procedure, but not in a standard SQL statement. Seems odd to me.

In all fairness, it's not that crazy. Sometimes, I have a tendency to make my SQL statements TOO complicated simply because MS SQL allows me to use so much T-SQL :) Breaking things up into smaller SQL statements might not be such a bad idea.

@Ben

It seems odd to me as well. There are a few conditional functions you can use outside stored procedures (http://snurl.com/7thwu), but it's not the same as having the full gamut to me.

Although I must say I normally use procedures when I get that complex so I'd never tried to use conditions outside of that arena before.

It looks like 6.0 won't change things either.

@Anthony,

Yeah, those are good functions. I wonder why they don't mention COALESCE() there. It seems to be the same thing as IFNULL() only it can take even more parameters.

What is always fun is to have one customer on Oracle, a couple on MSSQL, and a bunch on MySQL and even a few die hards on Access. Then try to keep track of which one while writing queries. Reminds me of switching back and forth from Visual Basic to C#. "Is that a {,[,or (?"

@Don,

Ha ha, yeah, I bet that is frustrating. ACCESS SQL is super frustrating. Going between MSSQL and MySQL, I can never remember how to put JOIN clauses in my UPDATE/DELETE statements. MySQL and SQL Server do it very differently and I always have to look it up.

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.