SQL Server Performance

ISQL/OSQL format question....

Discussion in 'General DBA Questions' started by sql_jr, Oct 11, 2007.

  1. sql_jr New Member

    Here's a silly little question, that I can't seem to find an answer. I simply need to set up a batch script using isqlosql, and want to export the results of a query to a .csv (comma-delimited) format. Isn't this possible? Please include cmd line sample. I can't seem to get it to format correctly. THX! [:$]
  2. satya Moderator

    Books online has such code example:
    You can easily write a .bat file with one osql command (osql is SQL’s DOS command line utility that utilizes ODBC to connect to the database) as such:
    osql –S 2L1MYMSSQL –U sa –P abc123 –r –h-1 –s “,” –d HealthDb –Q “set nocount; select * from membership” –w 700 –o membership.csv
    The command says to invoke osql with –S and the SQL server name (in this case 2L1MYMSSQL). The remainder of the parameters are as follows:
    -U sa -- the user logging in is the system admin (sa)
    -P abc123 -- the password for sa
    -r -- put messages to stderr
    -h-1 -- this is important to turn off column headings (you would not want them
    in an outgoing .csv file
    -s “,” -- defines the separator (in this case it is a “,”) between each field.
    -d HealthDb -- defines the database name
    -Q “set nocount; select * from membership”
    -- These define the query commands to use. The first command is “set
    nocount” which will tell osql to not echo the “n rows affected” message
    to the output file at the end of the query.
    Select * from membership extracts all fields from the membership table
    and they will be separated by a comma.
    -w 700 -- This is the width of one extracted record. If you don’t specify this, long
    lines will wrap around into many lines. 700 is the linesize for this example
    -o membership.csv
    -- Specifies the output .csv file. This is your final file that goes to the
    external entity.
  3. sql_jr New Member

    Thanks! However, although its putting commas in, there are a LOT of extraneous spaces and those little box symbols (i think they represent a tab or a space) Any ideas how to clean this up? BEST!
  4. satya Moderator

    Are you opening this up in Notepad?
    I hope not, you could open up using excel for that output file
  5. sql_jr New Member

    No, this excel....>
  6. satya Moderator

    Post the OSQL code you have used, let me try to see whether I get same or not.
  7. sql_jr New Member

    It's part of a batch job, so let me know if you have any clues..
    for /f "tokens=2,3,4 delims=/ " %%a in ('date /t') do @FOR /F %%i in (servers.txt) do @for /F "tokens=1 delims=." %%z in (scripts.txt) do @isql -S%%i -E -dmaster -r -h-1 -n -s"","" -ic:sqlreportssrvmaintplans%%z.sql -w 700 -o c:sqlreportssrvmaintplans%%i_%%z_%%c%%a%%b.csv
  8. psantosh12 New Member

    Thank you. This post is very useful for me.
    I want to trim all empty spaces from each field how to do this using OSQL statement.
    Thank you,
    Santosh

Share This Page