Wednesday, September 26, 2007

SQLCMD -- Part VI (Scripting Variables)

To list all available SQLCMD Scripting variables, do the following:

Step 1: Go to DOS prompt and open up SQLCMD
Step 2: type :listvar which would list all SQLCMD scripting variables.

In the screenshot you can see that the SQLCMDEditor and SQLCMDINI variable which we overwrote in the previous posts here and here are displayed.

Almost all of the other variables are self-explanatory :)

Creating our own local variables:

:setvar DB1 testBed
:setvar DB2 Adventureworks

use $(DB1)

Select getdate();

use $(DB2)

select top 5 city from person.address

Point to note:

1. If you execute :listvar again you can find these newly created variables getting listed there now. But please note that these local variables would be alive only till the life of the current session. i.e., If you exit out of SQLCMD once and come back and type :listvar these variables would be missing there.

2. Always the variables which we create will be in Uppercase only. Just try creating something like this:

:setvar NaMe Vadivel

You can see the variable got created as NAME in uppercase.

Check out these other options to get your hands dirty:

1. :help --> This would list out all the commands available for SQLCMD

2. -o --> this is for specifying output file path
3. :listvar --> I have talked about few of the variables in it. Just tryout the other variables yourself.

Hope this series of 6 posts would have given a general understanding of SQLCMD for the readers!

Tuesday, September 25, 2007

SQLCMD -- Part V (setting startup scripts)

There are instances where we might need to run some default scripts on a specified server once SQLCMD gets connected. It can be achieved in just three steps as explained below:

Step 1: Create a script file which you wanted to fire when SQLCMD gets connected to your SQL Server.

For keeping the example simple, I used the following line and saved it as SqlCmdStartUpScripts.sql

print 'Welcome today''s date is: ' + (convert(varchar, getdate()))

Step 2: Open DOS prompt and type set sqlcmdini=c:\vadivel\sql related\scripts\SqlCmdStartUpScripts.sql

Step 3: Then type SQLCMD and press Enter.

Refer the below screenshot for the sample output.

SQLCMD -- Part IV (set your favourite editor)

From my previous posts one can understand that it is possible to write SQL queries directly in command prompt with the help of SQLCMD utility.

Now let's assume we have typed a 'big' query and there is a typo there! Instead of going back and forth to edit it in command prompt won't it be easy if we are able to open the query in an editor and make the corrections there?

Yes its possible in SQLCMD. All we need to do is type ed and it will open up the last command/query in a text editor. FYI the default editor is Edit (the command line editor of MS DOS).

Step 1: Open up SQLCMD and connect to your SQLServer
Step 2: type any query of your choice
Step 3: type ed
Step 4: The query would have opened in the 'EDIT' utility of DOS. Once you are done with the change, save and exit from that.
Step 5: type go and press enter

Can I make 'ed' to open up notepad or any editor of my choice?
Yes it's possible.

Step 1: Open DOS prompt
Step 2: Type set sqlcmdeditor=notepad
Step 3: Open up SQLCMD and connect to your SQLServer
Step 4: type any query of your choice
Step 5: type ed
Step 6: The query would get opened in notepad. Once you are done with the change, save and exit from that.
Step 5: type go and press enter

Monday, September 24, 2007

SQLCMD -- Part III (Non-Interactive or batch Mode)

If you haven't gone through the first two posts about SQLCMD I would strongly recommend to go over it here and here before proceeding further :)

1. Executing a script file from SQLCMD...

i. Create a script file by typing in the following line and save it as Message.sql
print 'Welcome today''s date is: ' + (convert(varchar, getdate()))

ii. Now goto command prompt and type: SQLCMD -i Message.sql
here, -i is the switch to specify the input file name.

2. Executing series of script files (sample)

Lets create couple of .sql files and then see how to execute them in order from command prompt. Please note that i am just showing an example here :) there are better methods of doing the same which I would explain later in the series!

i) Copy paste the below script and name it as 01TableCreation.sql

Create table tblTest
Sno int identity,
FName varchar(20)

ii) Copy paste the below script and name it as 02InsertRecords.sql
Insert into tblTest (Fname) values ('a')

iii) Copy paste the below two lines and name it as test.bat

sqlcmd -U sa -P hotmail -S VADIVEL -d testbed -i "C:\Vadivel\SQL
sqlcmd -U sa -P hotmail -S VADIVEL -d testbed -i "C:\Vadivel\SQL

-U is SQL User name
-P is SQL Password
-S is the SQL Server name
-d is the SQL database name
-i is the input file to execute

iv) Execute the batch file

Now goto command prompt (Start >> Run >> cmd) and execute this batch file. The batch file would have created a table and inserted a record into it. If you want to deploy some DB scripts on a remote box for which you don't have access from Mgmt Studio you can follow this batch file route. So that the ppl who are having access to that SQL box can just run this batch file (after changing the values of the different switches, if need be)

SQLCMD -- Part II (Interactive Mode)

First get connected into the DB Server using SQLCMD using either the windows authentication or SQL authentication as explained in the previous post.

