Boy, I've had a busy few weeks. Lots of work at the office, a quick 2-day trip to Disney, and a hacking cold that left me sick enough that I couldn't even podcast with Bruce. He was fine on his own though -- I knew he would be...

Anyway, this Thursday Bruce and I will be interviewing Ed Brill, man about town. If you have any requests for questions or subjects to discuss, please post them as comments on the Taking Notes site. If all goes well, the podcast will get posted on Friday. If not... well, we'll cross that bridge if we get to it.

In other news, I saw a nice little Domino 7 ad in the sidebar of a ZDNet article a few days ago. Good to see that the Domino marketing is still going strong, even a month after Lotusphere. I haven't seen much activity on the Rapid FUD Responder blog though. Maybe that means there's no new FUD floating around? That would be nice.

Okay, gotta go. 24 is about to come on. A man has to have his priorities, you know.

Since there has been some recent discussion over what Lotus Notes "coexistence" means, I thought I would use my first Show and Tell Thursday installment to discuss a way that I often have to "coexist" my Notes applications.

Microsoft SQL Server. It's the golden database child of small to mid-sized IT departments. Without trying to spark any kind of religious debate over the merits of SQL Server versus MySQL versus Oracle versus whatever, let's just agree that SQL Server is pretty pervasive. And therefore, it's a technology that we'll want to "talk to" on occasion.

Programming Lotus Notes to access fields and tables and views on SQL Server is pretty much a cake walk. You can use LS:DO or DECS or ADO COM objects to grab them just like you would any other relational database, and use regular SQL calls to retrieve and manipulate data. No big magic there.

Where it gets interesting when you have a DBA (or "power admin") who actually starts doing some optimization on the server. They may set up stored procedures to run huge chunks of SQL code on the server, or DTS jobs to perform one or more tasks that would be inefficient or impossible to do using SQL (like rebuilding tables or grabbing external data or doing backups or something). Then they may stand back in awe of their creation and tell you, "Take that you Lotus Notes guy. Now you're gonna have to start writing some ASP.NET pages or C# programs to touch this stuff."

Yeah, well... no.

Here are some techniques you might find handy:

Calling a stored procedure

I'm using ADO COM objects here, but you should be able to use LS:DO to do this if that's your flavor. ADO is kind of nice for this sort of thing because you can use DSN-less connections, and because you can set your DSN-less connection up to use the OLE driver instead of the ODBC driver to access SQL Server (which is supposed to be faster).

Anyway, here's the code. It's not too different from doing a regular SELECT statement, but on recent versions of SQL server you have to execute a "SET NOCOUNT ON" statement before you call the stored procedure in order to have it return the results as a recordset (if there are any results). Like this:

Dim con As Variant
Dim rs As Variant

'** create the ADO objects
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'** connect to the server
con.open dsnConnectionString$

'** this is required to make Stored Procedures return results
'** on recent versions of SQL Server
rs.Open "SET NOCOUNT ON", con

'** now we can run the procedure
rs.Open "EXEC sp_myprocedure 'param1'", con

'** and if it returned any records, we can treat them just like
'** a regular recordset
If Not rs.EOF Then
    Print "First record, first column: " & rs.Fields(0).Value
End If

Running a DTS job

Another handy thing you can do is have Notes call a DTS job that runs on a remote SQL server. There are a lot of things a DBA can tell a DTS job to do, and it can be a very flexible and powerful tool. Especially because you can get your DBA to do all the hard work for you, and just call the DTS job whenever you need it. :-)

Normally you'll want to call the DTS job to run on your Notes server, either using RunOnServer or called from a web page. This is because you need to have access to the DTS.Package COM object, which is installed when you install the SQL Server Client. Since most users won't have the SQL Server Client on their workstations, running the code on the server means you can just install the client on the server once and be done with it.

Here's the code:

'** Based on http://support.microsoft.com/kb/252987/EN-US/
'** Make sure you've installed the Microsoft SQL Server client on
'** the machine this runs on, so the DTS.Package class is there
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1

Dim dtsPkg As Variant
Dim dtsStep As Variant

'** get a handle to the DTS package (dtsName$)
Set dtsPkg = CreateObject("DTS.Package")
Call dtsPkg.LoadFromSQLServer(serverName$, userName$, _
password$, DTSSQLStgFlag_Default, "", "", "", dtsName$)

'** run the package
Call dtsPkg.Execute()

'** check for errors
outputMessage$ = "Package completed successfully"
Forall dtsStep In dtsPkg.Steps
    If (dtsStep.ExecutionResult = DTSStepExecResult_Failure) Then
        outputMessage$ = "Package failed on step " & dtsStep.Name
    End If
