robert

How To Run SQL Scripts Via Command Prompt

by
Facebooktwitterredditpinterestlinkedinmail

Usually, when we run SQL scripts, it is done on an ad hoc basis.  We usually run one file at a time just once.  From time to time there is the need to run a set of SQL script files.  One common reason I’ve seen for doing this is to rebuild a database for testing.  If you had all of your create tables/stored procedure commands stored in a series of SQL script files, then you could rebuild your database fairly easily by executing the SQL files in sequence.

 
SQL Server provides a tool to do this called SQLCMD.

The sqlcmd utility lets you run entire SQL script files full of t-sql via command prompt.  Leveraging this tool, you can create a batch file to run the sql script files in sequence.

 
 
How To Execute A SQL Script File Via Command Prompt With SQLCMD

Although there are TONS of command line options for executing a sql script.  Just executing a sql script file with the basic options is very easy.

C:\>SQLCMD -i"c:\scripts\DropTables.sql"

The above command uses the -i option to specify the input file of DropTables.sql.  Everything inside this file will be execute on the current database.

 
NOTE: When you create your SQL script files, I always recommend putting a USE statement at the top of your script.  This will ensure that you are using the correct database when your dropping and creating tables.

 
 
Run A Batch Of SQL Script Files

Now that you know how to run one SQL script file… running multiple should be easy.  All you need to do is create a batch file and run one script after the other.

 
Your batch file will look something like this:

SQLCMD /i"c:\scripts\DropTables.sql"
SQLCMD /i"c:\scripts\CreateTables.sql"
SQLCMD /i"c:\scripts\CreateStoredProcedures.sql"
PAUSE

 
 
What About Errors?

So what happens if the SQL script file throws an error?  The above commands I showed you will just ignore any errors.  If you want to capture your errors, you need to add the command line option /b.  This flag will tell the sqlcmd utility to stop processing the script and output a DOS ERRORLEVEL value.  You can then check the DOS ERRORLEVEL value after each execution.

SQLCMD /i"c:\scripts\DropTables.sql" /b
IF %ERRORLEVEL% > ( PAUSE )

SQLCMD /i"c:\scripts\CreateTables.sql" /b
IF %ERRORLEVEL% > 0 ( PAUSE )

SQLCMD /i"c:\scripts\CreateStoredProcedures.sql" /b
IF %ERRORLEVEL% > 0 ( PAUSE )

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx

 

SQL Server Trim

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server trim function does not exist!  Although this seems a bit odd, SQL Server does somewhat have support for trimming data.  SQL Server has built in functions for Right Trim and for Left Trim.  Using these functions, you can accomplish a full trim.  I’ll first show you how to trim the different sides of the text and then show how to trim the full thing.

 
Trim Text Inline

The functions to trim text are RTrim() and LTrim().

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

-- Trims the spaces from the left side of the data
SELECT	LTRIM(@TextToTrim) AS LeftSpacesRemoved

-- Trims the spaces from the right side of the data
SELECT	RTRIM(@TextToTrim) AS RightSpacesRemoved

-- Trims the spaces from both sides of the data
SELECT	LTRIM(RTRIM(@TextToTrim)) AS FullTrim

 
If a full trim is something that you do often, you can create a SQL Server Trim function to make the process a little simpler.

CREATE	FUNCTION TRIM(@TextToTrim VARCHAR(8000))
RETURNS	VARCHAR(8000)
BEGIN

	RETURN	LTRIM(RTRIM(@TextToTrim))

END

You can use this new fancy SQL Server Trim function by calling it like this:

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

SELECT	dbo.TRIM(@TextToTrim) AS FullTrim

5 Quick Tips For Using SQL Server Management Studio

by
Facebooktwitterredditpinterestlinkedinmail

1) Script multiple objects by using the Object Explorer Details.

Scripting objects like tables and stored procedures is one great feature of SQL Server Management Studio.  But the way to script multiple tables at one time is not very straight forward.

To script multiple objects, first thing you need to do is open the Object Explorer Details window.  This is located under the VIEW menu at the top of the screen.

Object Explorer Details

When you click on an object’s folder (Tables, Stored Procedures, Views, etc) in the Object Explorer (located on the left of the screen), the Object Explorer Details window will show all of the available tables.  You can now highlight multiple objects… then right click and select the Script As option.

SQL Server Object Explorer-details-window-script

 
 
2) Add Line Numbers To The Query Window

Line numbers can be very useful when doing development.  By default, SQL Server Management Studio has this turned off.  To turn this on:

  1. Select Tools -> Options
  2. In the tree on the left, select Text Editor -> All Languages. Under the Display heading, check the box for Line numbers.

Enable Line Numbering

 
 
3) How To Refresh The IntelliSense Cache

