Wofda, LLC

sql lost

2011-03-01T14:14:00.002-07:00

TO THE FACE!

Lat/Long datum conversion

2010-09-20T10:13:00.005-06:00

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

2010-06-25T07:48:00.003-06:00

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

2010-05-19T14:39:00.003-06:00

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

2010-05-14T10:33:00.001-06:00

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

2010-04-09T11:49:00.002-06:00

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

2010-03-30T15:34:00.002-06:00

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

2010-03-11T10:23:00.013-07:00

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

2010-02-15T11:19:00.002-07:00

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

2010-01-14T10:25:00.000-07:00

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

2010-01-04T14:12:00.002-07:00

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

2009-11-30T21:03:00.000-07:00

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

2009-09-17T15:08:00.006-06:00

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

2009-09-16T15:28:00.002-06:00

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

2009-08-24T10:59:00.003-06:00

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

2009-07-24T14:36:00.002-06:00

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

2009-07-06T13:09:00.002-06:00

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

2009-07-03T14:41:00.002-06:00

Because I like math, and so do you:

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

REVISITED: SSIS 0x8000FFFF Catastrophic failure

2009-07-02T12:09:00.004-06:00

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

2009-06-29T09:22:00.002-06:00

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.

Hidden Cells in Excel

2009-06-29T09:12:00.002-06:00

Ever have a pesky cell in an Excel sheet that you can't seem to select, even when the rest of its row and column are visible and behave normally? It's most likely "merged" with an adjacent cell. To un-merge it, highlight a block of cells around the one you can't get to, right click and choose format cells (ctrl 1), go to the alignment tab (a) and un-click the 'Merge Cells' (alt m). You may have to click the 'Merge Cells' box twice to get it un-checked. This will un-merge all cells in the selected region.

Livin' in a Multi-Core World - Memory Models

2009-06-24T06:44:00.022-06:00

I had a conversation with Will and Phil recently that inspired this whole "Series." It was focused on Memory models in multi CPU environments more than anything, but I figured atomic operations would be a better starting point. This post ignores SPARC entirely, unlike my last one. This is really just looking at Intel-based architectures.

Memory access models have been on the minds of a small subset of programmers for a long time. It has always been something to consider in developing architectures on multi-CPU real-time systems. But now, with the advent of multi-core systems that don't use a common memory access model, it ought to at least be on the minds of all programmers.

Intel Itanium and Nahalem (new Xeon-based Mac Pro systems + Core i7) processors as well as AMD Opteron and AMD Athlon 64 post FX-70 (Quad FX) processors use a NUMA model for accessing memory. Presumably, the new Intel Sandy Bridge (To be released in 2010 some time) processors will too but that's just an assumption on my part. NUMA stands for Non-Uniform-Memory-Access. In a NUMA architecture, memory is physically "mapped" to specific CPUs. So in a quad-CPU NUMA setup with maxed out memory, each CPU has 1/4 of the memory mapped directly to it basically. In traditional UMA/SMP based models, the CPUs are separate from the memory controller and they all have equal access to the entirety of system memory.

In the SMP model, things are a lot easier to code, but there is a performance hit when several CPUs are operating on the same regions of memory. Each CPU has to keep its Cache in sync with what's actually in memory. So if CPU A has a segment of memory cached and CPU B goes and modifies it, CPU B has to tell CPU A, "Hey, I modified your cached memory" and CPU A has to go and update its cache or in real life, mark it as invalid. That means that if CPU A wants to access some part of the memory segment, even if it isn't the part that CPU B modified, it will have a cache miss and will need to read from physical memory again to update the cache.

In a NUMA architecture, CPU A would "own" that segment. CPU B would have to go to CPU A and say, "Hey, I want to do such and such to your memory, can you take care of that for me." And CPU A would oblige. There is an inherent disadvantage to this model too though. No implicit cache misses will be occurring, but now CPU B is consuming some of CPU A to do it's own thing. If CPU A is busy working on that segment of memory, CPU B could be starved for access to that memory. But even without starvation, why would CPU B be accessing CPU A's memory if CPU A isn't even USING that memory? This can and surely does happen ALL the time. Here's a more common case:

Process A is running on CPU A for a while, it allocates a bunch of memory from CPU A's memory bank. A context switch happens. Another process gets some of CPU A to do what it needs to do. Meanwhile Process A is sitting there waiting to continue on with its execution. Ah! good thing CPU D is idle now, so the OS hands CPU D over to Process A. Well, now Process A, running on CPU D needs to access memory that is on CPU A's banks. Every memory access needs to be put through CPU A and is way slower than the SMP model above. That stinks!

To avoid that scenario, we use Thread/Process Affinity to ensure that Process A stays on CPU A. That way, it's only accessing its own memory. But a programmer starts caring when we are working in a multi-threaded environment and different threads are working on different CPUs. Besides just binding specific threads to specific CPUs, shared data needs to be "allocated" on the CPU of the thread that is accessing it the most (well, it doesn't need to, but it should). Additionally, the whole thing should have an architecture such that as little cross-CPU memory access happens as possible. That way, you can have faster average access time of physical memory as SMP (because of the local memory controller, access to local memory is faster than SMP is to any memory), without the cache misses.

That's all pretty simple when you control the allocation of memory. On newer Linux kernels, when NUMA support is compiled in, a request coming from userspace into the kernel for some allocated memory will result in memory taken from the bank of the cpu from which the request originated (will be the same CPU that is executing the request of course), unless there is none available in that bank. I don't actually know if this is the case on Windows, but given the ability to set Thread affinity (thanks Mike for pointing that out) in Vista, I would presume it to work the same way. So in these scenarios, where ever your data is malloc'ed or new'ed is where your memory is allocated. Even if I'm wrong on Windows, Vista and Windows 7 introduce a number of new NUMA specific calls. For example:

VirtualAllocExNuma, CreateFileMappingExNuma, MapViewOfFileExNuma, GetNumaProcessorNode, and AllocateUserPhysicalPagesNuma

which can be used in place of malloc and an overridden static operator new()

Things get a bit more difficult in Managed .NET code. In .NET, all memory is managed by the garbage collector in a managed heap. Presumably, the Managed Heap is a single pool or collection of pools under the hood. This means that the .NET programmer has no way of controlling where in physical memory something gets allocated. Since something on CPU B could be using a pool in the managed heap that was allocated in CPU A. Even more of a concern is the fact that objects can and often are moved around in physical memory to optimize memory usage of the .NET runtime. The garbage collector also does it's thing on a dedicated thread, so cleanup may be occurring on an entirely separate CPU. Therefore, memory allocated in CPU A by the .NET runtime may not always remain in CPU A's banks. So I end this one with a question: How do you force the Runtime / Garbage collector to allocate and keep an object on a specific NUMA bank? (We'll just have to live with collection occurring on a separate thread and most likely, a separate CPU).

Livin' in a Multi-Core World - Atomic Operations

2009-06-24T05:30:00.013-06:00

The CPU market is now such that you can't even buy a single core x86 based processor any longer. While multi-CPU systems have been around for a really long time, consumer targeted apps were generally ok just pretending that wasn't the case. But now, doing so isn't so bright. A common pitfall of many programmers is to assume that 1 line of code = 1 operation. For example (in C++)


class RefCounter
{
public:
    RefCounted()
    {
        addReference();
    }

    void addReference()
    {
        refs++; // <------- here   
    }    

    void removeReference()
    {
        if (!--refs) delete this; // <----- or here
    }  
private:
    ~RefCounter()
    {
    }
    int refs;
};

Ignore the pitfall of this example that makes for a glaring crash when a stack allocated RefCounter is created. That's not part of the demonstration. Instead, let's look at refs++; . Dissecting that call, we find that a processor (pretty much ANY processor) will do the following:

1. load refs into a CPU register
2. add 1 to the CPU register that refs was loaded into
3. write refs back to the memory location from which it was loaded

So that's all fine and well. But what if you had a single shared object accross several threads that were all reference counting the same reference counted object. Let's say they are both modifying the count at almost the same time. Perhaps:

Thread 1: Load refs into CPU register
Thread 1: add 1 t0 register
Thread 2: load refs into CPU register
Thread 1: write refs back to memory
Thread 2: add 1 to register
Thread 2: write refs back to memory

We just wrote the same value twice and refs has incremented by 1 despite having addReference called twice.

In a single core environment, on most OSes this was almost never a concern. In a single core environment, multi-threaded isn't really multi-threaded. The CPU munches on thread A for a bit, switches contexts, and munches on thread B, switching from one to the other. The OS controls when those switches occur. But in those cases, Thread 1 and 2 aren't actually running at the same time. In multi-core environments, threads actually do run in parallel. And just because your using something higher level like .NET doesn't mean you're safe either. This exists across the board.

So to deal with this, we need to look at lower level Atomic Operations. First, for demonstration purposes, we'll examine a RISC CPU to show how this is done.

the newer (not that new) SPARC CPU instruction set defines the instruction 'cas' (Compare and Swap). cas takes 3 operators. The first one is the memory address to read from/write to, the second operator is what to compare to, and the third is the register that has the value to swap in (upon positive comparison).

for example (oh, this is assembly by the way):
mov 12, %o0
cas 0x12345, %o0, %l0

This will basically read the value from memory address 0x12345 and then, in a single operation, compare it to the value in register %o0. If the two match, %l0 and the memory address are swapped. This means that in the above example, if %o0 has a value equal to what is stored in 0x12345, %o0 won't change, and the value at 0x12345 and %l0 will be swapped. So using this, an atomic add might look like:


   ld (%o0), %l0
1:
   add %l0, %o1, %l1
   cas (%o0), %l0, %l1
   cmp %l0, %l1
   bne,a 1b
   ld (%o0), %l0

   add %l0, %o2, %o0

So to walk through what that does:

Load the first argument into local register l0
Add the second argument to l0 and store the result in l1
Compare and swap what's in memory to what I loaded before and store the added value.

This means that if the value hasn't changed since I loaded it, %l1 will have the same value as %l0 which was read from the memory address specified in the first argument.
compare %l0 and %l1
if they don't match, (execute the ld again) and start the add over
otherwise, return the added result (via the first output register)

Probably more than most people will ever need to know because who writes Assembly on SPARC really :-D?

Ok, so that was just to demonstrate the point of an atomic operation. Intel makes things way easier (btw, as a UNIX coder by nature, I'm proned to using AT&T asm syntax rather than the MS syntax, convert accordingly)


lock xaddl %ebx, [%eax]

You could go the RISC way on Intel too, but that is unnecessarily expensive. This basically does a read-add-write in a single bus operation, locking down [%eax] for the duration of the r-a-w. This is still way more expensive than a plain old add, which is why your compiler doesn't make ALL of your additions atomic.

The x*[blq] set of operations on intel are called "interlocked operations" or something like that. I only mention that because Microsoft software is based entirely on Intel (having been founded on the ability to handle over 640K of memory on the otherwise limited intel architecture), even if they have expanded themselves into the realm of ARM and other architectures (which is why they incorrectly call x86 with 64 bit instructions x64 rather than x86_64 or x86w64 or some such thing and not have to worry about the possibility of actually having to support an architecture called x64). The results of this relationship still show themselves today.

System.Threading.Interlocked is the class with all of the static functions you need to perform the above in .NET. System.Threading.Interlocked.Increment(ref v) will take care of the atomic assembly needed to perform the above. Take a look around the class and make sure to use those when you are doing simple operations on data that need to be thread safe. The alternative would be to mutex lock everything but that is more expensive by a factor of at least 50, usually more. For quick numeric operations that need to be thread safe, System.Threading.Interlocked is the way to go.

Refrac.net - David Craig

2009-06-22T14:00:00.001-06:00

A great resource for understanding how to identify (and treat) refrac candidates, as well as regarding the "old oil" segment of the industry in general:

http://www.refrac.net/

Highly recommended.

SSIS Update, Insert

2009-06-16T11:46:00.007-06:00

There are a few schools of thought about updating in SSIS seen here, here, and here.

All three approaches are innovative given the problem that was presented, updating a row in a database. Given there are millions of rows in a database though, even thinking about using the Lookup transformation to see which rows match is absurd. And yes I crashed BIDS a few times before I realized this. So thinking for a bit, I came up with a new way to update rows in a database that has millions of rows in each table.

The idea behind it is only touching the rows that you REALLY need to touch. Wash a used cup of coffee in the morning you don't touch your ENTIRE connection of china and silverware to do so nor do you go look through all of your clean and dirty cups to find which are dirty. You know that!. You just go grab what you need. Maybe in the first go around you are trying to wash cups and spoons. You try to grab a cup and a spoon. You get the cup but grab a spork instead. What to do with that?! It looked like a spoon but really wasn't. So you put it in the sink to take care of it later. Sometimes though you do wash what you need in one shot. The cup and the spoon. You succeeded! Congratulations!

What's the point of that in SSIS? Well first we try to bulk update the rows. If that doesn’t work grab the rows that you are trying to update (generally a select statement from a variety of tables) into a temp table (SELECT cup, spoon INTO ##KitchenTemp FROM Kitchen), Then go through each row and send them through the update row OLEDB Command transformation. If this fails then the error rows are pumped to an error database (this is something to later be discussed). Then, although SQL will take care of cleaning up your temp table, dropping it yourself just to be clean. After this is done, then any new rows can be inserted to the table.

So how to do this...

Create the following data flow transformations on your project. “Update Existing Info”, “Put rows to be Updated”, and “Drop temp table” are Execute SQL Tasks. “Update” is a Data Flow Transformation (DFT).

The purpose of the Update Existing Info is to do a bulk insert of all of the data. Row by row updating is a spendy operation and should only be done to catch bad rows that don’t update.
In the “Put rows to be updated” transformation, here goes your sql statement to put the data that needs to be updated into a global temp table.

The connection manager of this item must match the connection manager on both the Update DFT and the Drop Temp Table SQL Task. It’s property must be set to RetainSameConnection = True so that the temp table doesn’t go away when the package moves steps. (Temp tables are only visible during that session)

"select <<<>>>>
INTO ##MyTempTable
FROM
MyDatabase.dbo.MyTable x
LEFT JOIN <<<>>>
JOIN MyDatabase.dbo.MyTable w on w.Field1 = '09' AND w.Key1 = x.Key1"

Modify the “Drop temp table” transformation and change the temp table name for it’s SQLStatement to be DROP TABLE ##MyTempTable.
Again, the connection must be the same as the connection with RetainSameConnection=True.
Create a “SSIS Update String” for the package for the Update DFT. Example is as follows:

UPDATE MyTable
Set Name = ?, Address = ?, County = ?, Phone = ?
WHERE Key1 = ?

Paste it in the OLE DB Command part of the Update DFT It is on the component properties tab and the “SQL Command” at the very bottom. Nothing else needs to be done with it.
Go back to the control flow and put a breakpoint at the Update step.
Make sure that the DelayValidations are set to TRUE on the Update sequence container)
Hit play
Make sure that the Bulk update works

Hit Stop
Disable the UpdateExistingInfo bulk updater task.
Change the red line to a blue “On Completion” line
Hit Play
The package will run until it hits the “Pre Execute” of the “Update” Data Flow.
After it hits the breakpoint go into the data flow.
Double click on GetNewData in the Update DFT
Change then input method to be SQL Command
Make the command be SELECT * FROM ##MyTempTable
Click OK
Hit stop.
Double click on the OLE DB Command
Click on column mappings
Map each column as you defined it in your query. Param_0 is the first field in your query, Param_1 is the second and so on. It is important everything lines up just right.

Click OK
Only check the components that you use in the mapping above. DO NOT INCLUDE ANYTHING ELSE.
Remove the breakpoint

Enable the Bulk update task and change the blue line back to red so your package will then operate as intended.

Green boxes are your friend...and keep those cups clean - coffee is a necessity!

gmail - use it wisely

2009-06-12T16:26:00.001-06:00

Search operators for searching your gmail are really really useful. To me much of the point of gmail is being able to use google to search your own personal mail. Here's some basics about available operators:

Using Advanced Search

In particular, I use has:attachment all the time.

Another interesting SQL Site

2009-06-06T14:24:00.004-06:00

Our friend Eric has put up something pretty interesting about sql metrics, which is a recurring topic around the office. Worth clicking around a bit.

http://www.sqlmetrics.net/home

Check it out!

SSIS 0x8000FFFF Catastrophic failure

2009-06-03T11:21:00.003-06:00

Ever get this error using SSIS. Let's hope not because it's not very helpful and makes you wonder WHAT was catastrophic about it.

More is described below (and in this article).

This problem occurs with the 3.70.0690 build of the Microsoft SQL Server ODBC driver (Sqlsrv32.dll) but does not occur with earlier builds. Also, the problem does not occur when you use the native Microsoft SQL Server OLE DB Provider (Sqloledb.dll).

The Microsoft SQL Server ODBC driver is loading the Winmm.dll file for timing functions. While Winmm.dll is primarily a multimedia DLL, it contains some timing functions that resolve down to the millisecond level, and the Microsoft SQL Server ODBC driver is using these functions for internal timing issues.

In the 3.70.0690 build of the Microsoft SQL Server ODBC driver, Winmm.dll is being unloaded prematurely, and the function pointers are not being zeroed out. Therefore, the function pointer appears to refer to a valid address but the location it points to in memory is no longer valid.

This access violation (AV) is being caught by the exception handling in the OLE DB Service Components, and an 0x08000FFFF "Catastrophic failure" message is then returned to the client.

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft Data Access Components service pack that contains this hotfix.

If you don't want to change your drivers, generally I just rerun the package and the error goes away.

Case Statement inside a Sum

2009-05-22T13:47:00.003-06:00

OK maybe you already knew this, but this is the most adorable SQL I've written lately:

select apistate,
sum(case when currentoperatorid is null then 0.0 else 1.0 end) / count(wellid) as opdensity
from well
group by apistate

Gives you percent filled in for current operator. Cute, yes?

Microsoft.Windows.CommonLanguageRuntime

2009-05-21T09:51:00.001-06:00

Ever get this error in MS Vis Studio 2005 when compiling a solution that has multiple projects??
(lets hope not but if you do...)

error MSB3113: Could not find file 'Microsoft.Windows.CommonLanguageRuntime, Version=2.0.50727.0'

I freeked out becauses I thought somehow my CLR got uninstalled ... but how! My computer had been hibernating all night.

Well apparently it's a known bug ... MS describes the work around here here .

Pretty much if you're referencing a dll file in one of your projects - change it to reference the exe file instead and you'll get a successful build.

Cheers!

How to Get Row Counts From Your Database

2009-05-17T20:47:00.010-06:00

Currently, our team is working on an ongoing data loading project. After each iteration of data loading is complete, we like to take time to evaluate how well the data load went. One of the techniques we employ is getting row counts for each table in the database we are loading.

Sounds like a relatively simple task. Just create a sql query with a “SELECT COUNT(ID) FROM <table name>” query? In essence it is that easy. However, there are scenarios that make this simple task burdensome and difficult to maintain. For instance, what if your project not only is loading data but is also developing the schema in parallel? Another scenario might be if you have hundreds perhaps thousands of tables. Finally, if you have both of the aforementioned scenarios and you are promoting your database from development to QA, then from QA to staging and then staging to production, suddenly it is full time job just trying to keep up with the most mundane of tasks!

The following code is a query that will automatically iterate over all of your tables in your database and provide you with row counts. The query consists of a cursor that queries the <Database>.sys.tables table. The result set of that query is then used to generate “SELECT COUNT(1) FROM <table name>” for each row in the result set as a common table expression (CTE). In addition, the query is generated to union all of the table row count queries so that the table row count result set is returned as a single result set. Finally the query that is generated is executed and the results displayed. The following code example is programmed against the infamous Northwind database.

Code

DECLARE @query varchar(MAX),
@tempQuery varchar(MAX),
@DatabaseName varchar(255)

SET @DatabaseName = 'Northwind'
SET @query = 'WITH TableRowCount([Database], [TableName], [RowCount]) AS
(
'

DECLARE TableQuery_Cursor CURSOR
FOR
SELECT 'SELECT ''' + @DatabaseName + ''' AS [Database], ' + '''' + [name] + ''' AS [TableName], COUNT(1) AS [RowCount]
FROM [' + @DatabaseName + '].[dbo].[' + [name] + ']
UNION
'
FROM [Northwind].sys.tables
ORDER BY NAME

OPEN TableQuery_Cursor
FETCH NEXT FROM TableQuery_Cursor
INTO @tempQuery

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @query = @query + @tempQuery

FETCH NEXT FROM TableQuery_Cursor
INTO @tempQuery
END
CLOSE TableQuery_Cursor
DEALLOCATE TableQuery_Cursor

SET @query = SUBSTRING ( @query ,0 , LEN(@query)-7)
SET @query = @query + ')
SELECT [Database], [TableName], [RowCount]
FROM TableRowCount
ORDER BY [Database], [TableName];'

EXEC (@query)

I hope you find this code as useful as I have when analyzing your information systems.

SSIS "HELPFUL" Errors

2009-05-05T14:28:00.003-06:00

Ever run into the very useless helpless error codes that SSIS likes to give you ... well MS tried to help out a little:

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

Unhiding uncooperative rows in Excel

2009-05-01T18:07:00.002-06:00

If you ever try to unhide rows in Excel in the usual way (select before and after rows and choose Format->Rows->Unhide) and your rows remain hidden, check to make sure that the sheet is not filtered (ie choose Data -> Filter -> ShowAll).

Setting up a "new" computer

2009-04-17T10:03:00.003-06:00

Ever reformatted your computer and had issue after issue ... well if you haven't then you are my hero. Every time i reformat I run into something new. Paul pointed me to downloading the drivers from the website FIRST before you reformat so you can have a working copy of things incase bad things happen. This saved me this time. I downloaded the video driver and low and behold i didn't have to scramble to get my computer to display in the right mode. Another recommendation he made was to install virus scanner LAST as it could block some updates (I forgot to do this and yes indeed it blocked some of the updates I was trying to make on MS Sql Server 2005). So listen to Paul people and DON'T INSTALL VIRUS SCANNER FIRST!!!

Before installing anything - when you put the install CD in (for vista) it likes to just install a fresh copy of vista over your existing copy and not reformat the disk. Let's say you DO want to reformat. Well .. put the CD in .. and reboot your computer. By default most computers are set to boot the harddrive first (makes sense)- but you don't want to do that. Most computers (not all) are set where if you push F12 it brings up the boot menu. After you get to the boot menu - select CD and then you'll be able to boot from the CD. When you're going through the steps - make sure you reformat the drives. The Vista installs don't automatically do that and I found that it's a little kloogy to find where to format them. Format = nuke everything on the harddrive and erase the indexes and do other things that get rid of your info.

Another issue i ran into was installing visual studio 2005. I have Window Vista x32 Ultimate and for the life of me it wouldn't install the SP1 for Vista that you need to install for it to run. I kept getting this error:

WELL! Low and behold after much tears (rather frustration and using up my entire dictionary of 4 letter words) ... the solution found me. You MUST install SP1 (normal) before applying the VS 2005 SP1 for Vista patch. The Vista patch patches the patch (makes no sense) but that's how it works.

Also one more thing -

Since Vista likes to lock you out of things - I would recommend running everything as an administrator. You can set this by right clicking on the application shortcut (I chose VS2005) Go to Properties. Then there is an advanced button on the Shortcut tab. Click that. And then check the "Run as Administrator" box. This way you can get around the blockage that vista puts on "normal" user accounts.

Finally ... UPDATE YOUR COMPUTER. It took me about 4 rounds to get it to finally stop updating and restarting but it finally stopped.

Right now Mr Computer is running like a champ!

eq ne ==

2009-04-16T22:08:00.002-06:00

Here's a little Perl gotcha that'll make you experienced Perlers laugh at me. The good news is it took me less than a day to figure it out!

The Perl comparison operators "==" and "eq" are NOT the same. "==" is for use with numeric values, while "eq" is used for strings. Confusing them in a strongly-typed language would be a minor (and quickly caught) bug, but in Perl's fast-and-loose typing paradigm, confusing them can result in code that runs but does something very different than what you intended.

Play around with comparisons of explicity type-cast numeric and string values using both operators and you'll see what I mean - or just play it safe and stick to "==" and "!=" when you're comparing numerics and "eq" and "ne" when working with strings.

Uninstalling something that wont uninstall

2009-04-16T18:04:00.002-06:00

Ever been installing programs and then run into one that just hangs ... and you're stuck with a half installed program that you can neither install nor uninstall...

WELL!

Microsoft apparently ran into this too because they created this nifty little uninstaller tool here: http://support.microsoft.com/kb/290301

You just install it - click on the program that you need to get rid of and wellah!

Aggregating Booleans in SQL

2009-04-10T15:29:00.002-06:00

I've bumped into this now and again, but never bothered to figure it out. Today Trent and I really did need an answer. Or we could have simply decided to think clearly.

To do an aggregate or do this:
MAX(CASE WHEN boolean expr THEN 1 ELSE 0 END)

aggregate and:
MIN(CASE WHEN boolean expr THEN 1 ELSE 0 END)

Here's the link that got us started:
http://weblogs.sqlteam.com/jeffs/archive/2004/07/11/1744.aspx

Thanks Jeff whoever you are! Clever.

-k

SQL Cheats!!

2009-04-07T18:22:00.000-06:00

One of the most useful links I've found this week:

http://www.scribd.com/doc/399147/TSQL-Cheat-Sheet

Source and Target Worksheets in Excel/VBA

2009-03-30T10:48:00.002-06:00

Writing VBA code to move data around in excel sheets? Don't read and write from the same sheet concurrently. Reading from an existing sheet and writing to a new temp sheet (and then writing back from the temp sheet to the existing sheet later if desired) is much, MUCH faster.

Unicode primer

2008-12-28T16:38:00.002-07:00

I little dusty but perhaps useful if you've been fighting unicode (and I know some of you have):
Joel on Software: The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

Formatting Excel Charts in VBA

2008-12-10T09:14:00.001-07:00

If your Excel Chart lives in its own tab (i.e. it's a stand-alone chart instead of being embedded in a worksheet), you won't be able to manipulate the .chartArea property from code. Use the .plotArea property instead.

you know it's a good day when...

2008-12-03T12:36:00.001-07:00

Cool SQL Server cheatsheet

2008-12-02T13:23:00.002-07:00

I found a couple nice cheat sheets for SQL Server queries.

You can find them here and here.

The first is more advanced with data types, conversion, creation, etc.
The second is more usage based.

Enjoy!

SQLinForm - free SQL formatter

2008-11-26T01:37:00.004-07:00

Nice little free SQL formatter. I use the desktop version (java app), you can also use the web version. It's great - copy your code, switch over to the SQLInform window, hit ctrl-k to format the code, then ctrl-a, ctrl-c to copy it back to your edit buffer and then paste back into your app. Lots of control over the formatting, fast, what more could you ask. Great for code that's generated by a gui. Did I mention it's free?

http://www.sqlinform.com/

Toad for SQLServer

2008-11-25T19:39:00.003-07:00

Toad for SQLServer is a really nice alternative to SQL Server Management Studio for managing SQLServer databases. The table viewer has a load of nice features not available in SSMS, including sorting by clicking on column headers, and is much quicker for perusing the data and schemas in multiple tables / databases. The commercial version has even more useful features, but the free version is very useful. Check it out - http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm And if you're rich get the commercial version and let me know how you like it!

New Technology

2008-11-22T08:01:00.002-07:00

So for those of you who have been introduced to LINQ (and those not) here's a cool blog link that i read a lot that deals with the querying of excel tables (spreadsheets) using LINQ.

http://blogs.msdn.com/ericwhite/archive/2008/11/20/inner-join-of-two-excel-tables.aspx

Code on!

iWBD

2008-11-19T15:46:00.002-07:00

Why yes, this is a screen shot of a WBD on my phone.

Thanks for asking.

Automated Copy and Paste across instances of Excel

2008-11-19T14:46:00.002-07:00

If you're stumped about why your automated (from within a macro, etc.) copy and paste operations aren't working in Excel, make sure you are opening both the source and destination workbooks/worksheets from within the SAME instance of the Excel Application. For some reason, despite the fact that it works fine manually (i.e. NOT in a macro), transferring from one Excel Application object to another in a macro, you seem to lose the contents of the clipboard.

office life

2008-10-29T14:27:00.001-06:00

How many Hickeys does it take to get water?

More than you would think.

-k

Finding duplicates

2008-10-13T11:43:00.002-06:00

Ever need to find duplicates in a table?

Use table variables to do it is one of the easiest ways...
Table variables are deallocated after the transaction is done and are only visible for the duration of the transaction. They take the place of temp tables (popular in sql 2000)

DECLARE @tv Table(
Field1 VARCHAR(50)
Field2 VARCHAR(50) )

INSERT INTO @TV
SELECT FirstName, LastName
FROM Customers

SELECT Field1, Field2
FROM @tv
GROUP BY Field1, Field2
HAVING COUNT(*) > 1

Now you can do with whatever you want.

Cheers :)

Sweet plugin!

2008-10-13T08:29:00.000-06:00

http://www.oil-price.net/dashboard.php?lang=en

Check out this pretty cool site with a fantastic plugin

USB Hard Drive on an old Laptop?

2008-10-03T11:43:00.004-06:00

I recently got a WD Passport 250Gig external USB drive (Thanks, Boss!) for my laptop (an old Dell Inspiron 600m).

First thing I did was re-format it to NTFS (comes formatted FAT32 which limits filesizes to ~4GB)

Second thing I did was copy a bunch of data to the drive from my girlfriend's much newer, fancier laptop.

But when I tried to read any of those files with the Passport connected to my laptop, I got some very disturbing error balloons, some of which indicated that the drive had 'exceeded the power supply available from my USB port' and some of which suggested that data had been lost during failed operations on the drive. The drive showed up as a device and I could access files (very briefly), but after at most a few seconds of activity, the error balloons would appear and the drive would stop responding, re-set, and then I'd get the pop-up for 'new device - what action would you like to take?'

After some exploring and experimentation, I discovered that the data on the drive was not being lost or altered, but I could not successfully read much of anything from it (much less write to it).

Tried a few different solutions with no success, and eventually fixed the problem by buying an externally-powered Belkin 4-port USB hub for $30 at circuit city.

So in short, if you're running an old laptop and a heavy-current-drawing USB device, get a USB hub that plugs in to an AC power supply!

VERY HANDY TOOL! SQL2005 Service Manager

2008-09-29T17:28:00.003-06:00

SQL Server 2000 had a handy app that lived in the tray near the clock of your windows desktop. With this app you could see all SQL Server related services, and easily start and stop them at will. SQL Server 2005 did not come with this handy tool.

However at the following link a third party softare company that makes SQL Server tools has made a free one that works with SQL Server 2005.
http://www.sqldbatips.com/showarticle.asp?ID=46

This can be vary handy when trouble shooting SQL Server Standard and SQL Server Express problems because it will tell you if a server instance exists, what its name is(useful when building connection strings), and if the service is actually running.

This is a must to have when troubleshooting WFDE installs but is also just a handy tool to have in general for anyone that does SQL Server work or codes to SQL Server.

Scott

SPE 2008

2008-09-22T19:30:00.002-06:00

A happy booth is an effective booth!

(thanks guys you rock)

MS Access bug - new columns in linked table not visible from code

2008-09-19T12:48:00.004-06:00

This proved to be a very frustrating bug in MS Access:

If you have an Access application with a form bound to a linked table and you add a column to the linked table the new column will not be visible through code even though it is visible everywhere else. You can, for example:

See the new column in the table
See the new column if you query the table
Use the new column as the control source for a bound control on the bound form.

But if you reference "Me.myNewColumn" in VBA you get an error:
Method or data member not found

You can use columns without the Me. prefix so it's interesting to do this:
MsgBox TypeName(myNewColumn)

Prior to fixing the problem this msgbox will say "Empty"

The Fix:
Add a control to the form with the same name as myNewColumn. It doesn't have to be bound or have any relation to the new column except for sharing the name. Then delete the new control.

Me.myNewColumn will no longer yield an error and
MsgBox TypeName(myNewColumn)
will yield "AccessField"

I'm running MS Access 2003 (11.5614.5606) on Vista Ultimate.

Troubleshooting applications with windows event viewer

2008-09-17T12:28:00.004-06:00

Sometimes the best way to troubleshoot application errors that occur during install or other application operations is by looking at the logged entries in the windows event viewer.

You may right click on the My Computer icon and select manage to open the computer management console. Event Viewer appears in the tree on the left.

Alternatively you may go to Start>Run and type in Eventvwr.msc to get just the event viewer to appear.

There are several sections you may look in for these event log entries. For instance you can look at the applications node in the tree to view event log entries that pertain to application events. The source column is a good indication of what app created the event log entry. You can double click on the rows of interest and a dialog will popup with details of the error. These details are often the only bit of information you may have to go on to help your troubleshooting.

Hope this helps and happy trails.

Scott

VBA IDE is like a Skinner box

2008-09-15T21:30:00.002-06:00

I haven't posted anything useful in quite some time... I did discover something cool last month but I haven't had a chance to write it up properly. I'll get to that soon.

But this post is more food for thought than technical details. In VBA if you write a line of code that isn't syntactically correct (like if w/o else or assignment w/o a RHS) and then move your cursor elsewhere you get a popup error accompanied by the critical stop sound. As you can image this can be a powerful conditioner.

Having spent altogether too much time programming in MS Access I have become extremely reluctant to move the cursor off a line I know to be incomplete...

So i'll be working on typing this line:
dongle.version9 = m_ldpVersion9;

And I will realize I don't remember the RHS variable name and rather than leave the line like this:
dongle.version9 =

I will throw in a fake RHS:
dongle.version9 = foo;

just to keep from getting the popup. But, I'm programming in C++ now (which doesn't do the horrible popup thing) and yet I still put in place holders. I have been conditioned into doing something that is definitely NOT best practice by a stupid Microsoft construct.

drop all tables

2008-09-04T10:48:00.000-06:00

ever needed to drop EVERYTHING in a database?

now you can :)

