At work, someone made a request that required me to look through potentially hundreds of views in dozens of databases on our SQL Server. I certainly didn't want to examine each one at a time. How could I speed up this process with code?

Well, you can find all of your views by querying the sysobjects table, and you can retrieve the SQL behind the views by querying the syscomments table. Something like this works well:

This will retrieve the SQL code and names of all the views in the current database. This simple query is the heart of the solution. But I would like to retrieve this information for all of the databases.

Well, it's easy enough to get a list of all the databases. The sysdatabases table in the master database has that list. You can query that table, perhaps filtering out some of the system or sample databases included with SQL Server:

Now just combine this information. To accomplish this, we'll build a stored procedure that will create a temporary table, loop through the databases and query each one for its views, insert the view information into the temporary table, and return the temporary table.

Something like this will do the trick:

Now just execute the stored procedure and review its output.

This portion of the solution just retrieves the data. After writing this, I developed a really short and simple ColdFusion application that would output the database name, view name, and SQL to a table, and used some simple JavaScript to make it easier to search and filter the views. The client-side methods used to view and work with the data are obviously up to you.

If you've tried calling ColdFusion CFCs with RemoteObject over SSL in your Flex apps, you probably feel my pain already, because you have either tried to get it working and gave up, or had a heck of a time getting it working. Adobe's documentation for this specific need wasn't helpful to me, and discussion about it online is relatively sparse and unsure as well, from what I've seen. My intranet environment at work demanded that I get remote communication with CFCs working over SSL, so as I neared completion of my first couple Flex apps, I needed to get an understanding of this issue. Hopefully it will be useful to others.

So, what's the problem? In a nutshell, when you try to use the RemoteObject component in a Flex app to communicate with CFCs, the default installation of ColdFusion 8 and Flex Builder 3 will compile your app to communicate with the ColdFusion server over HTTP (http://yourserver.com/flex2gateway/), not over HTTPS (https://yourserver.com/flex2gateway/cfamfsecure), regardless of whether the SWF was loaded over HTTPS or not.

If your site exists in both HTTP and HTTPS, you may not even be aware this is happening. I wasn't aware of it while I was developing on my local machine. But our production server hosts its site only in HTTPS and the site on port 80 is a nearly empty site that redirects all requests to the SSL-protected version of the site. So this behavior broke my Flex app.

If your site exists only in SSL or sensitive data is being transmitted to and from the Flex app and the CFCs, you probably are trying to get RemoteObject to communicate over SSL.

Quick workarounds. If you have time and leniency to skirt the issue, you can avoid the RemoteObject/SSL issue. You can always use the HTTPService and WebService components instead of RemoteObject. But like most workarounds, this has its disadvantages and limits you from the benefit of using RemoteObject.

I imagine you also could set up a ColdFusion mapping to make the CFCs available on the HTTP site, but this solution is viable only if you really don't care about security, in which case you probably wouldn't have your site wrapped in SSL anyway.

The solution. What makes the solution tricky is that there are a few things that have to be configured properly, and there are some pitfalls along the way that can be misleading. The primary solution lies in some changes to the ColdFusion server's config files services-config.xml and remoting-config.xml. These both reside in {ColdFusion installation}\wwwroot\WEB-INF\flex\. For instance, on my Windows workstation, this is located at c:\ColdFusion8\wwwroot\WEB-INF\flex\.

1. Add the secure channel to remoting-config.xml. ColdFusion 8 already has a channel in its configuration for RemoteObject over SSL--it is called "my-cfamf-secure"--it just isn't configured to be used. So you have to add the "my-cfamf-secure" channel to remoting-config.xml. To do this, add <channel ref="my-cfamf-secure" /> to the <default-channels> node and the <channels> node for the "ColdFusion" destination.

In other words, your new remoting-config.xml file should look like this:

This brings me to the first pitfall to watch out for. Be sure to list <channel ref="my-cfamf-secure" /> ahead of <channel ref="my-cfamf" /> so that ColdFusion will try the secure channel first.

2. Add a property to make IE happy. At this point, your configuration will be adequate for Firefox. However, IE still seems to get tripped up and tries to access the insecure channel. To fix this, just add <add-no-cache-headers>false</add-no-cache-headers> to the <properties> node of the <channel-definition> nodes for "my-cfamf" and "my-cfamf-secure" in services-config.xml.

I won't display the entire services-config.xml file here, but your "my-cfamf" channel definition will now look like this:

And the "my-cfamf-secure" channel definition:

Some more pitfalls to consider. These can be really misleading, making you think your changes aren't working.

A. Flex Builder incorporates these settings into your app when it compiles the SWF. Go to your Flex project's properties, under "Flex Compiler", and you'll see a reference to your services-config.xml file. If your SWF is compiled on a workstation that hasn't had these changes, it will not work on your server, even if you did change your server's config files. So be sure that your local installation of ColdFusion has its services-config.xml and remoting-config.xml files updated just like your production server.

B. On a related note, be sure to recompile an app if you've compiled it before making these config changes. You can recompile in Flex Builder by going to Project > Clean. That option will ensure that you have a clean, recompiled SWF.

