Adding a Check Constraint to a Table–#SQLNewBlogger

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

I had to do this the other day, and while I guessed at the syntax, I wasn’t sure of it, so I thought this would be a good post.

The Check Constraint docs are good, and a quick search got me over there.

I was trying to add a column to a table that has a domain of values. In my case, I was aiming for something like this:

CREATE TABLE MyTable (somekey int, result tinyint)

The result value can be 0-4, and while front end validation can handle this, it’s easy to get lazy and assume they will. It’s easy for someone to forget, or use an application like SSMS or Access to edit data, or more.

Add a constraint. It will help with data quality.

My thought was to do this:

CREATE TABLE MyTable (somekey int,
result tinyint check result <= 4)

That actually is fine, and it’s almost what the docs show. After my column, I include the CHECK keyword, and then I can give the expression. The problem with this code is that I need to parenthesis added, like this:

CREATE TABLE MyTable (somekey int,
result tinyint (check result <= 4)
)

 

SQLNewBlogger

This was a 5 minute post, showing something I thought I knew, had to check, and then corrected. A good case of how I am improving skills.

Write a post like this for something you did in your job.

Posted in Blog | Tagged , , | Leave a comment

In the Air

I’m taking the rest of the week off. I get on a plane today to go spend the week on the East Coast of the US, going to see family. I’m looking forward to the trip, and I’m not worried.

This will be an interesting time, and I’m looking forward to seeing how things go, what might have changed for flying, and how I feel, emotionally, about flying.

Posted in Blog | Tagged | 1 Comment

Daily Coping 20 Oct 2020

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to make a list of things you are looking forward to.

I look forward to life, but a relatively short term list is here:

  1. November Denver SQL Server User Group talk
  2. A volleyball tournament in Denver in March (fingers crossed)
  3. Hawaii – planning on Jan.
  4. Getting on the slopes this winter
  5. Getting another car when kids grow up and get their own jobs next year.
Posted in Blog | Tagged , , | Leave a comment

Azure DevOps–Reconfiguring an Agent

This post looks at how to re-configure a local Azure DevOps agent when you need to change to a new pool or organization.

PAT Expired

I got a note that one of my personal access tokens (PAT) had expired, and I wanted to update the agent. I ran config, but saw this, I need to remove the config first.

However, when I ran “config remove”, which is how you can reconfigure an agent, I got this:

2020-10-12 10_04_34-cmd

In the image, the agent config asks for the PAT to allow it to authenticate to be removed. Since the agent doesn’t store this, and I don’t have it anymore, I can’t enter this. I can remove the agent from the portal, but there’s an easier way.

The Local Config

If you show hidden files, and if you are in tech, you always ought to do this, you will see a few in the agent folder. I name mine Agent, and inside, you can see there are .agent, .credentials, and .credentials_rsaparams files.

2020-10-12 10_17_22-agent

If I remove these three files, the agent doesn’t know where or how to connect. I can then run the config and re-set up the agent.

Posted in Blog | Tagged , | 2 Comments