Tuesday, June 13, 2017

That Scenery Keeps Changing!

Wow, but does it ever!
Being a consultant certainly takes you places!
So far I was in Dearborn long enough to make friends with people and get to know the place. I even took the short route from there to Rochester for SQL Saturday by driving along the north coast of Lake Erie (the Canadian side) and back! Then the budget ran out and it was time to leave.

Of course we had just moved to where my wife's "sudden" family lives - Tacoma - and bought a little house, even! It's nice, although they might have thought of adding a few inches here and there (sometimes I go to bed with a headache!)

So we've been here almost a month and a half now, but I've actually been here about 12 days! I've worked in Montvale (NJ), Santa Monica, Beverley Hills, and Mountain View (all CA) since I came here! Still, I have to say that it's fun! My only regret is not seeing the friends I've built up over the last six or so years at all the East Coast SQL Saturday events. There are far fewer out here, and they're much farther apart. Anyhow, I've sent an email to the guy who seems to run the local chapter and we'll see.

There's no yummy food this post either, I'm afraid, as the kitchen looks like a down-sizer from the last place, which is going to be rather difficult. One thing I can recommend, though, is that if you find yourself in a Shari's diner, order the pie shake! It's a milk shake with pie blent in too! Yummy!!!


Friday, December 09, 2016

An email I got from my favourite PC Maker!

Guess what System-76 sent me ... some code!!

michael@system76-computer:~$ sudo apt upgrade
[sudo] password for michael:
Package: laptops-desktops
Laptops calculating price... Done
    Lemur/Gazelle/Kudu: up to $155 off
    Oryx Pro: up to $265
    Serval/Bonobo: up to $285 off
Desktops calculating price... Done
    Meerkat: up to $155 off
    Sable/Wild Dog/Leopard: up to $130 off
    Silverback: up to $390 off

Description-en: This is a "System76 sale description" package. Powerful linux machines are on sale now. Plus you can receive an even greater discount depending on your component upgrade.

Do you want to continue? [Y/n]

Yeah - this reads like an advert - their advert! However, as the very happy owner of a System-76 Bonobo since November 2011 - a machine that is still among the fastest I have ever seen - I'm happy to recommend their computers - and take not one ha'penny for it!


Tuesday, October 25, 2016

A Change of Scenery!

It's been a while since I set finger to keyboard here - naughty me! - but that should change soon - I promise!

After a rather insanely busy first six months of the year - fun but busy - I took a deep breath, strapped all my knowledge about me, jumped ship, and was happy to be rescued by a passing mega-vessel! IOW, I've changed jobs (again), and am now a consultant for no less a company than Microsoft !

No, that doesn't mean that I'm moving to Washington or Oregon, much to the AG's bitter disappointment. Right now it means that during the week I'm to be found on the sunny Costa del Erie, on the beaches at Dearborn, helping out at a well-known purveyor of mobile goods that can be any colour so long as they're black!

Tech Tip for the Episode

If you're getting problems assigning new identities because it takes too long to determine whether you want a new one or just to find the one already in use, then try casting your mind back to Comp Sci 102 and principles of Operating Systems and Mutual Exclusion (mutex). After that horrifying excursion, jump here. You are probably familiar with locking rows or pages of data: these (Application Locks) are simply a lock on a word in memory, so are lots faster. They'll die if you restart SQL Server (of course!) and also when your process (spid) closes, so the memory gets released. They have a name, too, so you can do fun things like naming them according to the data you're trying to lock, thus creating locking without touching the table ...

Have fun!


Monday, August 15, 2016

What Broke, for Heavens' Sake?!

