Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: SQLEXPRESS from linux

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Sql Express to go...

    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
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2009
    Posts
    2

    Smile

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •