Installing SQL Server
When it prompts you for an install directory, it'll be something like "c:\downloads\x86". do not just accept this as the default. Add "\sqlserver2008" or something onto it, such that your destination is c:\downloads\sqlserver2008\x86.
You run the installer as normal. It'll probably prompt you to install the latest version of .net. This will take a while. You'll need to reboot.
When this happens, go through the normal install rigamarole (click Next a dozen times, etc).
The first roadblock I hit was this message: "The SQL Server 2005 Express Tools are installed. To continue, Remove the SQL Server 2005 Express Tools." So I hit the Back button a few times, but there was nothing in the "Choose components" box indicating sql server 2005 Express Tools. Off to google I went, and found this right away.
So, fix #1 was to uninstall SQL Prompt. Done. Moving right along....
The rest of the install went well.
One thing though: When I had to chose the instance name, it wouldn't let me choose the Default Instance, saying I already had an instance with that name. So I used "mesher" as the instance name. This is important for later.
Connecting to ColdFusion
At a minimum, you'll need to enable TCP connectivity for sql server. This is simple:
1. Start -- All Programs -- Microsoft SQL Server 2008 -- Configuration Tools -- SQL Server Configuration Manager
2. in the "SQL Server Network Configuration" section, find your "instance" and look for tabs for Protocols. Shared Memory should be enabled, but the others probably won't be. Right click on "TCP/IP" and enable it. Do the same for the "SQL Native Client" section.
At home, this is all I had to do to get CF talking to SQL Server 2008. At work, this is where the fun started.
I initially tried setting up a DSN in CFAdmin, with the server name of "bltr-mesher2\mesher", port 1433, but kept getting the "The requested instance is not running or isn't valid" error message from CF. This part bears some detail. Note that I used "\mesher" to denote my "mesher" instance. Remember... it wouldn't let me use the default instance when I installed. So right about this point, I was stumped. I KNEW the server name was right. So it must be a port problem. I didn't have any port problems when I did this at home, and as far as I knew, sql server always ran on port 1433. So what gives? I went to opened a cmd prompt and ran netstat, but didn't see anything suspicious. I tried removing the instance name, tried changing the port, but no luck.
Back to google where I found Matt Woodward's post, about SQL Server 2005 and CF.
The port settings described in this post were part of my problem. So I went to TCP/IP in the config manager, right clicked, selected "properties", then the "IPAddresses" tab, and sure enough, I had 0 in the "dynamic port" boxes and the port numbers were screwy. (When I installed 2008 at home, I had no such port BS to deal with.) So I blanked out the dynamic port boxes, added "1433" for the port in IPAll, and restarted.
Still no dice. WTF?
I played around with various things, googled and whatnot, telnetted in at port 1433 (worked fine), but couldn't figure it out. Finally, on a whim, I removed the instance name again from the "Server" field in CFAdmin.
And it worked.
I have no idea why this works. I'm sure there's a perfectly valid reason. I just don't know it.
Summary
1. When I got the error about SQL Server 2005 Express tools, I uninstalled SQL Prompt
2. When I chose my instance name, I wasn't able to select the Default instance. If you can select the default instance, do so unless you have a good reason for not doing so.
3. You have to go into SQL Server Config Manager and
a. Enable TCP/IP
b. Blank out dynamic ports and set your port number to 1433
4. After all this, if you're still having trouble connecting CF to SQL Server, and you've used an instance name, try removing the instance name from the DSN "Server" setting.
Subscribe in a reader
2 comments:
That sort of makes sense. You can only have one instance listening per TCP port, can't you? So the instance name doesn't really matter when connecting with TCP, because there's only one instance that can listen there.
the part about only one listening instance on a port makes total sense. i'd like to see the if() statement, wherever it is in the driver or sql server or some other communication layer, that's saying "if there's a named instance and it's listening on a port (default port only?), then if the instance name is passed in the JDBC url, go apeshit".
cuz that's what it looks like from the outside.
Post a Comment