[Warning: SQL Tech !]
Well, so you're working (like me) with Microsoft's SQL Server and you've got all this code, and every now and then it breaks. Usually because some crazy user did something dumb like put a tick ( ' ) in the middle of a text field, like they've been instructed not to do, or managed to add something like a hidden character, or whatever.
Anyhow, the code breaks, and the carefully crafted error trapping works perfectly, so you know the line of the procedure that it died on, and a fairly generic and opaque description of the problem itself.
However, there are two things that you don't know:
1. the actual piece of data that caused the failure
2. the history of the processing of that data up to the point of failure
Knowing the actual data that caused the failure and be a great help; knowing what happened to it on its way to the point of failure can be even a greater eye-opener, especially if you're working with code that you inherited from someone else now long gone.

The obvious way to handle this problem is to add some logging code to your program. Personally, I maintain programs where the data is usually in the form of messages, even when they arrive batched up in a text file, which move through the systems, undergoing one of a number of sequences of operations, depending on where they've come from, what their content is, and where they're going. Each of these messages is assigned a unique ID to identify it on its journey from receipt to emission.
So, I have a log table with these fields:

IDRecord id in the table
CreatedOnDate record was added
SubjectWhat is happening in general
TextDetailed Info about the situation
TrackingIDID of the message being handled
OriginName of the procedure writing the record
SequenceSequence number of call points in procedure

I also have a stored procedure that accepts the fields it needs (the last 5) and inserts a record into the table.
All well and good so far, until you get to the small matter of transactions. Very wonderful things, transactions, and very helpful. Very nice to use to prevent things that have to be done together getting only part-done. Rolling back a transaction allows you to re-try the whole thing again when you know what's going wrong and have fixed the problem. However, the rollback undoes everything the code has written ... including all the logging, so you never get to find out what it was that went wrong - just that it went wrong !

The Light!
This, of course, is not a whole heap of help, but one day I was reading a book on SQL Server and suddenly part of the description of committing (successfully completing a transaction) turned on a light in my head!

COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database

So if you roll back the transaction, instead of committing it, everything written is rolled back. That's why the records in the log table vanished with a rollback - that much I already understood.
However, what I didn't cotton on to immediately was the implication of the corollary - that things not actually written out are not deleted.
Actually, it was a few lines into a piece of code before something hit me! Variables don't get rolled back - because they're in memory and not written to disc. And what then hit me was that a Table Variable in SQL Server is a Variable - even though it's very often stored in TempDB (on disc!).

The Implementation
So, to keep my logs that will tell me what happened to cause the rollback, I wrote some code to copy the ones for this message out into a table variable of the same structure as my log table, did the rollback, and then stuffed all those saved records back into the log table.

Et Voilà! On trouve tous les donneés intacte!!

The code is like this:

declare @taLogTable ( [Field List] )
insert into @taLogTable
select * from dbo.LogTable where [specify which records I want]
-- ---------------------
rollback transaction
-- ---------------------
set identity_insert dbo.LogTable on
insert into dbo.LogTable ( [Field List] )
select * from @taLogTable where ID not in (select ID from dbo.LogTable)
set identity_insert dbo.LogTable off

But Wait - There's More
That's fine, but there's a small hiccough coming here.
Part one is that the system runs in two separate databases, and we use synonyms to refer to objects in one database from another. That's perfectly fine: if I need to save logging data that's been logged on another machine but is still part of this transaction, I simply use the appropriate synonyms for the tables involved:

declare @taLogTable ([Field List])
insert into @taLogTable
select * from syn_OtherDB_dbo_LogTable where [specify which records I want]
-- ---------------------
rollback transaction
-- ---------------------
set identity_insert syn_OtherDB_dbo_LogTable on
insert into syn_OtherDB_dbo_LogTable ( [Field List])
select * from @taLogTable where ID not in (select ID from syn_OtherDB_dbo_LogTable)
set identity_insert syn_OtherDB_dbo_LogTable off

So I'm using the synonyms obediently, so if I copy the code to a test database, or somewhere similar, where the name of the other database is OtherDB_Test instead of OtherDB then all will still be ok.

Yeah, well, almost. Close, but no cigar! Synonyms are great in SQL Server except that the set identity_insert command (which allows me to push values into a field which otherwise generates its own automatically) doesn't accept synonyms. So, everything has to be explicit, and therefore there's more more worry and work at the time of deployment in a release when you also have to check that all the set identity_insert statements are pointing to the correct databases!

