Recently I was working to populate a Date Dimension table and I discovered that the script used was basing the first of the week on my local machine. This was fine except I wanted to line up my weeks against our financial system which hand the end of the week on Friday, not Saturday is it looks like on a normal calendar.

Basically making the effective change below:

To do this, I performed these steps:

1. Open control panel
2. Go to Clock/Language/Region
3. Under Date and Time, click Set the date and time
4. Click Change date and time button
5. Click Change calendar settings link
6. Change First day of the week to your desired day
7. Click OK, OK, OK, OK to get out of the dialogue boxes
8. You are done!

Images of the steps are below (excluding the first step of opening control panel):

So I work from home. I also have a running machine. I haven't been running in a while because a) running is hard and b) everything has been packed away from moving around last year.

Well, the moves are over, everything is long since done and it's a holiday today (MLK) so the banks (one of which I work for) is closed. So today I got my gym room all fixed up. Setup the weights, positioned the treadmill so it's usable, etc etc. Now I need to be able to use my laptop while I am on it during the day. My first instinct was to look for a stand online. However, I had just got done actually using the machine and while on it I was listening to a TED talk about building houses (among other things) out of recycled materials. I did this with my laptop pedestal so it seemed like a logical fit. And why not?

So I used to use my laptop on here, but there was nowhere for the mouse, and you could prop the laptop on a groove on the machine control area. This works, but typing is at an angle. So that sucks. I was prepared to go to home depot and just buy some piping and maybe some other stuff to make something to kinda attach to the arms where you can hold on. I won't need to hold on for working because I will just have it at a very mild walk to get some movement during the day. Since I saw the TED talk I thought to myself "ha, I bet I can make something if I just look around."

Here's what I found and what I used (and the order I needed them):

1. 2 x Old Soda carrying plastic crates
2. 2 x Old Boards (about 45" long)
3. Duct Tape
4. Velcro
5. Old notebook

That's it. Here's how I made it:

1. Cut up the old crates because they had little Y grooves which helped them slide together, and also helped them slide right onto the place where you could hold on. Found my base yay! I cut these up so that I could slide all the Y portions together but since the hand rails sloped a little, I needed to be able to raise the front a little so it would be flat(ish). Problem solved, I just used an extra stack in the front and it all worked great.
2. Now I had the base, I just needed something to put it on. I was thinking about going to the store and just buying a board, but then I noticed I had two smaller 1x4 boards left over from some work doing trim on the outside of the house. Perfect! So I set them on top of the crates once they were positioned, and cut the ends off some. The ends I then I put between them and I just duct taped them together. I sat around and pondered some better ways to lash them together, but then I realized that the simplest way was to just duct tape them really tightly. It worked like a champ. Now I had my board
3. So now I needed a way to attach my board to the arms. I had originally thought maybe I would get a long threaded piece of metal and cut it to fit and use some nuts and washers and a hole in the board to bold the whole thing together. But that just seemed like overkill and would require a trip to the store. I had some bolts so I could bolt to the top, but that seemed like a little bit of overkill too for something so cheap and simple. Then I spied the magic solution sitting over with some old wires. Velcro! So I had one old piece of industrial strength Velcro. I dunno what that means, but I think it cost me like 10 bucks for the pack back when I got it. This was just a scrap. So I split the rough part in half just by making a small cut and tearing it down the middle. Then I put duct tape over the tops of old crates because they were full of holes. This would give something for the Velcro to stick to. Then I put on a strip for each side. After that I put a small piece of the soft stuff up towards the machine. This gave me more leverage to just pick it up when I wanted to take it apart and seemed to work well. You could add more if you wanted it to *really* stay put, but I just needed it not to move for the most part and this did the trick.
4. Now it was all assembled. My victory was almost complete. I needed a place for the mouse. I toyed with the idea of stapling some old cardboard to the top (there are spaces between the boards after all. Or maybe going to the store again. Not sure why I hadn't learned to not think of that yet. =P Finally I came up with the idea of just using an old notebook I got from a trade conference years ago. It's got really thick top and bottoms so it works great. I just sit it there, but you could fasten it down if you wanted it more permanent.
5. Profit!

That's it. Total cost was really just the price of using old stuff and about an hour of my time. If you had to buy stuff, then not free, but you could make something identical with just some pipe and some Y screws for walls and a board and some screws to screw that in really easily. But free is better. Here are some pictures. =)

1. Here are the old crates. Some of them cut up.

   

2. Here is the assembled board being placed down. You can see the duct tape holding the two boards and a block from the end of each to expand the width of the board. You can see the long strip of Velcro and the little strip in the back to keep everything in place. You can also see the crates simply wedged onto the bars.

   

3. Here you can see it with the top down and the machine visible. The old notebook is also on there for the mouse.

   

4. And finally here are two photos of the final assembly. It works great. =) sides look a little weird because I didn't cut the crates straight, but that's easily fixable if I ever need to. I don't foresee it being an issue. Best part is it's really easy to just pick up and move if needed for actual running in the morning/evening.

   

    

    

   Also, as a bonus, here is a picture of my super cheap pedestal I stand at to work during the day in the other room. It's just three 5 gallon paint buckets and one of those metal desk protectors. I think from IKEA.

   

This is pretty much a no brainer for code. However I went and looked and everything I found was either overkill, or i wasn't certain of the internals so i had to say run it, look at the properties etc.

So I wrote a simple app to do it and put it on codeplex at:
http://setfiledate.codeplex.com/

This problem stemmed from the fact that I got this weird DVD player and I needed it to play some files from a USB stick in a loop. It loops fine, the problem was ordering. No clue how the ordering was. I thought it might be one of the dates so I tried setting them all manually, no dice. It's not the end of the world as I'll just make a looping DVD or a single media file which will loop, but I tried this first. =)

Here's the set code if you actually came to this post looking for that =P

File.SetCreationTime(f, dt);
File.SetLastAccessTime(f, dt);
File.SetLastWriteTime(f, dt);

Where f is a string file path and dt is a DateTime value.

You can do this lots of ways. Here's a quick way from the command line:

iisapp

tasklist /FI "IMAGENAME eq w3wp.exe"

then just line up the PID numbers.

So the way I am getting this data is just normal dbcc showcontig in a db. But for some reason, when I saw the output of dbcc showcontig today, I just decided I *had* to parse it with the command line. /sigh I just wanted the logical scan fragmentation. So this is, in part, the output.

Profile_Lookup 99

MSSCrawledProperties 96

MSSManagedProperties 66

UserProfileEventLog 50

UserColleagues 40

UserMemberships 85

UserSites 66

MembershipRecursive 73

MembershipNonRecursive 1

Etc etc etc etc

To do this I just did a simple for loop with a call out to a …. I forget what these things are even called since no one uses them anymore or cares. I thought about trying to convert it to one command (that's what I started with) but there were scoping issues remember what the last hit was on the table name and it really just seemed silly about 15 minutes into playing with it so I just got it here and left it. =P

set tablename=

for /f "tokens=1,2,3,4,5 delims=:'-. " %%i in ('sqlcmd -E -S a_server -d a_sharepoint_admin_db -Q "DBCC SHOWCONTIG"') do @call :check %%i %%j %%k %%l %%m

set tablename=

goto :end

:check

@if %1 EQU Table set tablename=%2

@if %1 EQU Logical if 0 NEQ %4 @echo %tablename% %4

::@echo "%aa%" %1 %2 %3 %4 %5

:end

Here is some sample output from dbcc showcontig if you don't know what it looks like:

DBCC SHOWCONTIG scanning 'MSSQLogTemp' table...

Table: 'MSSQLogTemp' (2062630391); index ID: 0, database ID: 32

TABLE level scan performed.

- Pages Scanned................................: 0

- Extents Scanned..............................: 0

- Extent Switches..............................: 0

- Avg. Pages per Extent........................: 0.0

- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 0.0

- Avg. Page Density (full).....................: 0.00%

DBCC SHOWCONTIG scanning 'Versions' table...

Table: 'Versions' (2073058421); index ID: 1, database ID: 32

TABLE level scan performed.

- Pages Scanned................................: 1

- Extents Scanned..............................: 1

- Extent Switches..............................: 0

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 7562.0

- Avg. Page Density (full).....................: 6.57%

This actually becomes much sillier if you consider how easy it is to parse text with LINQ or if you just simply resort to using a DM to get this data. The following works fine and will break it out by each index. =P

select

    object_name(a.object_id) [table]

    , b.name [index]

    , avg_fragmentation_in_percent [fragmentation]

from

    sys.dm_db_index_physical_stats (db_id(), null,    null, null, null) a

    join sys.indexes b on

        a.index_id = b.index_id

        and a.object_id = b.object_id

where

    avg_fragmentation_in_percent > 0

Links for those of you who hate yourselves =P

http://msdn.microsoft.com/en-us/library/ms188917.aspx

http://msdn.microsoft.com/en-us/library/ms188917.aspx

http://technet.microsoft.com/en-us/library/cc966523.aspx

http://www.mssqltips.com/tip.asp?tip=1014

http://msdn.microsoft.com/en-us/library/cc917679.aspx

http://www.sqlhacks.com/Optimize/Fragmented-Data-2005

So, the question was posed on how to do this with LINQPad, and thus with LINQ. Here is one way. This is basically the same thing I did in my last post about searching for this kind of value. This also shows how to return an arbitrary result from a linq query to a very generic class object for later processing. Note that I was not able to use params because the table name couldn't be a param. I'm sure there is probably some more elegant way to do this, but this works fine and hopefully this is one of those scripts/snippets of code that you never have to use. Since you can just do something like this with SQL, I'm not sure of the usefulness of this code in a realistic setting, but I suppose if you needed to throw up a utility page and you already had a LINQ data context in your app for other stuff, you could do something in a pinch using executequery. I'm personally not a big fan of using executequery as it stands today. I generally just take it as a sign I need to switch to sql, but for something like this (I mean, I could be joining on a file with a list of text to look for right?), the versatility is nice to have. =)

void Main()

{

    this.ExecuteCommand("set transaction isolation level read uncommitted");

    string FindThis = "some text to find";

    string q = @"select

        '[' + table_catalog + '].[' + table_schema + '].[' + table_name + ']' [t]

        , '[' + column_name    + ']' [c]

    from

        INFORMATION_SCHEMA.COLUMNS

    where

        data_type in ('nvarchar','nchar','varchar','char')

        and character_maximum_length >= len({0})";

    string e = @"select case when exists(select * from {0} with (nolock) where {1} like '%{2}%') then 1 else 0 end";

    var matches = from a in this.ExecuteQuery<tc>(q,FindThis).Where (t =>

        this.ExecuteQuery<int>(e.Replace("{0}",t.t).Replace("{1}",t.c).Replace("{2}",FindThis)

            ).Single().Equals(1)

        ) select a;

    matches.Dump();

}

class tc

{

    public string t{get;set;}

    public string c{get;set;}

}

I am a huge fan of LINQPad. Something on my wish-list, as far as my own personal knowledge, for a longtime with it was how to query Active Directory information when I needed it. This was more for convenience because quite often I need to look up some AD info when I am looking at a scrub for some data regarding a user. Normally I just net use or dsquery/dsget etc. The key is System.DirectoryServices.AccountManagement. Note this is a .NET 3.5 and 4.0 only library so earlier than that, sorry. You aren't out of luck, but the other ways seem to be less cumbersome to me than this very easy way. Be mindful of the fact that performing a .Dump() on one of these objects will produce a TON of stuff. So doing something like User.GetGroups().Dump() is probably a Very Bad Idea^TM. Also, don't forget that since we're using LINQ to Objects, you will have case sensitivity issues unless you deal with it yourself.

1. Edit the Query Properties in LINQPad:

   

    

2. Add a reference to System.DirectoryServices.AccountManagement.dll:

   

    

3. Update the Additional Namespace Imports tab to reflect the namespace you want to import:

   

    

4. PROFIT!! =P

    using(PrincipalContext ctx = new PrincipalContext(ContextType.Domain, "MyDomain))

        using(UserPrincipal usr = UserPrincipal.FindByIdentity(ctx, IdentityType.SamAccountName, "MyUserID"))

            usr.Dump();

apparently you can use a transaction scope for this and just wrap everything that way. but to me that seems more complicated than just enabling it with execute command.

some LINQ code like (note that ‘this’ is a data context):

this.ExecuteCommand("set transaction isolation level read uncommitted");
MyTable.Take(5).Dump();

Produces this sql code:

set transaction isolation level read uncommitted
GO

SELECT TOP 5 <fieldnames…> FROM [MyTable] AS [t0]
GO

That’s what I want to see, so yay. =)

some links:

http://madprops.org/blog/linq-to-sql-and-nolock-hints/

http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx

ps. trying to use livewriter today. so far, not excited about it and still like word more =P hopefully i’ll get elightened.

After my recent post, I had a couple of conversations and an email exchange with a colleague of mine and I was reminded of the fact that I went originally out to search for my 'original' table space used script. I couldn't find it online (even though I found it on my hdd) and that prompted me to put together the newer method. Which is considerably shorter. But here is my original script which essentially I wrote by looking at what was in sp_spaceused and just formatting it more to my liking.

-- view all table sizes

-- author: royashbrook - royashbrook@yahoo.com

-- april 2005

select

so.id as [OBJECT_ID],

so.name as [OBJECT_NAME],

coalesce(j_rows.rows,0) as [ROWCOUNT],

coalesce(j_ru.sum_reserved,0) * cast(m.low as dec) / 1024 as [RESERVED (KB)],

d.data * cast(m.low as dec) / 1024 as [DATA (KB)],

(coalesce(j_ru.sum_used,0) - d.data) * cast(m.low as dec) / 1024 as [INDEX (KB)],

(coalesce(j_ru.sum_reserved,0) - coalesce(j_ru.sum_used,0)) * cast(m.low as dec) / 1024 as [UNUSED (KB)]

from

sysobjects so

join master.dbo.spt_values m

on m.number = 1 and m.type = 'E'

-- rows

left join sysindexes j_rows

on j_rows.indid < 2 and j_rows.id = so.id

-- reserved: sum(reserved) where indid in (0, 1, 255)

-- index: sum(used) where indid in (0, 1, 255) - data

-- unused: sum(reserved) - sum(used) where indid in (0, 1, 255)

left join

(

select

id

, sum(reserved) as sum_reserved

, sum(used) as sum_used

from

sysindexes

where

indid in (0, 1, 255)

group by

id

) j_ru

on j_ru.id = so.id

-- data: sum(dpages) where indid < 2

-- + sum(used) where indid = 255 (text)

left join

(

select

j_dpages.id

, coalesce(j_dpages._sum,0) +

coalesce(j_used._sum,0) [data]

from

(

select

id

, sum(dpages) [_sum]

from

sysindexes

where

indid < 2

group by

id

) j_dpages

left join

(

select

id

, sum(used) [_sum]

from

sysindexes

where

indid = 255

group by

id

) j_used

on j_used.id = j_dpages.id

) d

on d.id = so.id

where

objectproperty(so.id, N'IsUserTable') = 1