SQL Solutions – Part 2 bookmarks

Bookmarks combined with SQL solutions can start to give some serious power to a script library. In the previous blog post

I covered creating a script library, he’s a copy of one that i created / use daily. SO i can quickly access script associated with Daily check , extended events or blocking etc.

SQLScriptLibrary

I have a lot of scripts and can find what I am looking for quickly this way, but what happens when I have a bunch of script with lots and lots of smaller scripts, is there a better way of accessing / indexing to find what I want.

Yes.

Using bookmarks

This windows off by default, but can be switched on through menu View(1) Bookmark Window(2)

Boomarks

The bookmark window allows you to bookmark lines of code and quickly jump to that point in the code.Below it shows you what the buttons do

(1) Creates a bookmark on the current line of code in the query window

(2) Creates a folder so you can create different topic areas as shown below

(3) & (4) move between bookmarks , this jumps the cursor to that position in the scripts

(5) & (6) moves the cursor to the next bookmark in the current folder

(7) double clicking on the individual bookmark jumps to that bit of code

Bookmarks_adhoc

So I can write English statements such as “When was SQL installed” and it would jump to the script and the line in the script of the statement that shows me that

The previous post can be located here Reusable Script Library

Posted in Configuration, Management Studio, Productivity, Tips & Tricks | Leave a comment

Horizontal Whitespace & Vertical Select

Another demo from the June SQL Pass / SSUG presentation. Some of the lesser known features of management studio is the ability to vertically select data (see below) and also delete white space the following is an example of using these.

Scenario

You have been given a set of data product number / case number which is a string value that you need to run against a query.  You could copy these into Excel concatenate and add in (‘)   quotes before and after (‘) and add in commas (,) so you get a where clause like

SELECT * FROM table WHERE value in

(

'A1001'

,'A1002'

)

Code to create some example data to use

SELECT top 50

CASE WHEN ROW_NUMBER() OVER(ORDER BY ProductNumber) % 3=1

THEN SPACE(ABS(CHECKSUM(NEWID()))%(1-10+1))+[ProductNumber]

ELSE [ProductNumber] END AS [Product number Example]

FROM [AdventureWorks2014].[Production].[Product]

This generates the following result set,  as can be shown every 3rd row (1), (2) has a random space value, to create a replication of varying product values

Productcode

If we paste this into out query,   you  can see how the formatting is with the product number

HorizontalExample

So the product number ( every 3rd row contains a random space) to replicate product data being different lengths

(coolest feature in SQL Management studio alert warning, I really love this and use it a lot)

If you press the ALT key and drag your mouse cursor down vertically in SSMS

You can do a vertical select notice the thin blue line (1)

Vertical

You can now press TAB and all the code would move right, if you type in ,’ you get

all the code wrapped in quotes and comma’s added, so if you had 300 lines , you would have got 300 comma’s added with just 3 key presses.

If you tab across on the first row (1) and do the exact same thing you have quickly added in quotes around the string values

3steps

Next to remove the white space select  Edit from the menu (1) and Advanced (2) and Delete Horizontal White Space

DeleteWhitespace

You then get all the white space removed ! Just like that

Tidy

Posted in Management Studio, Productivity, Tips & Tricks | Leave a comment

SQL Solutions – Reusable script Library

As part of the SQL Pass Manchester / Leeds presentations I did, I showed how you can create your own Script Library to help code re-use within management studio. This is step by step guide to setting up a simple reusable script library in management studio.

First things first you need to in Management Studio

Select View  from the menu(1) and Solution Explorer (2)

Solutions

To create a set of solutions to hold a script library select File (1) New (2) and Project(3)

Projects

Ensure that SQL Server Scripts (1) is selected and give a Name for the project (2) and a file location (3)

Ensure that the Create directory for solution is ticked ( if required) (4)

Give the solution a name (5)

Click OK (6)

Script_Library

This creates the solution that can be seen in the solution explorer

Solution_explorer

Right click the solution and select add / New project

