sp_spaceused for all the tables in your database in one table


Why? Well, sometimes you just want it all in one table.

create table #t (

    tablename sysname,row_count int,

    reserved varchar(50),data varchar(50),

    index_size varchar(50),unused varchar(50))

exec sp_msforeachtable 'insert #t exec sp_spaceused ''?'''

select * from #t

drop table #t

author: Roy Ashbrook | posted @ Thursday, July 29, 2010 11:13 PM | Feedback (0)

How to regain administrative access to your own sql server


Question: how do I regain sysadmin role for myself if I am a local admin, but not a sql sysadmin currently. This scenario can happen for a variety of reasons, but if you are the owner and administrator of a box, how do you get to where you can manage it again? It's pretty simple. Basically stop sql, then start it in single user mode, then add yourself, reset sa, whatever. A sample script is below. This script makes several assumptions.

  1. Your server doesn't take longer than 10 seconds to start, otherwise modify the sleep line (or just do this manually and wait for it to start)
  2. You already have an account with access on the server. If you don't you can add it or just reset the SA password. I find that usually the people interested in doing this at least have access to a database on the server so you can just grant that group admin access for the duration of the recovery.
  3. You don't mind killing the sql server task ungracefully like I'm doing below. If you do mind that, you can always just do all this one step at a time instead of copy and pasting it or something.
  4. You're server binary is in Q:\MSSQL\MSSQL.9999\MSSQL\Binn\sqlservr. I assume that yours is not which is why I am using this in the script so change the path to wherever yoru sqlservr.exe is actually located.
  5. You already had sql agent running. If you didn't, you can drop the last line. If you did the first line will automatically end it with the /y

 

net stop mssqlserver /y

start "sqlserver" Q:\MSSQL\MSSQL.9999\MSSQL\Binn\sqlservr -x -m

sleep 10

sqlcmd -E -S localhost -d master -Q "EXEC master..sp_addsrvrolemember @loginame = N'domainname\somedomaingroupthatalreadyhasanacccount', @rolename = N'sysadmin'"

taskkill /IM sqlservr.exe

net start mssqlserver

net start "SQL Server Agent (MSSQLSERVER)"

author: Roy Ashbrook | posted @ Thursday, July 29, 2010 1:10 PM | Feedback (0)

ah yes…. LINQ is case sensitive….


So I am a huge fan of LINQPad. I use it all the time for ad hoc data processing mostly because of the .Dump() method which allows me to copy and paste something a little prettier than what I get outta mgmt studio or a simple command line processor. It's really pretty much the same to me, but for some reason normal people seem *wayyyyy* happier when they get a little blue box with some lines instead of just text with tabs or commas =P

Anyway, I was fixing to import some data from a file and I was joining it against the db, but there were some values to lookup that in the import were text files. No big deal, just join the values and get the ids based on the names. This did work, but I was trying to shrink the data being sent/retrieved over the wire so I did a pre-fetch on the distinct values and joined them on the ids on the lookup table. This worked fine, but I found that when I later went to join the object I retrieved against my file imported object, things didn't match. This made me a little crazy for about an hour or two as I kept coming back to it and writing queries that would show more and more of the data. It was particularly annoying because usually I just import this data into a table and do it all in sql, but I figured at some point I wanted this to be some kind of GUI so I'd do it in linqpad in preparation of using EF or Linq2Sql or whatever more .net'ey way of doing things. So since I have done this about 50 times in the past with no problems, it was irritating that it was causing me any time at all. Trying to 'research' what was up with my data caused me to need to do an outer join which is its own particular flavor of silliness (IMO) in LINQ.

So I let it sit overnight. Came back the next day and realized what was up. I was doing a linq2objects compare really because I was crossing the retrieved data (an anon type with just my id and name I joined with the distinct values from my import file) with my import data (a anon type with the stripped lines from my import csv) and linq2objects is case sensitive. For me, I am simply dealing with basic imports with all English chars etc so .ToUpper() on both sides of the compare fixed everything. But if you have to deal with larger international type imports with different globalization/culture concerns, you might need something else.

Anyway, here's a little code snip even though this almost doesn't really warrant one. There are some other ways to 'join' this data, but this is basically going to get the TableInDB and then It will do the compare client side, that's why it fails without the case insensitivity.

var data_with_keys_to_import =

    from f in DataImportedFromFile

        join t in TableInDB on d.TextValueForKey.ToUpper() equals t.TextValueForKey.ToUpper()

    select new {...};

 

And here's a few of links about case sensitivity stuff if you can't find your own on google/bing. I found the last one the most poignant.

  1. http://stackoverflow.com/questions/841226/case-insensitive-string-compare-in-linq-to-sql
  2. http://blogs.msdn.com/b/michkap/archive/2007/10/01/5218976.aspx
  3. http://blogs.msdn.com/b/michkap/archive/2005/03/10/391564.aspx

 

 

