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

Microsoft – SQL Client – Client Tools – Install

Background

Have a web server in our Lab environment that needs client tools access to our SQL Server.

By Client Tools we mean sqlcmd.exe and bcp.exe access.

Using Web Service we connect to a site and download text and xml data and write the downloaded data into files.  And, we now need to use bcp to feed the downloaded data into SQLServer and later using SQLCmd execute some Stored procedures to move data from the staging tables into the destination tables.

 

 

Utilities

Here are the SQL Server utilities we need and the role they play.

sqlcmd allows us to submit sql statements; and bcp allows us to populate and extract data.

Here is Microsoft’s synopsis:

Utility Summary
 sqlcmd The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches.
 bcp The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

 

 

 

ODBC & Native Client

For a while ODBC was Microsoft mainstay for client connectivity, then came OLE-DB, and now we are back to ODBC.

Read more here:

 

Here is where to find the drivers for each SQL Server Version:

Utility Summary Link
SQL Server 2014 Microsoft ODBC Driver 11 for SQL Server http://blogs.msdn.com/b/sqlnativeclient/archive/2013/01/23/introducing-the-new-microsoft-odbc-drivers-for-sql-server.aspx
 SQL Server 2012 Microsoft® SQL Server® 2012 Native Client http://www.microsoft.com/en-us/download/details.aspx?id=29065
SQL Server 2008-R2 Microsoft® SQL Server® 2008-R2 Native Client http://www.microsoft.com/en-us/download/details.aspx?id=16978

 

 

 

 

Command Line Tools

Here are the URLs for the command line tools

Utility Summary Link Prerequisite
SQL Server 2014 Microsoft Command Line Utilities 11 for SQL Server http://www.microsoft.com/en-us/download/details.aspx?id=36433 Microsoft ODBC Driver 11 for SQL Server
 SQL Server 2012 Microsoft® SQL Server® 2012 Command Line Utilities http://www.microsoft.com/en-us/download/details.aspx?id=29065 Microsoft® SQL Server® 2012 Native Client
SQL Server 2008-R2 Microsoft® SQL Server® 2008 R2 Command Line Utilities http://www.microsoft.com/en-us/download/details.aspx?id=16978 Microsoft® SQL Server® 2008 R2 Native Client

 

 

 

Summary

Again, simple stuff, but if you have not done it a while, it can make you head swirl.

SQL Server – SQLCmd – Error – Sqlcmd: Error: Connection failure. SQL Native Client is not installed correctly.

Trying to connect to a MS SQL Server Instance using sqlcmd.exe and got the error message below:

Sqlcmd: Error: Connection failure. SQL Native Client is not installed correctly.

Of course, Googled and discovered that it is probably a Windows Path issue (manifested by the system picking up a wrong \ mismatched dll).

Errors such as this is common in cases where multiple SQL Server Binaries are loaded on the system.

As a work-around:

  1. Review and correct your path
  2. Transverse to the folder that contains the version of the sqlcmd that you want and run sqlcmd from that folder

To fix:

  1. Un-installed previous Microsoft SQL Server Client