New_Solutions

Give the project a name in this case I have called it Daily Scripts (1) leave the location as is (2) and click OK(3)

DailyScripts

You can repeat these steps to create a set of folder as shown below.

Monthly_scripts

You can keep adding in projects and it starts to become a useful repository for scripts ( screen shot i have for my projects), which if you open the top level project ( in this case SQLScriptlibrary it will contain all sub projects)

SQLScriptLibrary

Now if you name the script appropriately you can order then and you have a quick

easy to access code / script repository. Note you can use letters to add in related script in an order.

Security

These are now quite useful to hold scripts that you use or want use again. There are a few advantages of these projects you, as they will remember the last connections you connect to. You can add to source control, pull down and share within teams so you all have access.

These also become useful with bookmarks which is covered in the next blog post

 

 

 

 

 

 

 

 

Posted in Configuration, Management Studio, Productivity, Tips & Tricks | 1 Comment

One for the Man united fans (or not)

Always being a security conscious professional I lock my machine all the time, Yep I haven’t in the past ( and learn’t from that) as no one wants to email the infrastructure team proclaim you love them or ask one of them out for a drink and bcc in the rest of the team.

In lots of places it seems fair game if you don’t lock your machine, we’ll one thing you can do to seek revenge, especially in environments that people have access to other’s machines through an UNC path is to have a play with the SQL splash screen

This might be familiar what appears when you start Management Studio ( this one obviously is sql2014 ( other versions are available)

ssms5.splash

This is basically a bmp file called ssms.splash.bmp which lives somewhere in the SQL install path such as

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\ssms.splash.bmp

Which you can update for people who annoy you 😉

ssms2.splash.jpg

Or this one, if you really wanted you could change it daily and watch people panic, or switch it back over when they report it to the helpdesk

ssms4.splash

And the one for the united fans out there ( or really the Liverpool ones) which I have as my current splash screen

ssms1.splash

or you could even go retro!

sql2000

 

 

 

Posted in Bit of fun, Tips & Tricks | Leave a comment

DBA / Developer Tool Set

As part of a SQL Manchester pass presentation I did called “The Proactive Database Professional” I showed  a few things that could make you more productive. One was a customisable tools set, which  I have detailed below

The first thing needed is a set of scripts, ( downloadable links at bottom of the blog from One Drive / Google Drive)  these consist of the following procedures.

The names are quite descriptive in what they do, but as shown shortly you can add much more to these to make them useful tools

DBA_tools_list

The code in the scripts are basically DMV( Dynamic Management views) and SQL that I have acquired/written over the years while being a developer / DBA / Data Architect.

One that should be on every production system is Adam Mechanics sp_WhoIsActive  procedure, which is also part of the tools.

These consist of routines that can help you monitor / check for issues or can get you out of a fix. I’ll list / show some of them in future blogs in more detail. But putting them on a development system and having a play will show you more yourself.

For each Procedure ( these are created as procedures to make them easier to use / remember – I do have a standalone version for system that you can’t roll out procedures too) I add it in the format as sp_DBA_[Name of the procedure] the method in the madness is that you can then create an index of these useful procedures.

This is done by creating 3 extended properties as below

Extended_Events

  1. Version This allows you to easily view versions on different servers and keep versions upto date
  2. Description – Says what it is on the tin, description of what the procedure does and how it can be used
  3. Parameters  – Parameters that the procedure takes

Then using the code from sp_DBA_tools procedure it displays the tools available with description , version and parameters is can take

Additionally I make this a short cut key ( Tools / Option / Environment / Keyboard)  – will need to open a new query window before it works

Short_cutkey

You will get something like the following by executing sp_DBA_tools

sp_DBA_tools

Rolling these out is easy you can roll out individually, Obviously we don’t want to do that as were being proactive database professionals right, so we can create a master roll out script. We can use SQLCMD – (Query \ SQLCmdmode in SSMS) below we specify file location (1), the best thing with this its on your local machine. So you can change to Server A or Server B and the script will still work as its looking at the same location

We then we specify the name of the SQL file which is the name of the procedure (2) and when executing it will run all the procedure in at a single time to that server. So literally its a 30 second job to roll these out onto a new server

Rollout_script

I also have a set of PowerShell scripts that allow these to be pushed out to many servers at a single time. This will be shown in the next blog post.

 

Links to zip file

GoogleDriveOne Drive

Posted in Management Studio, Productivity, Tips & Tricks, Uncategorized | Leave a comment

Excel formatting SQL results

Sometimes its the little things that get annoying like requesting some information from a DBA or a system admin at a remote site where you can’t run scripts but need someone to do it for you. Quick example you want to know what servers they have across their estate and ask them to run basically SELECT @@Servername  and send the result back in excel.

SELECT @@VERSION AS [SQL Server and OS Version Info]

So you run across some servers and get the nicely formatted results

Version A

Copy and Paste to excel

Version B

and the Format is all over the shop, so a handy bit of code to have is the following. You can use it for stripping out carriage returns / line feed and tab. Just replace the @@version with whatever you want to remove the control characters from

</pre>
SELECT REPLACE(REPLACE(REPLACE(CAST(@@VERSION AS VARCHAR(MAX)), CHAR(13),''),CHAR(10),''),CHAR(9),'')   AS [SQL Server and OS Version Info]
<pre>
Control character Value
Tab char(9)
Line feed char(10)
Carriage return char(13)

and the result, nicely formatted. Simples

Version C

Posted in Productivity, TSQL | Leave a comment

Sysadmin Shenanigans & Lying Error messages

One of the databases that is for DBA use was in restricted user mode , this allows only certain users access to the database, basically its sysadmin users , db_owner or db_creater access to the database.

I wanted to create a table to hold some capacity data / so current DB size and Log size etc ( script in another post to come)

I got the following error message. So started to problem solve.

Msg 916, Level 14, State 1, Procedure add_operator_check, Line 123

The server principal “S-1-9-3-1619820567-1096921846-2423696570-2687005632.” is not able to access the database “Database Named Changed to protect the innocent” under the current security context.

My initial thought was its the restricted user was causing issues , as this was a production server I tried to replicate the issue on my test server without success. Tried changing the ownership of the database was user to SA and visa versa with little success.

I double checked my permissions with my favorite check quick check on production

SELECT IS_SRVROLEMEMBER('sysadmin')

Which returns 1 if I am sysadmin or 0 if I am not. Turns out I was.  I then actually read the message in a bit more detail it referenced a procedure add_operator_check

SELECT * FROM sysobjects WHERE name  like '%add_operator_check%'

This returned 0 rows. Again the error message had got me, first time I had not read it , 2nd time I had taken it literally. I checked a couple more things, ruled out it was something to do with my account, getting someone else to try.

Next steps I am sysadmin and I can’t create a table what are the possible reasons, a database trigger I thought, so I checked that.


SELECT * FROM [sys].[triggers]

Got you. Yep It had returned several rows but the one that I was interested in

Database Trigger

But the next weird thing this trigger had actually been in place over a year and hadn’t been modified. But the last table created was in the current month. Curiouser and curiouser ( Can’t believe I am quoting Alice in Wonderland and Yes I did Google that to find out)

looking at the Trigger


CREATE TRIGGER [add_operator_check]
ON DATABASE
WITH EXECUTE AS 'mdw_check_operator_admin'
FOR CREATE_TABLE

The penny starts to drop, the CREATE TABLE statement is not running as myself, sysadmin but the mdw_check_operator_admin which is to do with the management datawarehouse, which wasn’t in use. Further investigation showed this user actually doesn’t have a login to the server hence the actual message so it did actually make sense even though it wasn’t a procedure!!.

The server principal “S-1-9-3-1619820567-1096921846-2423696570-2687005632.” is not able to access the database

 

Posted in TSQL | Tagged , , | Leave a comment