As a database consultant, I'm always building connection strings to remote servers in order to run SQL scripts on them. But when developing on a client machine, I would often forget where I stored this content (i.e. strings and scripts), or I could no longer access the source machine. What I wanted was a quick way to store and retrieve this content on any machine, without having to remember where I last saw it. I also wanted to share this content with co-workers, who would ultimately use some of it in their own production environment. To do that, I wrote a simple Visual Basic app that links to a shared SQL database of strings and scripts (called a "library"). That way, my content is always available, on any machine, by simply installing the app over the Web.
Typically, when I work on a client machine, the app is quickly downloaded so that my tools are immediately available. When finished, I sometimes remove the program for security reasons, although the program offers password-protection to prevent credential exposure.
For example, I recently helped install merge replication on a fleet of laptops housed in remote locations where the connections were spotty. Using replication scripts that I prepared in advance, co-workers could connect to those machines, install the app over the Web, and execute the setup scripts without editing them. Because run-time information was needed by these scripts, some queries were parameterized (a feature that the app supports), meaning that prompts will appear before execution (such as asking for user credentials). This saved a lot of time, since no cut-and-paste was required by staff to modify the base scripts. It also enabled me to silently maintain everything in the shared library.
This program may link to two libraries of strings and scripts. One is arbitrarily called Remote and the other is called Local. The app treats them the same, so that each can be anything (but not each other). These libraries are independent of each other. When using the app, you may switch between them at any time, or copy content from one to the other. That way, you can share one library with co-workers while the other is used privately. You only need to connect to the libraries that you want to use. If you choose to avoid using any libraries, you can still run the program to test and run interactive SQL. You can also save your connection strings in the app's combo box by hitting the Enter key (use Del to delete). Typically, Local refers to the library that's located on the machine that's hosting the app, and Remote means anything you want to share.
As a convenience, Web addresses (e.g.. http://www.dropbox.com) are considered valid connection strings, but you won't be running queries on them.
When the program opens, you are presented with an input box for entering connection strings and a window for writing SQL:
Fig. 1
After entering a connection string to some database, press the Connect button to open a session. If successful, the SQL window will display some stats about the server it found and a list of databases on the server:
Fig. 2
Then you can write some SQL before pressing the Execute SQL button to run your code:
Fig. 3
All we need now is a library to store (and possibly share) this connection string and SQL script.
Let's assume that WESTSHORE\SQLEXPRESS is the remote server (I'll use my home network for this article). To instruct the app to build a library on it, we must first connect to it in some way:
Fig. 4
After successfully connecting, we can build the library using the following commands in the drop-down list (Fig. 5).
Build Library database
Build Library tables
Build Library database login (optional)
Each command simply puts a script into the SQL window. Press the Execute SQL button for each of them (in proper order, of course). The third command is optional, allowing SQL Server Authentication instead of Windows Authentication. It does this by adding a login TestConnection with password test+connection (you may edit any script before execution).
This authentication is normally meant for non-domain users, but we'll use it here anyway. If you avoid using the third command, then the app will need to use Windows authentication to connect to it, as explained next.
Fig. 5
Now that the Remote library has been created, a connection string to it must be prepared for the program (Fig.6) so it can remember where to retrieve content. Here, you build the string that you want the program to use when connecting to the Remote library:
Fig. 6
After testing the connection string to the Remote library, you instruct the program to remember it by running the command: Enable connection string as Remote Connection:
Fig. 7
After you do that, the Connection Strings and SQL Scripts buttons will be automatically enabled (providing the Remote option in the Library box is selected).
Pressing these buttons (Fig. 8) will open forms that contain the Remote library's connection strings and SQL scripts.
Fig. 8
If you select Local in the Library box, they'll be disabled because there's no Local library. To enable them, simply add a Local library to your own machine in the same way that the Remote library was added. Again, there's no difference between these libraries (except content, perhaps). The app allows two of them so that one can be used privately, if you so choose.
Let's now assume that the program's libraries have been installed on both machines.
Suppose you want to change the location of either library later on. To do this, you can edit the program's connection string to either library by selecting the command: Configure Application.
A form will appear that allows you to change these connection strings, along with other settings (such as the app's start up connection):
Fig. 9
To test new connections, press the ! label to the right of each connection box.
This form is also used to set other features of the app.
The SQL Parameter Delimiters text boxes define the program's delimiters for interactive SQL, such as:
SELECT * FROM Employee WHERE LastName = '@@@Last Name?@@@' AND FirstName = '@@@First?@@@'
The Prompt for SQL Save options tell the program to silently save (to memory) the last SQL written, or require confirmation.
Now that the app has Remote and Local libraries, let's populate the Remote library with some content.
Suppose we want to, say, list server specifications on the Remote server, and share the SQL script (and corresponding connection) with others.
The easiest way to do this is simply to select Remote in the Library box before pressing the Connection Strings and SQL Scripts buttons to open data entry forms.
The program doesn't need to explicitly connect to the server containing the Remote library, since it already knows how to connect to it (a connection automatically occurs when each button is pressed).
But if you want to test your content (or create it dynamically) you must first connect to the Remote server before running SQL:
Fig. 10
When finished testing your SQL, press the Connection Strings button to save your connection string to the Remote library (Fig. 11).
Fig. 11
Similarly, press the SQL Scripts button to save your SQL script to the Remote library (Fig. 12).
Fig. 12
To copy the contents of the Remote library to the Local library, press the arrow label (Þ) from Remote to Local in the Library box.
The program doesn't need to explicitly connect to either server since it already knows the connections. Also, the current Library option is ignored.
So you just need to press an arrow label to copy content:
Fig. 13
Sharing content is a primary feature of this app, but writing interactive queries is also useful. When I work on a client's workstation, the program is installed so that I can quickly connect to the Remote library and retrieve content. If I produce a new script that's useful to others, then it's saved in the Remote library for others to use. Typically, that library is located on some machine in the client's own domain (so everyone can use it) while the Local library is used as a private store. That way, I no longer have to email scripts to clients, and bug fixes can be made without announcement.
Scripts that allow me to view backup information on production servers, viewing the replication status of all corporate workstations or running maintenance tasks on remote laptops are a few ways that I use this program from any machine.
Furthermore, there's no footprint of my activity on the host machine after the program is removed. So corporate passwords aren't accidentally left behind in some recently used scripting file.
Web addresses are valuable too, since relevant client sites can be quickly referenced (such as software installation pages or a corporate telephone directory).
There are cut-and-paste buttons all over the place. For example, the Copy SQL button copies the contents of the SQL window to memory while the Restore SQL button allows you to restore it to the SQL window later on during your session with this program. These buttons come in handy when screen-sharing over a slow connection (or where Ctrl C doesn't work in a multi-tier environment).
One of my favourite interactive queries looks for database relationships involving a single table:
SELECT
sys.foreign_keys.name,
parent_table.name AS parent_table,
parent_column.name AS parent_column,
referenced_table.name AS referenced_table,
referenced_column.name AS referenced_column,
sys.foreign_keys.delete_referential_action_desc,
sys.foreign_keys.update_referential_action_desc
FROM
sys.foreign_key_columns INNER JOIN
sys.tables AS parent_table ON
sys.foreign_key_columns.parent_object_id = parent_table.object_id INNER JOIN
sys.tables AS referenced_table ON
sys.foreign_key_columns.referenced_object_id = referenced_table.object_id INNER
JOIN
sys.columns AS parent_column ON
sys.foreign_key_columns.parent_object_id = parent_column.object_id AND
sys.foreign_key_columns.parent_column_id = parent_column.column_id INNER JOIN
sys.columns AS referenced_column ON
sys.foreign_key_columns.referenced_object_id = referenced_column.object_id AND
sys.foreign_key_columns.referenced_column_id = referenced_column.column_id INNER
JOIN
sys.foreign_keys ON sys.foreign_key_columns.parent_object_id =
sys.foreign_keys.parent_object_id AND
sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id
WHERE parent_table.name = '@@@TABLE?@@@' OR
referenced_table.name = '@@@TABLE?@@@'
ORDER BY
parent_table,
referenced_table
I work with a lot of legacy data, and it helps to have such queries on standby no matter where I'm working. Sometimes I'll run a fixed query on several connections in sequence (stored in the library, of course) to help detect structural differences.
Test Connection acts like a virtual sidekick where you can maintain and execute a shared library of SQL on any machine without requiring the presence of other software (such as SQL Server or Visual Basic).
It's a very small program, so it installs and loads in seconds.
Since the program knows nothing about SQL (except for the tiny code behind the Connect and Execute SQL buttons), it can be modified for other scripting languages.