Voice of the DBA

Tag: SQLNewBlogger

See Two Queries at Once in SSMS

I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

One of the things that I’ve struggled with a bit in SSMS is sometimes comparing the results of two batches. I’m sure many of you have executed a query, then make a change, and execute it again, losing your results. Or you are testing something in two query windows and need to switch back and forth. Sometimes doing this, and only seeing one set of results (or checking if a query is finished) is cumbersome.

A few years ago I was watching Brent Ozar tune queries at an event and one of the things he wanted to do was compare two queries and their execution plans. He used vertical tab groups, which is a great way of seeing two things at once.

Here’s how my screen ended up during the comparison I was actually doing of three queries. I was checking credentials using a before, after, and with the DAC.

Things are a bit shrunk down as I wanted the image to be viewable. I actually had this full screen on a 30" wide monitor, and I could more easily see the queries and results from each window.

The easiest way to do this is to start with a query:

I want to change something, add a login, and test again, but I don’t want to lose my results. I’d also like to do an easy comparison. What I can do is go to the Window menu and get a new Vertical Tab Group. I could also do a Horizontal one, but comparing results is easier for me with vertical ones.

Once I select this, my selected window will move to a new tab group, and I’ll see two places where I can run code and visualize the queries and results at once.

I selected the left hand query, then clicked "New Query" to get a blank window. I then cut and pasted my code from the right to the left. This is exactly what you might want to do when tuning queries, keeping the original on the right while you work on the left.

Now I have two places to work on code. In my case, I wanted the before and after view of Server_principals as I checked some admin changes. I could do things and keep re-running the query in one of these windows, but keep the results from the other one visible.

SQLNewBlogger

Once again I was doing something else and realized the vertical window trick was handy. I killed the three windows I had, set up a new query, shot the screen, ran through the process with more screen shots.

Less than 10 minutes.

References

Watch Brent Tune Queries – http://www.brentozar.com/sql/watch-brent-tune-queries/

June 15, 2015
Using the DAC with SSMS
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

While troubleshooting another issue, I needed to connect to SQL Server with the DAC. I couldn’t remember the syntax, so I looked it up quickly and ran into this link: Diagnostic Connection for Database Administrators. I added the ADMIN: to my connection in SSMS for a query window and clicked Connect.

This took entirely too long and then I got this:

That error resulted in a rabbit trail for me to debug this, but it worked out and I’ve learned a few things. The main one is to be sure that I’ve read the documentation and errors correctly.

As I tried a few things, including SQLCMD, I realized I had issues. I searched and found that the DAC gets set to a specific port. I checked the error log to get the number.

With that, I added it to my connection dialog, sure that this would work, but of course, it didn’t.

Eventually I stumbled on the post about errors and realized I should have checked the SQL Browser earlier. I thought about it, but discarded that thought because I could connect in other ways. Mistake. Check networking first, and networking is where the browser comes in. Sure enough, it was stopped.

The properties were set to DISABLED, so I had to change that before I could start it. I assume you can do that, if not, poke around the properties until you find that setting. With that changed, I started the service.

Then I could connect. Here’s the SQLCMD version.

With this working, the first command dialog at the top of this piece worked from an SSMS query window.

SQLNewBlogger

This was longer. What I thought was a quick lookup turned into a troubleshooting exercise that lasted about 20 minutes as I searched, read, experimented, etc. At least I made a bunch of screenshots as I was experimenting, so I had lots of data and didn’t duplicate anything.

The actual writing was only about 10 minutes, most of that looking through screenshots and trying to organize my thoughts.

References
- Enabling the DAC – http://www.mssqltips.com/sqlservertip/1801/enable-sql-server-2008-dedicated-administrator-connection/
- The DAC: Why, When, How – http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
- Errors when Trying to Use DAC Connection in SQL Server – http://www.sqldbpros.com/2013/10/error-when-trying-to-use-dac-connection-in-sql-server/
June 11, 2015
FORMATing Dates
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

FORMAT is a function that was introduced in SQL Server 2012. It is designed to format dates and numbers as date/times. It was added to try and reduce the complexity and cumbersome nature of CONVERT and CAST.

