Voice of the DBA

Tag: SQLNewBlogger

Enabling Database Containment for an Instance – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

I wanted to test a contained database feature the other day and ran this:
```
ALTER DATABASE [sandbox2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
```
However, this didn’t work. I ended up with an error:

Msg 12824, Level 16, State 1, Line 3

The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.

The issue is that the server instance needs to have contained authentication enabled in order to pass any authentication requests to the database
```
EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
```
Now I can run the code again to alter the database for containment.

SQLNewBlogger

The issue was obvious to me since I’d dealt with it in the past, but this is something you could solve and write up in 10-15 minutes.
October 4, 2017
Getting a New R Package–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was trying to test an R script the other day and got this:
```
> library(randomForest)
Error in library(randomForest) : 
  there is no package called ‘randomForest’
```
A common error if you have just a base R installation. In my case, I’d installed R Studio, but nothing else. I knew that many packages are installed at CRAN, but I’d never gotten one. So I did.

The first step was a quick google for syntax. In this case, r-bloggers.com had a nice short piece. The install.packages() function is used.

I decided to just try it. In may case, I was happy to use the defaults for now. I ran:
```
> install.packages("randomForest")
Installing package into ‘E:/Documents/R/win-library/3.3’
(as ‘lib’ is unspecified)
trying URL 'https://mran.revolutionanalytics.com/snapshot/2016-11-01/bin/windows/contrib/3.3/randomForest_4.6-12.zip'
Content type 'application/zip' length 177208 bytes (173 KB)
downloaded 173 KB

package ‘randomForest’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
	C:\Users\Steve\AppData\Local\Temp\RtmpUl3h6d\downloaded_packages
```
After that, my library() function ran fine.
September 27, 2017
Bulk Inserting Build Data–#SQLNewBlogger

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

One of the common tasks that many DBAs need to do is insert data into a database. Often this comes from various sources, but a CSV (comma separate value) format is common. One could use the data import wizard, but that seems to be very flaky with CSVs, so I’ll show a quick way to use the BULK INSERT command.

This command is a way to read files and load them into a table, similar to how bcp works. However, this is a T-SQL command, and can be included inside your database.

The basic format is

BULK INSERT <table>

FROM <source>

WITH <options>

For most CSV imports, this means we need to pick a table, in my case, the BuildStaging table, and a source file. My statement looks like this:

BULK INSERT dbo.BuildStaging
FROM ‘e:\Documents\ssc\BuildList_SQLServer2014.csv’

I also need some options. The basics for a CSV are:

WITH
( FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
);

There could be other items you want to enable, and there is quite a list. In my case, my file looks like this:

I have a header row, so let’s get rid of that by adding a FIRSTROW = 2 option.

Now when I run my command, the data is inserted.

From here, I need to work with the data and clean it futher for insert into other tables.

SQLNewBlogger

This was a quick task I needed to accomplish. I knew most of the syntax, but had to double check the option names, and ended up taking about 2 minutes to import the data and 10-15 to write this post.

And, I’ll likely remember how to do this import after spending time writing about it.

August 31, 2017
Adding a New Default to a Column
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

This is a fairly simple process, but I bet more than a few people don’t know how to do it. I had to double check some syntax the other day, and I thought this is a perfect SQLNewBlogger post, based on something I had to (re)learn.

If I have a table, there are existing columns and I want to add a constraint to one, I need to alter it. To do that, I’ll use the ALTER TABLE x ADD CONSTRAINT syntax.

One of the common use cases is to add a default date to a date column. For example, I have a Blogs table and want to ensure the CreatedDate column is always populated. I’d do that with
```
ALTER TABLE dbo.Blogs
ADD CONSTRAINT df_SysUTCDate DEFAULT SYSDATETIME() FOR createdate;
```
In this case, I use ADD CONSTRAINT and then name the constraint. Using specific names is always good since this means I can be sure that I have matches between development, QA, and production.

I then use the DEFAULT keyword and follow this with a function name. I then use the FOR and the column name. This means I’ve added a default constraint to the CreateDate column and if no value is included in an insert statement, sysdatetime() used.

This is a basic idea, but one that few developers think about or include in their design. Learn to use defaults and add them when you start building or adding columns.
August 21, 2017