Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113

    Unanswered: Newbie OSQL question.

    I've never used osql before but our setup is getting to the point where we have so many databases that it's becoming a pain in the rear end to keep them up to date when we release new software versions.

    My solution to this was to put the database update file (containing all new DDL, DML, stored procs etc) that we normally run manually against each database onto the production server and use OSQL to execute it against each relevant database through a stored proc that can loop through the databases.

    Having never used OSQL before I'm just trying to test it out on our development server... but I can't for the life of me figure it out.

    I've created a file thus:

    BEGIN TRANSACTION
    UPDATE ENTERPRISE
    SET CLIENT_VERSION_ID = '894'
    COMMIT TRANSACTION


    which I've saved to Temp and I'm trying to execute it via osql with the following command:

    osql -E -d dbname -i D:\Temp\Release.sql -n -o D:\Temp\Output\dbname.out

    What I'm getting in the output file is

    Msg 102, Level 15, State 1, Server DB01, Line 1
    Incorrect syntax near ''.

    I can see nothing wrong with the file - certainly if I copy and paste it into ssms it runs fine. And the update to the enterprise table does in fact take place and get committed to the database.

    So firsly where is the error coming from?
    Secondly, is this the best way to apply database updates to hundreds of databases or is there easier ways of doing it?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can tell you that everything you posted looks correct and works for me (the output file just contains a record of number of rows affected).

    The only thing that strikes me is that you do not specify an instance. As such, this will be executed on the instance local to where you run the command. Is this as it should be?

    An alternative method to executing queries against multiple servers is using SSMS 2008, but I assume this is an automated admin thing right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yes, and similarly the sql file must be on the D drive of the server executing oSQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aha! Just spotted something:
    "Incorrect syntax near ''."
    I didn't notice that initially - I thought it was a bog standard 'i'. It now appears to be a a coding issue.
    osql.exe and unicode files ? how to save your sql scripts with encoding - Jeff Widmer's Blog
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This will be my final post for this morning!
    sqlcmd is more feature rich than oSQL. I'm not even certain you can pass parameters to oSQL. As such, I would make that switch now before you invest any more time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If you have the list of database names in a file (one name per line) use the for command
    Code:
    for /F %n in ('type dbnames.txt') do osql -E -d %n -i D:\Temp\Release.sql -n -o D:\Temp\Output\%n.out

  7. #7
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Thanks Pootle - I understand the codepage thing.

    We generate the files through visual studio - not sure if we can change the encoding there, but I'll look into it.

    I'll check out sqlcmd and see if that better caters to our needs.

    Thanks again for the pointers.

  8. #8
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    SQLCmd does the job perfectly.

    Thanks Pootle

Posting Permissions

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