Dos Batch File – Special Characters

Background

If you end up having special characters as in the case of nice passwords and also needing to use delayed expansions in your batch file, you might have to escape those passwords.

Batch File

Deploy


@echo off

if not exist log mkdir log

SETLOCAL DISABLEDELAYEDEXPANSION

set "_errorlevelSaved="

REM Batch files - number of command line arguments ( nimrodm )
rem https://stackoverflow.com/questions/1291941/batch-files-number-of-command-line-arguments

set argC=0

for %%x in (%*) do Set /A argC+=1

echo Argument Count is %argC%

set "_app=sqlcmd.exe"

set _dbHost=%1

IF NOT "%2"=="" set "_dbUsername=%2"

IF NOT "%3"=="" set "_dbPassword=%3"

set "_appOption=-S %_dbHost% -b "

IF NOT "%_dbUsername%"=="" set "_appOption=%_appOption% -U%_dbUsername% "

IF NOT "%_dbPassword%"=="" set _appOption=%_appOption% -P"%_dbPassword%"

set "_filename=modules.txt"

echo appOption is %_appOption%

rem goto skipped

SETLOCAL ENABLEDELAYEDEXPANSION

set _fileCount=0

for /f "tokens=1,2 delims=," %%a in ('type %_filename%') do (

	set /A _fileCount+=1

	set _folder=%%a

	set _file=%%b

	set "_fileFull=!_folder!\!_file!"

	echo Processsing file !_fileCount!  - !_fileFull!

	%_app% %_appOption% -i !_fileFull!	

	set "_errorlevelSaved=%errorlevel%"

	rem echo _errorlevelSaved is %_errorlevelSaved%

	IF NOT _errorlevelSaved NEQ 0 GOTO exception

)

:skipped

goto completed

:exception

echo "Error Level %_errorlevelSaved%"

:completed

SETLOCAL DISABLEDELAYEDEXPANSION

endlocal

Invoke

Outline

  1. Server Name :- 118.90.10.1
  2. User :- dadeniji
  3. Password :- paul.winters

Actual Invocation


deploy 118.90.10.1 john paul.winters

Output

Output – Textual

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'dadeniji'..

Output – Image

sqlcmd.specialCharacters.inbatchfile.(dadeniji)20181209.0700AM

 

Workaround

In our case we have special characters in our password and we are getting failed logins, and so we have to escape each special character.

Outline

In this sample we escaped the period in our password.

  1. The actual password is paul.winters.
  2. The only character that needs to be escaped is the period.
  3. And, so we ended up with paul^.winters.

 

Actual Invocation


deploy 118.90.10.1 john paul^.winters

Output

sqlcmd.specialCharacters.inbatchfile.(john.smith)20181209.0707AM

Dedicated

Dedicating to Rob van der Woude’s.

Rob van der Woude’s Scripting Pages
Escape Characters
Link

SQL Server – DEDICATED ADMINISTRATOR CONNECTION ( DAC )

Background

Trying to review Extended Stored Procedures, but ran into a stumbling issue.

Recreate

Query

Issued a query against master.sys.all_extended_procedures to get a list of extended Stored Procedures.

SQL


select * from sys.all_extended_procedures

Output

Output – Image

sys.all_extended_procedure.20181205.0857AM

Output – Text

Msg 208, Level 16, State 1, Line 17
Invalid object name 'sys.all_extended_procedures'.

Remediation

SQLCMD

SQLCMD – Connect

Syntax


sqlcmd -A -S [computer]

Sample


sqlcmd -A -S localhost

SQLCMD – Query

SQLCMD – Query – master.sys.all_extended_procedures

Syntax


sqlcmd -A -S localhost -Q"select left(name, 30) as [name], dll_name from master.sys.all_extended_procedures"

Sample

sqlcmd -A -S localhost -Q"select left(name, 30) as [name], left(dll_name,30) as [dll_name] from master.sys.all_extended_procedures where name like 'xp%' order by 1"

 

Output

sys.all_extended_procedure.xp_.20181205.0911AM

Explanation

  1. xp_delete_file
    • dll_name :- xpstar.dll

 

SSMS

SSMS – Query – master.sys.all_extended_procedures

Syntax


EXEC sp_helpextendedproc;  

Sample

EXEC sp_helpextendedproc xp_delete_file;  

 

Output

sp_helpextendedproc.20181205.0915AM

Explanation

  1. xp_delete_file
    • dll_name :- xpstar.dll

 

Summary

To access certain SQL Server functionalities, please connect using Dedicated administrator connection (DAC).

 

References

  1. Microsoft
    • Docs \ SQL \ Relational databases \ System stored procedures
      • sp_helpextendedproc (Transact-SQL)

SQL Server On Linux – Installing Command Line Tools

Background

It is time to start playing around with command line client tools for SQL Server On Linux.

os

Each os has its own installation platform.

Our os is CentOS and so that is the os we singularly target.

Components

Here are the actual components that are part of SQL Server Client Tools :-

  1. sqlcmd
    • SQL Query Tools
  2. bcp
    • Transfer data in and out from SQL Server to text-file

 

Repositories

Review Registered Repositories

Syntax


yum repolist

Output

yum.repolist.20181130.0947PM

Explanation

  1. packages-microsoft-com-prod
    • That is the repo we are are looking for

Register Repository

If the Repository is not registered, please register it!

Syntax

sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

Output

yum.register.20181130.1000PM

Explanation

  1. Added Repositories
    • Added msprod.repo

 

Install Application