Victory In The End
There is, of course, a way to fix things to avoid this problem (which, if you're bitten by it, probably happens several months after the release and when you've totally forgotten all about it, because it only ever happens with errors, and they really don't happen all that often.

The way to do it is to use a stored procedure instead of the code after the rollback in order to achieve the same result. You pass in to the procedure the name of the database into whose LogTable the data has to return (I called it @strDatabase here), and also the table variable (@taLogTable). Then the code is like this:

declare @strSQL nvarchar(2000)
set @strSQL = 'SET IDENTITY_INSERT ' + @strDatabase + '.dbo.LogTable ON; '
set @strSQL += 'insert into ' + @strDatabase + '.dbo.LogTable'
set @strSQL += '( [Field List] )'
set @strSQL += 'select * from @taLogTable '
set @strSQL += 'where id not in '
set @strSQL += '(select id from ' + @strDatabase + '.dbo.LogTable); '
set @strSQL += 'SET IDENTITY_INSERT ' + @strDatabase + '.dbo.LogTable OFF; '

There is just one little extra piece of code you'll have to incorporate. 
SQL Server needs a type for each object coming in to a stored procedure, so you have to declare a user type for your table variable. This shouldn't be too much of a hardship, especially if you're using the same structure for all your log tables (a very good idea!). The parameters for that procedure turn out to be like this:
@strDatabase varchar(32),
@taLogTable taLogTable readonly

so not such a problem after all, and helps the programmers coming after you!

Have fun - keep cool!   Food next time!

Saturday, April 16, 2016

Do I have to have Windows 8 / 8.1 / 10 / whatever?

So you're looking for a new computer 'cos your old vintage 2003 laptop just ins't keeping up any more. Not to mention the fact that the case is cracked and split, the screen has a crack, some of the keys don't work too well since you spilled Mountain Dew on it, and your friends mock you for still using Windows XP.

So you want something cool. Something that you can make look the way you want it to look. Something shiny - something you can attach to the TV, even! But what do you really need? A friend just bought a new machine and found that she can't upload pictures to Facebook for some reason. She was also convinced to buy an antivirus program, only to find that the OS came with one! (PC World did a good article on the subject way back in July 2012 that's worth reading).

Check off what you do on this list:

Listen to MusicWatch DVDsCapture video for DVD
Word ProcessingUse a SpreadsheetCreate pictures, charts, etc
Create PresentationsDatabase WorkEnail
Anything with a browserWindows-specific programsMac-Specific Programs

Windows 8
If you're looking at Windows 8 (right) with horror, it seems like you had a lot of company. Don't buy this with the expectation that you can sit back and do nothing. Windows 8 had a large enough upgrade (to 8.1) that it is now officially no longer supported by Microsoft, just like Windows XP!

Windows 8.1
The replacement for Windows 8. Basically, Windows 8 with some GUI tweaks to appease the vociferous detractors. The appeasement didn't work!

Windows 10
The great white hope for Microsoft! "Everybody is going to upgrade their operating systems to Windows 10 because many features were added by the developers. The big thing is that the Start Menu button was reverted back that was previously removed/changed in Windows 8 and Windows 8.1" (here). Well, somewhat tongue-in-cheek there, and no comment at that time on the compulsory updates that seem to be causing a lot of people a lot of problems, but Win 10 does appear to be a genuine improvement - over 8 and 8.1, at least.

Here are some possibilities - maybe not all obvious:
Windows 7  --  this is probably the easiest thing to install on your PC. Just stick the disk in, blow away Windows 8, and install it. Easy, right? Well, there is a small step that you absolutely have to do first, but essentially yes.
Apple Mac OS/X - a very nice system indeed, if you can give up your Windows-specific programs. I doubt it'll run on your PC after you blow Windows 8 away!
Linux - there are lots of versions to choose from; you can "try before you buy" by using what's called a "Live Image" disc, where you boot off a CD and get the system - a little slow - that you would be using and you can see what you'll get before getting it.

For both Linux and Windows 7 there's a small procedure you'll need to go through before you can install them on your new machine. All Windows 8 machines are supposed to run with a replacement to the old and much-hacked BIOS, called UEFI. The requirement for this system, imposed by Microsoft, is supposedly to protect you and I (the un-knowing and helpless consumers) from malicious software ("malware") that would write changes into a special part of your hard disk - the part that contains the very first code that the computer runs every time you start it up. Once there it's very hard to get rid of.

This is a laudable move ..... except ....... very few instances of code doing that are around any more, as it's difficult to do and there are lots of easier ways to get your info. You may remember an uproar a few years ago about Sony trying to stop you copying their CDs (here)? Well if not, the skinny was that they decided to stop people copying their CDs in a rather proactive way, and took over their computers to do it! Not once (2005) but again in 2007! They received a rather large fine for being naughty!

So the idea with the new version of the BIOS - with UEFI - is that simple programs can't turn it off - it has to be a real live human. So you should be able to press Del or F2 or whatever the system wants you to press while it starts up, wander your way through the menus, and eventually find a switch to turn it off. Just how you'll identify the switch isn't, of course, defined!

As I said, this kind of attack, while it does exist, really isn't that common. What you are far more likely to encounter is something called "Phishing", or "Social Engineering", where someone sends you an email with an attachment that you open but which is, in fact, a program designed to do something bad. Similar examples include the perennial repeats of emails from the infamous "George Layba" promising you vast amounts of money in exchange for your bank account(which will be swiftly emptied if you do what they ask!). A larger and more sophisticated attack has more recently been coming to us all courtesy of the Chinese company that took over IBM's PC operations, Lenovo. Take a look here and here for info about Lenovo selling you computers with spyware already installed!! The second guy seems fairly blase about the whole thing, but could be right, I suppose. One should be more careful after getting caught three times!

Anyhow, what does this strange thing called Linux look like, and can I use it?
Well, the answer to the first question is somewhat frightening, because it is what you've asked for with no hope of getting - "whatever you like" ! Well, almost. There are around a hundred different distributions! Here's a list with links to many. Here's a site called DistroWatch, that tries to keep an accounting of the number of installs of each distribution and ranks them accordingly. Right now there are about 900!
If you take time to investigate you'll find that all these versions tend to form family trees. For example, Debian is the name of a basic distribution, and then there are a lot of distributions that take Debian and change certain things - such as trying to make it easier to use (Ubuntu), or specialise it for, for example, musical applications (KXStudio), and Distrowatch has a nice feature that allows you to select what you want from your machine and see what there is to choose from.
When I started out I chose Ubuntu, which was fine. However, the makers introduced a new interface called Unity, which I didn't really like (curiously enough it's rather like Windows 10 !) so I moved. To a descendant of Ubuntu called Mint.

As you can see, it actually looks rather like a version of Windows - I have it set up to have the menu drop from the top left, but the bar could be at the bottom. There are two columns in the menu drop-down - categories and programs. This means that you move the mouse over the category you want and, if you were using my system, you'd see this:
So the products that I have installed include (with their Windows equivalents)

  • GIMP (Photoshop)
  • ImageViewer (Acrobat)
  • Inkscape (FrameMaker)
  • MyPaint (Freehand drawings)
  • SimpleScan (scans from my Brother MFP)
  • Xara Xtreme (vector graphics, like Corel Draw!)

So yes, there are many programs out there that will do what you do on your Windows or Mac machine, but do it on Linux. Are they as polished as those commercial programs? Oftentimes not quite, but don't forget that they're usually free! That lets you try out various programs and make a good choice for your needs - or else use a combination of several - without going bankrupt in the process!

So no - you don't have to upgrade to the next version of Windows. I work with MS SQL Server using Windows every day, but use Linux for preference at home. In fact, Microsoft is porting SQL Server to Linux in 2016!