Then on the prompt you can type in the TSQL queries directly and press enter. In the next line, say Go and press enter to execute the query and see the result. See below an example to display the current datetime.

After displaying the result the cursor would stand on the prompt expecting for further queries from us :) Once you are done you can 'exit' out of sqlcmd utility like shown below:

By default, it get's connected to the default database of that login only. One can make use of -d switch to connect to a DB of their choice. Please refer the below sample where I make use of Adventureworks DB and query a table.

What is the difference between -q and -Q switch?

-q is for running queries from SQLCMD.
-Q is for exitting from SQLCMD immediately after executing a given query.

SQLCMD -q "Select getdate()"

This query will print the current datetime and then return to SQLCMD prompt.

SQLCMD -Q "Select getdate()"

This query will print the current datetime and then exit from SQLCMD automatically.

Sunday, September 23, 2007

SQLCMD -- Part I (Basics, Connectivity)

“SQLCMD” is a command line tool which was shipped by Microsoft along with SQL Server 2005. Previously SQL Server was having ISQL and OSQL as its command line utility. SQLCMD is replacing both of them (i.e., ISQL is not there in SQL Server 2005 RTM version. OSQL would also be eventually removed!!).

When “SQLCMD” is run from the MS-DOS command prompt, it uses the OLE DB provider to execute the given queries.

SQLCMD has batch and interactive modes. 'Batch mode' can be used mainly for scripting and automation tasks, while 'Interactive mode' is for firing ad-hoc querys.

i) To list all the parameters supported by SQLCMD utility, run the following in command prompt

ii) To connect to SQL Server using SQL Authentication
SQLCMD -U username -P yourpassword -S Servername

Once we press the enter key. If the UID/PWD is valid it would prompt you to enter the t-sql query to execute.

If at all the UID/PWD combination is wrong it would throw an error similar to the one shown below:

Msg 18456, Level 14, State 1 .......
Login Failed for user 'sa'

iii) To connect to SQL Server using Windows authentication
SQLCMD -S servername

By default, SQLCMD uses the trusted connection only. So no need of specifying -E parameter which is meant for this purpose.

iv) To connect to a named instance using the portnumber and the hostname

In the command prompt type Hostname and press enter. It would display the hostname of your PC.

SQLCMD -U sa -P hotmail -S tcp:VADIVEL,1433

Note: I have just mentioned 1433 here for example. Actually its not needed as its the default port for SQL Server.

v) How to know what is the port number configured in my box?

SQL Server's works by default with Port 1433. If it has been configured earlier to work on a different port then follow the below steps to find it out!

1) Open up 'SQL Server Configuration Manager'

2) Then double click on 'Protocols for MSSQLServer'


Some pointers:

1. NewSequentialID() and NewID() both generates the GUID of datatype of uniqueidentifier.
2. NewID() generates the GUID in random order
3. NewSequentialID() generates the GUID in sequential order.
4. NewSequentialID() can be used ONLY in default clause of a table.
5. NewSequentialID() is easily predictable
6. So if security is a major concern then go for NewID() instead of NewSequentialID().

Example to demonstrate NEWSEQUENTIALID():

Create table #tblDemoSequentialID
Column1 uniqueidentifier default NewID(),
Column2 uniqueidentifier default NewSequentialID(),
Fname varchar(30)

Pump-in few dummy records:

Insert into #tblDemoSequentialID (Fname) values ('Vadivel')
Insert into #tblDemoSequentialID (Fname) values ('Rajinikanth')
Insert into #tblDemoSequentialID (Fname) values ('Sivaji')

In this query 'Column1' would demonstrate that the 'NEWID' has generated GUID in random fashion. Also 'Column2' would contain GUID in Sequential Order (refer the below screenshot).

Select Fname as [First Name], Column1 as [NewID], Column2 as [NewSequentialID] from #tblDemoSequentialID

Flush the table and proceed to next demo:

Delete from #tblDemoSequentialID

Create another temp table:

Create table #tblDemoTWO
LName varchar(20),
Column_SeqID uniqueidentifier default NewSequentialID()

Lets insert data into both these tables alternatively. This is to prove that the NewSequentialID would be in sequential order :)

Insert into #tblDemoSequentialID (Fname) values ('VDSI')
Insert into #tblDemoTWO (Lname) values ('Yuvaraj')

Insert into #tblDemoSequentialID (Fname) values ('Verizon')
Insert into #tblDemoTWO (Lname) values ('India')

Insert into #tblDemoSequentialID (Fname) values ('Dhoni')
Insert into #tblDemoTWO (Lname) values ('Mahendra')

Select Fname as [First Name], Column1 as [NewID], Column2 as [NewSequentialID] from #tblDemoSequentialID where [Fname] in ('VDSI', 'Verizon', 'Dhoni')

Select Lname as [Last Name], Column_SeqID as [NewSequentialID] from #tblDemoTWO

Just go through the output of both the above queries. You could find it for yourself that the NewSequentialID columns in both the tables got incremented sequentially.

Clean up :

Drop table #tblDemoSequentialID
Drop table #tblDemoTWO