Sometimes technical, sometimes not, never a kitty

If you use a Treo 700w and SplashBlog, don't perform their automatic upgrade.  Their upgrader installs the wrong version of the software on your phone, and the client won't start.  There are two versions for Windows Mobile devices, one for those without a touchscreen (which they call the SmartPhone version) and a separate version for devices with a touchscreen (which they call the Pocket PC version).  Treo 700w users want the Pocket PC Version of SplachBlog.

Get the correct version at http://splashblog.com/portal_download.aspx.

I couldn't find a quick example when I was looking for one, so here's mine.

First, create your stored procedure with an output parameter:

CREATE PROCEDURE [dbo].[MySproc]
(@MyParameter varchar(50) OUTPUT)

And in your sproc, set your parameter to a value

set @MyParameter = scope_identity

Then, add an output parameter to your database command:

Dim _db As Database = DatabaseFactory.CreateDatabase
Dim _cmd As DbCommand = _db.GetStoredProcCommand("MySproc")
_db.AddOutParameter(_cmd, "@MyParameter", DbType.Int32, 4)

Then, execute your stored procedure, and assign the parameter value to a variable (remember to DIM the variable)

_db.ExecuteNonQuery(_cmd)
_result = _db.GetParameterValue(_cmd, "@MyParameter")

 

One of the great things about stored procedures is that you can put a whole bunch of different commands into one procedure, and call everything with a single command.  Gnarly!  However, if the procedureruns slowly, it's difficult to determine which command(s) are the problem.  Bogus!

One trick I use is to sprinkle little logging steps throughout my stored procedure, and try to narrow down which command is causing the problem.  These steps just log a step name and a timestamp into a table.

My table has the following structure:

CREATE TABLE [tblCommands] (
 [fldID] [int] IDENTITY (1, 1) NOT NULL ,
 [fldCommand] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [fldDate] [datetime] NOT NULL CONSTRAINT [DF_tblCommands_fldDate] DEFAULT (getdate())
) ON [PRIMARY]
GO

fldCommand is either a good name of the setp being logged, or something descriptive about what's happening in the procedure.  Just so long as I can find it again in the sproc.

Then, I add commands such as

insert into tblCommands(fldCommand, fldDate) values ('Final selection',getdate())

-- do my selection here

insert into tblCommands(fldCommand, fldDate) values ('End',getdate())

Reviewing the data from tblCommands:

fldID       fldCommand         fldDate
----------- ----------------   -----------------------
31          Start              2006-05-11 13:18:19.943
... (snipped for brevity)
39          Final selection    2006-05-11 13:18:24.240
40          End                2006-05-11 13:19:56.677

We can see that the entire procedure runs in roughly 1:40, with approx. 1:30 of that wrapped up in the final selection step alone.  By focusing on only the final step, I was able to optimize the query so that the entire procedure runs in 10 seconds.

I realize that with the naming convention I've used here, I'm likely to raise the ire of naming zealots.  I like appending a prefix to column names because that means I can use a reserved word such as 'Date' as a column name.  If you don't like it, you're fere to use a different convention in your own sprocs.

<update 2005-05-14>