ALTER procedure [dbo].[Usp_DropAllTables]
as

declare @name varchar(100)
declare @xtype char(1)
declare @sqlstring nvarchar(1000)

declare AllSPViews_cursor cursor for
SELECT sysobjects.name, sysobjects.xtype
FROM sysobjects
join sysusers on sysobjects.uid = sysusers.uid
--WHERE sysusers.name = 'USERNAME'
--where OBJECTPROPERTY(sysobjects.id, N'IsView') = 1 and sysusers.name ='USERNAME'

open AllSPViews_cursor

fetch next from AllSPViews_cursor into @name, @xtype
print '|' + @name + '|'
while @@fetch_status = 0
begin
-- obtain object type if it is a stored procedure or view
print @name + ' ' +@xtype

if @xtype in ('T', 'U')
begin
set @sqlstring = 'drop table [' + @name + ']'
exec sp_executesql @sqlstring
set @sqlstring = ' '
end
-- obtain object type if it is a view or stored procedure
-- if @xtype = 'V'
-- begin
-- set @sqlstring = 'drop view ' + @name
-- exec sp_executesql @sqlstring
-- set @sqlstring = ' '
-- end

fetch next from AllSPViews_cursor into @name, @xtype
end

close AllSPViews_cursor
deallocate AllSPViews_cursor