Is Application installed

Syntax


sudo yum list installed | grep -i mssql

Output

yum.list.installed.20181130.0908PM

Explanation

  1. Review installed mssql applications
    • mssql-server.x86_64
      • Server
    • client???

Review Applications

List all available applications.

Our options are :-

  1. yum list
  2. yum search

yum list

Syntax

yum list | grep -i mssql

Output

yum.list.20181130.0903PM

yum search

Syntax


yum search mssql

Output

yum.search.20181201.0757AM

Explanation

  1. The applications we need are :-
    • mssql-tools.x86_64

 

Application Info

List all available applications.

Syntax

yum info mssql-tools.x86_64

Output

yum.info.mssql-tools.x86_64.20181201.0736AM

Install Application

Install application.

Syntax

sudo yum install mssql-tools unixODBC-devel

Output

yum.install.mssql-tools.20181130.1045PM.PNG

 

Application Installed Location

whereis

Syntax


whereis [app]

Sample


whereis sqlcmd


Sample


whereis bcp

Output

whereIs.20181201.0742AM

Explanation

  1. Our applications, sqlcmd and bcp, are installed in the /opt/mssql-tools/bin folder

 

Invoke Application

sqlcmd

Syntax


/opt/mssql-tools/bin/sqlcmd -S localhost -U dadeniji -Q "select @@servername as servername"

Output

query.servername.20181130.0930PM

Explanation

  1. We are prompted for the password to our username
  2. We entered the username
  3. And, the query returns the username

 

References

  1. Microsoft
    • Docs / SQL / SQL Server on Linux
      • Install sqlcmd and bcp the SQL Server command-line tools on Linux
        Link

Running Big SQL Files against SQL Server – Day 2

Background

Quick follow-up to our last post.  In the post we discussed that we were not able to use sqlcmd.exe to post a relatively big file to SQL Server.

File Stats

Here is the file over EmEditor:

EmEditor-FileProperties

The File’s Size is 175 MB, and it has 160, 000 lines of SQL Statements.

 

Tools

SQLCMD.EXE

StackOverflow.net

Read more online and found this hint from “How do you import a large MS SQL .sql file?“.  Here is the Link.

Takuro suggested adding the -a option.  And, passing in the max value, which is 32767.

Code


@echo on
setlocal DisableDelayedExpansion
 
set "_APP_NAME=sqlcmd.exe"
 
if exist "D:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn" (
    set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
)else if exist "C:\Program Files\Microsoft SQL Server\90\Tools\binn" (
    set "_APP_FOLDER=C:\Program Files\Microsoft SQL Server\90\Tools\binn"	
)else if exist "D:\Program Files\Microsoft SQL Server\90\Tools\binn" (
    set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\90\Tools\binn"
)else (
    goto BINARY_NOT_FOUND
)
 
set "_APP_FULLPATH="%_APP_FOLDER%\%_APP_NAME%""
set "_user=dbuser"
set "_password=s#mplep1"
 

set "_folderOutput=\\APPSERVER\d$\DBScripts\ProductionToDR\sqlfiles"
set "_filename=dbo.PaymentCharges-full-v3.sql"
set "_filenameFull=%_folderOutput%\%_filename%"

echo _filenameFull %_filenameFull%
 
set "_destinationServer=DBSERVERDR"
set "_destinationDatabase=HRDB"
 
set "_destinationuser=%_user%"
set "_destinationpassword=%_password%"

@REM This option corresponds to an environment variable SQLCMDPACKETSIZE.
set "SQLCMDPACKETSIZE=32767"

@REM -b
@REM Exit on error

time /T

%_APP_FULLPATH%^
    -S %_destinationServer% -d%_destinationDatabase%  ^
    -U %_destinationuser% -P%_destinationpassword%  ^
    -i %_filenameFull% ^
	-a %SQLCMDPACKETSIZE% ^
	-b 

time /T
goto completed
     
:BINARY_NOT_FOUND
echo %_APP_NAME% not found!
  
:completed
setlocal EnableDelayedExpansion


Explanation

  1. Environment Variables
    • SQLCMDPACKETSIZE
      • set SQLCMDPACKETSIZE=32767
  2.  Argument
    • -a option
      • In our case we are passing in the %SQLCMDPACKETSIZE% argument

Options

 

Argument Explanation
-a Option a is used to specify a packet size in the range of 512 through 32767. It has no visible effect on the output.
Increased packet size can enhance performance of a large script execution by packing more data in the envelope.
However, the provider can choose not to grant this request, in which case SQLCMD defaults to the server default packet size.
This option corresponds to an environment variable SQLCMDPACKETSIZE.
-b Terminate batch job if there is an error

 

Listening

Listening to Mali Music – Beautiful @MaliMusic

Microsoft – SQLServer – Extracting Data (using sqlcmd\isql) – Produces padded columns

Earlier today found myself struggling with extracting data and the extracted columns and file being a big long.

Googled and Search High & low, and as always after a while found my fix.

I needed to add a “-W” to the sqlcmd line.

-- get demo data
   sqlcmd -s ";"  -i getData.sql -E -S DEMO  -o DEMO.txt

   -- get demo data (trimmed)
   sqlcmd -s ";" -i getData.sql -E -S DEMO -o "demo_pruned.txt" -W

BTW, it does not appear that isql nor osql supports “-W” the trimmed option; and so one more reason to go with the newer sqlcmd utility.
References:

  1. SQLCMD – References
    http://msdn.microsoft.com/en-us/library/ms162773.aspx