Voice of the DBA

Tag: T-SQL

#SQLNewBlogger – T-SQL ESCAPE for Wildcards

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

I ran into a really interesting issue recently. I was working with a table and wanted to determine if the first character of a string was a left bracket. However, I discovered searching for a bracket isn’t as simple as I expected.

Setup

Here’s a mock table and some data.

CREATE TABLE MyData
( myid INT IDENTITY(1,1),
mychar VARCHAR(50)
);
GO

INSERT dbo.MyData
(mychar)
VALUES
(‘This is a string’),
(‘”A Quoted String”‘),
(”’Single quoted string”’),
(”’more single quotes”’),
(‘[My bracketed string]’),
(‘[I like brackets]’),
(‘Can I find [this] string?’)
;
GO

I wanted to return only rows 5 and 6 (based on identity) and not the others. My first thought was that I could just make a simple query.

SELECT myid, mychar FROM mydata WHERE mychar LIKE ‘[%’

The results:

That didn’t work. As soon as I got zero rows, I remembered that brackets allow me to wildcard part of a query. I need to escape the bracket, so I decided to try and do that with a repeating character, as we do with quotes.

Still not working. I searched, and there is an escape character of a backslash as well, but that didn’t help.

Now I was really curious. I checked the page for LIKE, and saw there was an ESCAPE option. I never knew this existed. I read the entry, but then when I looked at the samples, I was slightly confused. Why were they using an exclamation point?

I read further and realized I hadn’t paid close attention. The escape character is the one I want to match up before the character I’m escaping. So I need to escape the trigger I’m going to use in front of the bracket.

This is easier to show than explain. Here’s what I first did.

It looks like I’m escaping the bracket, but then why do I need two of them? If I remove one bracket, this doesn’t work (shown here).

What happens is the first left bracket is a trigger for the compiler to evaluate the next bracket as a literal, not a wildcard. If I replace the first bracket and the parameter with the exclamation point, this makes sense.

It’s not often I’ve had to search for brackets, but checking for percent signs has been common, and this is handy. I can’t believe I’ve never had to do this.

#SQLNewBlogger

Once I played with this in my code, I realized this was a neat function. I mocked up the table and it took longer to just type the words around the code than actually figure things out.

This would be a nice short type of post for those of you that want to show you’ve learned a small thing.

References

LIKE – https://msdn.microsoft.com/en-us/library/ms179859.aspx

November 17, 2016
The GUI Helps Build SQL Prompt Snippets

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

SQL Prompt is an amazing tool for writing T-SQL. It helps you to concentrate on your code, without having to go off and examine Books Online or other documentation, or even worry about formatting.

One of the keys to making Prompt efficient for you, in your environment, is to use Snippets. These are short codes that insert a lot of T-SQL, like the SSF snippet. As I’ve worked to make myself more efficient, I’ve also tried to be efficient in my creation of snippets. One way to do that is using the SSMS GUIs.

For example, suppose I want to build a snippet for databases. I can open the Create Database dialog. This has the places where I can specify the name, owner, size, growth, options, etc. You can see the main page below.

Once, I’m happy, I click the “Script” button at the top, as shown here.

This gives me the code in a window, where I can then customize for my template parameters and then paste in the snippet.

The same thing for logins and users. I create and drop a lot of security princpals, so I can right click on logins, as I have here in the Object Explorer.

I’ll get a New Login dialog, and I can add in the items that matter, like defaults, password, types, etc.

When I now click the Script button, I get my code. Note, I’ll cancel out of the dialog, and work with the code shown here.

I can cut and paste this into the Snippet edit box. In the image below, I’ve deleted the old code for cl (“Create SQL Server login”) and put my code in. I change the specifics (username and password) to template parameters by putting a name in between two dollar signs ($). This will give me a quick way to customize the snippet in each case.

Note that I need to replace each instance of “JohnDoe” with “$username$” for this to work well. I did that before I saved the snippet, as well as added a template for the database.

Now when I type “cl”, I get the snippet.

and hitting Tab gives me the code.

That’s a quick and easy to add Snippets for those items you perform often with the GUI in SSMS. You will code quicker and more consistently, and you’ll create new objects without thinking.

Hopefully, you’ll see the value in SQL Prompt and start using snippets to improve your ability to code quickly and take the hassles and guesswork out of cleanly building SQL Code. You can also read a similar piece I wrote on the Redgate blog.

Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

You can see a complete list of SQL Prompt tips at Redgate.

November 8, 2016
Using TRY..CATCH in SQL Prompt

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

I learned something new recently about SQL Prompt. I was working on documenting and experimenting with snippets and found a quicker way to write code.

One of the things I think is great is to use the TRY..CATCH structure. I don’t do it enough, but I want to do this more, and make it smooth. SQL Prompt includes a tc snippet, which gives me this code when I type t-c-TAB.

You’ll notice that my cursor is in the CATCH block. Why? Well, if you pause after t-c, you’ll see the snippet and code (unless you’ve turned off the window). Notice the $CURSOR$.

This means the cursor appears there. Not too handy. However, also notice the $SELECTEDTEXT$ snippet. This is handy.

Let’s change our code and include an update statement. For example, suppose as part of a procedure, I’m writing this code:

I’ve got a procedure, and I’ve forgotten to include TRY..CATCH. Certainly I can surround the highlighted code with the structure, but that’s cumbersome, even using Prompt’s intellisense.

There’s a better way.

Notice the little SQL Prompt icon in the side bar. Prompt is active, and if I click CTRL, I’ll get this:

I’ve activated SQL Prompt in the context of my highlighted text. Now I can type t-c and see the snippet.

If I hit TAB, I get this:

Pretty cool. My update is surrounded by the snippet code, and placed where the $SELECTEDTEXT$ placeholder was used.

Use this. Write code quicker and make it better.

Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

You can see a complete list of SQL Prompt tips at Redgate.

October 24, 2016
Creating a Logon Trigger–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Suppose you want to audit logins for your SQL Server instance. There are multiple ways to do this, but Logon Triggers have a few advantages. First, they get data into a table that most of us are familiar with, SQL Server. Second, they guarantee that the event is captured on the instance if the trigger is enabled.

There are plenty of other uses for these triggers, but beware that you can cause problems if your code doesn’t execute flawlessly.

I’ll show you how to create a basic logon trigger here. Note, these are server level items, and you’ll need to be able to create the trigger in the master database.

The structure of this code is similar to other triggers. We’ll use the CREATE TRIGGER DDL. Where this differs from DML triggers is that we use the ON ALL SERVER command. For auditing, I tend to set this as an EXECUTE AS ‘sa’, but you may choose a different type of account.

Here’s my basic code:
```
CREATE TRIGGER LogonTrigger
--ALTER TRIGGER LogonTrigger
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    INSERT DBAAdmin.dbo.LoginAuditing
    SELECT SYSDATETIMEOFFSET(),
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        APP_NAME();
END;
GO
Note in this code  I’m specifying a specific table to store data in. This has to exist.
```
I also use the FOR LOGON event. You can scope triggers for other events, at the server or database levels, and read more about DDL triggers in BOL.

Once a user attempts to logon, the trigger fires and a simple insert takes place. If there are errors in inserting this data, the user may not be able to logon.

There is a lot to write about logon triggers, but for this short piece, I’m just showing hot to get started. Please, please, please, be careful with these and read the documentation carefully. Be sure you understand how they work and how to disable them. If you implement one, test it extensively.

SQLNewBlogger

I had a small issue building a logon trigger, and thought I’d get a few posts written on the topic. This was 5 minutes work since I had the code and just wanted to describe things, but I’ve got a few more posts sketched on this topics as I’ve learned more.

Learning something, solving a problem, writing about it. This is a good way to show someone you are learning about a topic and developing some skill.
October 20, 2016