SQLS*Plus for SQL Server: Register License, Startup Scripts and start working

SQLS*Plus for SQL Server: Issue “sqlsplus.exe –r” command and paste your license token to register SQLS*Plus

STARTUP SCRIPTS

When SQLS*Plus starts, and after CONNECT commands, the two sql files are being executed:

1. login.ssp — SQLS*Plus profile
2. login.sql — User profile

The files may contain SQLS*Plus commands.

CONNECTING TO DATABASE SERVER

Database server connect command:

“connect username/password@server\instance:database”

When connecting from inside database session use \\ to prefix instance name:

“connect username/password@server\\instance:database”

Example:

D:\ >sqlsplus sa/password@192.168.1.160 -- Connect to SQL Server instance using database username andpassword
SQLS*Plus: Release 2.0.1.8 - Production on Tue Nov 1 17:07:45 2011

Copyright (c) 2010, 201x, Memfix. All rights reserved.

SQLS*Plus is free for an individual use and a commercial use on a single SQL Server
instance.

Please visit http://www.memfix.com or email support@memfix.com to purchase
required…
Connected to:

Microsoft SQL Server RTM, version 9.00.1399.06, Developer Edition (64-bit), current
database: tempdb

0:sa@192.168.1.160> show dbs; -- “show“ command (sqlsplus) to list all accessible databases

Database Name

AdventureWorks
BusinessServiceIISRepository

BusinessServiceRepository
CapacityPlanner
CompositeWebAppRepository
master
model
msdb
OrderProcessorForwardRepository
OrderProcessorRepository
StockTraderDB
StockTraderWebAppRepository
tempdb

0:sa@192.168.1.160> use AdventureWorks; --“use” command (SQL) to select database
0:sa@192.168.1.160> show db;
database is "AdventureWorks" -- “show“ command (sqlsplus) to show current database
0:sa@192.168.1.160>

SET DEFAULT DATABASE FOR CONNECTION

1) Command line:

sqlsplus.exe sa/<pwd>@192.168.1.160:AdventureWorks

or

sqlsplus.exe sa/<pwd>@192.168.1.160\ SQLSERVER2008:AdventureWorks

2) SQLSDBNAME environment variable

SET SQLSDBNAME=AdventureWorks

3) SQLCMDDBNAME environment variable (sqlcmd variable)

SET SQLCMDDBNAME =AdventureWorks

CONNECTING WITH TRUSTED CONNECTION / WINDOWS AUTHENTICATION

1) Connect from command line:

sqlsplus.exe –E

– connect to default local database instance

or

sqlsplus.exe -E@HOST\SQLSERVER2008

– connect to specified remote database instance

or

sqlsplus.exe -E@HOST\SQLSERVER2008: AdventureWorks

– connect to specified remote database instance and database

2) Connect from SQLS*Plus session

connect -E

connect -E@HOST\\SQLSERVER2008

connect -E@HOST\\SQLSERVER2008: AdventureWorks

CONNECTING WITH USERNAME OR PASSWORD THAT CONTAINS ‘@’

Unless password is entered interactively, prefix @ with \.

Example:

connect user/pass\@word@ HOST\\SQLSERVER2008

When connecting from inside database session use \\ to prefix @:

0:sa@server\SQLSERVER2008> connect user/pass\\@word@ HOST\\SQLSERVER2008

START SQLS*PLUS WITH NO CONNECTION

Use “/nolog” to start SQLS*Plus without connecting to database

This option is useful if connect statement is in the script and for security reasons should not be
externalized in command line

For Example:

sqlsplus /nolog @t4

t4.sql:

connect sa/xxxx@prodsrvr1;

set pages 0;
use tempdb;
db;

define tbl = sys.objects;
select count(*) c1 from &tbl;
quit

CONNECTIVITY ENVIRONMENTAL VARIABLES

1) SQLSUSER / SQLCMDUSER
Default connect user

2) SQLSPASSWORD / SQLCMDPASSWORD
Default connect user password

