Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Script to run .sql script from Linux to MS SQL server

Posted on 2011-10-25
31
Medium Priority
?
917 Views
Last Modified: 2012-05-12
HI

I want to develop a script which could run .sql script from Linux machine to MS SQL, that .sql script may be fore fetching select query result or to update records in a table or to alter a table.

Please suggest if its possible to run .sql script from Linux machine to run .sql script on MS SQL server.
0
Comment
Question by:ashuji
  • 20
  • 10
31 Comments
 
LVL 7

Expert Comment

by:Alpha Au
ID: 37023525
you may have a look on this.

SQL Server ODBC Driver for Linux/Unix
http://www.easysoft.com/products/data_access/odbc-sql-server-driver/getting-started.html
0
 

Author Comment

by:ashuji
ID: 37024051
Hi

How can I use the DataSource created by EasySoft into my script.  What I want to do is to run .sql script on MS SQL server.  Nothing mentioned about it in that article.

Please suggest if thats possible.
0
 

Author Comment

by:ashuji
ID: 37024178
Also, this EasySoft is paid solultion, I am looking for free solution.  I belive we can use JDBC and its API to run .sql scripts, but don't know how to, if will be helpful if you could guide ?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 19

Accepted Solution

by:
xterm earned 2000 total points
ID: 37024484
The best way to do this would be to install FreeTDS (http://www.freetds.org/)
Then you can compile SQSH (SQL Shell, from http://www.sqsh.org/) which is a client that will connect to your MS SQL server, which can be scripted to run your queries.

If your Linux is rpm-based, you might be able to get lucky and just get packages of both and then you don't have to compile anything:
As root on your linux box run "yum install freetds freetds-devel", or you can get the packages from http://rpmfind.net/linux/rpm2html/search.php?query=freetds&submit=Search+...
sqsh has some binaries here:  http://www.peppler.org/downloads/binaries/

I think that will be your best option, and it is totally FREE :)  Good luck!



0
 

Author Comment

by:ashuji
ID: 37024586
Thanks, I am using RHEL 5, I'll try this and update status here.

In the mean time can you tell me if using isql in shell scriping is more simpler ?  Please refer to the link below:

http://www.unix.com/shell-programming-scripting/45212-isql-query-unix-shell-script.html 

0
 

Author Comment

by:ashuji
ID: 37024620
One more question about FreeTDS, can it be used to run .sql script which has select/update/insert statements ?
0
 
LVL 19

Expert Comment

by:xterm
ID: 37024675
I don't know much about isql - I believe you'd have to have the actual Linux Sybase distribution installed in order to get the "real" isql.  There may be some java-based clones out there that you can use however.   With RHEL5 it'll at least be a no-brainer to install the freetds libs, and that opens up a multitude of tools that you can use to connect to MSSQL databases (including php, which is probably already installed on your system)

FreeTDS itself doesn't do any connecting - it's just the library that has the functions to connect to MSSQL built into it.  Client software such as sqsh uses those libraries in order to connect.  I believe sqsh can take a file full of select/update/insert queries as input, yes.
0
 

Author Closing Comment

by:ashuji
ID: 37044845
Exact solution.
0
 

Author Comment

by:ashuji
ID: 37055227
HI

I am using RHEL 5 with following kernel:

[root@amerusahdctst01 FreeTDS]# uname -a
Linux amerusahdctst01 2.6.18-274.3.1.el5 #1 SMP Fri Aug 26 18:49:02 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux


I am experiencing problem while trying to install sqsh client, I downloaded its i386 arch RPM build form the link you mentioned above. if I try to install it using 'yum localinstall' it shows unresolvable dependency problem as below, I don't find resouce to install these missing libraries, any help here :

[root@amerusahdctst01 FreeTDS]# yum localinstall sqsh-12.5-2.1-1.i386.rpm
Loaded plugins: rhnplugin, security
Setting up Local Package Process
Examining sqsh-12.5-2.1-1.i386.rpm: sqsh-12.5-2.1-1.i386
Marking sqsh-12.5-2.1-1.i386.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package sqsh-12.5.i386 0:2.1-1 set to be updated
--> Processing Dependency: libcomn.so for package: sqsh-12.5
--> Processing Dependency: libcs.so for package: sqsh-12.5
--> Processing Dependency: libct.so for package: sqsh-12.5
--> Processing Dependency: libintl.so for package: sqsh-12.5
--> Processing Dependency: libreadline.so.4.1 for package: sqsh-12.5
--> Processing Dependency: libsybtcl.so for package: sqsh-12.5
--> Finished Dependency Resolution
sqsh-12.5-2.1-1.i386 from /sqsh-12.5-2.1-1.i386 has depsolving problems
  --> Missing Dependency: libintl.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
sqsh-12.5-2.1-1.i386 from /sqsh-12.5-2.1-1.i386 has depsolving problems
  --> Missing Dependency: libsybtcl.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
sqsh-12.5-2.1-1.i386 from /sqsh-12.5-2.1-1.i386 has depsolving problems
  --> Missing Dependency: libreadline.so.4.1 is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
sqsh-12.5-2.1-1.i386 from /sqsh-12.5-2.1-1.i386 has depsolving problems
  --> Missing Dependency: libcs.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
sqsh-12.5-2.1-1.i386 from /sqsh-12.5-2.1-1.i386 has depsolving problems
  --> Missing Dependency: libct.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
sqsh-12.5-2.1-1.i386 from /sqsh-12.5-2.1-1.i386 has depsolving problems
  --> Missing Dependency: libcomn.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
Error: Missing Dependency: libintl.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
Error: Missing Dependency: libreadline.so.4.1 is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
Error: Missing Dependency: libcs.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
Error: Missing Dependency: libcomn.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
Error: Missing Dependency: libsybtcl.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
Error: Missing Dependency: libct.so is needed by package sqsh-12.5-2.1-1.i386 (/sqsh-12.5-2.1-1.i386)
 You could try using --skip-broken to work around the problem
 You could try running: package-cleanup --problems
                        package-cleanup --dupes
                        rpm -Va --nofiles --nodigest
The program package-cleanup is found in the yum-utils package.

Please help here
0
 

Author Comment

by:ashuji
ID: 37055244
I tried to install these dependency but unable to find suitable package/rpms to install these dependency.
0
 
LVL 19

Expert Comment

by:xterm
ID: 37056793
Sounds like you haven't installed freetds-devel packages yet - that sqsh package is looking for the sybase libs that are provided by them.
0
 

Author Comment

by:ashuji
ID: 37057563
I have already installed freetds devel
0
 
LVL 19

Expert Comment

by:xterm
ID: 37057648
Okay, please do:

rpm -qa freetds-devel | grep libintl

Let me know what it says.
0
 

Author Comment

by:ashuji
ID: 37058420
I will chk this tomorrow and let you know, thanks for your quick help
0
 

Author Comment

by:ashuji
ID: 37061071
Hi

Please find below the output:

[root@amerusahdctst01 FreeTDS]# rpm -qa freetds-devel | grep libintl
[root@amerusahdctst01 FreeTDS]# rpm -qa freetds-devel
freetds-devel-0.91-1.el5

Note:
I could not install FREETDS from YUM as YUM could not find it, so I downloaded it from some website and installed it.
0
 
LVL 19

Expert Comment

by:xterm
ID: 37061081
Well, that changes everything - that's why the sqsh RPM can't find the dependencies, because they are not in the RPM database.

You can either compile SQSH from source against the libraries you installed manually, or you can do this:

rpm -Uvh --nodeps sqsh-12.5-2.1-1.i386.rpm

But you may get library errors when you run sqsh, because it will be looking for freetds libs in standard locations (like /usr/lib) and if you installed them in /usr/local/freetds or something, it won't find them.

If you installed them somewhere else, then before you run sqsh you will need to do:

export LD_LIBRARY_PATH=/path/to/directory/containing/your/libintl.so

Then sqsh will work after that.
0
 

Author Comment

by:ashuji
ID: 37062069
ONe question, which YUM repository may I use if I want to installl FeeTDS, FreeTDS Devel and SQSH using YUM.
0
 

Author Comment

by:ashuji
ID: 37062333
I wanted to ask which YUM repository may I use if I want to installl FeeTDS, FreeTDS Devel and SQSH using YUM.  

also please let me know if I install SQSH without dependencies, will it still work normally for my script which will make connection with MS SQL 2008 server and run .sql file ?
0
 

Author Comment

by:ashuji
ID: 37062409
HI

I installed SQSH without dependencies using the RPM command you specified in your last reply, it does not work, please refer to the logs below:


[root@tst01]# sqsh -S10.192.1.50 -Utestdbuser -Ptestdbuserpass
sqsh: error while loading shared libraries: libcs.so: cannot open shared object file: No such file or directory[root@tst01]# ping 10.192.1.50
PING 10.192.1.50 (10.192.1.50) 56(84) bytes of data.
64 bytes from 10.192.1.50: icmp_seq=1 ttl=127 time=2.75 ms
64 bytes from 10.192.1.50: icmp_seq=2 ttl=127 time=0.294 ms
0
 

Author Comment

by:ashuji
ID: 37062413
10.192.1.50 is IP of MS SQL server.
0
 
LVL 19

Expert Comment

by:xterm
ID: 37062621
rpms for freetds are here:
http://rpmfind.net/linux/rpm2html/search.php?query=freetds&submit=Search+...
http://rpmfind.net/linux/rpm2html/search.php?query=freetds-devel&submit=Search+...&system=&arch=

I already told you how to fix that error above, see line containing "export LD_LIBRARY_PATH"
0
 

Author Comment

by:ashuji
ID: 37062866
But these libraries does not exist in my system, please refer to the logs below:

[root@tst01]# locate libintl.so
/usr/lib/preloadable_libintl.so
/usr/lib64/preloadable_libintl.so

[root@tst01]# locate libcs.so
[root@tst01]#

I need to know how could I install these libraries which are dependencies of SQSH or how can I run SQSH without these libraries.

FreeTDS and FreeTDS-devel i have already installed:

[root@tst01]# rpm -q freetds
freetds-0.91-1.el5

[root@tst01]# rpm -q freetds-devel
freetds-devel-0.91-1.el5
0
 
LVL 19

Expert Comment

by:xterm
ID: 37063217
It appears you will need the sybase open client libraries to run sqsh, not sure if this is a recent thing.
0
 

Author Comment

by:ashuji
ID: 37063545
Please suggest if Sybase open client is free to use and also let me know how to install it.  As per my information thats not free but have to buy licenses.
0
 
LVL 19

Expert Comment

by:xterm
ID: 37063648
I'm sorry, but I looked around too, and it doesn't appear that they're freely available anymore.
0
 

Author Comment

by:ashuji
ID: 37063679
SO, that means I can't use SQSH at all for this.  Is there any other way to do this.  May be somehow using JDBC ?
0
 
LVL 19

Expert Comment

by:xterm
ID: 37063796
I think the easiest would be to do it with PHP:

yum install php-mssql php-cli

You may need to take a dll off your MySQL server and put it on your Linux machine according to the Requirements link here:

http://us2.php.net/manual/en/book.mssql.php

Now you can just create local php scripts to connect to your database and run your queries.  For example:



#!/usr/bin/php -q
<?php

// Connect to MSSQL and select the database
mssql_connect('KALLESPC\SQLEXPRESS', 'sa', 'phpfi');
mssql_select_db('php');

// Create a new stored prodecure
$stmt = mssql_init('NewUserRecord');

// Bind the field names
mssql_bind($stmt, '@username',  'Kalle',  SQLVARCHAR,  false,  false,  60);
mssql_bind($stmt, '@name',      'Kalle',  SQLVARCHAR,  false,  false,  60);
mssql_bind($stmt, '@age',       19,       SQLINT1,     false,  false,   3);

// Execute
mssql_execute($stmt);

// Free statement
mssql_free_statement($stmt);

?>

Open in new window

0
 

Author Comment

by:ashuji
ID: 37064098
But i dont know perl/php scripting
0
 
LVL 19

Expert Comment

by:xterm
ID: 37064169
It's very simple to learn, that's my best suggestion to you.  I can help you create the script.
0
 

Author Comment

by:ashuji
ID: 37175597
HI

Can you help me building such script using PERL, and which does not require Sybase libraries ?  Is it possible ?
0
 

Author Comment

by:ashuji
ID: 37283750
HI

Ok, I am ready to learn PHP, please let me know how this script can be built in PHP so that I could run .sql from Linux shell into MS SQL server.

If you like I can open another question for this, please let me know.  Give some guidelines atlest.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Often times it's very very easy to extend a volume on a Linux instance in AWS, but impossible to shrink it. I wanted to contribute to the experts-exchange community a way of providing a procedure that works on an AWS instance. It can also be used on…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

581 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