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!
[…] Accessing MS SQL Server from command line in Mac OS X and Linux/Unix […]
Accessing MS SQL Server from command line in Mac OS X and Linux/Unix http://bit.ly/9E4YSn
[…] post is a followup to a recent post I wrote. The issue at hand is that data are stored on a SQL server (Microsoft Server 2005 or […]
Just found your article and tested on my OSX Lion, using brew. Brew was slick, but freetds will not compile. I am also using beta XCODE, so that maybe a contributor. i will try again once XCODE releases have stabilized. Let me know if anyone has it working on LION.
sqlsplus/SQLSPlus – SQLPlus for SQL Server
There is a free command line tool “SQLSPlus” (on http://www.memfix.com ) which is like Oracle SQLPlus but for Microsoft SQL Server 2008, 2005 and 2000.
SQLS*Plus is very flexible with data formatting (set lines size, pagesize, etc), variables (var, &, &&), spool, HTML output, etc – seems like a very good alternative to standard command line tools, such as sqlcmd, osql or isql
you may know that microsoft had released SQL Server ODBC Driver 1.0 for Linux, which has sqlcmd and bcp command line tools (only work with SQL Server 2008 R2 and later version) just like the two tools under Windows system. But sqlcmd/bcp only support SQL Server 2008 R2 and later versions.
SQL Server ODBC Driver 1.0 for Linux download link: http://www.microsoft.com/download/en/details.aspx?id=28160
damn f**k captcha
Very good blog! Do you have any helpful hints for aspiring writers? I’m planning to start my own blog soon but I’m a little lost on everything. Would you propose starting with a free platform like WordPress or go for a paid option? There are so many choices out there that I’m completely overwhelmed .. Any suggestions? Thanks!
Scientists suppose that as a minimum 10 percent of the universal population inherits one or many of the genes that create a predisposition to psoriasis. This takes good care of skin and solves the problem. On the opposite extreme, severe psoriasis also can cover huge parts of the body.