Voice of the DBA

Tag: syndicated

  • A Little Learning with Pluralsight

    I’ve been working on a new presentation on testing using the tSQLt framework and was curious how other people are using the framework. I read through the documentation and various articles on Simple Talk. I also went through the Google Group and StackOverflow tags for tSQLt, gathering lots of viewpoints on how the framework is used.

    However I wanted more. I’ve tested T-SQL code before, but usually in the simplistic way that I see most SQL Server developers running tests. Contriving a bit of data, building a query that checks things, and moving on. I’ve never written formal SQL tests, and wanted to learn something.

    I turned to my friend, Google, and was surprised to see a course on Pluralsight pop up. I didn’t think tSQLt was popular enough to build a course, but apparently it was, so I signed on and went through the course. It was interesting, and I got a nice set of hints on how Dave Green uses tSQLt and some ideas on how I could enhance my presentation.

    Then I went looking for a few more courses. Since I was a bit laid up with my knee surgery, I was stuck in bed and rather than watch TV, I wandered around the Pluralsight site a bit. I was surprised to find a course on SQL Prompt and went through that, getting a few tips and tricks that I hadn’t used before. I also started a course on CTEs from Joe Sack, and I’ve learned a couple tricks in there.

    Overall, I’m impressed with Pluralsight. I’ve had a subscription for some time as an MVP, but hadn’t accessed it. However I’ll be spending a few minutes here and there working my way through the offerings, trying to learn a bit more about SQL Server, as well as tackle some C# and security.

  • Quick Tips – SQL Prompt Stop the Yelling

    I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

    Lower Case Keywords

    One of the things that many developers like is lower case keywords. If you examine some C# code, you’ll often find that they have keywords in lower case. For example, here’s a sample from MSDN.

    // versioning.cs
// CS0114 expected
public class MyBase 
{
   public virtual string Meth1() 
   {
      return "MyBase-Meth1";
   }
   public virtual string Meth2() 
   {
      return "MyBase-Meth2";
   }
   public virtual string Meth3() 
   {
      return "MyBase-Meth3";
   }
}

    When I’ve gotten projects from Red Gate, I see something similar.

    However, if I enter some code in Management Studio, by default, SQL Prompt will format it like this:

    prompt_aa

    Notice the upper case keywords. Personally I like these, but many developers may not. Fortunately there’s an easy fix. Access the SQL Prompt menu in Management Studio and select options (circled below).

    prompt_ab

    This will bring up the options dialog. Select the CASE item as shown below.

    prompt_ac

    On the upper right side, you’ll notice that you have drop downs for keywords, built-in functions and data types. By default these are all set to uppercase. However you have a number of choices.

    prompt_ad

    I’ll change mine to lowercase, click OK, and then reformat my code. I now see this.

    prompt_ae

    Quick and easy, and I can now read code that looks more like what developers are used to. Maybe I’ll leave things set like this…

    You can see a complete list of SQL Prompt tips at Redgate.

  • Attaching All Databases with PowerShell – The Overview

    TL;DR Script is here: Git Hub Powershell Scripts. It’s the attachdbs.ps1 and will attach all databases in a folder to a SQL Server instance, if they don’t exist.

    I wrote a PowerShell script recently to actually accomplish a task I that I needed. What’s more, this was the first time I thought that Powershell might prove more useful than other methods. This series looks at my script, and this part examines the first part that I wrote.

    After my problems with Windows 8.1 and my reinstallation of SQL Server, I had a problem. I had no databases.

    I had the files. I had backup files. However the instance didn’t have any databases registered. I started down this path.

    attach_a

    However that seemed inefficient. I actually had a pattern of things that I knew needed to be done, I had a bunch of repeatable work, this sounded like it should be a PowerShell type task. I could have done it in T-SQL, or grabbed a script from SQLServerCentral, but it made more sense to load databases with PowerShell.

    The Start

    Of course I started Googling, but didn’t see any posts that shower someone with mdf/ldf files and needing to attach them to an instance without knowing what you had. What I had was an instance, with no backup/restore/detach history.

    attach_b

    I also had a bunch of mdf/ldf files in a folder. As well as some folders for Filestream/Filetable information.

    attach_c

    What did I do? I’ve got the script on GitHub, and you can grab the latest version at: Powershell Scripts (choose the attachdbs.ps1 file)

    This post will give an overview of what I needed to do and I’ll post more details about how I built the script in pieces. The overview of the process is:

    • Get all MDF Files in a folder
    • Connect to a SQL Server instance and loop through all databases
    • If a file name (less the .mdf) does not exist as a database, track this.
    • Get the log file associated with an mdf
    • Attach the mdf and ldf files to the SQL Server.

    That’s what I needed to do and development went in those stages. Certainly there were issues, but I got it working as of this post. When I ran my script, I saw these results:

    attach_f

    In SSMS, I had my databases.

    attach_d

    I even had my Filestream stuff in place. SQL Server handled that for me.

    attach_e

    I’ll include other posts that talk about the details of how I build this, which took about 3 hours one day, and an hour the next.

    References

    Here are a few posts where I picked up bits and pieces of what I needed to do.

  • FileTable– Using GetParent for Inserts

    In a previous post, I looked at the ways in which I could insert files into a Filetable folder programmatically. I used a NEWID() generation process, which mimics the constraint that is coded in the Filetable schema. However there was a comment asking if I could, or rather would, use the HierarchyID methods. It was on my list to investigate, so here it is.

    I have a file, actually an image, that looks like this:

    circle

    It’s a circle image, and the insert statement actually looks like this:

    INSERT  INTO dbo.Pictures
        ( name
        , file_stream
        )
VALUES  ( 'circle.jpg'
        , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
        );

    That puts the file in my Filetable in the root. However suppose I actually wanted that in a folder. Here’s my Filetable share and you’ll note I have a folder called Shapes.

    filetable_zb

    Can I insert this file directly in there, using T-SQL? Let’s try.

    I wrote some code that would get the parent path and use that with GetDescendent(). However that didn’t work. I ran this:

    DECLARE @parent HIERARCHYID, @node HIERARCHYID
SELECT @parent = path_locator 
 FROM dbo.Pictures
 WHERE name = 'Shapes';

SELECT @node = max(path_locator)
 FROM dbo.Pictures
 WHERE path_locator.GetAncestor(1) = @parent

SELECT @parent, @node;


INSERT  INTO dbo.Pictures
        ( name
        , file_stream
        , path_locator
        )
VALUES  ( 'circle.jpg'
        , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
        , @parent.GetDescendent(@node,NULL)
        );

    and got this:

    Msg 6506, Level 16, State 10, Line 20

    Could not find method ‘GetDescendent’ for type ‘Microsoft.SqlServer.Types.SqlHierarchyId’ in assembly ‘Microsoft.SqlServer.Types’

    I found a note on StackOverflow that the implementation of the hierarchyID in a Filetable isn’t the same as elsewhere. I’m not sure if that’s the case, but I haven’t been able to document it.

    However I decided to then try the method proposed as a comment in my last post. Insert, and then move.

    DECLARE @id TABLE ( id UNIQUEIDENTIFIER )
DECLARE @streamid UNIQUEIDENTIFIER
  , @parentpath HIERARCHYID
  , @folder VARCHAR(1000);

SELECT  @folder = 'Shapes';

INSERT  INTO dbo.Pictures
        ( name
        , file_stream
        )
OUTPUT  inserted.stream_id
        INTO @id
VALUES  ( 'circle.jpg'
        , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
        );

SELECT  @streamID = id
FROM    @id
SELECT  @parentpath = path_locator
FROM    dbo.Pictures
WHERE   name = @folder

UPDATE  dbo.Pictures
SET     path_locator = path_locator.GetReparentedValue(hierarchyid::GetRoot(),
                                                       @ParentPath)
WHERE   stream_id = @StreamId

     

    In this code, I insert the file, but save the PK, the streamID using the OUTPUT clause. From there, I get the path of the folder, and then I use the GetReparentedValue function to move the file to the folder.

    That works fine, and I look in my folder after running this and see the file.

    filetable_zc

    I’m not sure what the difference with the hierarchyIDs is with a Filetable, or why operations on Filetable structures cause errors with some functions, but this seems to work and is likely a better way to handle any of your T-SQL manipulations with Filetable data.