Eric brings up a good point in his comment below about using the Query Analyzer (part of the SQL Server 2000 tools).  I have used the Query Analyzer extensively, and one place it falls short is with dynamic SQL statements in your queries.  If you have properly formatted dynamic sql (see http://www.rjdudley.com/blog/Is+Dynamic+SQL+In+Your+Stored+Procedures+Vulnerable+To+SQL+Injection.aspx), and especially more than one dynamic SQL statement, the Query Analyzer can't analyze the sproc.  Adding sinks like I have above can help debug an entire sproc, especially those with dynamic SQL.

I dislike how VS 2005 handles solutions, but I thought it was just me.  Victor Garcia Aprea has a good commentary on the subject:

Of course, I still think Visual Studio is totally messing up the Project and Solution terms, and that such core concepts should deserve a bit more of attention; here goes my view on it so you can take a look and make your own mind

Glad to know I'm not alone.

Hat tip: Pat Santry

This is the final week to register for BITS in Edinboro, PA. There will be many presentations provided, here is an overview of the sessions:

Keynote: Convergence and Cellular Data
Don't Touch Any Switches: Leading IT for the Non-IT Leader
Delivering Value to Your Enterprise - A Survival Guide
Information SuperHighway-Construction Ahead
Are You and Your Networks Ready for IP Telephony?
RFID Overview
DotNetNuke Overview
Monitoring and Pro-Active Response with Linux
How Secure Are You? Practical Security Strategies
Are You Missing Out on the RSS Revolution?
Extending the LAN

Secure Converged Networking

Panel Discussion: Aligning Business Goals with IT Solutions
Panel Discussion: Computer Forensics Examiners in the War on Terror
Workshop: Computer Forensics
Microsoft Vista Office Preview

Get 'em while you can at http://www.pghtech.org/Networks/InformationSystems/dotnet.asp.

Hat tip: Eric Kepes

Part of my work involves a SQL Server 2000 data warehouse, updated nightly with extracts from an ERP system.  I knew that the DTSs tended to run long, and I wanted to get a better idea of exactly how much data we were moving around.

First thing I did was add a table with the following schema:

CREATE TABLE [tblDtsLog] (
 [fldEntry] [int] IDENTITY (1, 1) NOT NULL ,
 [fldStep] [int] NOT NULL ,
 [fldStepType] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [fldStepName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [fldTimeStamp] [datetime] NOT NULL ,
 [fldNotes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [fldPackage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

This is a pretty general structure, and allows me to add different logging and debugging steps to the DTS.  fldEntry is simply an incrementing value to help sorting what happened when.  fldStep refers to the stepf of the DTS package, fldStepType is a short description of the DTS step.  I use fldStep because someone always asks me what that step is doing when we're looking at the numbers, and it's easier to log it than open the DTS and find it.  fldStepName is there because I inherited almost all of these DTSs, and the names don't always describe what's going on.  fldTimeStamp is the execution time of the step, fldNotes are some information I want to include about the step (usually just a rowcount), and fldPackage is the name of the DTS package.

One of the early steps for most DTSs is the extract from the ERP system to an intermediate table.  Depending on the package, the data in the intermediate table used for comparisons or modified before being migrated to the production tables.  By adding  a SQL task immediately after the extract, I could get an exact idea of how many data rows we just moved:

select * from IntermediateTable
insert into tblDtsLog(fldStep, fldStepType, fldStepName, fldTimeStamp,fldNotes, fldPackage) values (1,'Begin', 'Extract Step 1',getdate(),@@rowcount, 'Some DTS')

This simly adds one entry to the above table with a count of how many rows exist in the intermediate table.   For other steps, I use the Begin and End flags in conjunction with the getdate() to see how much time elapsed for the processing of a particular matching or modification step.  By sprinkling these liberally through my DTSs (some packages have 15-20 of these logging sinks in them), I have excellent insight into what is running slow, and then we can examine each step and try to optimize it.

Recently, we used the results of this monitoring to make a small tweak to one step of one DTS, which resulted in an overall gain of 2 hours of processing time in the overall update!

Freaky--we were just talking about this yesterday afternoon.

The latest SQLServerCentral.com update came out today, and the lead story is an update to an older article titled "Copying DTS Packages to a Different Server".  The article has some code samples to show you how you can copy DTS packages from one SQL Server 2000 to another.

If you're lazy, don't bother with the code.  Instead, there's a great GUI tool called DTSBackup 2000 from SQLDTS.com.  Using DTSBackup 2000 couldn't be easier--choose your origin server (supply credentials if necessary), choose the DTSs you want to copy, choose your destination server (supply credentials if necessary), add in the owner pwd if necessary, and click Transfer.  Within minutes, your packages are transferred.  It's sweet, and works like a charm.

I use this to copy DTSs from my dev/test server to the prod server, and back again if necessary.  Layout in the visual designer gets a little goofy, but that's the only downside.

The freeware app ADBWeather Plus runs on a Treo 700w!  This was my favorite weather app on my Axim X5, and with the EVDO network, forecast info and satellite images download fast.  You can install this using a CAB or a desktop installer.  Sweet!

In his "Replacing Start/Run" episode, Scott Hanselman talked about TiddlyWiki as a great little organization tool.  Pocket IE doesn't support JavaScript, so there's no hope of using TiddlyWiki with that.  Opera's latest release is so tantalizingly close to working with TiddlyWiki (which would make both a very useful app for me).  Opera can almost render the page correctly in full page mode, and the rendering leads to navigation difficulties.  But it's soooo close.  I'm not sure if it's an issue with Opera or TiddlyWiki.

They’re here…

Samsung has officially launched its new super-small PC, dubbed the Q1, that is essentially a cross between a laptop and a PDA. It lets users write on the screen, play movies and games, and stay connected to the Internet through wireless hotspots.

Read more: http://www.newsfactor.com/story.xhtml?story_id=0020002BEJ70

Technorati: UMPC

My fabvorite Today screen replacement is now available for Windows Mobile 5.  I blogged about this previously for my Axim X5, and was hoping it would be available for my Treo 700w. 

Full information at http://www.sbsh.net/products/pocketbreeze/.

Technorati: Treo 700w | Windows Mobile 5 | Pocket Breeze | Today Screen

Join us on May 31 from 6-8 pm for Part 2 of Tony Casale's talk on custom server controls in ASP.NET 2.0.  We'll meet at Communifax HQ; directions at www.communifax.com.

We received some nice press in the Pittsburgh Tribune-Review recently.  We were interviewd for an article titled "Blogs offer new avenue for businesses":

Brides-to-be love to read anything pertaining to weddings. That's why the owners of a Butler floral shop reach out to them through a blog, or Web log, that invites their comments.

Richard and Kathy Dudley of The Bloomery post messages and pictures with wedding ideas: Mini bouquets can be made with a deep purple calla lily that looks almost black, for example.

http://www.pittsburghlive.com/x/pittsburghtrib/s_445885.html

Well, one thing has changed.  In response to the article, we have had new customers check out our blog and website.  They found our prom gallery, and came in to order their prom flowers.  Progress!

You can find our blog at www.floristblogs.com and our regular site at www.bloomery.com; our wedding website is www.bloomeryweddings.com, and our wedding blog is www.bloomeryweddings.com/blog.

Technorati: The Bloomery | Florist Blogs | Bloomery Weddings | The Wedding Blog

Well, it’s not quite the Powerball, but still pretty cool:

Congratulations. You won 8th Prize in the April 2006 Contest. As you may know, a lot of people competed, but only a few won. Nicely done! Please also keep in mind that your prize is not just a stupid, geeky gift, but it's also a symbol to represent your contributions to the development community. When folks ask you how you won, you can tell them that it was by helping out other geeks. Be proud and brag.

My prize was Polarity; you can see the other prizes from last month at http://www.community-credit.com/CommunityCreditPrizePage.aspx (as well as this month’s prizes).

Utilizing the forces of magnetism, players take turns placing hovering magnetic discs on the board in order to impose chaos on their opponent. This chaos, which causes discs that are in play to come together in a flash of magnetic frenzy, results in scoring opportunities. Stacks of more than one disc (towers) are counted as points and the player with the most points at the end of a game wins. Those are the basics.

Want to compete for June?  Get on board at www.communitycredit.com.  This month’s prizes are totally cool, so don’t sign up yet.  Seriously.    Wait until June.

Technorati: Community Credit

Now playing: The Offspring - Pretty Fly (For a White Guy)

So I returned home, and the temp files were still deleting.  Taking a chance, I stopped the File Explorer, and simply renamed the Temp directory to Temp2, and created a new Temp directory.  That was actually quick.  I un-selected everything I could to sync, and did an AvtiveSync.  It took a bit, but it sync'd.  I started the updater, and after a moment or two, it told me I already had the update installed.  So I called Palm again.  Kudos to Palm support--second ring, an actual tech support person answered and we tried to fix it.  We deleted temp files, soft reset and rebooted, reinstalled the updater, still no dice.  After 45 min, I had to go to a 6th B-Day party, still with no phone.  The install hung right at the end of copying files, and never began the Updating step.

We came home, and I tried it again.  Deleted the few files in my new Temp directory, deleted the TreoUpd123 folder, removed the PalmUpd program, rebooted everything, re-installed the updater, changed the VERS file to 1.12, and let it go.  Removing the PalmUpd program is something we did the first time I called support, but not the second.  Finally, everything worked perfectly, and my phone is back online.  Not to mention ActiveSync is a lot quicker since I cleaned up the error I was getting.  Direct Push e-mail is awesome, and MS seems to have fixed a few things with the POP/IMAP e-mail as well.  Frustrating for a bit, but it's all better now.

The third page of the installer site (http://www.palm.com/us/support/downloads/treo700wupdater/verizon.html) has some troubleshooting which talks about some of the problems I had.

Technorati: Treo 700w