How do I connect to SQL Server using Emacs?

Question

What steps do I take? Any gotchas to be aware of or tips to enhance the IDE experience that are specific to SQL Server when using Emacs?

Scrophulariaceous

Answer 1

Connecting

To connect to a SQL Server database instance from Emacs:

M-x sql-ms RET
M-x sql-mode     

You will be prompted for standard connection information specifically the following:

• User
• Password
• Server
• Database

For SQL Server Authentication, type in the necessary user and password info. However, if connecting via Windows Authentication, then press RETURN for both user and password leaving them blank.

Viewing Output Results

Note that to see the text of any output results in the *SQL* buffer, the 'go' statement should be called at some point. A couple of ways of doing this.

For example, this sql statement will execute but it will not show any result text in the *SQL* buffer in its current format:

select 'foo' as bar

However, if a 'go' is appended to the end:

select 'foo' as bar
go

the following will be displayed in the *SQL* buffer:

 bar   
 ----- 
 foo

(1 row affected)

Alternatively, if you do not want to have 'go' statements littering the text of your SQL script then call 'go' on the fly to see all output results since the last time that the previous 'go' statement was sent to the sql process:

C-c C-s go RET

This is helpful if you need to view any error messages that might not initially show in the *SQL* buffer.

Scrophulariaceous

@Mark sql-ms (Microsoft SQL Server) – Scrophulariaceous

@RayVega: connecting with Windows Authentication, leaving user/password blank, but getting this Error: No user selected. Try with -U or -E switches – Berkey

@Berkey Are you perhaps using SQL Server CE (Compact Edition)? I believe Emacs uses osql to connect to sql server which is not supported by that version: https://mcmap.net/q/582931/-can-i-use-osql-with-sql-server-compact-edition – Scrophulariaceous

Answer 2

sometimes the display of emacs's sql-ms sucks because that some columns displayed are too wide for reading.

here are some skills for bad output experience.

1.

M-x toggle-truncate-lines

toggle truncate-lines can improve some readable.

2.

select left(columnName, 25) from table

this truncates the column width to 25 characters. This works perfect for me.

source is here: http://bloggingmath.wordpress.com/2011/02/03/using-emacs-as-your-sql-interface/

Peonir

Answer 3

Also, see here:

https://github.com/kiwanami/emacs-edbi

This way i just use odbc to connect to everything through DBD::ODBC.

Dezhnev

Answer 4

The author mentions "gotchas" when trying to connect to an SQL server.

When trying to connect to a mysql server, which as the top-voted answer suggests is done via M-x sql-mysql, I found that I continually got

Unable to locate SQL program 'mysql'

Following the suggestion at this SO question concerning emacs, sql and Macs, I set the following value:

(setq sql-mysql-program "/usr/local/mysql/bin/mysql")

which was the location/path to my local mysql binary.

Don't know why this variable is not mentioned in any other tutorials I've seen, so I post this here, as an aforementioned "gotcha" when connecting to a mysql server.

Pirri