Forta.com - Blog

Comments

Hi Ben,

I have a blog entry about CF & SQL Server 2005 Express.

http://www.demirkapi.net/avblog/index.cfm?mode=vie...

I think it would be helpful.

# Posted By O?uz Demirkap? | 1/10/06 6:47 PM

Great, thanks for sharing!

--- Ben

# Posted By Ben Forta | 1/10/06 8:11 PM

Thanks Ben!

Noted at http://www.actcfug.com/index.cfm?fuseaction=TipVie...

Am in the middle of two CF projects so sticking to 2000 for now (a well known quantity after all these years, eh?)

Thanks for the advice however!

PT

# Posted By Peter Tilbrook | 1/11/06 4:11 AM

http://www.robisen.com/index.cfm?mode=entry&en...

I think this link would be helpful to the users as well

# Posted By Mujeebur Rahmansaher | 1/11/06 3:03 PM

We have tested our Intranet and public website since an early release of SQL 2005 using CF MX & MX 7. Other than the mentioned user gotchas, which were small once I read the book, everything seemed to work great -- and was fast. Didn't notice any problems with our main CFC or stored procedures. They're not hugely complex, but I think our full production roll-out should go very smoothly. Anyways, just our observations! Cheers!

# Posted By Garrett Wiedmeier | 1/12/06 10:17 AM