DataTable.Compute

2008-08-27T08:23:00.001-06:00

Have a datatable and want to actually do some REAL SQL on it ... like SUM, AVG, MAX, MIN .. aggregrate things?

Well - there's a DataTable.Compute function that takes in 2 parameters and works like this...

DataTable.Compute("MAX(PERF)", "WELLID=7") ... and that'll find your max perf where your wellID is 7.

enjoy :)

C# Design Guidelines

2008-08-27T08:09:00.002-06:00

This group of documents from Microsoft is titled "Design Guidelines for Class Library Developers" but is useful for any C# developent as a "best practices" type reference.

http://msdn.microsoft.com/en-us/library/czefa0ke(vs.71).aspx

VBA Constants

2008-08-26T11:00:00.002-06:00

Is anybody still using VBA for Access??? Well, if you are, and you're wondering how to declare constants and whether those declarations involve implicit typecasting or not, let me enlighten you:

Const myVarname [as type] = expression

And yes, typecasting is done for you, based on the type of 'expression' (as evaluated by the compiler). You may also supply a type explicitly if you like.

Examples:

Const pi = 3 (type = Integer)
Const pi2 = 3.14 (type = Double)
Const pistring = "phil" (type = String)
Const pi3 As Integer = 3 (type = Integer)
Const pi4 As Double = 3.14159 (type = Double)

