SQLEXPRESS from linux

ucfcarver · #1 (**permalink**) 10-06-09, 15:50

I am new to working with servers and need some help on a project that has been given to me.

I have a linux box that collects data in a comma separated file. I need to transfer the data to a remote sqlexpress server.

I have been working on this for a little while and know that the server has ASP and ASP.NET installed, also working is ODBC.

I can connect to the server using tsql command from the freetds package as follows

tsql -H server -p port -U user -P password

I am trying to automate the process so that there does not need to be any interaction selecting the file and transferring the data.

I have been trying to write a script to accomplish this but have had no success. This is what i have.

#!/usr/bin/env bash

Tsql_auth='tsql -H server -p port -U user -P password'
Tsql_insert='INESRT INTO Route_1 VALUES('
Tsql_end=') go'

$Tsql_auth

$Tsql_Insert 1,1,1,1,'Fri Aug 28 14:18:29 2009' $Tsql_end

Here is the way the test file is written

1,1,1,1,'Fri Aug 28 14:18:21 2009'
1,2,3,4,'Fri Aug 28 14:18:23 2009'
1,3,4,8,'Fri Aug 28 14:18:25 2009'
1,4,6,14,'Fri Aug 28 14:18:27 2009'
1,5,7,21,'Fri Aug 28 14:18:29 2009'
1,6,11,32,'Fri Aug 28 14:18:31 2009'

Any help that can be offered would be greatly appreciated.

LKBrwn_DBA · #2 (**permalink**) 10-06-09, 16:22

Try something like this:

Code:

#!/bin/bash
#
# The following is a test file:
cat - <<! >InFile.txt
1,1,1,1,'Fri Aug 28 14:18:21 2009'
1,2,3,4,'Fri Aug 28 14:18:23 2009'
1,3,4,8,'Fri Aug 28 14:18:25 2009'
1,4,6,14,'Fri Aug 28 14:18:27 2009'
1,5,7,21,'Fri Aug 28 14:18:29 2009'
1,6,11,32,'Fri Aug 28 14:18:31 2009'
!
#
# Here is the beginning of the script:
#
Tsql_auth='tsql -H server -p port -U user -P password'
#
# Prepare the SQL statements using the input file:
#
awk '{print "INSERT INTO Route_1 VALUES("$0") go";}' InFile.txt >outFile.sql
echo "COMMIT;" >>outFile.sql
echo "EXIT;" >>outFile.sql
#
# Execute the tsql:
#
$Tsql_auth <outFile.sql
#
# Done.
#
exit 0

ucfcarver · #3 (**permalink**) 10-07-09, 14:13

Thank you for your help.

The code you went did not work as it was but after some digging i was able to modify it to connect and update the database.

I have added the working code at the bottom if you are interested. Thank you again for getting me on the right track.

Code:

#!/bin/bash
#
#
# Here is the beginning of the script:
#
Isql_auth='isql DSN user passwordt'
#
# Prepare the SQL statements using the input file:
#
awk '{print "INSERT INTO Route_1 VALUES("$0")";}' rider1.txt >outFile.sql

#
# Execute the Isql:
#
$Isql_auth < outFile.sql

#
# Done.
#
exit 0

Internet Services

Web Development

Digital Marketing

Consumer Tech