Changing from osql to sqlcmd

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Changing from osql to sqlcmd

Changing from osql to sqlcmd

Rate Topic

Display Mode

Topic Options

Author

Message

marlon.seton

Posted Monday, December 10, 2012 7:23 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:49 AM
Points: 546, Visits: 188

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.

Post #1394610

sgmunson

Posted Monday, December 10, 2012 7:27 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

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)
Smile

Weight Loss Tips

Post #1394614

marlon.seton

Posted Monday, December 10, 2012 9:36 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:49 AM
Points: 546, Visits: 188

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?

Post #1394678

Lowell

Posted Monday, December 10, 2012 9:59 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 11,648, Visits: 27,758

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1394696

sgmunson

Posted Monday, December 10, 2012 10:05 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

marlon.seton (12/10/2012)

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)
Smile

Weight Loss Tips

Post #1394698

marlon.seton

Posted Tuesday, December 11, 2012 3:22 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:49 AM
Points: 546, Visits: 188

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.

Post #1394994

« Prev Topic | Next Topic »

Permissions