Explicity types over-ride automatically generated ones, of course, so:

Const pi as Integer = 3.14

will result in an Integer pi whose value is 3

Data crunching books

2008-08-19T15:26:00.003-06:00

This is highly recommended by Roland:

http://islandia.law.yale.edu/ayers/

I'll probably get a copy and have it hanging around the office if you would like to page through it. We also have all the books by Tufte:

http://www.edwardtufte.com/tufte/

available for loan. Lemme know if you want to check them out...

File-scraping from secure servers

2008-08-13T15:55:00.002-06:00

If you're scraping files from a secure Web server (URL begins with "https://..." instead of "http://..."), this might save you some hassle:

http://www.christopherlewis.com/WGet/WGetFiles.htm

Grab one of the versions that is SSL-Enabled (version 1.11.1 or later). I've used 1.11.1 on my XP box and it works right out of the zip file (no need to register or configure anything). You may have to use the --no-check-certificate switch when accessing secure servers if your connection isn't able to verify the site's certificates.

A new google test

2008-08-13T08:16:00.002-06:00

The following is from Rob, and I found it wonderful on a number of levels. So I'm sharing with you:

"
Sharepoint?hmm...
I have a new approach to evaluating platforms like sharepoint, inspired by http://xkcd.com/458/
Go ahead, read it.
I'll wait..

ok:
Results 1 - 10 of about 131 for "I hate sharepoint". (0.17 seconds) vs
Results 1 - 10 of about 1,970 for "I love sharepoint". (0.33 seconds)
Apparently, it's an order of magnitude more lovable than hate-able, so you should do just fine. :)
"

Importing DBF files into SQL Server with SSIS

2008-07-20T23:42:00.003-06:00

I was wondering why sometimes I have no trouble importing .dbf files with SSIS and other times it's a no-go. Rebekah has a nice Word doc that explains the specifics of doing this, but there is one more thing you have to know, or you'll go insane like I did. Anyway, many thanks to Timothy Chen Allen for pointing out that the .dfb files must be named in 8.3 notation! MS either doesn't know this or doesn't feel like sharing that particular piece of info. Here's an excerpt from Timothy's blog (http://timothychenallen.blogspot.com/2007/07/how-to-import-dbase-tables-into-sql.html):

-----

Ah, SQL Server Integration Services (SSIS). Just when I'd learned to love Data Transformation Services (DTS) in SQL 2000, they completely changed how Extraction, Transformation, and Loading (ETL) is done in SQL Server 2005.

That's okay. Something else to throw on the resume.

At my current position I have to read in dBase files (DBF) a lot. We use ESRI ArcMap and everyone here uses ESRI shape files, which put data in DBFs. So I had to learn how to read in DBFs in SSIS.

I was going to go into all the troubles I had doing this, but the fact is there is just so much that can go wrong here. Here is one way to make things go right:

Change the name of the DBF file you want to read in to be 8.3 format. That is, 8 characters before the dot and 3 after. e.g. If the data file is named "2007 Data Load for Finance.DBF", change it to FIN2007.DBF or some such. SSIS will reckognize the DBF if the file name is not in 8.3 format, but will not be able to read data from it. This caused me much heartache before I figured it out.
In SSIS, set up an OLE DB Source. Hit the "New..." button for the OLE DB connection manager". Choose "Native OLD DB\Microsoft Jet 4.0 OLE DB Provider" as the provider.
For the Database File Name, put in the path to the DBF, but not the DBF file name itself. You will not be able to browse for this, because SSIS is looking for an MDB file at this point, which is not what you want. e.g. if your DBF is in c:\databases\FIN2007.DBF, put in "C:\databases\".
Click the "All" button, scroll up to Extended Properties, and put in "dbase 5.0". If you don't do this, SSIS will try to read your dBase file as an Access file, which will fail. While you're here, you can hit "Test Connection" and it should work.
Hit OK until you are back at the OLE DB Source Editor screen. Choose the name of the database file from "Name of the table or the view".
At this point you should be able to hit "Preview..." and see your data. You can now use this DBF connection as a data source.

Windows: Easy check to see if logged in local user is Local Admin

2008-07-14T10:14:00.004-06:00

Some software installs(WFDE) require user to be local admin on box because C# windows apps run with the rights of the logged on user. Here are 2 quick ways to check for that. This stuff comes straight off Google. Google is your friend.

1) A quick check to see if the user is a local admin is to go Start>Run and type CMD to get the dos command promt window. Once this DOS window is open it will show which user is logged in the path of the commad promt. You may enter "net localgroup administrators" without the double quotes, this will display if the current logged in user is a member of the local administartors group on the local machine.

