Getting ColdFusion To Work With MS SQL Server Express 2005 Databases

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

@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/

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.

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:

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.

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,

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.

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

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. :)

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

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 (?"