Escaping special symbols in Sqlcmd

Question

I have a bat file which executes bunch of sql scripts when i install my application. In installation dialogs user sets dbname, login and password which are then used in this bat file. Here it is:

SET _server="(local)"
SET _db=dbname
SET _user=sa
SET _pswr="!#$%^&*()<>"

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regsql.exe -S %_server% -d %_db% -U %_user% -P %_pswr% -A all

"%systemdrive%\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.exe" -S %_server% -d %_db% -U %_user% -P %_pswr% -i .\createDB.sql

pause

Everything works great until password doesn't contain any special symbols. But if it does i get following error "Login failed for user 'sa'.". So it's obvious, that somethins wrong with password. And now the question: How can i escape these special symbols in bat file?

PS: I tried replacing:

< - &lt;
> - &gt;
& - &amp;

But it doesn't help.

Thanks!

Answer 1

I had the same challenge today with using SQL Agent Job Tokens together with sqlcmd for deployment, in particular Invoke-Sqlcmd. This has a conflict as the Tokens are interpreted as sqlcmdvars.

The way in which I got around this was to escape the sqlcmdvar declaration by escaping the dollar symbol as shown in the below sql script. Together with this I declared and set sqlcmdvar variable with the declaration of the Token itself (minus the dollar sign). This allowed me to call the SQL Agent Job script via sqlcmd and still have it preserve the Token declaration within the step declaration.

    declare @Job_name varchar(200)
    ,@Step_name varchar(200)

set @Job_name  = '$(ESCAPE_SQUOTE(JOBNAME))'
set @Step_name = '$(ESCAPE_SQUOTE(STEPNAME))'

print 'The is example print statement!'
print 'The is example '+@Job_name 
print 'The is example '+@Step_name

The sql script contain the sql agent job declaration: "example sql agent job with tokens.sql"

:setvar JOBNAME (ESCAPE_SQUOTE(JOBNAME))
:setvar STEPNAME (ESCAPE_SQUOTE(STEPNAME))

USE [msdb]
GO

/****** Object:  Job [ExampleJob]    Script Date: 23/10/2023 13:27:48 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 23/10/2023 13:27:48 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ExampleJob', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'This is just an example to show how to Tokens alongside SQLCMDVARs.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'*****', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Example Step]    Script Date: 23/10/2023 13:27:49 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Example Step', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'declare @Job_name varchar(200)
    ,@Step_name varchar(200)

set @Job_name  = ''$$(ESCAPE_SQUOTE(JOBNAME))''
set @Step_name = ''$$(ESCAPE_SQUOTE(STEPNAME))''

print ''The is example print statement!''
print ''The is example ''+@Job_name 
print ''The is example ''+@Step_name', 
        @database_name=N'master', 
        @flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Example Schedule', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=2, 
        @freq_subday_interval=10, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20231023, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=N'8400e289-6b41-47fd-8692-ad7e74fd7146'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The powershell script calling the above sql script:

Invoke-Sqlcmd -InputFile "C:\Users\{user}\Downloads\example sql agent job with tokens.sql" -ServerInstance "(local)"

**Please remember to replace the {user} within the path mentioned above with your local filepath. Also remember to replace the owner_login_name value with something valid for your SQL instance.

Answer 2

Tested as well with windows batch file on Windows 2008 Server SP2 with sqlcmd. Only needed to escape the % sign as %%. No need to escape the ^.

Answer 3

You need to escape the percent (%) character and the caret(^) character by doubling them. Here is a list of problematic command line characters

SET _pswr="!#$%%^^&*()<>"