I recently had the requirement to execute a DTS on a remote SQL Server via ColdFusion. Doing it via CFExecute was the only method I could find in the Books online, and all the ASP scripts I found seem to asusme your SQL server runs IIS or is on the web server your site is on. Luckily I eventually found this rather handy answer over at pengo works. http://www.pengoworks.com/index.cfm?action=articles:spExecuteDTS the only caveat I have found is that you cannot do anything remotely to the SQL server. I found that I had to login to the server, run enterprise manager, login as the DBO of the database I want to install the DTS on and do it from there. Otherwise it will not run.

On on of our CFMX7 multi-server installs, we started getting these 2 errors when a customer tried to setup a MS Access DSN. Error 1

Unable to update the NT registry.Cannot open
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources: Windows error
number 5 occurred. Access is denied. This error is actually caused by the user under which JRUN or ColdFusion MX runs under not having access to the specified registry key. This is most likely to happen if you are running the CFMX service under a user other than SYSTEM. Which was the case for me as our servers are locked down and secured and do not use any of the default install configurations. You need to edit the permissions on the ODBC key and give permission to all users under which your CFMX services run. Error 2

Unable to update the ColdFusion MX 7 ODBC Server.An error occurred when
performing a file operation WRITE on file
C:\JRun4\servers\\cfusion.ear\cfusion.war\WEB-INF\cfusion\db\slserver54\admin\xact.inp.The
cause of this exception was: java.io.FileNotFoundException:
C:\JRun4\servers\\cfusion.ear\cfusion.war\WEB-INF\cfusion\db\slserver54\admin\xact.inp
(The system cannot find the path specified). This one is caused by the non existence of the folder "slserver54". If you are running multiple instances, this folder is not copied by default when you create a new instance, so you will need to copy it over from the default "Cfusion" instance. the location where this folder should on your installation be is given in the error.

Many people have had the old creeping death problem, where JRUN just consumes more and more memory until it finally keels over and dies. Often this has been due to a memory leak that a patch has fixed or soimething easy to identifiy like massive cached queries. But we just had a client with the problem, and their server was CFMX7.0.1 and all patched up, so it had to be an application problem. It turned out to be a rather unusual one, so I thought I would blog it. Having spent a considerable time looking at all the usual issues, caching, or lack of, persistent variables, slow running pages, drivers etc, and tweaking code that looked like it might be the cause, I was coming up empty. Then after dumping out the entire session scope for the application, I discovered that the clients shopping cart CFC was persisting itself inside every session, and with over 1200 sessions in use, this was just eating up more and more memory. This handy bit of code, is what will dump out all your sessions. Thanks to Alex @ pixl8.co.uk for his help and putting me onto these handy java methods. <cfscript>
sessiontrackerObj= createObject("java","coldfusion.runtime.SessionTracker");
activesessions = sessiontrackerObj.getSessionCollection(application.applicationname);
nosessions=ListLen(structkeyList(activeSessions));
</cfscript>
<cfoutput>
<h3>Total Sessions : #nosessions#</h3>
</cfoutput>
<cfdump var="#activesessions#"> So the lesson to be learnt here is, watch out what you put in your sessions or other persisted scopes. In this example you only needed a single instance of a CFC to be invoked, and it should be used to populate an external structure in the session. Another example of why using the THIS scope in a CFC is bad. Here is a nice blog on other runtime methods you can use to access and manipulate session data. http://jehiah.com/archive/extended-operations-on-coldfusion-sessions

Something interesting I just discovered. For some reason only known to Macromedia ColdFusion makes seperate db calls outside of the and it does not use the the username/password you have specified in your tag. So If you do not store the username/password in the DSN (which you shouldn't do anyway on a shared server) you are going to get the error below in your application logs. [Macromedia][SQLServer JDBC Driver]Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01 From my testing it appears that the transaction processing does actually still work, and rollbacks do occur if all queries do not complete. But there may well be some other drawbacks to this behaviour. To avoid this error you can move all your transactional processing into stored procedures instead.

I really need to start storing this somewhere, so my blog seems as good a place as any. Lets start with some useful links. ColdFusion MX: Tips for performance and scalability As you will find mentioend above, it suggests updating your JVM. This is certainly one of the first things you should do to boost performance. the JVM that comes with CF is a bit pants. You may also find that you have problems with the JDBC drivers that come with CF. I would also suggest upgrading these. The MySQL drivers that comes with CFMX doesn't support MySQL4.x proeprly for a start,s o you might want to upgrade that.