• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

parsing/analyzing sql server output

Hi,

Here's what i'm doing:

I've got sql server 7 running a job that executes an stored procedure ( http://support.microsoft.com/kb/251004/ - this is the SP)

it outputs a text file, which contains this block of text in the middle of the file:

DBCC OPENTRAN FOR DBID 7 [testDB]
Transaction information for database 'testDB'.

Oldest active transaction:
    SPID (server process ID) : 36
    UID (user ID) : 1
    Name          : user_transaction
   LSN           : (28046:334:1)
   Start time    : Apr 20 2005  2:56:17:077PM


NOTE: it may not always contain this block, if there are no active transactcions..

I need VB to read in the file, find this block (if it exists), read in the Start time, and compare the Start time to the current time.  if it is an hour old or more, I need to send out an email alert.

Can i get some good VB references for all the techniques i need, or example code?  I don't have much vb experience, most of it is in asp or vb.net.  I need ref/examples for parsing txt files, parse date, comparing dates, sending email...

also, can I just run this all from a .vb file?  the sql server job needs to be able to exec the vb after executing the stored procedure.

any info would be appreciated.

thanks - Trevor
0
trevorhartman
Asked:
trevorhartman
  • 6
  • 4
1 Solution
 
PePiCommented:
do we assume the header for this block will always be "DBCC OPENTRAN FOR DBID"? You will need to use Microsoft Scripting runtime Object, you can use this link for reference:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjfile.asp

once you can read the file, check for the string "DBCC OPENTRAN FOR DBID" by using the Instr function. If it is found, your next step would be to locate the string "Start time". from there you can easily parse the time portion and you can use the function DateDiff to find out if it's an hour old.

Let me know exactly where you want specific help. Hope this gets you off the starting block.



0
 
trevorhartmanAuthor Commented:
That reference you gave me - is that the complete reference for Windows Script using VB?  I don't even know how to print to the cmd line... (i've never used VB for windows scripting).

also, is it possible to send out emails with this?

thanks -Trevor
0
 
trevorhartmanAuthor Commented:
what would be great is a very basic script with some simple functionality, just so i could figure out the syntax and environment
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
trevorhartmanAuthor Commented:
is Microsoft Scripting runtime Object the same as WSH?
0
 
PePiCommented:
It is also called FSO or File System Object.
Here are some links that provides sample codes using FSO.

http://www.freevbcode.com/ShowCode.Asp?ID=1981
http://www.wdvl.com/Authoring/ASP/FSO/
http://www.aivosto.com/visdev/fso.html
http://www.georgehernandez.com/xProgramming/VB/zMisc/FSO.htm


This will probably keep you busy for some time. Let me know if you need further assistance.


Cheers!


0
 
trevorhartmanAuthor Commented:
excellent, thanks for the links...

anything on Emailing though?
0
 
PePiCommented:
with regards to emailing, the answer is Yes. just as long as you have a live internet connection ;)

try this:

add Microsoft MAPI control to your form

then

With MAPISession1
       .Username = [Mail Profile]
       .SignOn
End With

With MAPIMessages1
      .SessionID = MAPISession1.SessionID
      .Compose
      .MsgNoteText = [Message to be sent]
      .RecipAddress = [Recipient Address]
      .MsgSubject = [Subject]
      .ResolveName
      .Send      
End With

With MAPISession1
       .SignOff
End With
0
 
trevorhartmanAuthor Commented:
I'm not using a form, this app will exec behind the scenes using WSH... how can I include MS MAPI with WSH?

thanks a lot! - Trevor
0
 
PePiCommented:
I guess you can create the MS MAPI object just like you create any other object using VBScript or JScript although I am not 100% sure on this one.

0
 
trevorhartmanAuthor Commented:
thanks, i think i can figure it out from here

-Trevor
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now