author: Roy Ashbrook | posted @ Tuesday, July 20, 2010 2:29 PM | Feedback (0)

Showing status in c# loop using the modulus operator (%)


I needed to take some records in one file, do some stuff with the record, then put the results in another file. The 'do stuff' part was taking a bit longer than I thought it would so I wanted to see status. This sample illustrates taking 1000 records from a source file, uppercasing each line, putting it in another file, and letting us know every 100 lines.

using(TextWriter tw = new StreamWriter(@"uppercase.txt",false))

{

    int i = 0;

    var q = File.ReadAllLines(@"lowercase.txt").Take(1000);

    foreach(var v in q)

    {

        tw.WriteLine("{0}", v.ToUpper());

//print status on every 100th record

        if ((i % 100) == 0) Console.WriteLine("{0}/{1} processed", i.ToString(),q.Count().ToString());

        i++;

    }

    Console.WriteLine("{0}/{1} processed", q.Count(),q.Count().ToString());

}

author: Roy Ashbrook | posted @ Friday, June 25, 2010 9:08 AM | Feedback (0)

ROW_NUMBER() AND PIVOT AND TEST DATA, OH MY!


So the other day I got the following question about reporting on some log data:

The data being loaded is a bunch of machine log files. (E.g. MachineName Status Timestamp) The status is a simple Start or Stop. My question is can I use Datediff between sequential records to determine elapsed time between start and stop? I know this would work if the start and stop data were in the same record and I believe this will work between sequential records but I'm fuzzy on what's best and putting it into action

Below is the end result of my answer. There are three basic parts of the solution below:

  1. Build some test data and stage it
  2. Group the data and make some keys to re-join it
  3. Write a little report to show what we are after
  4. (bonus) a little pivot table to show some other groupings on the data =D

   

-- our test data will simulate 10 machines that make 100 total

--    connections per day that last between 1 and 300 seconds.

-- this sample log is stores three items, the box name, what

--    happened (start/stop) and a timedatestamp

-- this data is supposed to happen in a sequence mostly. there

--    could be overlapping time events with the machines, but since

--    we would just eliminate that in a sort by machine later anyway

--    i am not going to worry about that and we will do everything in

--    one long sequence. our start date will be getdate() if there is

--    no date already in the table.

   

-- this table var will hold our 'raw' data

declare @t table

(

box char(5)

, evt varchar(5)

, tds datetime

)

-- loop to fill in our temp table. basically just some vars and some loops with random info

declare @i int,@ii int, @box varchar(5), @tds datetime

select @i = 0,@ii = 0

while @i < 10

begin

    select @box = 'box' + CAST(@i as char(1))

    while @ii < 100

    begin

        select @tds = coalesce(MAX(tds),getdate()) from @t

        insert @t values(@box, 'start', @tds )

        insert @t values(@box, 'stop', dateadd(SECOND,RAND()*300,@tds))

        set @ii = @ii + 1

    end

    select @i = @i + 1, @ii = 0

end

   

-- this is our 'staging' table that we'd be importing this data into for analysis

declare @tt table

(

box char(12)

, evt varchar(5)

, tds datetime

, group_key int

, primary key clustered (box, evt, group_key)

)

   

-- this represents our load. we are adding a row_number line to provide

--    a unique extra key for each set of box/evt combos

insert @tt

select box, evt, tds

, row_number() over (partition by box,evt order by tds) [group_key]

from @t

   

-- now we can select from our loaded data table twice, once for each

--    of our two events start/stop and join based on the group_key which will

--    already be unique for each set based on our load above.

-- this shows us the data how we want to see it. or at least how i was

--    originally asked for it.

select

starts.box

, starts.tds [start]

, stops.tds [stop]

, DATEDIFF(ss,starts.tds,stops.tds) [duration]

from

@tt starts

join @tt stops on

stops.evt = 'stop'

and stops.box = starts.box

and stops.group_key = starts.group_key

where

starts.evt = 'start'

   

-- here is a little pivot table to show you how many connections

--    in the timeframes took certain lengths of time to complete.

-- you could do this a number of ways, but i don't get to use

--    the pivot command enough so i figured i'd tack it on here =P

select

    *

from

    (

    select

        box, case

            when duration < 30 then '<30s'

            when duration between 30 and 60 then '<60s'

            when duration between 60 and 120 then '<120s'

            else '120+s'

            end [d]

    from

        (

        select

            starts.box

            , DATEDIFF(ss,starts.tds,stops.tds) [duration]

        from

            @tt starts

            join @tt stops on

                stops.evt = 'stop'

                and stops.box = starts.box

                and stops.group_key = starts.group_key

        where

             starts.evt = 'start'

        ) ii

    ) i

PIVOT

(

count(d)

FOR d IN ([<30s],[<60s],[<120s],[120+s])

) p

author: Roy Ashbrook | posted @ Tuesday, June 22, 2010 12:00 AM | Feedback (0)

Careful with your LINQ joins (WHERE IN TSQL with LINQ)


So today I needed to cross reference some stuff in a text file with some stuff in a db. No big deal. Had about 200 unique values to lookup a few pieces of info out of a larger table in a db. The target table had about a half million rows in it, but was indexed on this particular column I needed to lookup on so it seemed like it should be no big deal. I have found myself using LINQPad more and more for little ad-hoc stuff like this.

Anyway, I was prepared to do something similar to this:

var list =

        from lines in File.ReadAllLines(@"c:\myfile.txt").Distinct()

        join c in MyTable on lines equals c.MyTable_ID

        select c;

The problem with this is it grabs the entire table back and joins it. That's not really what I want and not optimal. If you look at the sql generated it should have one select that grabs the entire table.

However you can do something like this:

var list =

    from lines in File.ReadAllLines(@"c:\myfile.txt").Distinct()

    select

        (

            from c in MyTable.Where (cd => cd.MyTable_ID == lines)

            select new {

                c.field1,c.field2,c.field3

                }

        ).SingleOrDefault();

 

Now this query will generate a bunch of separate queries, each will be a query for the fields you request with just the single id you asked for. This will also give you a null record for non lookups, so you could figure out a way to handle it. Or at least see the empty lookups. This might be what you want, but in my case, I wanted a single query. I was hoping to generate an 'IS IN' type tsql where statement. One single query. The query above *did* reduce my query time from 'OMFG this is never going to complete and people will hate me for hanging up the server' to about a minute or two.

In contrast, I settled on this statement:

var list = from c in MyTable.Where(cd =>

File.ReadAllLines(@"c:\myfile.txt").Distinct().Contains(cd.MyTable_ID))

    select new {c.field1,c.field2,c.field3};

   

This statement took about 10 seconds and produced the query I was looking for. A big parameterized dealio that passed in a bunch of stuff for a 'where xx in yyy' clause.

What was also interesting was where the text manipulation took place in the queries vs sql or in IL depending on how I structured the LINQ statement. When I concatenated field2 and 3 inside of the select new statement in the second statement, it created a subselect in each statement that concatenated the two and then filtered by the id, when I did it in the third statement, it didn't do anything with it in the sql and just did it on the client side.

   

I'm a huge fan of LINQ now after being a hater for quite a while for it being yet another tool to distance developers from SQL. I use it a lot for adhoc reporting stuff and a variety of analysis. I'm not sure if you would want to do something like this on a regular basis in a regular app, but not sure why not. I'd be skeptical about unattended logic crossing machine boundaries like this on a regular basis in a middle tier somewhere, but I sure do love the versatility for my purposes. You can easily do all this in vs, but I gotta give some mad mad props to the LINQPad guys for a really great product. The main point of this article should be that just because you are using LINQ doesn't mean you can't take some time and really analyze what you are doing to make sure you are doing things the right way. I have seen several articles and blog entries slamming various orms or data access methodologies. Generally, they all seem to do what they are supposed to do for the most part if you just use them correctly and pay attention to what you are doing.

 

