Explicitly using tempdb with ##tables

I had someone ask me last night if this statement would create a permanent table in tempdb with a strange name or a global temp table:

CREATE TABLE tempdb..##mytable
( id int

My suspicion was that this would always be a temp table, but since I usually don’t include tempdb, I decided to test things. I quickly opened up SSMS and tried it. I got this message:

2016-04-21 13_55_14-Microsoft Solitaire Collection

That seems to imply what I suspected. The ## override everything and determine the table type. When I look in the list of tables, I see my table there as a temporary one.

2016-04-21 13_56_21-Start

This is the behavior I’d expect, and it acts the same in SQL 2012, 2014, and 2016 (RC2).

I don’t think there’s an advantage to using tempdb..##mytable, or even creating a permanent table (until restart) using tempdb.dbo.mytable, but if there is, please note something in the comments.

Changing the sa Password with SQLCMD

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.

I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

SQLCMD is a command line tool, so open a command prompt.

2016-04-06 12_47_33-Photos

Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

Once you’ve connected, you can issue this code:

ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

Once I’ve done this, test the sa login from a new session and verify it works.


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.


SQLCMD – https://msdn.microsoft.com/en-us/library/ms162773.aspx

ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

Promotions and Conflicts of Interest

I noticed my co-worker, friend, and PASS board member, Grant Fritchey, posted a note on members of the PASS Board of Directors (BoD) presenting pre-cons at SQL Saturday events. It’s potentially a legal issue, and conflict of interest. I’m glad that the issue is being raised, and discussed publically. Here’s Grant’s question:

The question is simple, for a PASS branded event, should a member of the PASS board receive payment?

There’s two parts to this, because there are two events. There are events that the organization runs and takes legal and financial responsibility for, and there are events associated with PASS, but run by others who have responsibility.

My short answer is yes to local events, and no to events run by the PASS board. I’ve read through some of the comments and I have some thoughts.

PASS Run Events

First, events run by the organization, such as the Summit and BAC, are different. The BoD can vote on aspects of these events, and can override the decisions on which individuals are chosen. With that being the case, I think there is a clear conflict of interest here and for the limited time the BoD members serve, they should not be a part of these events. Whether they receive direct payment or not, I’d say no.

There was a conflict about this a few years ago, and I think it was justified. If you serve, you can’t present a pre-conference (or post) session. You have other duties, and a responsibility here.

If your business or your employer wants you to be a part of this event in a different way, resign one position or the other.

SQL Saturdays

Really this could be any event that PASS might support or lend their name to, but doesn’t have any financial (or likely, legal) responsibility. This is trickier, as certainly the ability to bestow favors on the organizers of these events in terms of choosing them for PASS run events is possible. However I’d say that this is very unlikely, and hasn’t been an abuse of power. If that changes, I’d change my opinion

I think that the BoD members are still speakers and well respected trainers, and I really have no issue with them being accepted to present a pre-con and being paid by the events. I’d like the fact disclosed, but this doesn’t seem to be a conflict of interest to me at this point.

It’s also good for the community.


As I write more code, especially database code, and I collaborate with others (or myself), I find that I have the need to merge code more and more. It’s inevitable, and since I work across a couple machines, I even find that I need to merge my own code at times.

One way to do this well is use a merge tool of some sort. KDiff3 is a popular one, that’s free, and it’s one that integrates nicely with SQL Source Control, which I also use for various tasks.

If you install KDiff3 (use “choco instal kdiff3”) then you get a basic tool that allows you to compare files. If you start it, you’ll get a simple interface that lets you select multiple files and view them together in one interface. If you don’t use version control, apart from making a mistake, you also probably manage code like this.

2016-04-07 14_20_47-Settings

Or you might have this:

2016-04-07 14_23_22-Settings

Either way creates problems. If you have multiple people doing this, then you have other issues.

KDiff gives you a nice interface to see the differences between files. As you can see below, I have the changes marked in each file.

2016-04-07 14_26_43-Settings

I can edit the files directly, or choose to merge them together. If I do the merge, I’ll get a third window that has the merged code.

2016-04-07 14_28_41-Settings

As you can see, if I click the merge area, I can choose the code from either of the original files.

On the right is a scroll bar that lets me know where in the file the differences are.

2016-04-07 14_26_49-Settings

Software developers think nothing of these types of tools, but DBAs aren’t as easily used to using tools like this. I’d suggest that you download kdiff3 and give it a chance in trying to reconcile any code between team members or servers.

And start using Version Control. It’s easy and incredibly useful.

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.

2016-04-06 12_47_33-Photos

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

2016-04-06 12_51_23-Photos

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).

2016-04-06 13_01_33-Photos

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:

2016-04-06 13_01_47-Photos

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.

2016-04-06 13_07_03-Photos

If I enter go, I’ll get this:

2016-04-06 13_07_15-Photos

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

2016-04-06 13_07_26-Photos

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.

2016-04-06 14_00_07-Start

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.

T-SQL Tuesday #77–My Favorite Feature

tsqltuesdayThis 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.

2016-04-07 13_50_00-Movies & TV

I set up a one time schedule.

2016-04-07 13_49_51-Settings

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

2016-04-07 13_50_12-Movies & TV

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.

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.

2016-04-06 12_40_31-Photos

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

2016-04-06 12_42_22-Photos

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.


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.

Win a SQLBits Pass

You can win a free conference pass to SQL Bits, in Liverpool, May 4-7. It’s easy, just post a review of one of their previous events. Haven’t been? No worries, post a review of a video of one of the sessions. You can find those here:

If you want to just register, there’s a few discount code slots left, so use 4pr1l-F00l when you register.

This is my favorite event, and I hope to see you there.

MDF File Password Confusion

I had never seen this, but I ran across a blog that mentioned an MDF File password here. The post really looks at ways to reset the administrator password for the “sa” account in SQL Server. However it has some mistakes and issues. I tried leaving a comment, but comments are disabled.

With that in mind, I decided to respond to a few things and clear up confusions.

With regards to the post, I think it’s confusing in that the text notes an MDF file password, but all the instructions are really about resetting the “sa” account password. sa is the built in sysadmin account in SQL Server, which isn’t related to the MDF file. The MDF file is the extension of the main data file for a database. You can change this, but there isn’t a good reason to do so. Note, the .ndf files are the same format, though by convention, these are the 2nd, 3rd, and other files added to a database.

There also isn’t a password on these files. I can open them in notepad (not recommended) or xvi32, and there isn’t any requirement if I have read access in NTFS to the file. It doesn’t matter if this is the master database or any user database. If you have NTFS permissions, you can read the file.

Now interpreting is different. SQL Server interprets this, and it requires permissions itself to access the server process, either sysadmin, or normal login. However, you can use ORCAMDF or MDF Viewer, or some other tool to read the files. The information contained in an mdf/ndf file is just formatted in a certain way. If you spend a lot of time, you will understand how to interpret the format.

Changing the sa password requires that the SQL Server service be running and you connect in some way. The post gets the methods right, but says that you must stop the service, which is only needed if you access the file some other way (ORCAMDF, xvi32, etc.). If you want to change the sa password, there are a few choices:

  2. Use SQLCMD
  3. Use osql
  4. Use one of the above methods with SQL Server restarted in single user mode
  5. Use a third party utility.

Any of these first four will work, and feel free to use whichever fits your situation. The last one is one I do not recommend as I can’t be sure any third party products will work correctly here.

Ultimately I’m a little embarrassed by this post, as it appeared through our syndication process on SQLServerCentral. We don’t review these posts, so there is no quality control. Most of the posts on this blog are good ones, but this one appears to be by a guest author and it’s one I’d ignore.

Quick Encryption with Always Encrypted

What do you need to do in order to access data in a SQL Server that’s encrypted with Always Encrypted? It’s not much, and it’s really simple.

  1. The certificate used for encryption
  2. A parameter in the connection string

That’s it. It’s a small list of things.

I was experimenting with this, and I set up encryption on a VM, then copied the certificate backup to another VM and installed it in the Certificate Store.

2016-03-28 17_56_00-Settings

This is all I changed on my C# application to enable encryption.

strConnstring += “; Column Encryption Setting = Enabled”

I had a connection string built, and I added this one little option to the end and when I queried my encrypted table, I could read the data.

There are certainly more caveats and more to learn about encryption, but this shows how easy it can be to change your application. Just alter the connection string.