Replacing MS SQL “Enterprise Manager” with Emacs

Emacs can be used to talk to MS SQL-Server. The benefits include, among other things, that you get a history of all of your commands in a single text file and you can bring up previously entered comands with M-n and M-p just like you do at the Lisp REPL prompt. Sometimes its just not worth opening up Enterprise Manager if you only have a quick thing or two to edit or check on. And if you’re doing your development in EMACS, then you might as well stay inside of it instead of jumping out to another program….

To connect, just type M-x sql-ms. You’ll need to know your server name and password and stuff, of course, when the command window prompts you. Once in, you can type SQL commands to your heart’s content. If you’re wondering why your commands don’t return anything you either forgot to type “GO” to send the command… or maybe there was a syntax error in your SQL. Type something like “Select 1” and “GO” to find out what the error message was. (For some reason, you have to send a working query in order to get the error message back.)

The following queries can replace some common tasks done from within Enterprise Manager:

–Get Table List
SELECT SUBSTRING(name, 1, 20) FROM sysobjects WHERE xtype = ‘U’ ORDER BY 1

–Get Columns List
SELECT SUBSTRING(name, 1, 20) FROM syscolumns WHERE id=object_id(‘Your_Table_Name_Here’)

–Get Stored Procedure List
SELECT SUBSTRING(name, 1, 20) FROM dbo.sysobjects WHERE (type = ‘P’) ORDER BY name

–Get Stored Procedure Parameters
SELECT SUBSTRING(parameter_mode, 1, 10), SUBSTRING(parameter_name, 1, 20), SUBSTRING(data_type,1,10)
FROM information_schema.parameters
WHERE specific_name=’Your_Proc_Name_Here’

–Get Text of Stored Procedure (ugly)
sp_helptext ‘Your_Proc_Name_Here’

And now the bad news…. Due perhaps to quirks of both isql and emacs, queries with several columns can look pretty ugly when you get their result set. That’s why I used SUBSTRING in the commands above. (I can’t figure out how to turn off the automating line-wrapping.) For one thing, the column names get shifted over annoyingly because a string representing the amount of time it took to run the query gets printed at the beginning of that line. Also the column widths are generally way too big.

If you want to take the text of a stored procedure to another buffer, just type C-SPACE to set the mark after you type GO for that command and just before you hit ENTER to execute the SQL. When the results are returned type M-w to put it in the kill ring. Use C-x C-f to open up a new file… and C-y to paste in your stored procedure code. If you’re going to be editing a lot of stored procedures then you’ll probably want a macro to clean up all of the erroneous white space you’re gonna be pulling in with this process. Type C-x (to start recording a macro. Type C-M-% to do a regex replace. Replace [ ~]+~[ ~]+ with ~, where the ~ character is a carriage returned entered by typing C-q C-j. Type M-< to go to the top of the file. (Each square bracket should contain both a space and a carriage return.)  Type ! to apply the replace to the whole file from the cursor point down. Then type C-x ) to finish recording the macro. To reuse the macro on other files, just type C-x e.

This is overall a pretty mediocre experience, but it demonstrates that it is possible to work on SQL Server from Emacs. With the right .emacs file, though, much of this can be streamlined and made easier to deal with….  Of course, you may not always be working on a machine that has Enterprise Manager installed, in which case going this route allows you to avoid some of the irritating free-ish versions of Query Analyzer that I’ve seen floating around….

Update 7/12/2007:

Fix the Word-Wrap nightmare while in the sql-mode with M-x toggle-truncate-lines.


3 Responses to “Replacing MS SQL “Enterprise Manager” with Emacs”

  1. Dark secrets of Emacs Word Wrapping « Learning Lisp Says:

    […] as good as notepad with “Word Wrap” turned off. Note that this command makes using the SQL mode much nicer… though sql-ms/isql still screws up your column headings with the timer text: […]

  2. Chris Says:

    I know this is rather old, but I was wondering how I would use sql-ms if I use my Windows authentication to log in to the server. It seems that I can’t just use my Windows username/password.

    Thanks for your help.

  3. Ryan Says:

    Two things:

    First, Chris, I’ve just managed to connect to a server using windows authentication by not entering anything for username and password. That seems to work. Pretty cool.

    How can I programmatically run queries against a database? I guess we’ll be getting back text from the mssql, but if I’m doing simple queries, I should be able to parse the response into a list or something. Any pointers or short tutorials on this sort of thing would really, really be appreciated.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: