Voice of the DBA

Tag: SQLNewBlogger

Cleaning up bad dates–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I got some data recently from an online service, MapMyRun.com, where I track my workout data. I’ve been doing this for years, but with some of the instability and security issues with services, I decided I need to periodically grab a copy of my data and load it.

This post describes an issue with cleaning up the date data.

When I tried the load from SSMS (Tasks->Load Flat File), I got errors if I attempted to insert into a date field. My goal is to use the pattern of loading to a staging table and then merging data into my main table, so I decided to just load into a staging table. When I did this, here is what I saw.

For a human, the dates make perfect sense. For a computer, however, translating this to a date via an implicit conversion doesn’t work well. No problem, I can fix this. I’ll replace the date:
```
SELECT top 10
 CAST( REPLACE(swh.Date_Submitted, 'Aug.', 'Aug') AS DATE) AS SubmitDate
 , *
FROM dbo.staging_workout_history AS swh
```
This works great.

However, if I scroll through the entire file, I find issues with other dates. Once I remote the top, I get this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Hmmm, there are other issues. I could remove my CAST and scroll through, but there’s an easier way. I’ll use TRY_CAST() instead. If I restructure my query, I can run it to completion.

Now I can scroll through. I could also add a test for getting NULL from TRY_CAST to find the problematic dates. Here’s where I see more problems.

It seems that whoever exported data, from whatever system, decided periods in months make sense. I can amend the REPLACE in this way, which should fix things.
```
SELECT
 TRY_CAST( REPLACE(swh.Date_Submitted, '.', '') AS DATE) AS IsItADate
 , *
 FROM dbo.staging_workout_history AS swh
 
```
Now I see this seems to work

One More Problem

I’m glad I kept scrolling through a full year. That’s because I saw this:

Who thinks Sept is the abbreviation for September? Technically it is, and I see April, June, and July spelled out, so the inconsistency in this extract is bizarre to me. Perhaps there is a database that formats English month names like this and deals with conversions, but this seems like sloppy programming to include “April”, “Jan.”, and then “Sept.” in your data set.

In any case, I can add another REPLACE() to my code.
```
SELECT
 TRY_CAST( REPLACE( 
                   REPLACE(swh.Date_Submitted, '.', '')
                   , 'Sept', 'Sep') AS DATE) AS IsItADate
 , *
 FROM dbo.staging_workout_history AS swh
 WHERE  TRY_CAST( REPLACE( 
                   REPLACE(swh.Date_Submitted, '.', '')
                   , 'Sept', 'Sep') AS DATE)  IS null
```
This converts all the dates in my column to the expected value. Now I need to do the same thing for other date columns, change TRY_CAST() to CAST() and I can import data.
September 23, 2020
Git Tricks–Getting a New Remote Branch–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is probably more for me than anyone, but as I’ve been working in more team demos, I find I need to up my git skills. In this case, a fellow team member created a new branch, and I needed to get it on my local machine.

I know many git clients will make this easy, but I always like to see what happens at the CLI. This keeps my skills fresh, and I can see all the git output, some of which might be hidden in a client.

In this case, the team member had created a branch on Github. I’ll demo this by creating my own branch as feature/demotest. I see the branch online.

However, I don’t see it locally.

If I know the name, I get fetch this, or pull it, from the remote. In this case, either of these code items will work.
```
git pull origin feature/demotest
```
or
```
git fetch origin feature/demotest
```
Once I do this, I can checkout the branch, which will match the remote with this code:
```
git checkout feature/demotest
```
Now it’s a branch that is tracking the remote with my local copy.

SQLNewBlogger

This was a quick Google search to figure out how to do this, but since I’ve had to search a couple times in the last month, I decided to write this and cement the knowledge in my head. At least, I hope it does.

This took about 5 minutes to write and demo. A quick thing, but a good way to show some learning and knowledge on your blog.
September 16, 2020
FILESTREAM v FileTable –#SQLNewBlogger

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

I was really interested in the FILESTREAM technology when it came out. Unfortunately, the access from only Win32 APIs made this a pain for many people. I get why this is needed, but without having some tooling to make it easy to add/edit/delete blobs.

In SQL Server 2012, we got FileTable, which simplified development and allowed everyone to easily get data in and out of SQL Server.

This post looks at a few differences between the technologies.

FILESTREAM is essentially a folder surfaced as a column in SQL Server. You can create a filegroup to hold FILESTREAM files, and these are paths. The path is managed by SQL Server, and when you designate a column as containing FILESTREAM data, a new folder is created.

None of that matters, as you should NEVER go to these folders, but this is also how some other things in SQL Server (like In-Memory OLTP) also work.

Access to this data is with a Win32 API, and it’s semi-complex. Not hard, and really you can use the sample code (with some additional error handling) to do the work you need. On the application side, you need to do some stream handling and manipulation, but that’s really not difficult. It can be cumbersome, but it’s easy.

FileTable was an additional layer that gives you SMB, or Windows Explorer, access to the data in the FILESTREAM column. Except, you can’t make a FileTable column, you create a FileTable, which has some restrictions, limitations, and required columns. You can add some things to the table, but really you want your FileTable to be a vertical partition of your regular OLTP data. Separate out the blob stuff from other items in your entity.

Both save your data in database backups, both are more efficient for semi-large blobs moving in and out of SQL Server.

Neither works in the PaaS cloud, though they could. I’d hope that FileTable especially would be added, with some enhancements to smooth the way a FileTable is structured, ubt I think with the focus on database level technologies (contained in a db), this technology is likely never getting any enhancement.

September 9, 2020
Don’t Forget Unique with FILESTREAM–#SQLNewBlogger

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

While testing FILSTREAM with SQL Clone the other day, I kept getting an error while trying to create a table. I’d click Execute and see this:

My mind kept focusing on the ROWGUIDCOL part, and not thinking unique. It’s been a few years since I worked with FILESTREAM as it’s not an Azure feature and things have been going that way for me.

In any case, after running this a few times, and then checking an old demo, I realized that I had forgotten UNIQUE as an attribute for the column. Once I added that, it worked.

The docs for CREATE TABLE shows this that unique is listed as a constraint property, but under the FILESTREAM section, is does say this about the ROWGUID column: “This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint.”

Don’t forget this, but if you do, read the error message,

SQLNewBlogger

This post took me about as long to write as it did to realize I was being silly and forgetting to read. Overall, this was about 10 minutes to compile, take the screenshots, and get the references.

When you write, look for places you’ve learned something, and then use those as ideas for blogs.

August 24, 2020