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

Db/2 LUW – Loading SQL Server / WorldWideImporters

Background

Unfortunately DB/2 LUW Sample database is very small.

Let us copy data from SQL Server’s sample database, WideWorldImportersDW, over to our DB/2 instance.

Outline

  1. In SQL Server
    • Retrieve Data
  2. In DB2/LUW
    • Create database
    • Create Schema
    • Create Tables
    • Remove existing data
    • Load new data

SQL Server

Retrieve Data

Outline

Using SQL Server’s BCP retrieve data from SQL Server.

Dimension.Date

Code


setlocal

set "_sqlInstance=localhost"

set "_database=WideWorldImportersDW"

set "_columnList=*"

set "_datafileFolder=datafile"

set "_option= -c -T -t"," "

if not exist %_datafileFolder% mkdir %_datafileFolder%

bcp "select %_columnList% from [%_database%].[dimension].[date]" queryout %_datafileFolder%\dimension.date.txt  -S %_sqlInstance% %_option%

endlocal

Db/2 LUW

Database

Create

We provided a sample script for creating a new DB/2 LUW database here.

Schema

Create Schema

Dimension

Code

 CREATE SCHEMA "Dimension"

Table

Create Table

Dimension.Date

Code

DROP TABLE "Dimension"."Date"
//

CREATE TABLE "Dimension"."Date"
(
    "Date" date NOT NULL,
    DayNumber int NOT NULL,
    "DAY" varchar(10) NOT NULL,
    "MONTH" varchar(10) NOT NULL,
    ShortMonth varchar(3) NOT NULL,
    CalendarMonthNumber int NOT NULL,
    CalendarMonthLabel varchar(20) NOT NULL,
    CalendarYear int NOT NULL,
    CalendarYearLabel varchar(10) NOT NULL,
    FiscalMonthNumber int NOT NULL,
    FiscalMonthLabel varchar(20) NOT NULL,
    FiscalYear int NOT NULL,
    FiscalYearLabel varchar(10) NOT NULL,
    ISOWeekNumber int NOT NULL

)
IN TS_WIDEWRLD_REG04
//

ALTER TABLE "Dimension"."Date"
      ADD CONSTRAINT "PK_Dimension_Date"
      PRIMARY KEY
      (
        "Date"
      )
//        

Load data into DB/2

Outline

Using Db2 Import utility let us copy our comma delimited file into DB/2 LUW.

Dimension.Date

Code


setlocal

set "_datafile=..\bcp\datafile"

db2 connect to WideWrld

set "_table=\"Dimension\".\"Date\""

rem db2 import from /dev/null of del replace into  %_table% 

db2 "truncate table %_table% reuse storage ignore delete triggers immediate"

db2 commit

db2 import from %_datafile%\dimension.date.txt OF DEL modified by coldel, insert into %_table%

db2 CONNECT RESET

endlocal
Output

dm2Import_20180918_0405PM

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.