If you’ve ever added a table or stored procedure, you know that the IntelliSense does not know about it.  You can refresh the cache pretty easily.  To refresh the IntelliSense cache, just selecting Edit -> IntelliSense -> Refresh Local Cache.

SQL Server IntelliSense Refresh Cache

 
 
4) Highlight Columns Of Text In Query Window

This trick actually works in Visual Studio also.  You can highlight and manipulate text in different rows without having to highlight the entire row.  This is a little hard to explain, so I will show you pictures of what I mean.

Query Highlight Columns

To do this fancy trick, all you need to do is hold down the ALT key while you click and drag.

So, how is this useful you ask… good question.  I use it for 2 different things.

  • First is for aligning the sql statement. If you highlight text you can just hit tab and it will tab the highlighted text in more.
  • The second thing I use it for is copying data. If you only want to pull out a specific set of data, this is super easy.

 
 
5) Drag And Drop Objects On To Query Window

If you ever want a fast way to place a table name, column name, or other object in to a query… you can just drag and drop.  Just drag the object (table, column, store procedure, etc…) from the object browser and place it where you want it on the query window.  It will insert the fully qualified name in to the query with no typing needed.

Drag-Drop Objects

Cannot Insert Explicit Value For Identity Column

by
Facebooktwitterredditpinterestlinkedinmail

If you try to insert in to a table with an ID column, you could get this error “Cannot insert explicit value for identity column in table ‘<<table>>’ when IDENTITY_INSERT is set to OFF.”  This is not the end of the world and is actually pretty easy to get around.

 
Why Are You Getting This Error?

The reason you’re getting this error is because you are trying to tell SQL Server what the ID value for a row in the table should be.  If you have an identity column on your table, SQL Server will want to generate that ID value for you automatically.  The error is essentially saying, “Hey, I’m supposed to generate the ID for you… you aren’t supposed to tell me what it is!”

 
How Can You Fix This?

There are two easy ways to get around this.

 
If You Want To Tell SQL Server What The ID Value Should Be

If want to specify what the ID values should be for the records you are inserting, then before you execute the INSERT statement, you need to run a small sql command to turn identity inserts on.  Identity inserts allow you to populate the value of an identity column with a specific value.

SET		IDENTITY_INSERT Animal ON

INSERT
INTO	Animal
		(AnimalID, AnimalName)
VALUES	(1,        'Horse'),
		(2,        'Pig'),
		(3,        'Cow')

SET		IDENTITY_INSERT Animal OFF

NOTE: You can only have identity insert on for one table at a time.  To insert in to a second table, you will need to turn the identity insert off on the first table before inserting in to the second table.

 
If You Want To Allow SQL Server To Generate The ID

If you don’t care about what the ID values are for these records, you should just allow SQL Server to generate the ID values for you.  To do this, just specify the columns that are in the table and leave off the identity column.

INSERT
INTO	Animal
		(AnimalName)
VALUES	('Horse'),
		('Pig'),
		('Cow')

 
Reference: http://msdn.microsoft.com/en-us/library/ms188059.aspx

Case Sensitive Compare

by
Facebooktwitterredditpinterestlinkedinmail

Occasionally we need to do a string comparison in SQL Server as a case sensitive compare.  By default, case sensitive compare is not turned on for tables in SQL Server.  It’s actually pretty easy to change a string comparison to a case sensitive compare.  You can do this by using the COLLATE clause.

Read below to find out why this works and other tips on doing a case sensitive compare.

SELECT	A.AnimalID, A.AnimalName, AFT.FeedingTime
FROM	Animal A
INNER	JOIN AnimalFeedingTime AFT ON AFT.AnimalName = A.AnimalName COLLATE SQL_Latin1_General_CP1_CS_AS

 
 
What Is The COLLATE Clause

The COLLATE clause is something that you can add to the end of an expression to set the collation of the operation to a specific collation.  So in the example… the string equal comparison is being changed to a collation of SQL_Latin1_General_CP1_CS_AS.

A collation in SQL Server is something defines how data is compared.  This could be things like case sensitivity, acceptable characters, etc…  Most of the time (for English installations of SQL Server) it will be a collation type of SQL_Latin1_General_CP1_CI_AS.  This is because the Latin character set covers English and other Latin based languages.

The two most common collations that I deal with are SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS.  You can see that they are very similar.  In fact… the only difference is the _CI_ and _CS_.  These actually stand for Case-Insensitive and Case-Sensitive.
 
 
Field Level Case Sensitivity

Even better… you don’t need to set the collation on every sql statement that you want to make case sensitive.  There is actually a setting that you can apply to a field in a table.

NOTE: that if you are joining on this table, both collations must be the same or you will need to specify the collation to use.

Create a new table with a case sensitive field

CREATE
TABLE	Animal
(AnimalID		INT,
AnimalName	VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS)

Change a table’s column to be case sensitive

ALTER
TABLE	Animals
ALTER	COLUMN AnimalName VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS