Voice of the DBA

Category: Blog

  • Escaping a Dollar Sign in PoSh–#SQLNewBlogger

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

    I’ve been working more with PowerShell lately and ran into a problem I thought would be easy, but it wasn’t. So I decided to blog what I learned.

    Assigning a String

    I had a string that I wanted to use in a command. In this case, the Start-Service command. Here’s what I wrote:

    Start-Service -Name “SQLAgent$$SQL2016_QA”

    This didn’t work, mainly because of this error.

    2016-11-15 14_32_20-powershell

    PoSh thinks my string is “SQLAgent\”, not “SQLAgent$SQL2016_QA”. That’s strange, at least to me. Let’s try a variable.

    2016-11-15 14_33_06-powershell

    OK, I know I need to escape the dollar sign. I ran a quick Google search, because that’s way quicker than looking through documentation and found a piece on escaping strings. It mentions the backtick (`) as the character to use. Let’s try that.

    2016-11-15 14_35_41-powershell

    It works. Now to try to start the service.

    2016-11-15 14_38_46-cmd - powershell (Admin)

    No error, but it is it running? Yes.

    2016-11-15 14_39_26-cmd - powershell (Admin)

    This is a quick look at string work in PoSh. The more I try to automate work and get things to run themselves, the handier I find PoSh. Since I tend to work with named instances, this was valuable.

    #SQLNewBlogger

    This was one of those items that I spent about 5-10 minutes figuring out and then another 10 minutes shooting screens and duplicating my work. The writeup was easy, and it will help me remember how to do that in the future.

  • Getting Team City working with BitBucket

    As a part of building a CI/CD home lab, I set up TeamCity in the past. I plan on using this for multiple projects, and in fact, I’d built a basic Hello, World C# application as my first build.

    Now it is time to work on something more complex. As a part of my SQL Server Builds project, I decided to host the Ready Roll build here. That means connecting TeamCity to BitBucket.

    I had no idea how this might work, and some searching showed that this needed a plugin prior to v10, but the capability was built-in after that. I’ve been meaning to upgrade, so I did that first. As you can see, I have my TC v10 system, and when I go to create a new project, BitBucket is a first class citizen.

    2016-11-15 11_57_33-Projects — TeamCity

    Once I picked this, the system has me authenticate with Bitbucket (I won’t show this) and then get a key and secret that I paste into the dialog. This is fairly easy to follow, but for security purposes I’ll skip that. Once I’m authenticated, I can see my repos:

    2016-11-15 14_21_23-Create Project From Bitbucket Cloud — TeamCity

    I pick this repo and a project is created. In fact, TeamCity detects that I’ve got a VS solution in the repo, so it sets that as my build config.

    2016-11-15 12_02_58-Build Configuration — TeamCity

    That’s pretty cool, and I’m hooked up. Or am I? Let’s see. I’ll manually run a build.

    2016-11-15 12_43_56-Sqlserverbuilds RR __ Build _ #1 (15 Nov 16 12_36) _ Overview — TeamCity

    It doesn’t work. However, if you read the error, you’ll realize that the ReadyRoll project type doesn’t seem to work. That’s because I’m building on this server, which is separate from my development machine. I need to install the ReadyRoll binaries there.

    A quick download from Redgate, and I install ReadyRoll. Now when I click “run”, I get this:

    2016-11-15 12_44_05-Sqlserverbuilds RR __ Build _ Overview — TeamCity

    Success.

    There’s more to getting setup, but this is a quick look at getting TeamCity hooked up to BitBucket.

    If you want to try ReadyRoll, grab an eval.

  • Two Weeks to Live! 360 and VSLive Orlando

    A short break in speaking, as I haven’t been on stage since Oct 11.

    LSPK42

    Now it’s two weeks to Live! 360 in Orlando and VS Live, both happening in Orlando on Dec 5-9. You can still register, and save $500 with my link.

    This is my first time at this event, though I’ve been to other VS Live events. I really like these conferences because I get the chance to attend non-SQL sessions and broaden my excitement for other parts of the Microsoft platform.

    If you’ve got some training dollars left for 2016, consider coming down and joining me today.

    Register and use LSPK42 to save $500.

  • #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:

    2016-11-11 18_20_37-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

    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.

    2016-11-11 18_21_41-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

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

    2016-11-11 18_25_01-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

    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.

    2016-11-11 18_24_42-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

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

    2016-11-11 18_26_32-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

    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.

    2016-11-11 18_26_50-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

    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