2) If you need to quickly tell if the person currently logged onto a PC is a local administrator of the Windows box, right click on the START button. If you see "Open All Users" rather than just "Open," the account is in the local Administrators group.

Concerning WFDE, if either of these 2 methods reveal that the currently logged in windows user is NOT a member of the local administrators group on the local machine, you will need to log out, log back in as an administartor, then add the user in question to the local administrators group through the Control Panel, then log back in as the user in question with the newly added local admin rights and the install should go fine. Feel free to google how to add a windows user to the local administartors group or just click this link for more info:

http://www.lockergnome.com/windows/2006/09/01/add-a-user-account-to-the-administrators-group-in-xp/

Similar info may be Googled for Vista.

-Scott

INCREASING IIS SOAP MESSAGE SIZE LIMITS

2008-06-30T22:58:00.012-06:00

You can have SOAP message size up to 2GB, but it requires some modifications in your application configuration file.

Web.config settings:
The following attributes can be assigned to the <httpRuntime> tag in the <system.web> section of the Web.config file.

1) maxRequestLength
Specifies the limit for the stream buffering threshold, in KB. The default value is 4096 (4 MB).

2) executionTimeout
Specifies the maximum number of seconds that a request is allowed to execute before being automatically shut down by ASP.NET. The value of this setting is ignored in debug mode. The default in .NET Framework 2.0 is 110 seconds. In the .NET Framework 1.0 and 1.1, the default is 90 seconds.
To enable large message size, which can take large periods of time, increase the value of this property.

Modify web.config and make these modifications to your application for large message size.

The configuration, allowing message size of up to 100MB and upload periods up to 1 hour, should look like the following:
Web.config settings for 100MB and 1 hour

<configuration>

<system.web>

<httpRuntime maxRequestLength="102400" executionTimeout= "3600" />

</system.web>

</configuration>

clip

2008-06-30T18:55:00.002-06:00

Windows Vista has a new utility called clip that takes piped input and stores it in window's clipboard. This is very handy for getting a list of files. For example...

dir/b *.xls |clip

Nothing is written to stdout but a quick ^v drops the results wherever you want them

Huge Access 2007 bug

2008-06-18T13:33:00.003-06:00

I just spotted this on ZDNET / Tech Republic:

Warning: Access 2007 may delete instead of compact a database

Date: June 18th, 2008
Author: Susan Harkins
Category: General
Tags: Microsoft Access, Access 2007, Databases, Microsoft Office, Storage, Enterprise Software, Software, Data Management, Office Suites, Hardware

When you compact a database, using the same name, Access is supposed to create a new compacted database and delete the original. Access 2007 has a huge bug though. Instead of the above, Access deletes the old database without creating the new one — nice.If you try to compact and Access 2007 displays a warning that it can’t delete name.accdb after compacting it and suggests that you remove the read-only status and try again, do not click OK or Access will most likely destroy your database. I put off writing about this for a while because I couldn’t recreate the compact/delete problem myself.

An April hotfix supposedly fixes the problem, but since I couldn’t recreate the bug, I couldn’t test the hotfix with any certainty. The good news is I’ve not heard any horror stories about the hotfix. In addition, the word is that it does indeed fix the compact bug.

I’m wondering just how big a problem this bug was — did anyone run into it before the hotfix was available?

-- Excerpted from: http://blogs.techrepublic.com.com/msoffice/?p=577&tag=nl.e550

SQL & NuLL

2008-06-16T21:06:00.002-06:00

So just a short blurb .. but there's a few things about sql's NULL that you should know.

if you have a query

SELECT a.id, b.id, b.firstName
from tableA a
join tableB b on a.id = b.id

and if you have a.id, or b.id equal to NULL this query won't join properly.

NULL <> NULL.

----

MAX(girl_age) + max(boy_age)

if boy_age is ever null and girl_age is not .. the result will be null

--

in short - it's kind of like a 0 in math. 0 * NUMBER = 0.

but if you run into errors or unexpected datasets in sql and are doing things that have potentials to have nulls in their columns, then that is probably why.

happy querying!

Trouble staying connected with web-based Halco VPN?

2008-06-15T17:30:00.003-06:00

Recently, I started having problems staying connected to the Halco VPN with the new web-based VPN client (I had been used the old stand-alone VPN client until fairly recently). With the web-based client, I was getting kicked off my connection after just a few minutes of idle time.

Easiest solution: Fire up MS Outlook. Its mail-checking queries are sufficient to keep the connection alive. As a bonus, you can check Karen's Halco mail for her and forward anything that looks important to her gmail :)

Go Rockies!

2008-06-13T16:45:00.003-06:00

Despite John's no holds barred commitment to his rally cap, the Rockies failed to pull it out in the end. But it was still a very purple day.

-k

fuzzy data mining...

2008-06-10T12:00:00.003-06:00

If you're doing a lot of data mining from an internet source you might risk getting banned by the site you're hitting continuously so it's useful to write data miners that act human

#sleep.pl
$time = rand;
$time = $ARGV[0] + $time * ($ARGV[1] - $ARGV[0]);
sleep $ARGV[0];

yeah I could have done all that on one line but Perl becomes unreadable pretty fast when you start doing that so I'll split it out for your sake

Anyway this sleeps for a random number of seconds between the two arguments. So I generate bat files that alternate between wgets and "perl c:\code\perl\sleep.pl 50 90" I'm guessing this will get past most log file scanners that are looking for automated hits.

Primary keys on unknown datasets

2008-06-08T10:23:00.004-06:00

Ever been given the task to import a large dataset with unfamiliar data in SQL and need to put some keys on the table?

Well if you haven't - it's definitely something that you will have to do before you die.

Since databases are starting to infiltrate everything from medical records, to oilfield, to the real-estate world - you never know what kind of data you will have to deal with. Each job you have comes with different datasets and there's no way you can know the PK for your data (generally speaking.)

SQL has this handy too to help you out!

"sp_help"

To use this command - you would callit like this:

sp_help mydatabase.dbo.table1

Then it will give you all sorts of statistics of the table including the name, location of the table, columns in the table, identities of the table, suggested index of the table, row guid cols, etc.

This is a very handy tool that has come to the rescue and has cut off hours of keying data.

Enjoy!

First and last day of the month in Access

2008-05-30T09:03:00.002-06:00

Of course it is easy to figure out the first day of the month for a given date

CDate(Month([PROD_DATE]) & "/1/" & Year([PROD_DATE]))

The last day of the month is a little trickier, but not too bad. Figure out the first day of the next month, and then subtract a day!

DateAdd("d",-1,DateAdd("m",1,CDate(Month([PROD_DATE]) & "/1/" & Year([PROD_DATE]))))

Yay date math.

Merging Assemblies

2008-05-28T12:19:00.003-06:00

So if any of you find yourself with a .exe app (or .dll) that you want to deploy and there's a billion references you used, it looks really bad on a client's computer to copy a lot of assemblies over. Not only does it clutter up the directories with random files, but it could cause some issues if a reference was accidentally deleted.

There are 2 ways I found to do this:

Way #1
It is called ILMerge by Microsoft. Simply put, its super easy to use. In the properties of a project go to the Build Events. In the post-build section is where you put your command:

Syntax is basically: ILMerge.exe /ndebug /target:Dll (or exe) /out:OUTFILENAME ORIGINAL_FILE_TO_MERGE_ASSEMBLIES /log ASSEMBLY1 ASSEMBLY2 ASSEMBLYn

Below is an example of one I used for a class library. I wanted my output file to be the same file as my build and wanted to merge in the acrobat control and office viewer control.

"C:\Program Files\Microsoft\ILMerge\ILMerge.exe" /ndebug /target:Dll /out:$(TargetPath) $(TargetPath) /log $(TargetDir)\Refs\AcroPDF.dll
$(TargetDir)\Refs\officeviewer.ocx

And bam! After build rather than needing 3 files to run the control, you only need 1, huzzah!

Way #2
This is a little less elegant, but it works much better in some cases. Way #1 is awesome, but say the assembly needs to be in the GAC in order for it to work. Then how do you regsvr32 an embedded file? I'm not sure on that, but here's my workaround. In the Resources of your project import the assemblies needed. Then in the constructor of the main class, add these lines of code:

