sql lost

TO THE FACE!

Lat/Long datum conversion

Latitude and longitude coordinates are not quite as universal as we tend to think. A given coordinate pair gives a position on a virtual grid that has been draped over the globe. As surveying techniques have advanced the US has been re-surveyed a number of times. Because a new survey will never perfectly match the old one the virtual grid distorts and shifts slightly.

The two datums that are frequently used as references for coordinates are North American Datum 1927 and 1983 (NAD27 and NAD83 respectively). The distance between identical coordinates in the two datums varies from 10 to 100 meters in the contiguous US (see map in Wikipedia article linked above).

The National Geodetic Survey has a very handy tool here that will convert between NAD27 and NAD83. The website accepts individual coordinate entries or a text file of points to convert.

Data Visualization

A very nice summary of some basic data presentations. And a few less common ones too.

A Tour through the Visualization Zoo

Directional Drilling 101

Drilling directionally is a good deal more complex that a traditional vertical well drilling and includes alot more math and fancy tools to determine where in 3-D space the borehole(s) are. For a quick tutorial check this out! www.ogs.ou.edu/highlightsfiles/HDRILLChap4DirDrill.ppt

msdb cannot be opened. it has been marked suspect by recovery

SQL SERVER 2005/2008

So you go to use SQL Server one day and open up a query window and suddenly get this message:

msdb cannot be opened. it has been marked suspect by recovery

Lovely.

Assuming you have backups of your system databases (like we all do *of course*) you could just restore a new database over the mdsb one.

Well - in my case that wouldn't work. Still the same error kept popping up.

Then I tried to rename the msdb files and create a new msdb...here's how\
0. Shut down your database.
1. Since you cant detach the broken dmsb you have to start sql server with flag 3608. So for 2005 statedown SQL Server and run this command:
C:\Program Files\Microsoft SQL Server\[Instance Name]\MSSQL\Binn> start sqlservr.exe -c t3608
For 2008 run C:\Program Files\Microsoft SQL Server\[Instance Name]\MSSQL\Binn> start sqlservr.exe -c t3608 -s [Instance Name]
2. Now rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the C:\Program Files\Microsoft SQL Server\[Instance Name]\MSSQL\Data directory
3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\[Instance Name]\MSSQL\Install directory
And in theory...4. Shutdown and restart the server without the 3608 trace flag

Well for me I got errors when I ran then instmsdb.sql file.

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

User does not have permission to alter database 'msdb', the database does not exist, or the database is not in a state that allows access checks.

Lovely.

Ok so after spending way too long on this, I decided that maybe my a thread of luck I could patch SQL Server and it'd come back to live...but nope. So don't try that.

Then I got the idea to repair my SQL installation (an option with SQL 2008 in the SQL Server Installation Server) - but then soon realized I had to have the install disk which I didn't have. So that I idea was wrong.

SOOO! (Yes there is an end to all of this) -

On a site linking to msdn http://msdn.microsoft.com/en-us/library/dd207003.aspx

WAS THE ANSWER!!!!!!!!!!!!!

Hurray!

Running this:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release> Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]