3) SQLSSERVER / SQLCMDSERVER
Default SQL server host and instance

4) SQLSDBNAME / SQLCMDDBNAME
Default database to connect to

5) SQLSPATH / SQLPATH
Environment variables that specify search locations of the SQL scripts. SQLS*Plus searches for the SQL scripts, including “login.ssp” and “login.sql”, starting from the current directory and after that in the directories specified by SQLSPATH first and SQLPATH after it. SQLSPATH and SQLPATH is a semicolon separated list of
directories.

BATCH EXECUTION OF SQL SCRIPTS

Make sure to use double slashes (“\\”) in the path, i.e “d:\\x1.sql”

You can call batch sql file as below:

sqlsplus.exe sa/<pwd>@192.168.1.160 @d:\\x1.sql

In this example we connect to default instance of SQL Server on a server and execute sql script
x1.sql.

or

sqlsplus.exe sa/<pwd>@192.168.1.160\SQLSERVER2008 @d:\\x1.sql

In second case we connect to specific instance (in case there are more than one)

Sample x1.sql content — includes “quit” command to insure that program quits after script execution

--
set pages 200
set lines 200
select * from master.dbo.sysprocesses;
quit

Execute script with no connectivity information on a command line

Use “/nolog” on SQLS*Plus command line and include “connect” command into the SQL script

For example:

sqlsplus.exe /nolog @x1.sql

Note: x1.sql contains connect command, i.e.:

”connect sa/<pwd>@192.168.1.160”

MULTIPLE DATABASE SESSIONS SUPPORT

SQL> connect sa/xxxx@192.168.1.160 --Connect to database

Connected to:

Microsoft SQL Server RTM, version 9.00.1399.06, Developer
Edition (64-bit), current database: tempdb

0:sa@192.168.1.160>
0:sa@192.168.1.160>
0:sa@192.168.1.160> connect -l --List current sessions (1)
0 sa@192.168.1.160
0:sa@192.168.1.160> help --connect Help on “connect”

=====SessionMgr=====

connect - create a new session
disconnect - disconnect session nr

try help -v or <command> -h for detailed help.
0:sa@192.168.1.160> help -v connect --Detailed help on “connect”

=====SessionMgr=====

NAME
connect - create a new session

SYNOPSIS
connect <connectString>
alias: conn|c
-a create additional session, don't disconnect
-l list connections
-s sessNo switch to sessNo (as reported by -l)

DESCRIPTION
Connect creates a database session. You can have multiple
sessions at a time, which may save you some time, when you
need to switch sessions frequently.

The sqlsplus is useful when you run sqlsplus from within emacs
and you started it with a bad connect string.

NAME
disconnect - disconnect session nr

SYNOPSIS
disconnect <sessionNr>
alias: dis

try help -v or <command> -h for detailed help.
0:sa@192.168.1.160> connect -l --List current sessions (1)
0 sa@192.168.1.160
0:sa@192.168.1.160> connect -a sa/xxx@192.168.1.170 --Connect as additional session to new database

Connected to:

Microsoft SQL Server RTM, version 9.00.1399.06, Developer
Edition (64-bit), current database: tempdb1:sa@192.168.1.170> connect -l --List current sessions (2)
0 sa@192.168.1.160
1 sa@192.168.1.170
1:sa@192.168.1.170> connect -s 0 --Switch to session 0
0:sa@192.168.1.160> connect -s 1 --Switch back to session 1
1:sa@192.168.1.170> quit

MULTI-LINE SQLS*PLUS COMMANDS

The SQLS*Plus commands can span multiple lines, as long as dash “-‘ is used at the end of each
continuing line.

For example:

TITLE LEFT 'User Report' -
> RIGHT 'PAGE:' -
> SQL.PNO SKIP 2

SPECIAL DATA SELECTION FUNCTIONALITY

• Vertical Output — allows to see large column sets as a vertical output “set vout on”
• Table data “grep” — search for data across all columns
• Data purge — purge table data in a small chunks