Changing from osql to sqlcmd

  • Hi All,

    I have one use of osql in my system and I want to change it to sqlcmd. Here's an extract of the code that builds the command that currently includes osql (you don't need to know the language but if you really want to know it's ProIV):

    $$_COMMAND = 'osql'

    + ' -U ' + $_USER_ID

    + ' -P ' + $_PASSWORD

    + ' -D ' + $_DATASOURCE_NAME

    + ' -h-1 -e -n -b -i ' + $$_SQL_SCRIPT

    IF $$_LOG_FILE # '' THEN

    $$_COMMAND = $$_COMMAND + ' -o ' + $$_LOG_FILE

    ENDIF

    Looking at osql and sqlcmd in http://msdn.microsoft.com/en-us/library/... I can see the following parameters will be the same with sqlcmd (or at least it looks to me like they will be same):

    -U, -P, -h-1, -e, -i and -o

    My question is, what are the sqlcmd equivalents of -D and -n? All and any assistance gratefully received.

  • I'd have to go look to see if I could find equivalents, but I suspect you've already searched and come up empty, so I'll ask if you've considered using an SSIS package instead of sqlcmd ?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Hi Steve,

    Thanks for your message.

    Yes, I've tried to search for the sqlcmd equivalent of -D and -n but to no avail.

    I've not considered SSIS because I know nothing about it apart from what the initials stand for. We build what is currently an osql command on the fly and run it via the 'command line execute' command of the programming language we use. Would SSIS be suitable for that?

  • marlon i looked at this page, and osql @ the cmd prompt, and this is what i think:

    i tried to put comments in, i hope the comment start chars are dbl slashes.

    http://msdn.microsoft.com/en-us/library/ms162773.aspx

    $$_COMMAND = 'sqlcmd'

    + ' -U ' + $_USER_ID

    + ' -P ' + $_PASSWORD

    + ' -d ' + $_DATASOURCE_NAME //case change.

    + ' -h-1 ' //hide Headers

    + ' -e ' //echo input

    + ' -n ' //remove numbering No sqlcmd equivalent?

    + ' -b ' //On error batch abort is DOS error the same as osqls?

    + ' -i ' // input file

    + $$_SQL_SCRIPT

    IF $$_LOG_FILE # '' THEN

    $$_COMMAND = $$_COMMAND

    + ' -o ' //output file

    + $$_LOG_FILE

    ENDIF

    my first guess at good code?

    $$_COMMAND = 'sqlcmd'

    + ' -U ' + $_USER_ID

    + ' -P ' + $_PASSWORD

    + ' -d ' + $_DATASOURCE_NAME

    + ' -h-1 ' //hide Headers

    + ' -e ' //echo input

    + ' -b ' //On error batch abort

    + ' -i ' // input file

    + $$_SQL_SCRIPT

    IF $$_LOG_FILE # '' THEN

    $$_COMMAND = $$_COMMAND

    + ' -o ' //output file

    + $$_LOG_FILE

    ENDIF

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • marlon.seton (12/10/2012)


    Hi Steve,

    Thanks for your message.

    Yes, I've tried to search for the sqlcmd equivalent of -D and -n but to no avail.

    I've not considered SSIS because I know nothing about it apart from what the initials stand for. We build what is currently an osql command on the fly and run it via the 'command line execute' command of the programming language we use. Would SSIS be suitable for that?

    It looks like Lowell has you covered, but yes, you could, potentially, use the command line invocation to run DTSEXEC.EXE to run the SSIS package, but you'd also need someone to write the package using BIDS, and then would have to worry about execution security context, so I'd only go for that if what Lowell posted doesn't work.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thanks for everyone's assistance.

    I don't think -d in sqlcmd will work for us as this expects the db_name whereas we are providing the data source name to -D in osql; this value is provided by the system's .ini file, which doesn't specify the db name. To keep changes to a minimum, I think we're going to go with the -S option in sqlcmd instead of -D in osql as we have the necessary values (server\instance_name) available within our system's .ini.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply