Skip to content

FileTable–Adding a file to a folder

At my Filestream/Filetable talk yesterday at SQL Intersection, someone asked me about programmatically adding a file to a Filetable. Easy enough, I thought, since I’d seen someone do this in .NET and was thinking this has to be simple. Turns out it’s not quite so simple.

I was assuming that I could use the GetDescendent method of the hierarchy ID for the path_locator field to get the path you needed and insert that. It turns out that’s not correct.

I searched around when that didn’t work and found this post from Bob Beauchemin and a question on StackOverflow. If the two, Bob’s post explains things better. He actually dug into the Filetable, looking at the constraints and defaults in the schema. If you do that, you can find this constraint on the path_locator field:

filetable_h

The code in this constraint looks like this, which matches with what Bob and SO show as the way to calculate the path.

ALTER TABLE [dbo].[Explorer] ADD  CONSTRAINT [DF__Explorer__path_l__6477ECF3]  
DEFAULT (CONVERT(HIERARCHYID, '/' +     CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 1, 6))) + '.' +
     CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 7, 6))) + '.' +
     CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()), 13, 4))) + '/')) FOR [path_locator]
GO

This means that your insert statement needs to perform this calculation. For me, I decided to insert a jpg of a circle into the table, under the folder “books”. The folder looks like this:

filetable_g

Since I have the binary values for a circle image, I’ll use those, and then the code below. The first part of this code calculates the new path that’s needed, basing this on the path_locator value for the “Books” folder, and then building a NewID(). From there, it’s a simple insert.

DECLARE @path HIERARCHYID
DECLARE @new_path VARCHAR(675)
 
SELECT  @path = path_locator
FROM    dbo.Explorer
WHERE   name = 'Books'

SELECT  @new_path = @path.ToString()
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         1, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         7, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         13, 4))) + '/'
INSERT INTO dbo.Explorer
        ( stream_id
        , file_stream
        , name
        , path_locator
        )
VALUES  ( NEWID()
        , 0xFFD8FFE000104A46494600010101006000600000FFE100684578696600004D4D002A000000080004011A0005000000010000003E011B0005000000010000004601280003000000010002000001310002000000120000004E00000000000000600000000100000060000000015061696E742E4E45542076332E352E313000FFDB0043000201010201010202020202020202030503030303030604040305070607070706070708090B0908080A0807070A0D0A0A0B0C0C0C0C07090E0F0D0C0E0B0C0C0CFFDB004301020202030303060303060C0807080C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0C0CFFC0001108000A000D03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00E97FE0E87FF82CA7ED35FB04FED85E09F03FC2BD6A5F87BE1097408B5C4D523D2ADAF1BC41746795248CBDC4722F970848C18940399373E43478FD75FF00827CFC68F177ED17FB0FFC2AF1D78F3461A078C7C59E19B2D4F57B110B42B14F244199846DF346AFC384392A1C024E335E95E2EF87BE1FF880968BAF687A3EB6B612F9F6C2FECA3B916F27F7D3783B5BDC60D6C5007FFFD9
        , 'Circle.jpg'
        , @new_path      
      );
go

 

If I now look at the folder, I see this:

filetable_j

An insert into the folder of a file.

I could easily adapt this to stream in the binary values from a .NET application, or OPENROWSET, but this works well enough.

Nothing Scheduled

As of right now, my United account has no flights booked. My Hilton Honors account has no hotel rooms reserved. My National account has no car rentals to pick up.

And I have nothing scheduled for the rest of 2014.

For now. I suspect things will change soon, but it’s a good feeling for now.

High Performance Encryption

It is becoming more and more important to protect your data from anyone that should not access it, including the DBA. Learn how you can use column level encryption as well as TDE to protect your data files, with minimal performance impact to your SQL Server instance.

This session will look at securing data

  • in flight
  • at rest
  • inside the database

We do this with

  • IPSec/SSL Communications
  • Transparent Data Encryption
  • Partial Hash Bucket values with symmetric encryption.

Slides:

Code:

 

Smart Gadgets

I saw this post on what a smartwatch should look like and it got me thinking. I’ve got a Kreyos watch coming (I hope) and it fulfills some of the things I was looking for, and a few that the Pebble didn’t.

However I wonder where we’ll go with gadgets in the future. My son and I sometimes dream, trying to decide what we’d like to see built in the future. One of the main things we talk about is how we might wear things. A bracelet of some sort seems to be the consensus. Getting something larger that we can see better, but more convenient then a smart phone. Already I think the 5″ ones are a bit large and cumbersome to use. One of my complaints about the Galaxy S4 was that I couldn’t easily use it with one hand.

Glasses are an interesting idea. Google Glass has some good ideas, though I think the idea of including the camera was one that caused a lot of concern from people, and rightly so. Even though cell phone cameras haven’t caused too much of a problem, there certainly have been privacy incidents. I think I’d like the idea of getting some information in my field of vision, and I’d like to try some “smart glasses” at some point.

I found this post on gadgets out there, and most of them aren’t revolutionary and a few aren’t interesting. A USB drive in cuff links or a necklace? Doesn’t seem that interesting to me. What I’m looking for is perhaps some type of mix of gadgets that can work together.

Could I get a phone that connects with various devices that can run for a long time? Perhaps a belt buckle that provides power and also tracks movement like my Fitbit flex? It can connect to my watch as well, providing me with information from a device in a pocket. Ultimately I’m not sure what would be enhance the phone much. Any jewelry might not provide much more than notification of information through vibration, or possible light, and you’d still need a device.

Ultimately I think I’d like to have more ways to connect devices together. Easily get my phone to project on a larger monitor when it’s available, like in the kitchen or car, perhaps even using a camera there to communicate with others.

My hope is we’ll get gadgets that communicate, but with the separation of platforms and the reluctance for many to work together on common APIs, I’m not sure we’ll get there.

An Open Letter to Brogrammers

It’s not in the language I would write it in and it’s not necessarily safe for work, but it gets the point across.

An Open Letter to Brogrammers

One reason I do support Women in Technology, and other groups opposing discrimination. Apart from it’s poor behavior, it’s often just not even valid.

How Long Before You Upgrade?

It’s 2014. SQL Server 2000 is 14 years old, but there are still quite of you managing instances. SQL Server 2005 is 9 years old, and I’m sure more of you still deal with that version. I know because I work for a software vendor and I’m constantly asked if our software will run against those two versions.
For many of you, however, if you’re managing a SQL Server 2000 instance, it might only be 9 or 10 years old. Your company might still have been installing SQL Server 2000 in 2005. The same is true for SQL Serve 2008. I wouldn’t be surprised to find companies still setting up 2005 instances in 2008 or even 2009.
Companies don’t care much about versions. They tend to mostly care about databases getting the job done, and sometimes, support. Many organizations don’t see value in upgrading too often because of the overhead. I suspect many managers would prefer to get many years usage out of a platform before they change in order to minimize work that doesn’t add value to their business.
The question this week asks you about the longevity of your database instances. Think about the average instance, or even the majority of your applications and how long they will remain on a particular version.
How many years will you run a platform before you upgrade it?
Years ago I heard someone at a large Fortune 100 company say their stated policy was to get 10 years of service out of a database server. At the time I thought that was a long time, but the more I think about it, the more I think that might be a minimum amount of time I’d want from a platform.
Let us know this week what you experience, and perhaps what you’d prefer.
Steve Jones

If or When?

I saw this post recently about security and preparing for a data breach. The title caught my eye because it implies that we’re all doomed. Do the rest of you think that? Is it a question of when we’ll have a security breach not if?

Given the headlines, the news we find out about companies not disclosing security issues, the back doors and poor code in much software, is it any wonder that people think it’s a “when” and not an “if”? Given the lack of realization from many companies that suffer incidents that they were even attacked, perhaps that’s an assumption worth making.

We’ve been hacked at SQLServerCentral in the past. I don’t think we’ve been hacked in many years, but I also have no way of knowing. That’s the difficult part of dealing with bits. If they get copied, there’s not necessarily a trace of anything amiss. It’s quite possible that many of us have no idea that our bits are being copied. Every read is a copy of data and how long did the NSA read data without most of us being aware? How sure are we that they, or some other organization, hasn’t been reading much more than was disclosed?

I’d hate to think that our systems are so porous that we’re all likely to get hacked at some point. It’s probably technically possible, but hopefully not likely for most of us. However we should consider that it will happen and ensure we have some handle on our data security. It’s hard, and complex for most of us, and I’d like to think that Microsoft will recognize this and build better controls and features into future versions of Windows and SQL Server that enable easier auditing, granular permissions, and separation of duties.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Vote for me at SQLBits

SQL Bits XII is coming this July to Telford in the UK and voting is underway for sessions. I’ve submitted a few and am hoping to get accepted to go back. You can help.

You can log into the site and vote for the sessions you’d like to see. I’d prefer you vote for me because I’d like to go see friends and meet new people, but feel free to vote for the sessions you’d like to see.

The ones I’ve submitted are:

I’m not sure how long voting lasts, but take a few minutes today and pick your ten favorite sessions.

Problems with Big Data

Big Data is constantly in the news. We’ve been asked at SQLserverCentral to try and develop some articles, perhaps even a stairway to explain what Big Data is and how we might use it. I’m still trying to grasp the concepts myself, and unlike the amorphous cloud, I’m still looking for some good examples of what Big Data really is.

When I ran across this piece warning that Big Data isn’t the final solution to all our questions in the world, I wasn’t surprised. The piece notes that Google Flu hasn’t been very accurate in its predictions of outbreaks. At first glance, this gives lots of credence to the idea that the good, solid data analysis and mining techniques we’ve used for years are just as good as any new Big Data fad.

However as I read more about the piece, it’s not that big data and the analysis of large quantities of information is flawed, it’s that a solid hypothesis matters. Researchers need to be willing to evolve their algorithms as they learn more about a problem. Probably they should also assume their algorithms are not correct until they’ve proven their ability to predict actual trends for some period of time.

We’ll constantly be searching for ways to better interpret information and make better decisions. No new technology or product is going to magically solve our problems. Good solid understanding of the problem domain will continue to matter as much as the data itself.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 1.8MB) podcast or subscribe to the feed at iTunes and Mevio . feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

T-SQL Tuesday #53–Why So Serious?

tsqltuesdayIt’s an interesting topic this month for T-SQL Tuesday #53. When I read Matt Velic’s invitation, I became intrigued. I like jokes. I like messing with people, and I was thinking about the recent April Fool’s jokes I’d written and those in the past that have been fun. I’m going to enjoy this.

You can participate as well on any month. Just write a blog post and publish it on the second Tuesday of the month, linking back to that month’s invitation. Follow the #tsql2sday tag on Twitter or set a reminder and Google on the first Tuesday of the month.

If you hurry, you can participate this month.

Not So Serious

The number one rule is know your audience. I’d be very careful who I might play this joke on. Note that this is also a bit of a abuse of privileged accounts.

There are a few utilities from Sysinternals (owned by Microsoft) that are very useful for administrators. However they have also been very handy for practical jokes as well. Specifically I’ve used the PsExec and PSKill to amuse myself.

I once worked in a group that had four administrators to run our systems. We lost one of our people to a better job and the corporate management decided to "promote" an internal employee to replace him. This new person was a "paper MCSE", who had studied for the exams. He was making an effort to grow his career and that was something to admire.

However he was a jerk.

He had the mistaken impression, as many people that have achieved something they set out to do, that they know more than they do. In this case, much of his "book learning" and boot camp work wasn’t appropriate for the real world. What’s more, when we would work with him to teach him how things worked differently for us, or explain why the book recommendation wouldn’t work, he was arrogant and dismissive of us.

After suffering through a few months of his dismissive attitude and desire to avoid learning more in the real world, we decided to play a few jokes on him. We downloaded the sysinternals tools and also wrote a few scripts to command line launch tools with specific scripts or settings.

One day when he picked up the phone to work on a call, we watched him start to connect to a server. Using PSList and PSKill, we’d kill his connection. Then we’d quickly use PSExec to launch another connection to a different server. At times we could be creative and redirect him to a development server instead of a production server. He’d swear he was fixing something for a user, but since he was on the wrong server, the user didn’t see the item working correctly.

We were sporadic in our efforts, only causing issues a few times a day when we felt the problem wasn’t too critical. Eventually my manager realized what a few of us were doing and had us stop.

It was a bit mean, and not something we should have done maliciously. I was young, and let myself get irritated. In later years, I out grew some of this childishness and limited jokes to people that I genuinely liked, changing settings, altering wall paper, creating aliases that pointed to different systems, and even unplugging people mice and connecting my own in an adjacent cube.

Joking around at work can be fun, but remember that it shouldn’t be malicious and it can’t prevent people from getting work done. We certainly don’t want to get someone fired, as much as we might like to at times.

Follow

Get every new post delivered to your Inbox.

Join 4,323 other followers