File.WriteAllBytes(PATH, Properties.Resources.YOUR_RESOURCE_FILE);
ystem.Diagnostics.Process.Start("regsvr32", "\"" + Path.Combine(PATH, YOUR_RESOURCE_FILE_NAME) + "\" /s");

And bam! On run, it will save the resource to a file and regsvr32 it! Your golden!

Variable-Dimensioning Gotcha in VB

2008-05-27T21:21:00.002-06:00

This might be old news to you folks, but it surprised me...

Consider the following (perfectly legal) VB code:

Dim x, y As Integer

I have always assumed this syntax is equivalent to:

Dim x As Integer
Dim y As Integer

As it turns out, this is NOT TRUE.

Dim x, y As Integer

is actually equivalent to the far more sinister:

Dim x
Dim y As Integer

or

Dim x As Variant
Dim y As Integer

Variants, while useful, are generally to be avoided whenever possible, as they create a lot of behind-the-scenes overhead at run-time, so Dim statements like this can be quite costly:

Dim x, y, z, p, q, r, sumtotal, runningsum2, foo, cat, bob As Integer

Happy coding!

Error converting numeric to numeric.

2008-05-27T15:13:00.003-06:00

This error baffled me. Actually I laughed when I first saw it.

So here's the deal.

SQL server 2005. Stored proc. I was trying to insert some information into a database via a little front end application that I had written in c# (2.0 platform).

No biggie, right? I had done it a million times before.

Then I got this error. I had been staring at my computer for hours though and thought I was halucinating or going bugeyed. So I rebooted. Same error.

I called it quits for the evening.

Next morning same deal.

Then I went record by record inserting the things in the dataset that I was sending through my stored procedure. Around record 50 I finally noticed it.

Whenever you get this error:
MAKE SURE YOU DON'T HAVE A DATA-TYPE OVERFLOW. I was trying to insert "1030" into a NUMERIC(5,2) type field.

full database search

2008-05-22T14:36:00.002-06:00

let's say you have a nasty database and somewhere in there you KNOW there's a piece of information but you don't want to go through and look at every column in every table.

WELL! HERE! is a link that shows how to do such things. it goes through and hits system tables to query every table, every field in your database.

for a small database it's super fast. for a moderate sized database (like 1-2 gig), be willing to wait 5-10 minutes - so know what you're after!!

Making .NET components work with COM

2008-05-20T22:33:00.001-06:00

The following tricks will help you get your C# .NET assemblies to work with COM (from un-managed VBA code in Access, for example):

1.) Check the 'Register for COM Interop' box on the Build tab of the Project Properties dialog before building

2.) Sign (Strong-Name) the assembly using a key file. There are various ways to do this - the easiest is probably via the 'Signing' tab on the Project Properties dialog (will build a key-file for you if you like). (I'm not sure if this is strictly necessary for COM interop, but it does allow you to put your assembly in the GAC on the client machine)

3.) Implement an explicit public interface to your assembly class (not absolutely necessary for interop, but Intellisense will not work without it - only the signatures in your interface will show up in intellisense on the COM side). It is important to note here that you also need to suppress the creation of a default class interface in order to make your own interface the default. The simplest way to do this is to set the classinterfacetype attribute to 'none' for the class(es) you want to expose to COM. The default interface will then be the first one in your inheritance list (after the colon in your class definition). Setting the classinterfacetype to 'autodual' also works, but this practice seems to have other drawbacks and is not usually recommended.

4.) You will need a default constructor with no arguments for any class you wish to expose to COM. Additional constructors are fine, but you will only be able to instantiate from the COM side via the no-argument constructor.

5.) Anything you would like to be COM visible needs to be declared public in your class. You can turn COM-visibilty on and off for any public item in your assembly using the COM Visible attribute (defaults to true, I believe).

6.) After building your assembly, register it and create a type-library (using Regasm.exe or similar) and place the assembly in the same directory as the COM project that will consume it (if this is not possible or desirable, you can also place the assembly in the Global Assembly Cache (avoiding DLL Hell because you strong-named the assembly when you built it). Some of these steps may or may not be performed for you by the development suite you're using (Vis. Studio will register your DLL and create a TLB for you, at the very least).

7.) On the COM side, be sure to remove the old reference (to the TLB file), save your work, then re-add the reference whenever your TLB has changed (if you make changes to the .NET assembly and re-build it, for example). I like to also manually un-register the old DLL and register the new one before re-adding the reference in VBA (even though Vis Studio seems to take care of this itself)

Thoughts on the DJ Basin Energy Expo

2008-05-19T11:40:00.003-06:00

Admittedly, I'm a newcomer to the oil and gas industry. So, when I went to the DJ Basin Energy Expo, I wasn't sure what to expect. I figured I'd just see a bunch of booths with people peddling their products and chatting with colleges. The might be a few pens and pencils for swag. Secretly, I was hoping there was going to be a top secret demo of some new technology akin to "Mr. Fusion."
While I didn't see any civilization-altering miracle energy source, I was pleasantly surprised to find there was a ton of cool stuff there.

Some of the highlights:

A scale model of a drill that was actually lit and operational
USGS core samples
Actual equipment including a command center, mixer, and rig
Cool swag including water bottles, collapsible flying disks, and live blue spruce saplings
Free food and ice cream!
Representatives from EnCana, Cudd and many others that patently answered all my dumb questions

I was really impressed by the fact that the expo's central purpose was to educate the public. I learned a ton of information about my new field, got some cool swag and met some nice people.

What more could you really ask for?

cont of SSIS

2008-05-12T09:32:00.002-06:00

So something I mentioned in my last post was how cool the scripting component that SSIS has.

Well.

Something that I should also say, is that if you want to include an outside reference to a DLL (say a 3rd party one for poops and laughs) you have to do something super dooper funky becuase of it's limitations.

1. Copy the assembly from the location where you find it to %windir%\Microsoft.net\framework\v2.0.xxxxx
2. Add a reference in the script task editor under the Project | Add References menu.
3. In the script, add the Imports AssemblyName statement
4. Make sure the assembly is in the GAC

what is this thing called SSIS

2008-05-09T07:58:00.002-06:00

There's a few ways to deal with data. Lots of times, it's best to use an ETL tool. There's cheap OTC tools that you can use - Microsoft Excel and Access being some of the more common choices. Then there's spendy stuff.

The spendier you get - the more user friendly they get too.

But what happens when you are a budget (surprise!), you have a set structure, and you want a repetable process that you have god-like power over?

Well - I would say use DTS. Which is now known as SSIS (Sql Server Integration Services).

SSIS is a super powerful package that Microsoft gives with SQL Server 2005 to handle data import, transformations, and loading into SQL. Since it's the first release, there are a handful of bugs in it. That being said - it's super useful.

Here's a few uses that I like out of it

1) File preprocessing
-say you have a file that's, to put it bluntly, a bitch, and has no formatting whatsoever, but you know the information you're looking for. SSIS provides you a tool to pre-process the file using .NET. in 2005, it's vb.net *only*, but like all .net things, you can write it in c# and then just import the library and call it in vb.net, if you're a c# guy.

2) Error Handling
-let's say you don't care about truncation errors. No worries! your code will STILL run! you can just tell SSIS to ignore that TYPE or error . . . .

2a) let's say you do actually care about truncation errors - you can have SSIS output it to a "error file"

And best of all - the most helpful site i've found with it . . . HERE!

Clear a ton of TextBox controls on a Windows form.

2008-04-17T01:49:00.009-06:00

If you ever need to clear a bunch of textbox controls on a Windows application form, here’s a quick and efficient way to do so. This especially comes in handy if you have a form with a ton of textboxes in multiple container type controls (Panel, TabControl, GroupBox, Form, etc) that are nested throughout your form. You can pass individual containers, or since a form is a type of container you can pass an entire form to the method.

ClearAllTextBoxes(this); //clear every textbox on form

public void ClearAllTextBoxes(Control Container)
{

foreach (Control ctrl in Container.Controls)
{
//if it's a textbox control, initialize it
if (ctrl.GetType() == typeof(TextBox))
((TextBox)ctrl).Text = "";

//if it's another container type, do the recursion
if (ctrl.Controls.Count > 0)
ClearAllTextBoxes(ctrl);
}

}

You could also apply this to a webform; however, as you are probably aware, there are other ways to do this without a trip to the server.

Count (Distinct) query in Access

2008-04-09T16:21:00.004-06:00

If you think you've lost your mind because Access won't let you write a Count(Distinct X) query, you may still be right but here's how you do it anyway (excerpted from http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx)

