Solved

SQL server 2005 command line client

Posted on 2011-03-12
11
588 Views
Last Modified: 2012-05-11
Is there any client program available to connect to SQL server 2005 or SQL server 2008 via the server name and login credentials and then run sql query to dump the output to a .csv file?

I do the same way with mysql server. I have mysql client and I connect to the database and query via an automated script. Can I do the same for SQL server 2005 database?
0
Comment
Question by:toooki
  • 6
  • 3
  • 2
11 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 475 total points
ID: 35119860
on windows,  sqlcmd can do the job

http://www.sqlservercurry.com/2010/01/using-sqlcmd-to-export-sql-server-data.html

but as your question put under Zone Unix as well, then maybe you need to try this link, too

http://linux.softpedia.com/get/Database/Database-APIs/sqlcmd-42911.shtml
0
 

Author Comment

by:toooki
ID: 35119958
Thank you.
It seems I need Python installed in order to use sqlcmd .
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35125289
You don't need Python to use SQLCMD - it's installed as part of the SQL Server Client Connectivity option, and you can use it without any additional installs. The first link JoeNuvo provides is exactly what you're looking for, if I understand your question correctly.
0
 

Author Comment

by:toooki
ID: 35135512
Yes that is right. Thank you.
I just worked on my laptop that has SQL Server 2005 express installed.
Is there any way to make it work on a Linux server that does not have SQL Server 2005 installed?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35138958
Are you talking about a Linux version of SQLCMD that connects to SQL Server databases, or one that connects to MySQL databases? I don't believe a SQL Server version of this tool exists for Linux, but I'd be surprised if a version for MySQL wasn't installed as part of the client tools.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:toooki
ID: 35144437
Thanks.Yes you are right..
I was asking about a Linux version of SQLCMD that connects to Microsoft SQL Server database. I understand that may not exist. So I cannot run SQLCMD on a Linux server.

Yes I used MySQL on Linux server and that works..

But was wondering if I could connect to another Microsoft SQL Server from this Linux server...looks like I cannot.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35144500
since I don't have linux myself, I can't try the sqlcmd from URL I given to you.
if you think it worth your time, you should give it a try.
0
 

Author Comment

by:toooki
ID: 35144821
Yes, I looked at the documentation for the Linux sqlcmd tool. But the README file says:
sqlcmd requires the following:

Python 2.5 or better
The Grizzled API (automatically installed if you use easy_install to install sqlcmd)
The enum package (automatically installed if you use easy_install)
Appropriate Python DB API drivers for the database(s) you want to use. (See Database Types, below.)
Windows only: You'll also want the ipython pyreadline package, available via easy_install or from http://ipython.scipy.org/dist


I do not have permission to install Python or so on the Linux server.

0
 

Author Comment

by:toooki
ID: 35144838
I am trying to run the sqlcmd on a Windows server. And the sqlcmd is downloading the dump file. The problem is that the file gets padded by huge amount of blank spaces.

My sqlcmd is:
sqlcmd -U myUser -P myPwd -S myServer.myco.com -d myDB -Q "select LTRIM(RTRIM(F1)), LTRIM(RTRIM(F2)) from dbo.myTab" -o "D:\myFile.txt" -s","

Problem is not at the source table. I do not know how to get rid of these blank spaces. The sqlldr command cannot detect the file content.

 dump.txt
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35145093
to run on Windows with sql installed, maybe using BCP is better way.

try following command

BCP "select LTRIM(RTRIM(F1)), LTRIM(RTRIM(F2)) from myDB.dbo.myTab" queryout D:\myFile.txt -c -t\t -r\n -U username -P password -S myServer.myco.com

note
1) output will have tab as delimit
2) I don't sure about BCP tool for linux, but from what I looking for, it also required Python which is not able to do in your situation.
0
 

Author Comment

by:toooki
ID: 35162326
JoeNuvo, thank you for the help but the BCP command did not work. Got syntax error/etc. So I am ok with the sqlcmd and I will trim the values before loading to sqlldr. Seems it is working.
Thanks everyone for help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now