Voice of the DBA

Tag: SQLNewBlogger

Azure SQL Database – Adding a Login
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.

I am trying to slowly do a little work in the Azure SQL Database world, building some skills that I can use, and I can teach you a bit more about. One of the things I needed to do lately was add a new login to my system, which isn’t intuitive in the portal.

I did go to the Managing Databases and Logins in Azure SQL Database to get some help, but it wasn’t completely clear how to do this.

I setup a database recently for a test project, but I wanted a different user for this database than others I have on this particular Azure SQL Server. I am using the new (2015) portal, and there didn’t seem to be a good way to add a new login, so I connected to do this in SQL. The reference below showed me the standard “CREATE LOGIN” statement I’d use with an on-premise instance, so I tried to run that.

CREATE LOGIN sscqa WITH PASSWORD = ‘mypwd‘; GO

I got this:

OK, no issues. I changed the connection to the use the master database. I used the options after clicking the “Change Connection” button and put in master.

I connected and ran the code to get this:

I needed to not only change my database user connection, but also use the admin user I have. In this case, it’s the jt user I inherited from Jamie Thomson as part of the Hosted AdventureWorks project he started.

Once I did that, the login was created without an issue. Now I need to link it to a user.

SQLNewBlogger

This took about 5 minutes to write, and 5 minute to reshoot some screen shots. The rest was really the 20 minutes I spent mucking around, researching, and reading how to do this. Hopefully this is quicker for me, and you, if you have this need.

Anyone could write this, and I’d encourage you to write your own stories about working with an Azure database if you want to learn about the topic.

Reference
- Managing Databases and Logins in Azure SQL Database
July 13, 2015
Get a comma separated list
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.

I was working on a test of sorts and wanted to return multiple values as the output, but as a single variable. In other words, I couldn’t return a result set, I needed to return a string.

I knew this was easy, and decided this would make a nice simple blog. Here we go.

Let’s start with a simple table. Here’s one that has a few rows in it.

CREATE TABLE MyTest
( id int);
GO
INSERT mytest values (1), (2), (3);
go

I want to return the values “1, 2, 3” as a string, in any order. Here’s how it works:

DECLARE @i VARCHAR(MAX);
SELECT @i = COALESCE(@i + ‘, ‘,”) + CAST( Id AS VARCHAR)
FROM MyTest;
SELECT @i;

The COALESCE is important as the first time this runs, we have a NULL for the variable. In this case, we return an empty string. This is almost like the inverse of the operation that ends recursion. We add in the first row, and we end up with ‘1’ as the string.

Note: it could be 2 or 3 in the string as I don’t have an ORDER BY. DO NOT depend on the order of insertion in the table. If you care about ordering, always include an ORDER BY.

The next execution has a blank string (NOT NULL), so that is returned. In this case, we have ‘1’ + ‘, ‘ for the first part. The second part adds in the next row.

This continues, and I get a nice set of output.

SQLNewBlogger

This one was short. It took me almost as much time to write the code and find a reference as it did to write the post. Five minutes.

References

This is basic T-SQL, but here’s another look at this.
- http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/
July 8, 2015
Defining Foreign Keys at Table Create Time
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

How many of you can define a foreign key when you create the table? Probably a few of you, but I bet most of you are like me and don’t necessarily know the syntax. I have often defined these later, which is fine. As long as they get defined.

However I knew I needed a specific key when I was creating a table and couldn’t remember the syntax, so I had to search and learn how. I used Google and saw a few links from MSDN, but those tend to be overly documentation heavy. One of the links was to SQL Authority, run by Pinal Dave. He does a great job of simplifying things (and he’s a friend), so I followed that link. I could see the syntax and tested it in minutes.

It’s easy to create a Primary Key in CREATE TABLE, and I wrote about that for one of my first SQLNewBlogger posts. The Foreign Key is similar, but not quite as simple.

Imagine that I have a parent table:
```
CREATE TABLE orders
  (
    orderid INT IDENTITY(1, 1) CONSTRAINT Orders_PK PRIMARY KEY ( orderid )
  , orderdate DATETIME
  , complete BIT
  );
GO
```
I now want to create a child table and link the orderid in the child to the parent. I can do it like this:
```
CREATE TABLE OrderLines
  (
    orderlineid INT
      IDENTITY(1, 1) CONSTRAINT OrderLines_PK PRIMARY KEY ( orderlineid )
  , orderid INT
      CONSTRAINT orderlines_order_fk FOREIGN KEY REFERENCES orders ( orderid )
  , qty INT
  );
GO
```
Note that I define a constraint inline, just as I did for the parent. However I note this one is an FK and it "references" another table. In this case, I list the Orders table and put the columns in parenthesis.

Quick, easy, build your FKs inline when you know about them in advance.

SQLNewBlogger

While trying to remember how to create an FK, I ran a search and chose the reference below to start. A matter of seconds had me seeing the syntax and writing the code.

Putting this together was less than ten minutes.

References

Creating Primary Key and Foreign Key Constraints – http://blog.sqlauthority.com/2008/09/08/sql-server-%E2%80%93-2008-creating-primary-key-foreign-key-and-default-constraint/
July 2, 2015
Updating Extended Properties on a Table
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.

I wrote recently about adding an extended property to a table. As part of what I was testing, I also needed to update properties, changing values back and forth. It’s fairly easy to do so, and I wanted to document this for my own reference.

The sp_updateexteendedproperty is analogous to the sp_addextendedproperty procedure. Here’s the code I used to change my property value on the table from the last post.
```
EXEC sp_updateextendedproperty 
@name = N'PKException', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;
```
As you can see, I pass in the same parameters. The procedure then changes the parameter in the table. A quick check in SSMS will show you the values changed. In my case, I was changing the value from 0 to 1 to test a query.

The property does need to exist. If I execute this:
```
EXEC sp_updateextendedproperty 
@name = N'PKcheck', 
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = 'SalesTax3'
;
```
I get an error thrown from the database engine.

Msg 15217, Level 16, State 2, Procedure sp_updateextendedproperty, Line 112

Property cannot be updated or deleted. Property ‘PKcheck’ does not exist for ‘dbo.SalesTax3’.

This is a good way to handle this, as a TRY..CATCH can trap the error and do an insert instead of something else.

SQLNewBlogger

This was another side post from my testing of a solution. As I used this code to solve a problem, I kept a copy and made a few screenshots. This one was about 10 minutes in total.

References

sp_updateextendedproperty – https://msdn.microsoft.com/en-us/library/ms186885.aspx

sys.extendedproperties – https://msdn.microsoft.com/en-us/library/ms177541(v=sql.90).aspx
July 1, 2015