....
We received a question from a reader named Eric week before last about how to write a Count(Distinct) query in Access. In other database applications such as SQL Server, Oracle, and MySQL, the Count(Distinct) aggregate is used to count the number of unique items in a given rowset.

To get a count of the number of unique colors in the table, you could write a query such as:
SELECT Count(Distinct Color) AS N FROM tblColors

This would return the value 4 as there are four unique colors in the Color field in the table. Unfortunately, the Access Database Engine does not support the Count(Distinct) aggregate. To return this value from an Access table, you would need to use a subquery such as:
SELECT Count(*) AS N
FROM (SELECT DISTINCT Color FROM tblColors) AS T;

How to create a delay (sleep) in VBA

2008-03-26T00:03:00.002-06:00

For those rare occasions when you actually need to slow down VBA:

Excerpted from http://support.microsoft.com/kb/162150

Use an API call to suspend the execution of (an Office App) for a fixed amount of time.The Kernel32 contains a function that pauses a program's execution for a specified amount of time, specified in milliseconds. To use the function, it must first be declared in the General Declarations section of the module in which it will be used:

Declare Sub Sleep Lib "kernel32" Alias "Sleep" _
(ByVal dwMilliseconds As Long)

Use the following syntax to call the Sleep function:
Sub Sleep()
Sleep 1000 'Implements a 1 second delay
End Sub

Can't Connect to VSS2005 in VS2008?

2008-03-22T15:16:00.003-06:00

So I currently ran into a little problem that turns out it's also a little problem for a lot of other people, but not a lot of fixes out there about it.

So here's your problem .. you have VS2008 installed (all shiny looking even!) and want to connect to a VSS2005 database. Well initially, it's not recognized. You actually need to patch your client of VSS so that VS2k8 will read it.

There's also a little lecture here to read about it:

But if you don't feel like reading all of that here is the link for the fix. It takes about 5 minutes total to download and patch your version of VSS.

Happy coding!

-Rebekah

The Baby!

2008-03-11T09:06:00.001-06:00

Little Matthew Burkhart caught mid thought. He's really real.

Scary Accurate

2008-03-11T09:02:00.001-06:00

From Rob, From the NYTimes opinion section.

Time Zero Months query and other hints

2008-03-05T19:51:00.004-07:00

To get a type curve you have to adjust to time zero, or sequential month. Easy to do. First make a table that has the min month for each well in your production table. Perhaps something like this:

SELECT HPDI_PROD.ENTITY_ID, Min(HPDI_PROD.PROD_DATE) AS MinOfPROD_DATE INTO MinMax
FROM HPDI_PROD
GROUP BY HPDI_PROD.ENTITY_ID;

Then add a column to your production table to hold the new data you are about to create, and do a little date math:

UPDATE HPDI_PROD
INNER JOIN MinMax ON HPDI_PROD.ENTITY_ID = MinMax.ENTITY_ID
SET HPDI_PROD.SeqDate = DateDiff("m",[MinOfprod_date],[Prod_Date])+1;

Now you can do type curves as cross tab queries (grouping on whatever, in this case I did operator, you might do formation or township or ... )

TRANSFORM Avg([Liq]+[GAS]/6) AS BOE
SELECT Operator FROM HPDI_PROD
INNER JOIN HPDI_DESC ON HPDI_PROD.ENTITY_ID = HPDI_DESC.ENTITY_ID
GROUP BY Operator
PIVOT HPDI_PROD.SeqDate;

Or you can get the first year of production for each well:

SELECT HPDI_PROD.ENTITY_ID, Sum(HPDI_PROD.LIQ) AS CumOil, Sum(HPDI_PROD.GAS) AS CumGas, Sum(HPDI_PROD.WTR) AS CumWtrFROM HPDI_PROD INNER JOIN MinMax ON HPDI_PROD.ENTITY_ID = MinMax.ENTITY_ID
WHERE (((HPDI_PROD.SeqDate)<=12) AND ((MinMax.MaxOfSeqDate)>=12))
GROUP BY HPDI_PROD.ENTITY_ID;

And while I'm at it, here's how you add dashes to an API number:

UPDATE [Table] SET [API Number] =
Left([API Number],2) & "-" &
Mid([API Number],3,3) & "-" &
Mid([API Number],6,5) & "-" &
Right([API Number],2);

May your curves never decline,

Karen

practical xml

2008-03-03T07:28:00.001-07:00

here is the xml worksheet that we had at the lunch and learn. click and make it bigger.

this is what happens ...

2008-03-03T07:26:00.002-07:00

when you have 4 developers and 4 cyclists in the same room. we all group together.

Conditional page break in Access 2003 / VBA report

2008-02-29T20:16:00.003-07:00

Here's a link to MS's website that explains how to create a conditional page break in an Access report:

http://office.microsoft.com/en-us/access/HP051873361033.aspx

Basically, you insert the page break into your report (using the page break tool), create a VB OnFormat event handler for the Page Header section, and in the event handler set the page break control's Visible property to False to hide the page break. Then in the format event handler for the report section that contains the break, set the control's property to true if the condition is met. Intellisense will not find the Visible property on the page break control, but it still works. Thanks Phil.

good excel blog

2008-02-25T09:25:00.001-07:00

hey world! this blog rocks if you are a vba programmer or a vba not porgrammer http://smurfonspreadsheets.wordpress.com/

i'd highly recommend putting it on your blog role.

over and out!

stuff into PDF

2008-02-04T15:46:00.000-07:00

ever want to send someone a PDF instead of a word document? or excel? or whatever ...

well adobe pro is pretty spendy. if you're not the type to spend a lot of money - i've got an alternative for you! that's only $20!!

http://www.docudesk.com

They have a free trial too that runs so you can make sure that everything runs ok. But you can believe me - it does!

It asks you to install a GNU post script writer - which is just like asking your computer to learn how something new is encoded - so make sure you install that or else it won't work - but the standard edition is only $20 and i would say that since this is less than a bottle of vodka - it's a good buy!

Units of Measure in MSAccess

2008-01-27T20:23:00.000-07:00

Ever wonder what units VBA uses for the MSAccess 'Left' and 'Width' properties by default? Turns out they are 'TWIPs'. Stands for 'TWentieth of an Inch Point' There are 1440 of them in an inch, if you use the PostScript standard 72 points/inch (which Access obviously does). So if you want your control to be an inch wide and centered on your 8-inch-wide report, set its 'Left' property to 5040 and its 'Width' property to 1440. If you're doing this in code, you can leave the units off (Access/VBA assumes TWIPS). If you're typing the values into the properties dialog, you have to include the text 'twips' after the number. Access then auto-converts the value to inches and replaces 'twips' with a double quote.

API codes

2008-01-22T17:03:00.001-07:00

Hey I found a site with state and county api codes!

http://www.spwla.org/library_info/api_codes/APICountyCodes.htm

Very handy once in awhile.

-k

Web Design 101

2007-12-21T15:19:00.000-07:00

Two things that you should do to a website that has a username/login for it.

1) Make sure you tell your user if they input their password wrong

2) Always allow for password retreival ... NOT LIKE THE ORACLE SITE!

oracle vs sql

2007-12-19T22:50:00.000-07:00

so a common debate we have in our office is what is better - oracle or sql. well for different situations, either is *perfect*.

generally speaking too, oracle's pl/sql is super similar to sql server's tsql.

well, us who write sql in our sleep know about the @@ inserted command which you would use as so:
INSERT INTO mytable(val1, val2, val3)
SELECT '1','2','3'
SELECT @@INSERTED

that will return the row that you just inserted

but in oracle it's not exactly like that. a while ago, Will told me about this "dual" table, which is pretty spiffy and useful in this case to do what sql does.
so oracle would go like this:

INSERT INTO mytable(val1, val2, val3)
SELECT '1','2','3';
select ds.mytable_seq.currval
from dual;
commit;

so you can run that all in one transaction and it gives you the ID of whatever you just inserted. pretty cool no?

things to remember!

2007-12-14T14:20:00.001-07:00

let's say you get SUPER lazy and just don't delete a lot of test and development databases for a while...

no problem!

after you decide to clean up,make sure to detach each database, toss it on a dvd or something and save it just incase you need it.

something you also NEED to do is make sure that any users associated with that database are inactive or now have a home with a different default database. if you don't - you'll get all sorts of nasty error messages!!