(the password is optional as I didn't have one - but rather my domain user account which has admin privilidges).

The output was just the MS copyright information - no errors.

Then I started my database back up and eureka! master, model, msdb, and tempdb were there! Now all of the other databases had been detached but that's no worry considering I couldn't log in a few hours ago.

So that's that...and let's hope you never have this happen to you!

Handy tool for MS Access developers

Anyone who's spent much time writing queries in MS Access knows how frustrating the query editor is. Its poor feature set, unreadable font and strange, undocumented bugs render it nearly unusable for anything but the most basic query writing.

http://download.cnet.com/SQL-Query-Tool-Using-ODBC/3000-10254_4-10057486.html

I started using this tool earlier this week and have been very pleased with the results. However, a word of caution... when using this tool against MS Access databases, normal Access SQL syntax will not work... strings must be single quoted (not double quoted) and the wildcard characters are _ and % (not *). There may well be other SQL differences I haven't uncovered yet.

I don't know exactly why this is the case but it's not specific to this tool... any 3rd party access to MS Access databases encounters the same problem.

WPF Toolkit install issues on Windows 7

When trying to install the latest wpf toolkit release for windows 7, there is an issue apparently with the naming of .net 3.5 sp1 that causes the toolkit msi to think .net 3.5 sp1 is not installed (when it is). To fix it, I came across this post http://peterblomqvist.blogspot.com/2009/12/installing-wpf-toolkit-june-2009-on.html, which outlines a way to remove the launch condition thus enabling the toolkit to install!

C# (.NET actually) for C++ programmers: static variables

The Problem

Surely I'm not the only programmer that has just started C# without formal training or a book or something. Perhaps there's other out there that also come from a C++ world. Static variables can be used for all types of design patterns other than a singleton which seems to be the only thing msdn can imagine a static variable to be used for.

Maybe we want a global thread-safe index generator:

volatile long MyClass::s_current_idx = 0;

Or perhaps there's something more complex we wish to accomplish

class MyListener
{
public:
    MyListener(unsigned short port);
    ~MyListener();

    unsigned short port() const;
private:
    int m_sockfd;
}

Let's pretend this is a class that just accepts tcp connections at the specified port. Then we have:

class MainProgramThingy
{
public:
    MainProgramThingy();
    ~MainProgramThingy();

    bool pingListener() const;

private:
    void makeSocketConnection(const char *server, unsigned short port);
    void closeSocketConnection();

    static MyListener s_Listen;
}

And of course, the definition:

MyListener MainProgramThingy::s_Listen(8080);

MainProgramThingy::MainProgramThingy()
{
    ...
}

MainProgramThingy::~MainProgramThingy()
{
    ...
}

bool MainProgramThingy::pingListener() const
{
    bool rt = !makeSocketConnection("localhost", s_Listen.port());
    if (rt)
        closeSocketConnection();
    return rt;
}

void MainProgramThingy::makeSocketConnection(const char *host, unsigned short port)
{
    ...
}

void MainProgramThingy::closeSocketConnection(const char *host, unsigned short port)
{
    ...
}

Great! Now in theory, right when the application starts up, a tcp port is opened to 8080 and there's a MyListener ready to accept connections. You don't even have to touch MainProgramThingy. You can just connect with telnet or something and it'll partially work (provided the '...'s are actually implemented properly. If you expand on the example more and kick off a thread that actually accept()s connections and handles them, you're golden.

.NET can make a lot of this concept easier (we'll just assume you MUST open this port and do some custom stuff with it) with System.Net stuff. However, go ahead and implement the above and see if it works as intended... Specifically, try to start up an application that doesn't reference MainProgramThingy outside of the above. No socket will be opened. No MyListener will even be constructed. But you designed it such that the lack of a reference wouldn't matter, the tcp listener should still be there.

The Explanation

So it turns out the .NET CLR has this cool "smart" feature when it comes to static variables. The .NET CLR figures out when to initialize static variables and does it on demand. There's some tweaks that you can apply that will influence this. For example, you can set it up so that when it enters a function, it checks and initializes all static references that *might* be accessed in that function. Or you can set it up so that it only checks and initializes upon access (This might only actually work in the .NET runtime version 4.0). In either case, the above wouldn't work without an explicit reference to MainProgramThingy at runtime.

Of course, there's a reason for this. And most C++ programmers already know what it is. C++ provides nothing in any specs about the order to initialize static variables in. Of course, logically, in the same code file, they would be initialized in the order they are defined. But what if you have 2 code files that are joined at link time? Which one is initialized first? gcc at least used to do it just based on the order the .o files are passed into the linker at link time. But that's just gcc. There's no guarantee that another compiler or a more current version of gcc won't do it entirely differently.

This can be a major problem when you have a static initializer in filea.cc that accesses a static member in fileb.cc. You could just end up with corruption all over the place on account of fileb.cc's static variable not being initialized before it's accessed by filea.cc. This can be even harder to track because the memory is already allocated on startup for fileb.cc's static var. It's just not set yet. This means no access violation or valgind memory error (except potentially a read of uninitialized memory, which are sort of littered throughout anyway from more basic static access) - just some problems cascading down the line to pop up later during a run. This concept is demonstrated in the static initialization fiasco scenario.

With the .NET way, the spec is that static variables are initialized on an as-needed basis. This is great because the programmer doesn't even need to worry about static initialization problems other than the blatantly obvious scenario of circular dependency, which would be a logic error on the programmer's part. But in solving this problem, .NET introduced another one; namely, the one stated above.

The Solution

In the case of .NET architecture, static methods are not built into a static library with a fixed address based on the library load - none of your code gets executed merely by loading an assembly. Static members are actually attached to the Type itself. As soon as the type is initialized, static methods are. Types are initialized on demand per the dynamics above. So now they are pseudo-statics in my mind. Initially, a static meant static - a single instance fixed in place in this global universe... this implemented on the mechanics of a library being loaded. But in .NET, they are explicitly a part of the Type metadata which is a .NET CLR-specific thing. So while they operate on a similar paradigm, a .NET CLR static is not the same thing as a C++ static member.

There is no great way around the gotcha that makes it all work like C++. But if you want to initialize the type without accessing an object of it's type, there is a static method in the .NET libraries that will get that done for you:

RuntimeHelpers.RunClassConstructor(typeof(MainProgramThingy).TypeHandle);

This will initialize the type thereby initializing the static members. I was hoping to find something to trigger some code on an Assembly load. I guess such is not possible. So the only workaround I've found is to call the above in your entry point.

Good, Free Anti-Spyware Software

Every few years, my Windows machine comes down with something nasty, resulting in a frustrating couple of days involving lots of safe mode and picking around in my registry, etc.

A few years ago, I started researching and using commercial anti-spyware programs as part of my dis-infection repertoire. For a while, my favorite has been LavaSoft's Ad-Aware.

In my most recent bout with the mal-ware armies, however, Ad-Aware (the free version) wasn't really up to the task. Neither, as it turns out, was the Symantec software that came with my computer.

What did the trick? SuperantiSpyware

Nice interface, lots of scanning options, and most importantly, it cleaned up my system... good as new!

10 things to remember when using SSIS

1) Variables are case sensitive
2) (when using 2005) Whenever you are making a script from the script component and typing in your variables, don't put a space between them
3) Make sure you assert correctly at the end of a script (Success of Failure)
4) Save often. VS crashes at the strangest times
5) Make sure your variables are at the right scope. You can spend hours trying to figure out why "TotalRowCount" isn't at the parent scope....
6) BIDS Helper does wonders for moving variables around created in the wrong scope (admit...we all do it) www.codeplex.com/bidshelper
7) Even though it may take more time at the beginning - try to put as much error handling in the packages that you think you're going to need. It's tedious and ..well it sucks to go back through your packages adding in extra stuff (although - you will eventually do it someday - development lifestyle).
8) If you try to edit the XML of the package directly, make sure you make a backup of the package first. The smallest mistake could corrupt the package and you don't want to be left with the task of writing a new package
9) Make sure visual studio and SQL Server are patched. The patches generally fix some of the annoying errors that occur
10) Make the transformations in your package as few as possible.

Access Float -> Int Bug/Feature

This one's a doozy. Not so much because it's there... but rather because it's actually DOCUMENTED. Look up the help in Access 2003 for either the Cint() or the CLng() Type-conversion functions and scroll down to the 4th paragraph under 'remarks'.

When the fractional part of the argument passed to CInt or CLng is exactly .5, those functions round to the nearest EVEN integer. So 0.5 gets rounded DOWN to 0, but 1.5 gets rounded UP to 2. I have tested this and witnesses the behavior myself, but I won't blame you if you test it yourself before believing me.

Invictus

OUT of the night that covers me,
Black as the Pit from pole to pole,
I thank whatever gods may be
For my unconquerable soul.

In the fell clutch of circumstance
I have not winced nor cried aloud.
Under the bludgeonings of chance
My head is bloody, but unbowed.

Beyond this place of wrath and tears
Looms but the Horror of the shade,
And yet the menace of the years
Finds and shall find me unafraid.

It matters not how strait the gate,
How charged with punishments the scroll
I am the master of my fate:
I am the captain of my soul.

--William Ernest Henley

Nice code site

Who wouldn't love a site that codes the lyrics to "99 Bottles" 
in 1290 different coding languages!

http://99-bottles-of-beer.net/

The MS SQL 2000 sample below was provided by David Teviotdale:

select
   CASE (a.aa * 10 + b.bb)
       WHEN 0 THEN 'No more bottle of beer on the wall, no more bottles of beer. ' +
                  'Go to the store and buy some more, 99 bottles of beer on the wall.'
       WHEN 1 THEN '1 bottle of beer on the wall, 1 bottle of beer. ' +
                   'Take one down and pass it around, no more bottles of beer on the wall.'
       WHEN 2 THEN '2 bottles of beer on the wall, 2 bottles of beer. ' +
                   'Take one down and pass it around, 1 bottle of beer on the wall.'
       ELSE
           cast((a.aa * 10 + b.bb) as varchar(2)) + ' bottles of beer on the wall, ' +
           cast((a.aa * 10 + b.bb) as varchar(2)) + ' bottles of beer. ' +
           'Take one down and pass it around, ' +
           cast((a.aa * 10 + b.bb)-1 as varchar(2)) + ' bottles of beer on the wall.'
   END
from
   (select 0 as aa union select 1 union select 2 union select 3 union select 4
   union select 5 union select 6 union select 7 union select 8 union select 9) a
cross join
   (select 0 as bb union select 1 union select 2 union select 3 union select 4
   union select 5 union select 6 union select 7 union select 8 union select 9) b
order by a.aa desc, b.bb desc

Hacking Excel

So I know there's a way to do this better...but here's a hack for Excel.

If you're looking to get the first sheet (regardless of name) into your little program...check this out

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile
+ ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

conn = new OleDbConnection(strConn);
conn.Open();

//get data in first worksheet
cmd = new OleDbCommand("SELECT * FROM A1:IV65536", conn);

The A1:IV65536 can be replaced with any range that you know your data is in. If you're wanting just to look for data on a worksheet (gotta know the name of the sheet), you'd do soemthing like :

"SELECT * FROM [HulkSMASH$]A1:C65"

Happy Excelling!

pattern matching with PATINDEX() and wildcards in T-SQL

The SQL Server patindex() function is useful for doing limited pattern matching in SQL selects. Here are some useful links concerning patindex() and wildcard usage in T-SQL:

http://msdn.microsoft.com/en-us/library/ms188395.aspx - patindex() ref page with examples
http://msdn.microsoft.com/en-us/library/ms187489.aspx - nice wildcard usage examples
http://www.devguru.com/technologies/t-sql/7116.asp - more wildcard examples

While you don't get full regular expressions, the wildcards combined with patindex() do provide some amount of useful pattern matching in SQL select statements.

Iphone Protection

So by accident I found a great way to keep your iphone protected from the elements. I was out for a ride on the Motorcycle and was worried that it might rain and mess up the Iphone if I got it wet.

I grab a standard ziplock baggie from the cabinet and tossed my iphone in that. To my surprise all the touch features work perfectly through the bag without haveing to take it out of the baggie to check messages and emails. This could also work for being out on a boat, near a pool or around kids playing with water guns Etc. along with being completely water proof it doesn't take up much space.

I now keep a ziploc bag in each of my motorcycle jackets just in case it looks like it will rain on me while I am out and about.

iPhone Tethering

Well, it's summer time! Time to sit by the pool while you code away getting a tan. What!? Your pool doesn't have WAP!? No problem, grab your sunscreen and read this article sent to me by Dan, you'll be online in no time!

Simple Hack to Enable Tethering on iPhone OS 3.0

Bach's Little Fugue in G Minor

Because I like math, and so do you:

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

REVISITED: SSIS 0x8000FFFF Catastrophic failure

A while back I posted about SSIS 0x8000ffff Catastrophic Failure.

Well after doing everything on the list I posted about (which fixed my problem once mind you)...the issue came back. When I ran the package individually, it ran just fine. But when I ran it from the master package - it crashed with the 0x8000FFFFF error.

There's not a lot of data inside the error message that SSIS gives you so I started messing with stuff that could potentially cause something that SSIS would call "catastrophic".

I looked at the isolation and transaction levels of each component, retained connections of my connection managers. Master package variables, etc. Nothing I changed made the problem worse or better (ok I did make it worse but that's not important).

Finally - it occured to me to check the script that I have in one of my components. My package looks as follows:


First I get some data - then insert it using an OLEDB Destination (This is used over the SQL Server destination so I could have row by row error handling whereas the SS Destination doesn't allow for that). Then I have a script that puts my error rows into a string. I then grab some extra package data and insert the rows into my error database.

Pretty common practice ... except on my Get Error Data step...

Here's what it looked like before whenever the catastrophic error was happening:



And then after I fixed it:


So pretty much what was happening is the variables, for whatever reason the code seems to be stepping over itself and erroring - whereas if you stick it in the try/catch block, nothing chokes and it's nicely handled in the catch block.

So if you get this error - make sure you have your script component nicely coded and error handled.

Symantec LiveUpdate Failure

Ever get a REALLY unhelpful error message from Symantec's LiveUpdate? Something along the lines of "LiveUpdate Failed. You should run LiveUpdate again later. If it still fails then, it's broken."

Well, if it gives you an error code, and if that error code is LU1803, then you might be tempted to google that error code.

And if you're a paranoid conspiracy theorist like me, you might be suspicious of some of the recommended 'fixes' in the first few google results.

But the paranoia is unfounded... You really SHOULD delete the files... all the liveupdate settings files and the contents of the liveupdates downloads folder... Then re-run LiveUpdate and all will hopefully be well.

Or maybe I'm just in cahoots with the authors of all those 'helpful' links google gives you...

muAHAHHAHAHHA!

No, just kidding... It really worked for me.