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

Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.

Forward

Discovered that our BETA database which we restore each Saturday morning was unavailable.

Sought to find out why it was stuck in recovery mode

Error Message

Here is the trail end of our log file


80 percent processed.
81 percent processed.
82 percent processed.
83 percent processed.
84 percent processed.
85 percent processed.
86 percent processed.
87 percent processed.
88 percent processed.
89 percent processed.
90 percent processed.
91 percent processed.

Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.

D:\Scripts\RestoreDB>echo ERRORLEVEL is 1 
ERRORLEVEL is 1


 

Diagnostic

Task Scheduler

Scheduled Task

ScheduledJob-Settings

 

Explanation

  1. Settings
    • Set: Stop the task if it runs longer than: 3 days
    • Reality: yes, our restore takes time.  It started on Saturday @ 12:15 AM and aborted at 3:11 AM
      • And, so at about 3 hours
      • We should still have about 2 days and 21 hours of grace

 

SQLCmd

Sqlcmd – Payload

Command


D:\Scripts\RestoreDB>sqlcmd -S (local) -d master -e -E  -i D:\Scripts\RestoreDB\restoreDBCSTrafficSchoolBetaSingleFile.sql 

Explanation

  1. We are not passing in the -t parameter
    • BTW, -t sets the query timeout

 

sqlcmd Utility
Link

querytimeout

 

SQL Server

ErrorLog

Checked the error logs

Nothing in the current log.

Image

ErrorLog-20160730-0311AM

 

Textual



2016-07-30 03:10:01.29 spid62      Setting database option SINGLE_USER to ON for database nettraffic_db.
2016-07-30 03:10:01.29 spid62      Setting database option SINGLE_USER to ON for database nettraffic_db.
2016-07-30 03:10:01.64 spid62      Starting up database 'nettraffic_db'.
2016-07-30 03:10:01.65 spid62      The database 'nettraffic_db' is marked RESTORING and is in a state that does not allow recovery to be run. 
2016-07-30 03:10:01.83 spid62      Recovery is writing a checkpoint in database 'nettraffic_db' (14). This is an informational message only. No user action is required.
2016-07-30 03:10:02.09 spid62      Starting up database 'nettraffic_db'.
2016-07-30 03:10:02.13 spid62      CHECKDB for database 'nettraffic_db' finished without errors on 2011-08-03 00:53:32.183 (local time). This is an informational message only; no user action is required.
2016-07-30 03:10:02.24 spid62      Setting database option MULTI_USER to ON for database nettraffic_db.
2016-07-30 03:10:02.24 spid62      Setting database option MULTI_USER to ON for database nettraffic_db.
2016-07-30 03:11:02.27 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2016-07-30 03:11:12.56 spid14s     Service Broker manager has shut down.
2016-07-30 03:11:12.61 spid14s     Error: 17054, Severity: 16, State: 1.
2016-07-30 03:11:12.61 spid14s     The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.
2016-07-30 03:11:13.48 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Explanation

SQL Server is being shut down at 3:11 AM.

 

Event Viewer

Event Viewer – Setup

EventViewer-Setup

Microsoft updates were being applied on Saturday early morning between 3 and 3:15 AM.

 

Summary

Looks like the message “Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.” is due to the application of MS Windows Update against the server.

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