Batch DOS-special characters and SQL 2005

Question

Batch DOS-special characters and SQL 2005

freewarefw

Right there with Babe

Points: 715
More actions
May 9, 2011 at 7:39 am

#389478

Hi,
questions regarding password SQL2005 with special characters.
we work with batch MS-DOS for automatic migration and test on SQL2005.
ex : modif.cmd
when we execute CheckSQLVersion.cmd the parameters are :
CheckSQLVersion.cmd SERVER_NAME DB_NAME, USER_NAME, MOT_DE_PASSE
into the password, in sql2005 we could get special characters.
ex : p%1&2
into the batch, I execute the command :
call ISQLB %1 %2 %3 SQLSERVER_VERSION_RETRIEVE.SQL %4
@if %errorlevel% == 1 goto EXIT
when I run the batch, I got an SQL error.
- login failed.
how can I pass password with special characters ?
ISQLB contains :
sqlcmd -b -m-1 -r 0 -S %1 -d %2 -U %3 -P %7 -i %4 -o %TEMP%\ISQLB.TMP -w 360 >> %5
%1 = Server name
%2 = DATABASE name
%3 = User ID
%4 = File TO execute
%5 = Log file name
%6 = Output file name
%7 = PASSWORD
thank you in advance for your help

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

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

Brandie Tarvin SSC Guru Points: 173053 More actions · Answer 1

I'm a little confused. How are you passing the command? Are you executing a .bat file? Are you using xp_cmdshell? Is this through SSIS or SSMS or a command prompt?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Have you tried surrounding the parameter values with double-quotes?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

freewarefw Right there with Babe Points: 715 More actions · Answer 3

Hi,

here the order of execution :

1)-run file migrate_all.cmd that execute the command :

call CheckSQLVersion %1 %2 %3 %4 %5

@if %errorlevel% == 1 goto EXIT

2)-execute CheckSQLVersion.cmd that execute the command :

CALL ISQLB %1 %2 %3 SQLServer_version.sql %LOGFILE% %SQLFullVersionFile% %5

3)-execute ISQLB.BAT that execute the command :

- sqlcmd -b -m-1 -r 0 -S %1 -d %2 -U %3 -P %7 -i %4 -o %TEMP%\ISQLB.TMP -w 360 >> %5

I hope there is clear enough.

thanks in advance.

Brandie Tarvin SSC Guru Points: 173053 More actions · Answer 4

Not quite clear enough, unfortunately. How do you call the .cmd files? What program is causing them to run? SSMS query window? An SSIS package (run from BIDS or from the SSMS GUI "Run Package" interface)? A SQL Agent job?

Knowing this information will help us help you troubleshoot the error.

"login failed" sounds like a security issue, but we need to know what kind of security issue (i.e., what account is being affected).

Also, remote into your Dev box, open a command prompt, then paste in the actual commands both files are running. See if they work as expected or if they error out. Let us know the outcome.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

freewarefw (5/10/2011)
Hi,
here the order of execution :
1)-run file migrate_all.cmd that execute the command :
call CheckSQLVersion %1 %2 %3 %4 %5
@if %errorlevel% == 1 goto EXIT
2)-execute CheckSQLVersion.cmd that execute the command :
CALL ISQLB %1 %2 %3 SQLServer_version.sql %LOGFILE% %SQLFullVersionFile% %5
3)-execute ISQLB.BAT that execute the command :
- sqlcmd -b -m-1 -r 0 -S %1 -d %2 -U %3 -P %7 -i %4 -o %TEMP%\ISQLB.TMP -w 360 >> %5
I hope there is clear enough.
thanks in advance.

Try it this way:

CALL CheckSQLVersion "%1" "%2" "%3" "%4" "%5"

CALL ISQLB "%1" "%2" "%3" SQLServer_version.sql "%LOGFILE%" "%SQLFullVersionFile%" "%5"

sqlcmd -b -m-1 -r 0 -S "%1" -d "%2" -U "%3" -P "%7" -i "%4" -o "%TEMP%\ISQLB.TMP" -w 360 >> "%5"

** Note that all parameters were surrounded with double-quotes. Even when quoted there are still some characters you need to watch out for. Here is a good article on the topic: http://www.robvanderwoude.com/escapechars.php

You may need to double-up on your percent signs as well as escape some special characters using the caret.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Mike01 SSChampion Points: 11395 More actions · Answer 6

In your original post you wrote that you were calling CheckSQLVersion with 4 parameters, then you listed 5 in the last post. Is Parm4 in this file the password or is it Parm5. From the thread it looks like you are expecting it in Parm 5, but it seems it is in Parm4

Original Post

CheckSQLVersion.cmd SERVER_NAME, DB_NAME, USER_NAME, MOT_DE_PASSE