I've had to search for this several times in the last few weeks, (ever since Mark Drew pulled me into the dark side that is local development.) I figured if I blog it, I'll always have the answer at hand.

If you are restoring a Microsoft SQL database from a backup onto a new server, and you need to re-associate the server login name to the database username, you need to call the "sp_change_users_login" stored procedure. That will allow you to reconnect them.

I ran into my first inexplicable crash that I eventually traced back to the ColdFusion Server Monitor. Now first off, this isn't a problem or bug with the Server Monitor. This is to be expected. The server Monitor adds overhead to requests, and if you have an intense process, it's going to generate a lot of monitoring data. It's possible that you might reach its limit.

I just wanted to let people know what a crash caused by the monitoring service looks like, because it doesn't give you a message that "You have left the monitoring service on in production!"

I had a long running complicated process crashing on my local workstation. It did work on our communal development server. So it wasn't just the process itself. I thought maybe it was that my laptop wasn't a server class machine. But actually, the virtual machine that we are testing on wasn't tremendously more powerful.

The browser session would error out with a message that said:

500

Java heap space

java.lang.OutOfMemoryError: Java heap space

After digging in the JRun logs for awhile I found this:

Of course I didn't bother actually reading this error until just now when I copied and pasted it. It clearly indicates that the problem is in the Monitoring Servlet Filter. In any case, after much trial and error, I turned off memory tracking and then turned off profiling. Once I turned off profiling the error went away.

I had to do a repetitive database task yet again the other day, and I stumbled onto the fact that Ant has full blown support for SQL. The only challenge is getting it to work with the Microsoft SQL we use here. It's not exactly self evident, mostly because I'm not a Java programmer, so I figured I would share the information.

First download the Microsoft SQL JDBC driver from Microsoft:

Download SQL Server 2005 JDBC Driver 1.1

Once that's done, execute it, and place the packaged contents somewhere on your computer.

Then fire up Eclipse. You'll have to add the JDBC driver to the classpath for ANT:

• Go to "Window"
• Choose "Preferences"
• Expand "Ant"
• Choose "Runtime"
• Select "Ant Home Entries" This will cause buttons on right to be enabled
• Press "Add External Jar"
• Navigate to where you put the SQL JDBC driver
• Add sqljdbc.jar
• Hit Apply and hit "Ok"

Now that we have a SQL Driver on our machine, all that's left to do is to write the Ant tasks that will use it.

<sql driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"

url="jdbc:sqlserver://${dbserver}:1433"

userid="${dbusername}" password="${dbpassword}"

print="TRUE">

SELECT CURRENT_TIMESTAMP

</sql>

Obviously, you'll want to use your application's database password to do that. Also, I included the SQL directly, but you can also call a .sql file with statements in it.

There are a few gotcha's:

• "Go" intermittently causes issues. Use a semicolon to separate statements
• Use [database name] doesn't seem to work
• Putting Database passwords in an .xml file will expose them.
  • Rename build.xml to build.ant.
  • Make sure you don't expose .ant files to your webserver.

What can you do with this? Well I have a couple of ideas:

• Delete rows created by tests.
• Run Consistency checkers
• In conjunction with an export script, synchronize schemas and stored procedures between two databases.

You may also want to look at the documentation for the SQL Ant Task.

You may or may not have noticed a little unplanned downtime. The webserver that Numtopia.com is on died last Thursday. My hosting provider got ColdFusion up by Saturday. But I don't run BlogCFC like all the other cool kids, I run Movable Type which needs Perl and a special MySQL Perl driver. Getting my hosting provider to re-install it (it was there pre-crash) was an exercise in patience. It took almost a full week for them to do so.

Now I've been oscillating between impatience (I want this up) and understanding (this isn't a business, and I've had to deal with people with custom configs post crash.) I've decided that for ColdFusion hosting, for $12 a month; 1 week of downtime for my ability to post to my blog over the course of 2 years, is probably acceptable.