Has anyone tested the sql 2005 xml data type with coldfusion? I would like to know if CF can handle it, and if so if it is return to CF as an XML object. (I can dream can't I?)

Thanks

# Posted By Craig | 1/13/06 4:40 PM

Just released from Microsoft, SQL Server 2005 JDBC Driver:

http://www.microsoft.com/downloads/details.aspx?fa...

# Posted By 6dust | 1/19/06 4:46 PM

We were in production with sql 2000 and CFMX7 for no problems for the past year +.

Ported to SQL 2005 and Cold Fusion (both load balanced) servers would have every 10-15 min, without any changes to the database, system settings etc. Just SQL 2005.

After a painful day of troubleshooting while in production, it turns out one of our tables needed to have indexes added.
After that the problems went away. Again, this was never an issue in 2000.

It appears there is either a bug in 2005 or some alternate way it processes queries.

Just want to make sure no body gets stung with this same issue.

# Posted By Michael R | 1/24/06 8:22 AM

If you were to download SQL Server 2005 JDBC Driver, what would you do with it to enable CFMX to use it?

Drop the jar file in the cfmx lib directory?

# Posted By Andrew Mercer | 3/13/06 10:46 AM

I have installed the SQL Express..and CF 7.0.1 and cannot get the DSN to work. This is what I get:
Connection verification failed for data source: Test
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'ed'.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'ed'.

Both the SQL server express and CF are installed on the same machine and I have created a user cfserver with pw cfserver to run the SQL server but I cannot connect to it and setup the dsn. Any ideas. I have set the TCP/IP and the authentication to windows/server.

# Posted By Ed Welch | 6/26/06 12:47 PM

the post is now here

http://www.robisen.com/index.cfm/2006/6/2/ColdFusi...

# Posted By robi | 8/18/06 4:59 PM

Have you tried MSSQL 2005 64-bit with CFMX7?

I'm guessing JDBC can handle both 32 and 64 bit platforms, but I haven't found out for sure yet, this should be invisible to the client.

# Posted By Justin | 11/8/06 1:58 PM

hi guys. I am trying to set up both Coldfusion mx 7 and SQL server 2005 to work, but still i am having troubles. I've enabled TCP\IP support in SQL server . but this is what i get when i am trying to add a new database to Coldfusion mx 7:
"Connection verification failed for data source: users
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01" plz help.

# Posted By ivan | 1/1/07 2:26 PM

I'm having the same problem as Ed Welch. Ed have you found a solution for this? I've been working on this for two days and a couple hours with MS.

Seems just the JRun is having a problem using CF 7.0.1. Wierd error to see Login failed for user.

I'm downloading updater2 now to update to 7.0.2. But would still like to know if you've resolved this and how. I can connect to the SQL 2k5s running 32 bit OS's but not the 64 bit OS.

# Posted By Len Nagy | 1/5/07 11:31 AM

Unbelieveable...the problem was caused by a userid that was too long. The SQL userid had 17 characters. We shortened it and bang, worked like a charm.

So ODBC, Mgmt Studio, even a java class could authenticate with the long userid, but CFMX 7.0.2 won't. JRE 1.4.2

We even tried installing the newest Microsoft JDBC driver ( type 4 ) for SQL Server 2k5, no joy.

Well, maybe this will save someone else two days of frustrating diagnosis, lol.

# Posted By Len Nagy | 1/5/07 3:47 PM

I was wondering if someone could help me. I am creating a website (for coursework) and I am using an SQL database and Coldfusion. I want to create an SQL query that will read in a users selection from a list(an <SELECT> <OPTION> format), match it to a database, then spit out the results into a marquee. I almost got it working then I fuffed it up. Can anyone help? Or reccommend any books to look at?

# Posted By Tracey | 3/6/07 12:46 PM

Does anybody know if it's possible to connect ColdFusion to SQL Server using a Windows Authentication?

# Posted By David Ball | 5/2/07 6:34 PM

David, CF is not running as a user, so there is no user to authenticate. I've not tried it, but, if you have CF running as a user account then in theory it should be able to authenticate as that user.

--- Ben

# Posted By Ben Forta | 5/2/07 6:36 PM

I am having trouble running ColdFusion Server 5.0 with SQL Server 2005.
Any suggestion on how to get the data sources to work?
The datasource verifies in CF Admin.

# Posted By David | 7/5/07 4:59 PM

David, CF5 came out 5-6 years before SQL Server 2005. We've never tested them together, and don't plan to. You really need to upgrade your CF.

--- Ben

# Posted By Ben Forta | 7/5/07 5:03 PM

Thanks for the feedback Ben.
Another subject: I cannot seem to find any refernece to a MySQL connection in the CFAdmin
ODBC configuration. What am I missing?
Thanks.

# Posted By David | 7/5/07 7:22 PM

David, honestly, CF5 is so long ago that I don't even remember if we provided MySQL drivers then, sorry. :-(

--- Ben

# Posted By Ben Forta | 7/5/07 7:27 PM

We've been searching the web over and over, again for this answer, but have found conflicting stories. We're running CF 7.0.2 on a windows 32 bit environment with SQL server 2000. It's worked fine for years. Well, we're about to upgrade that server to a Windows 2003-64 with a AMD64 processors. We'll also be running SQL 2005 64 bit. From what we've read sofar there shouldn't be any problems. I need to know if anyone has set CF up this way? If so, what were the problems and how did you fix them?

Or is it simply as easy as I'm reading on this page? Nobody mentioned what version of SQL 2005 they were running.

# Posted By Dave Melton | 7/6/07 9:12 PM

Dave, yep, the comments here work with 32bit or 64bit SQL Server. Of course, CF8 (in beta now) officially supports SQL Serer 2005 and 7 does not.

--- Ben

# Posted By Ben Forta | 7/8/07 9:46 AM

Realizing it may not be a wise decision for best performance to install CFMX 7 and SQL Server 2005 on the same machine, nevertheless, could it still work ok or is there some other reason why we should NOT attempt to get this to work?

# Posted By joe | 7/18/07 8:28 AM

Just an update to my prior comment. Sofar, all has installed and worked together just fine. Also, for those migrating from an old win2k server to a newer win2003 one, I'd highly recommend using microsoft's IIS migration tool.

# Posted By Dave M | 7/18/07 11:02 AM

We've got ColdFusion 7.0.2 on a 32-bit Windows clustered server, and are planning to upgrade to SQL Server 2005 on a separate 64-bit Windows server. Should that work okay, or would we be advised to wait until we upgrade to CF 8?

# Posted By Charles | 7/20/07 1:00 PM

Charles, yep, should be fine.

--- Ben

# Posted By Ben Forta | 7/20/07 1:16 PM

OK I've been beating my head against the wall for a week trying to figure out why could fusion wont recoginze my datasource.

I'm running vista, sql 2005 express and cf 8 devleopers edition.
Heres the error:
* Connection verification failed for data source: TestDB
An exception occurred when executing method verifydatasource.
The cause of this exception was that: coldfusion.sql.Executive$ConnectionVerificationFailedException: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running..
SQLCritUpdPkg_ENU
What does this mean?...

Here's what I tried so far to resolve this:
1. enabled tcp/ip in sql config manager, verfied that it's using port 1433 in IPALL
2. telnet 127.0.0.1 1433 - that works ok
3. dowloaded SQLCritUpdPkg_ENU.exe and installed it - I read on a fourm somewhere that this disabled some ports due to a worm.. but i dont think that was the problem anyway since i ping'd 1433 and it was ok
4. And yes I've logged in directly to SQL server using the u/p..
and I've changed the security settings in SQL to use both sql server and windows authenticaion

Any help from anyone would be awesome..

PS: I've also gotten this same error when I had CFMX 7 installed...

# Posted By Jason | 7/28/07 6:46 PM

We are planning SQL 2005 x64, CFMX 7 on a 2003 Web Edition server. I aware that Web edition will not load SQL 2005 on second server. Also that is is restricted to web apps only, to the point of not even sharing a printer. Any other problems?

# Posted By Mel | 8/23/07 3:59 PM

I too had just a few issues with configuration but aside from that Cold Fusion and SQL server 2005 seem like they where made for each other. I’ve been able to increase the amount of available resources for our business applications and thus have been delivering better service to both our clients and employees since we upgrade to 2005. We are now looking at performing several <a href=http://www.stratavia.com/datapalette.php>database upgrades</a> as the new SQL upgrade will allow us do these upgrades without service interruption to our clients.

# Posted By Denise | 12/21/07 3:16 AM

We have been having problems with our DB connections as well. We have just upgraded our DB server and Web server to Windows 2003 by wiping them and and doing a fresh install. When setting up datasources, we got the "login failed for user 'xxxx'" message. We are using SQL Server 2000 and have tried CF 6.1 and 7 with the same result.

Having read some of these posts, I tried configuring our DB server with a shorter password and eventually a blank password temporarily and this worked. After some more investigation, I discovered that using the pound sign was causing all our problems. It appears that this character gets translated along the line and the correct password fails to be sent to the SQL Server.

# Posted By Andrew Barber | 5/19/08 7:34 AM

Ben,

I am using Coldfusion 7 with Microsoft SQL Server 2005 express on my laptop. Everything seems to be working fine except, when I try to do an update or insert, nothing happens. I do not get an error, but at the same time nothing gets inserted or updated in the database. When I place that same code (SQL and Coldfusion) on a production server everything works fine. I recently upgraded my ram on the laptop to 2 Gig, thinking it may be a memory problem, but nothing changed.

Please help, I'm at my wits end here.

# Posted By DD | 6/24/08 10:18 AM

DD, you need to look at SQL Server logs or traces to see what is happening.

--- Ben

# Posted By Ben Forta | 6/24/08 8:41 PM

Hey all..

We're on CFMX 7.02, windows 2003 web edition, apache 2.0.58. We've had endless problems with cpu saturation in Sql Server 2005 -- even after completely upgrading hardware and grabbing latest OS and SQL patches, the problem has returned. We've been fighting this from different angles, adjusting cfqueries and making sure DB config is optimal for our environment.

I came across the kb article here:
http://www.adobe.com/go/42dcb10a

And thinking about applying this latest version of the datadirect drivers for cfmx7. What caught my eye was the statement:
61161 - Insert trigger causes CPU to spike to 100% using the 3.4/3.5 drivers.

Comments? What is the official answer on CFMX7 and SQL Server 2005.. is 2005 supported?
We're already down that road so too late for us, curious if you guys agree with trying out these latest JDBC drivers first before trying Coldfusion 8. Thx. -Steve

# Posted By Steve | 9/2/08 8:00 PM

Here are some important points to add:

1. If you are using a secondary MSSQL instance, the default listening port (1433) is not enabled. You must go to the SQL configuration utility to configure a static port. This port must be different from the listening port on the primary SQL instance. If the ports are the same, the second instance will not start.

2. Chen configuring the CF server field, if you are using a secondary SQL instance, you will need to use the format <server>\<instance name>. If you are missing a hosts file entry for the SQL server on the unix side, you will have to use the IP.

# Posted By Timothy Lutz | 10/24/08 3:35 PM

[Add Comment]

ColdFusion And SQL Server 2005