End Forall

'** tell the user what happened
Print outputMessage$

Hope that helps. Even if it's not something you end up using now, you might want to store it in your memory banks for later. You never know when you're going to need to "play nice" with a Microsoft SQL Server.

technorati tag: Show-n-tell thursday

I finally figured out how to clear individual auto-complete entries in Firefox. There was one place where I accidentally misspelled my name, and the bad spelling always came up as the first choice. Drove me crazy.

Anyway, when you start typing in the input field and the auto-complete list comes up, just arrow down to the bad entry and press Shift-Delete. Bye bye mistake.

BTW, you can also do this in Internet Explorer by highlighting the entry and pressing the Delete key (no Shift). But who still uses IE?
;-)

So, I finally had my endoscopy done a few days ago. Actually, it turns out that "endoscopy" is a slightly broad term. The actual name of the specific procedure I had done was an esophagogastroduodenoscopy. The rough translation of that word is: "we're gonna take a garden hose that has a camera and flashlight on the end of it, stick it down your throat, and take a look around."

The weird thing about the whole procedure (if you can pretend that it's not weird to have a camera shoved down your esophagus) is that they drug you with something that makes you forget the entire procedure. Technically you're still conscious through the whole thing (so they tell me), but there's something about the sedative they give you that makes you not remember. It's like you're knocked out, but not really.

So pretty much, they drugged me, had their way with me, and I woke up in another room an hour later.

It's kind of spooky that there are chemicals that can cause you to just forget an entire hour of your life. Not that I'd like to remember a procedure like that, but it's a little unsettling. I picture it to be like a military interrogation drug or something.

Anyway, I was very surprised that my throat wasn't completely raw after the whole thing was done. I guess they use good lubricants. At some point between when I woke up and when my wife drove me home, the doctor talked to me and showed me pictures of a couple of sphincters (I'm guessing it was my stomach but I suppose you can never be sure), and I'm pretty sure he told me I was fine. The whole conversation was a little fuzzy.

I've got a follow-up appointment in a couple weeks, so they'll probably tell me a few more details then. Hopefully they won't drug me again beforehand, so I'll be able to focus a little more.

A few Notes development items in the news for you:

• Formula Lists -- There's a great little "cheat sheet" for getting unique entries in Formula language lists on the DomLike.net site. Along a similar vein, Jake Howlett just asked about generating a list of years and got some good responses right away.

• Calendar and Schedule Fields -- a recent LDD post by Chris Stoner had a link to the ultimate Domino Calendar and Schedule reference doc. Especially good are pages 98-123, which tell you exactly what all of the fields on a C&S doc are for, and what the valid values are (which, if you've ever tried to figure that out yourself, is next to impossible). If I may be so bold, I'd like to say that my Repeating Dates Database might be a good supplemental read if you're curious about how the repeating dates functionality works in Notes (or at least how I think it works).

Normally I try not to be too much of a technology basher... I realize that all technologies have good points and bad points, and as I've stated before: "given the right context, everything sucks."

So, that being said, I still just had to laugh when I recently got a door-hanger from Microsoft that said "Microsoft Security Please!". See the scanned image above and/or to the right (you can click it to see a larger image).

I mean really, no matter how pro-Microsoft you are, you can't deny that MS has had it's share of security issues over the past 7 or 8 years. No matter how optimistic you are, do you really think it's a good idea to print up a slick, full-color strip of cardboard that says "Microsoft Security Please!" (including the exclamation point) that is meant to be hung on my door? It's an advert for the Microsoft Security Assessment tool, but still, it seems to invite ridicule.

Apparently someone thought it was a good idea. I wonder how much that would go for on eBay...?

Since everyone else is doing it, here are the results of the evals for the Java Jumpstart session that Tom and I did (if the numbers don't quite add up, that's because some people left things blank):

	Excellent	Good	Fair
How would you rate the quality and relevance of the information in the session?	72	12	0
How would you rate the quality and effectiveness of the speaker(s)/facilitator(s)?	76	7	0

	Yes	No	Undecided
Did the information presented in this session/BOF affect your decision to further consider or implement the IBM/Lotus product and/or solution discussed?	65	6	11
Would you recommend an updated version of this session/topic for upcoming IBM/Lotus technical events?	81	2	0
Would you recommend this speaker(s) for upcoming IBM/Lotus technical events?	84	0	0

I just have to say, I am amazed at the positive response. Not only from the evals, but also the fact that AWStats tells me there have already been just over 2,000 downloads of the Java Samples database from the session. That's just in the past week. And that's just the downloads from here -- Tom had them posted on his site too (although the Lotusphere Wiki links point here).

Thanks everybody!