C. If your SWF file is actually served on port 80, it will not be allowed to communicate over the secure channel unless you tell it that it is permissible to do so by setting up a crossdomain.xml file. Check out Shannon Whitley's post SSL, crossing domains, and Flex to read a bit on that.

Tools for the job. With the right tools, you can test all of these things out on your own. For instance, Firebug and Fiddler are great tools for Firefox and IE respectively that clearly show your Flex app's behavior, and whether it is attempting to access the secure or insecure channel. They also show the app attempting to access a crossdomain.xml file when the SWF file was loading over HTTP, because it's first attempt is to make a cross-domain connection to the secure HTTPS channel.

The great thing about this configuration is that your SWFs will use the secure channel when they can, but silently fail over to the insecure channel. So your development workstation doesn't have to use SSL, but your compiled apps will utilize it when you move them to your production server. This is cleaner than setting up a separate "Destination" in remoting-config.xml, or other solutions that require changes to your app's code. Alas, I am still green in the Flex universe and don't claim anything different. Nevertheless, this solution is working perfectly for me and I hope it proves useful for you.

Both Linux and Mac OS X have zip, gzip, and bzip2 command-line tools. What about Windows? If you're trying to do some scripting to automate some archiving or backup, and you want it to be a classic, WinZip-compatible .zip file, how can you do it?

WinZip offers a WinZip Command Line Add-on free of charge--if you already own a copy of WinZip Pro!

You shouldn't have to pay for command-line zip. And you don't have to. Enter Info-ZIP. This workgroup has been maintaining free, portable, high-quality versions of zip and unzip. They have plenty of command-line arguments like you would expect from an open source project.

So, with this project's executables in your system path, you can write up a batch file that is executed as a Windows scheduled task. Maybe something like this:

This will zip the c:data directory. Arguments: -q to do it quietly, -S to include system files, -r to recurse into subdirectories. Finally, use -i to point to a file that indicates the exact files to include, by means of a carriage return delimited list.

You can alternatively use -x to specify only which files should be excluded. Perhaps something like this:

The command-line flags are all optional, of course. This tool is certainly a must-have for the Windows scripter.

Ray Camden recently gave another friendly reminder to use a site-wide error handler for your ColdFusion site if you're not using <cferror> or onError in your Application.cfc. I've been doing a lot of fine-tuning on my intranet apps at work lately, and realized I never implemented any across-the-board error protection like this.

So, using TechNote 19198 as a starting point, I began writing my error handler.

Being that all my apps go into Subversion now, I am obsessive about putting specific settings of an app into config files, so that my light OCD doesn't flare up when those settings need to change (heaven forbid I bump a revision number just for a configuration change!).

This presents a light challenge for a couple reasons. First, the error handler is executed within the application scope of the application that threw the error. It does not automatically run the application.cfm or application.cfc in its own directory. Second, the working path is also the path of the template that threw the error.

These facts threw a wrench in standard operation for me. I store my config file settings in the application scope; that wasn't going to work for the error handler. And I'd like my config file to be in the same directory as my error handler, so I need to be able to FIND the config file.

Finding the config file is easy. Although ExpandPath() will operate from the working path of the erring template, GetCurrentTemplatePath() will still return the error handler's path. Use the GetDirectoryFromPath() function and append the filename of your config file.

Great, now we have a path to look at.

I use a custom tag (hey, it was written before CFMX) named <cf_AppConfig> that receives a config file path, retrieves the application settings from the file, and stores them in the Application scope. In my case, I needed to modify this custom tag to allow me to specify the scope the settings would be written in. Then, I simply write the settings in the Variables scope instead of the Application scope, to avoid accidentally overwriting any values of the existing application (or throwing an error if the Application scope isn't available!).

I can now cleanly retrieve settings from a config file for my error handler.

What kind of settings am I placing in this error handler's config file? Well, the error handler accomplishes three tasks: (a) Display a friendly error page, instead of the ugly ColdFusion default error handling. (b) Log the error with <cflog>. (c) Send details of the error via email.

That said, I put the email sender, recipient, and subject line in the config file. I also could put the name of the log file that <cflog> should write to.

I also have a config setting for the "Mode" of the server. In my case, I have a <cf_SiteConfig> custom tag that retrieves various site-wide settings from a global config file stored outside the web root. The "Mode" is one of those settings; it is set to "Production" for our live server and "Development" for my local PC and my QA server.

By looking at the "Mode" of your server, you can have your site-wide error handler "disable" itself on your development boxes. After all, I don't want to be getting email notifications all day long from my development server as I'm debugging my apps, and I don't want to hide the error information. To do that is simple. Manually throwing an error in your error handler will cause ColdFusion to abort the error handler and resort back to its default error management.

You could accomplish something similar by just looking to see if you're on localhost, or 127.0.0.1.

That way, if the browser is on the same machine as the server (i.e. while you are developing locally), the error handler would abort. The CGI.REMOTE_ADDR approach would work for you while developing and browsing on a local machine, but would not work for a QA server. For that, store the "Mode" in a config file.