Voice of the DBA

Category: Blog

Basic SQLCMD–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.

SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times. I recently opened a command prompt.

I then typed SQLCMD. After a delay, I got this:

The issue here is that I don’t have a default instance on this machine. All of mine are named. I need to provide a –S parameter, with a server name (and possibly instance name).

I do that and I’m connected. By default, SQLCMD (and osql) try to use Windows Auth. The 1> indicates that the utility is ready for T-SQL queries. You need to know your language here as there’s no help.

I can enter code, and check my user name. I do this, and get a 2>. The end of a batch is indicated with “GO” and this will execute the batch. You can see how this works below:

I can use this to make my code easier to read. I can format code as I would in an editor, though be aware you can’t go back and edit previous lines.

If I enter go, I’ll get this:

Not so easy to read. I have to scroll up to even figure out what the display is:

As you can see, using SELECT *, or retrieving too many columns make results hard to read. You would to wise to pick only those columns you need to return.

To leave SQLCMD, you can type exit, which will return you to the command prompt.

This is a short look at SQLCMD. The older, osql, utility functions the same way, and both are good, lightweight ways to connect to your SQL Server instance.

April 14, 2016
SQL Saturday #492–Phoenix
It’s almost time for SQL Saturday #492 this weekend in Phoenix. I haven’t been there in a few years, but I’m heading back for a quick trip to the desert.

I’ve got two sessions scheduled this weekend, and if you’re attending, I’ve love to see you at one of my sessions:
- End to End Protection with Always Encrypted in SQL Server 2016
- Get Testing with tSQLt
The event has grown quite a bit, with 13 tracks. I’m hoping a lot of people in the Phoenix area are coming out for a free, exciting SQL Server conference.
April 13, 2016
T-SQL Tuesday #77–My Favorite Feature
This month is an interesting, but tough topic. The blog party is hosted by Jens Vestergaard, and his invitation is short and simple. Pick your favorite feature and write about it. This is good, because SQL Server has grown so much, I’m sure that many people will choose different things. However it’s hard for someone that works with many different features.

My Favorite Feature

I’ve been working with SQL Server since 1991. I’ve worked with all the Windows versions, and a few on OS/2. That means I’ve had the chance to manage and develop applications on:
- SQL Server 4.2
- SQL Server 6/6.5
- SQL Server 7/2005
- SQL Server 2008/R2/2012/2014/2016
I’ve seen the platform grow and expand quite a bit. I’ve spoken on a number of topics over the years, as my jobs have changed and my emphasis has wandered. Of all the features available, however, if I have to choose one, it would be…

SQL Agent.

I’m a programmer at heart. I grew up admiring the power of computers to execute code over and over again. I appreciate the ability of computers to remember things and remind me, or to handle them on their own.

SQL Agent allows that. Over the years, I’ve taken advantage of SQL Agent to perform maintenance on systems, to alert me to issues, to run a process that needed to be performed. I can even schedule one off jobs, having them delete themselves. I can’t tell you how many times someone has asked me to run something “later” on the server, often during the evening. It’s easy to schedule a job for later, have it run one time, and then let it disappear.

For example, Andy asks me to run a query tonight that gathers some data. I create a new job and step.

I set up a one time schedule.

Certainly I can set alerts and logging, but in notifications, I can have this job disappear.

I certainly want to make sure I have results saved, but this allows me to execute code, without much effort, and in a way that doesn’t clutter up my system.

I’ve found SQL Agent to be incredibly easy to work with, and quick to build jobs running against my SQL Server. I don’t need to setup connections, like I might need to with the Windows scheduler.

If you haven’t experimented with SQL Agent’s capabilities, or you don’t use it extensively in production, you should.
April 12, 2016
Changing the sa Password with SSMS–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

Changing sa with SSMS is really simple. Follow these steps:

Connect to SSMS as a sysadmin. You can check this for your login. Then expand the Security folder and the Logins folder. Right click the “sa” account and choose properties.

Once you do this, you’ll get the login properties dialog, and see the Password text field at the top in the General tab.

You can type a new password in the Password box, and confirm this in the Confirm Password box. Password policies checks are up to you, though I’d recommend you use them.

Click OK and the password is changed. You can then connect a new query window as “sa” and verify your password.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

April 11, 2016