Voice of the DBA

Category: Blog

  • OT: The Next STEM Generation

    Now, it’s not a Star Trek reference. I’m actually breaking a bit from life this weekend to try and move my son forward in life. We’ve arranged a tour of the University of Colorado – Boulder campus, specifically with an eye on the aeronautical engineering program. He’s interested in that area, and we are trying to give him options for university next year.

    I’ve hoped my kids would enjoy computers, and they do, but not like I enjoy them. They aren’t interested in building things with computers. They just want computers to do their bidding.

    My oldest wasn’t very interested in computers at all, though I was able to teach him some SQL for his college GIS class the last year. We worked on understanding how to query data, but he never really understood the concepts well and needed help regularly. However he did learn enough to be able to alter my queries to meet the changing needs of different data sets.

    My middle son is a math/science whiz, and spent a few weeks one summer learning programming at a camp. He built a few small applications, but beyond that he wasn’t interested in learning more. While he like chemistry and aeronautics, programming seems silly to him.

    My daughter is the most savvy in moving data around, building web pages for visualizations and working across platforms. She seamlessly will use Windows, OSX, iOS, and Android on any given day across her four devices without blinking. She moves and uses data all the time, but hasn’t ever wanted to do more. She has done well in math and science, and is on pace to get to calculus her senior year of high school.

    I do want to encourage kids to try STEM subjects, and feel comfortable. My daughter certainly has felt pressure, and has female friends that feel the same pressure, to not work in STEM fields. I’d like to eliminate that, but more importantly, I want everyone to try the topics and really get a chance to experiment.

    If you don’t like STEM topics, that’s fine. Find another area you do enjoy more, but at least give STEM a try.

  • Maturing Your Database Development Process–Version Control

    At Redgate Software, we have a progression of the stages of a database development pipeline. These are the various ways in which you can better engineer your database development to ensure smoother releases to production, with less issues. There are five stages:

    • Manual (S0)
    • Source Control (S1)
    • Continuous Integration (S2)
    • Release Management (S3)
    • Monitoring (S4)

    As I travel around, speaking on these topics, I find many people working in development stops that are really at the S0 level.

    For databases, that is. For their .NET or Java or PHP software, quite a few are at S2, and maybe working towards S3 in many projects.

    That’s a disconnect, and it’s one that we’d like to see changed at Redgate. Certainly we can help and we’d like you to use our products, but more, we want to see better development all around.

    We’re Trying to Help New York City

    In a few weeks, on August 27, 2015, I’ll be in New York to help teach our Database Source Control workshop. Ike Ellis (Crafting Bytes) is teaching the class, and I’ll be there to support him and run the labs. This is a course that Grant Fritchey, myself, and a few others at Redgate Software have built.

    This is the chance to learn how your organization can implement version control for your database, just as most of your developers probably already have for the other software you write.

    We’ll cover setting up Source Control, deploying changes from versions, branching, merging, and more. This is a great hands-on introduction to stabilizing your database development. We’ll provide a VM with labs that you will actually complete.

    We’ve put this first step to building a DLM pipeline on sale for $100. If you’re close to NYC, consider taking a day off and joining us at the Microsoft office in Manhattan for a little database education.

    If you can’t make this workshop, look through our schedule and join us somewhere at a future time.

  • Loading XML Data–CONVERT Option 2

    I was playing around with some XML lately, and had to load a file that looked like this::

    2015-07-22 14_40_59-mathis.xml - Notepad

    I ran a simple query, one that used the OPENROWSET and a CONVERT to load the data. 

    WITH XmlFile (Contents) AS (
SELECT CONVERT (XML, BulkColumn) 
FROM OPENROWSET (BULK 'C:\mathis.xml', SINGLE_BLOB) AS XmlData
)
SELECT *
FROM   XmlFile
GO

    However, that didn’t work. I received this message in SSMS.

    Msg 6359, Level 16, State 1, Line 1

    Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.

    Hmmm. That seems to make sense. Let’s add an option to CONVERT of 2. I’ve assumed the last parameter is the one mentioned, as with date conversions, and added that. I can hover with SQL Prompt and see that.

    2015-07-22 14_43_13-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (71))_ - Microsoft SQL Server

    Let’s change the code:

    WITH XmlFile (Contents) AS (
SELECT CONVERT (XML, BulkColumn,2) 
FROM OPENROWSET (BULK 'C:\mathis.xml', SINGLE_BLOB) AS XmlData
)
SELECT *
FROM   XmlFile
GO

    That works.

    Why?

    If you go to the BOL page for CAST and CONVERT, you will find an XML styles section. There are possible values of 0 (default), 1, 2, and 3. In this case, the 2 enables an internal DTD processing, which basically uses a default document of sorts for parsing the XML. No external DTD is needed and this is treated as a standalone document.

    I am not an XML expert, but I’m guessing here that I’ve included a document that doesn’t conform to some specification and the additional style parameter allows SQL Server to ignore some of what’s there.

    If anyone knows more, I would like to better understand how this works.

  • Connecting, Saving, Discarding, Branching, and Merging in the Big Apple

    We’ve got another DLM workshop on Database Source Control taking place in New York City on August 27. It’s in Manhattan, at the Microsoft office all day, with lots of hands on labs to help you learn a number of skills:

    • connecting your database to a VCS with SQL Source Control.
    • discarding changes and recovering an older version of your code from a VCS.
    • branching your database code
    • merging changes from branches together.
    • using migration scripts for custom code changes and preserving these in your VCS.
    • and more

    Register today, and spend a day with Ike Ellis and myself  learning about database version control. The class is only $100 for a limited time, so make plans to join us.

    You’ll need to bring a laptop, but we’ll provide a VM ready for you to practice the skills on, and we’ll provide the food and refreshments for the day.

    Using a Version Control System is essential for application developers, and it should be for database developers as well. Learn how you can start to ensure your database code is protected and versioned, just like all your other code.