December 10, 2012 at 7:23 am
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.
December 10, 2012 at 7:27 am
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)
December 10, 2012 at 9:36 am
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?
December 10, 2012 at 9:59 am
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
December 10, 2012 at 10:05 am
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)
December 11, 2012 at 3:22 am
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