What I didn’t know, which I did like, is that the FORMAT command can be driven by language settings. For example, if I take a specific date, like today, I can reformat the date based on a language code.
```
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'
```
The results of this code are different, based on the cultural settings. Of course, the Chinese settings is really the only good way to show dates without confusion.

This is interesting in that if you can get the regional settings for the client, you can easily return data in a format that makes sense to the user. Of course, we typically don’t want to do too much formatting on the server.

The default language is that of the current session. For dumb uni-lingual people like me, this isn’t an issue, but it might matter for people that speak multiple languages and might move from system to system.

It is interesting in that you can easily format the data from a date with FORMAT. For example, I change orders to match the Chinese format:
```
DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'YYYY/MM/DD');
```
That gets me “2015/07/20”.

I can easily change to other formats, adding in times, or even partial times that might not make sense. For example, the result below named “Hours” has only the date. The “Date and hours” has the date and the hour only.
```
DECLARE @d DATETIME = GETDATE();

SELECT  'Default' ,
        FORMAT(@d, 'd')
UNION
SELECT  'Full Date' ,
        FORMAT(@d, 'YYYY/MM/DD hh:mm:ss.ffff t zzz')
UNION
SELECT  'Date and hours' ,
        FORMAT(@d, 'YYYY/MM/DD hh')
UNION
SELECT  'Date and minutes' ,
        FORMAT(@d, 'YYYY/MM/DD mm')
UNION
SELECT  'Hours' ,
        FORMAT(@d, 'hh');



--      ,FORMAT ( @d, 'yymmdd', 'en-gb' ) AS 'CleanUS'
```
The results are:

Date and hours    2015/11/20 04

Date and minutes 2015/11/20 11

Default           5/20/2015

Full Date         2015/11/20 04:11:28.9600 P -06:00

Hours             04

I think that there are lots of reporting queries where the formatting of dates would be handy and easier with FORMAT than CONVERT. It certainly is more intuitive to read than seeing something like “,110” in code.

I still have the habit of using CAST and CONVERT when I’m changing types, and I’ll continue to do that. Especially as FORMAT is really limited to date types. However when trying to get dates to render in proper formats, it’s a good choice.

SQLNewBlogger

This post came about while I was checking on another issue. I happened to run into the FORMAT command and hadn’t used it much, so I spent a few minutes messing around. This post came out of around 5 minutes of experimentation and 15-20 minutes of writing.

References

FORMAT – https://msdn.microsoft.com/en-us/library/hh213505.aspx

Formatting Types in the .NET Framework – https://msdn.microsoft.com/library/26etazsy.aspx
June 3, 2015
DB_Owner Querying for Database Options

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

What can a user with the db_owner database role do? I assumed anything inside of the database (create/alter objects, assign permissions, back up the database, etc). However someone asked recently about whether someone could read database properties. I’d assume they can, but I needed to check.

I decided to start by creating a new login. I have lots on my test instance, but I went with just building a new one. I used the GUI to add [DBOwnerTest] because it’s quicker. I assigned a password, set a default database, and mapped this user to db_owner in a database.

I then opened a query window and changed the connection:

Now I could easily run a query for properties and see the results:

Note the connection at the bottom of the image above.

Here’s where it’s a little interesting. I disconnected Object Explorer and connected back as DBOwnerTest. I see this:

Looks normal. According to the BOL documentation for permissions, db_owner gets View Any Database as well as control over their own database. I can see the properties of the Sandbox database (where I’m db_owner).

But I can’t see properties of other databases.

I also see my login, but not the couple dozen others I have, other than sa. That’s curious, and perhaps not good. However this isn’t the place to delve into that.

I have the ability to query through databasepropertyex(), which I’ve documented in another post.

Certainly db_owner conveys lots of rights to the user, and certainly the ability to see some of the outside of the database container, such as the options and properties, as well as other databases.

Writing

This was based on a question I saw posted at SQLServerCentral. It took me about 5 minutes to set up a test login and query for information. I had to perform a few searches and try some queries. I spent a few minutes researching databasepropertyex(), which became another post.

All told, this was about a 15 minute post.

You can do this. Join the #SQLNewBlogger group and start documenting your career. You can see all my posts that fall into this area by looking through the SQLNewBlogger tag here.

References

Permissions of Fixed Database Roles – https://technet.microsoft.com/en-us/library/ms189612%28v=sql.90%29.aspx

May 21, 2015