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.
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
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?
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.
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...?
|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|
|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).