[edit: scott hanselman blogged about this as well (although better) so here's a link to that too =P]

author: Roy Ashbrook | posted @ Tuesday, June 08, 2010 6:20 PM | Feedback (0)

Some EF Weirdness (LINQ to Entities does not recognize the method 'System.String ToString()')


So I was writing a really simple EF web method today. It just checked for the presence of two values in a table and would return true or false.

[WebMethod]

public bool weird(long l, string s)

{

using (someEntities db = new someEntities())

return db.things.SingleOrDefault(x => x.s == s && x.l == l.ToString()) != null;

}

 

The code above gives me the following error:

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression

This error gets thrown on the l.ToString(). It apparently has to do with LINQ expressions being evaluated somewhere else other than where it is built. Makes sense since ToString() could be anything as far as LINQ is concerned. I would think that things like string and int would be kinda handled, but hey, it's no biggy.Very simple work around, you just have to actually pass it a string in your LINQ expression like so just make a string and pass it in like so:

[WebMethod]

public bool canAccessCustomer(long l, string s)

{

string sl = l.ToString();

using (someEntities db = new someEntities())

return db.things.SingleOrDefault(x => x.s == s && x.l == sl) != null;

}

 

One could argue about taking in a long that is stored as a string and why it's not a long or some equivalent number in the db, but sometimes things are the way they are. =P

I found a couple of different pages referencing this one, but this seemed to be the best although it had a bunch of other 'solutions' other than simply passing a string in instead of running the function in your LINQ query which seemed the simplest to me.

author: Roy Ashbrook | posted @ Friday, June 04, 2010 10:10 AM | Feedback (0)

SQLCE on IIS


So this apparently isn't supported, but I just thought I would jot down some notes about how to get it working. It is really not that hard as long as you don't mind installing SQLCE on your IIS server. I am still working on how to distribute the SQLCE files with the application.

 

I have a very small set of data that is mostly read and so I wanted to just dump it on a web server and not have a sql server. SQLCE seemed perfect for this although apparently that's not a use it's currently designed for. Here are some links I had open while I was working on this:

there are like a billion other links. I simply setup the db in a project where sqlce was allowed, and then copied the classes into the other project. A little connection string massaging and it worked fine.

 

Anyway, none of that is new/original, I couldn't find anything really current regarding how to get the sqlce dataprovider working on iis. There were a lot of references to machine.config and web.config/app.config fixes to put the provider in there etc. None of these worked for me. What did work was:

  1. Turning on the feature. I guess it is disabled by default. I simply added a line in the global.asax application start.

protected void Application_Start(object sender, EventArgs e)

{

AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

}

 

  1. Next, I (insert a long time of fiddling with the approaches above to get it to work but didn't) simply installed the sqlce runtime. I downloaded it from http://www.microsoft.com/downloads/details.aspx?familyid=E497988A-C93A-404C-B161-3A0B323DCE24&displaylang=en and installed the 32bit version. I am running .net 4.0, vs2010, windows server 2003 r2.

Hopefully I can find a way to distribute the SQLCE dlls with the app and it will just work since doing this install defeats one of my purposes which was to just simply be able to distribute a tiny set of data along with a simple access/manage set of services and not have to do any dependency installs. I have to say I think it's pretty stupid not to allow this to just work or to provide any clear instruction on microsoft's part. The realistic option for a normal person (and even for me) is to just install sqlite or do something else. I don't need full gui tool interoperability and I don't mind the hoops I have to jump through to get the classes setup etc, but I do wish if I had a project, any project, with a reference to that data provider that it would deploy whatever was/is needed easily. I'm sure there *is* a way to get it to work, but I haven't been able to find it. C'mon MS, toss me a token document that says 'if you want to use sqlce with anything, deploy these dlls, and do this in your solution and it'll work anywhere.' Or at least tell me where the data provider is weirded out on iis so I can fix it. I thought I was set when I found the info in step 1 above, but nope =P

author: Roy Ashbrook | posted @ Thursday, June 03, 2010 11:21 AM | Feedback (0)

How to automatically play and loop a youtube video


I like to do this every once in a while when I am practicing a song or if I just want to listen to the same song over and over again. But I always forget how and have to look it up. So now I can just look back here. First you need the video ID, this is in the of any normal youtube video you are watching. For example:

http://www.youtube.com/watch?v=Ed49S2nyBD0

The ID from this video is Ed49S2nyBD0. Just replace the that ID in the url below and form the URL the same as below and autoplay and loop till your heart is content. =P

http://www.youtube.com/v/Ed49S2nyBD0&autoplay=1&loop=1

I should note that there are some videos which won't play on other sites, there may be a way around that by passing other values in the urlstring, but I don't know it. The video referenced here is a zac brown band recording. If you try and do this with the 'official video', it won't play and will make you go watch it on the site.

author: Roy Ashbrook | posted @ Wednesday, June 02, 2010 3:37 PM | Feedback (0)

How to setup blogging to subtext from word2007


Specifically, how to set this up for OUR blog =P Thanks to Dennis and this article for the info. These are steps for someone that doesn't already have a blog setup in word 2007. I will assume if you do, you already know enough and the settings/info from this will be enough.

  1. Click new document and choose 'Blog Post'. The following screen will come up. Click 'Register Now'.

     

  2. You select 'Other' from the Blog drop down and click Next.

     

  3. This is where the fact that we have multiple blogs appears to differ from the instructions in the link above. I filled out the below screenshot using those instructions and the following results in a failure. You have to give the full url path to your blog if you have a blog below the main site. So the url for the blog post URL for my blog is http://www.drowningintechnicaldebt.com/royashbrook/services/metablogapi.aspx, not just http://www.drowningintechnicaldebt.com/services/metablogapi.aspx. It's pretty easy to test though. If you go to the url, you should see a method screen similar to the second screen clip. Put in your username and password and click OK.

     

  4. You should see the following success screen. If your username is wrong or anything like that, you'll get a popup that says it can't register it or something similar.

     

  5. OK, finally, I will note that I followed the advice on codingmonk and also clicked 'Picture Options' in step 3. The default picture provider was 'blog provider' so I just clicked OK and moved on. I don't know if that's necessary, but I did it as well.
  6. Enjoy posting from Word 2007! =D

author: Roy Ashbrook | posted @ Wednesday, June 02, 2010 11:00 AM | Feedback (0)