Accessing MS SQL Server from command line in Mac OS X and Linux/Unix

Unfortunately in my current line of work, I’ve been pulling data from MS SQL Server. My current workflow involves remotely logging into a windows machine, and from there, using the sql server software (MS SQL Server Management Studio) to issue queries and save data. This especially sucked in that I couldn’t write in the query file where I want my output results to go. I had to manually right-click the results, copy, and paste into a txt file.

Recently I started using Oracle’s SQLDeveloper with the 3rd party plugin jTDS to skip the remote login. SQLDeveloper allowed me to log directly into the SQL Server. This did not work well in that copying the results caused issues (memory?). Also, Query Analyzer was only available if the DB was Oracle’s. The added benefit was not great.

I finally found FreeTDS by googling “linux ms sql server command line.” To install it on Mac OS X, I needed to install it via Macports (sudo port install freetds). I had issues with it initially because it could not connect with the SQL Server of interest. The problem was that I needed to specify the correct TDS version; in my case, it was version 4.2 (see this linked from this thread). You can connect using two ways:

 <pre class="src src-sh"><span style="color: #eedd82;">TDSVER</span>=4.7 tsql -H myserverIP -p 1433 -U myusername -P mypassword

or

 <pre class="src src-sh">tsql -S myserverinlist -U myusername -P mypassword

with the following in “~/.freetds.conf”

 <pre class="src src-sh">[adrc]

host = myserverIP port = 1433 tds version = 4.2

See this for some examples. If you have connection issues, make sure you could telnet to the server using the right port first. See this. More information on the freetds.conf file can be found here.

For a query, remember to put go at the end of the query for it to execute (credit).

Be careful when routing input and output (“<” and “>”) as other messages from the tsql command will be present. Will post a solution when I find out.

Update 4/16/2010 – Batch output results from a query

I found out FreeTDS comes with the commands freebcp (bcp emulation/replication) and bsqldb. To get batch results, issue

bsqldb -U username -P password -S server.from.TDS.list -h -t '\t' -i query.sql -o query.out
## in 2nd line, has "-" characters (separate headers and data). to remove them:
bsqldb -U username -P password -S server.from.TDS.list -h -t '\t' -i query.sql | sed -n '2!p' > query.out

## or use freebcp
-- freebcp "$(cat query.sql)" queryout query.out -S server.from.TDS.list -U username -P password -c
## for freebcp, do not use "GO" in query

The above freebcp method does not work for me with the error Cannot bcp with TDSVER < 5.0.

Credit for for bsqldb goes to here. Removing the 2nd line is from here. This showed me how to get the correct freebcp command line syntax.

Next up is getting odbc (unixODBC to be exact) working!

record music from last.fm using shell-fm

as i mentioned before again and again, i’ve been working on to get shell-fm to record music from last.fm. originally, i was trying to get shell-fm pipe to tee to save the songs and play using madplay (or mplayer) based on this method. this worked. i just need to write scripts to post process the songs to get id3 tags, etc. things work, and i have something like this in my shell-fm.rc file:

<pre class="src src-sh">extern = tee <span style="color: #ffa07a;">"/Users/vinh/shell-fm-saves/%a;%t;%l;%d.mpg3"</span> | madplay -Q -

##extern = tee “/Users/vinh/shell-fm-saves/%a;%t;%l;%d.mpg3″ | mplayer -cache 32 -

however, if u look at the end of the same page, it mentions hacking the source code because u can get shell-fm to automatically save free files, and get id3 tags on there. hopped on #shell-fm on the freenode and spoke to the author. (i tried this before but it didn’t work — reason was i didnt do a make clean first…i had residuals from the fail install since its on a mac).

now, to get shell-fm to compile with recording of every songs and get id3 tags working, just install libtag via MacPorts, modify play.c (search for freetrack and comment out that part):

<pre class="src src-C"><span style="color: #00ffff;">if</span>( <span style="color: #ff4500;">/*</span><span style="color: #ff4500;">freetrack &amp;&amp; strlen(freetrack) &gt; 0 &amp;&amp; </span><span style="color: #ff4500;">*/</span> haskey(&amp; rc, <span style="color: #ffa07a;">"download"</span>)) {

to compile, go in the source directory, do a make clean, then do the following on a mac:

<pre class="src src-sh">cc -o shell-fm -D__darwin__ -Os -Wall -W -I./include/ -DLIBAO -I/opt/local/include -I/opt/local/include -L/opt/local/lib -lmad -lm -Wl,-framework -Wl,AudioUnit -L/opt/local/lib -lao -lpthread -DTAGLIB <span style="color: #fa8072;">`pkg-config --libs taglib_c`</span> *.c

the author helped me figure out the DTAGLIB stuff, and how to get this command originally (had to remove the main.c in the middle and change the last .a file to *.c).

did:

<pre class="src src-sh">cp ./shell-fm /usr/bin/

cd ../manual make sudo shell-fm.1 /usr/share/man/man1/

now, go to ~/.shell-fm/shell-fm.rc and put in download = /Users/vinh/shell-fm-saves/%a – %t.mp3 and all completed songs will be saved WITH id3tag

hacking/editing portfile (MacPorts) to compile madplay for use with shell-fm (last.fm)

so recently i compiled shell-fm, a command-line last.fm player. in order to record music with shell-fm, i can output the stream of incoming data into tee, which saves the data to a file and at the same time pipes it out to stdout, and have that piped to madplay, a command-line player.

i had mplayer installed, but in order to get the piping of the streaming data to work, i had to to specify a cache ‘| mplayer -cache 32 -‘ (- means to take from stdin) in order for the music to play at the right rate. otherwise, the mplayer plays at a slower rate. the shell-fm wikidot recommends madplay.

madplay doesn’t compile easily on a mac os x, so i had to resort to MacPorts for the installation. it installs fine, but madplay plays noise. googled it, and stumbled on this post. he figured out it was an endian issue.

i tried applying his patch (‘patch < temp.patch’) to the original source code, however, it didn’t compile. figured out that i need the patch and the flags (CFLAGS, LDFLAGS) from Macports as well. i didn’t know how to apply both patches. hopped on #MacPorts on the freenode, and spoke with toby.

i created the 2nd patch and called it temp.patch in /opt/local/var/macports/sources/rsync.macports.org/release/ports/audio/madplay/files. edited /opt/local/var/macports/sources/rsync.macports.org/release/ports/audio/madplay/Portfile and added temp.patch to the list of patch files. now running ‘sudo port install madplay’ did the trick after uninstalling (sources and portfiles still around). voila, i have a working install on mac os x.

toby committed the new patch to macports, so now things should work. to get the new patches, i had to run ‘sudo port -d sync’ to sync the newest patch files. i uninstalled and reinstalled and the new official patch works.

i learned how macports operated and how to apply patch files in this adventure.