|
Author Topic:   SQL Scheduler logging file conversion
VorDesigns
Member
posted February 20, 2003 12:37 PM            
Hi,

I have problem; I need to read a file generated by MSSQL200's Scheduler that contains the processes in a DTS Package I wrote to dump a database, pack it in a compressed gz format file and then ship it off to an offline backup server where it is then restored.
Unfortunately the offline server connection is not consistently available.
I developed a script to read the log file and then pass/fail based on a string.
My bad, I copied the log file out of notepad which stripped all of the null characters that MSSQL so intellently puts after each and every character. Now I need to figure out how to strip out the null characters from the file and still preserver the carriage returns and line feeds.
Any suggestions?

IP:

Rex R
Member
posted February 20, 2003 01:13 PM            
well I suppose one could do it in basic. how big a file are we talking about?

IP:

VorDesigns
Member
posted February 20, 2003 02:05 PM            
Hi Rex and thanks for the asstance.
I am already running an ActiveX script in the dts package to read the file.
My problem actually arose from the fact that I was opening the log file in notepad on the server and then copy/pasting it into a file locally.
Notepad is extremely basic and doesn't recognize null characters so my source file to read looked great when I copied it from notepad into a local document.
The problem is that SQL Server logging output (not a database log which is different) contains null characters between each and every character. Thanks MS, again, not.
So, my script ran great locally but craps out when I emplaced it on the client system.
Below is a sample of the log and the actual code I wrote that works great on a text file that doesn't have null characters which VB sees but I can't firgure out how to copy into my vb applet to use.
Thanks again for the hand.
SAMPLE LOG OUTPUT copied and pasted:
ftp> prompt
put BDEDump.zip
200 PORT command successful.
150 Opening ASCII mode data connection for BDEDump.Zip.
226 Transfer complete.
ftp: 1720958 bytes sent in 0.15Seconds 11397.07Kbytes/sec.
-
APPLET CODE FOLLOWS (apologies to old coders, I don't do this much):
'**********************************************************************
' Visual Basic ActiveX Script
' Title: CheckTDLog
' Use: Read log file and search for file transfer success
'************************************************************************
'-----------------------------------
'Open this file for reading
'-----------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\DBBackup\TransferDump.log", 1) Function Main()
'-----------------------------------
'Read the 1st line
'-----------------------------------
strLine = objFile.ReadLine
'-----------------------------------
'Check the first line for our search string
'-----------------------------------
If strLine = "226 Transfer complete." Then
Success = "Y"
Else
Success = "N"
strLine = " "
End If
'-----------------------------------
'Now read the rest of the file until done
'-----------------------------------
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If Success = "N" Then
If strLine = "226 Transfer complete." Then
Success = "Y"
Else
End If
Else
End If
strLine = " "
Loop
'-----------------------------------
'Now check to see if we were successful
'-----------------------------------
If Success = "Y" Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
'-----------------------------------
'Application clean up and close
'-----------------------------------
objFile.Close
End Function

[This message has been edited by VorDesigns (edited February 20, 2003).]

[This message has been edited by VorDesigns (edited February 21, 2003).]

IP:

VorDesigns
Member
posted February 20, 2003 04:42 PM            
Sailor,

If you read through the post, you will see that the problem is the output log and and the way that vb sees it.

While WordPad formats the file, it doesn't do anything for my problem which is stripping the null characters out of an SQL package log file programattically (sp).

So I guess I'm asking you, other than heckling, what was your solution?

P. S., Outlook must have read your post because the link in the notification wouldn't work where it did work for Rex. Had I heeded Outlooks behavior, I wouldn't have wasted my time reading your post.

[This message has been edited by VorDesigns (edited February 20, 2003).]

IP:

Rex R
Member
posted February 21, 2003 01:38 AM            
er, um, right. most of your post went sailing right on by me. Q null characters ascii codes < 10 yes? my idea is more on the order of open file for random access, read each character sequentially, if asc > 9 then b$ = b$ + a$
etc. etc. if R > EOF then end
rather a simple and brute force approach, though I was able to change textures a bit for wolfenstein3d with it(2 step process)

IP:

VorDesigns
Member
posted February 21, 2003 02:39 AM            
Hmm, you made me think of ascii value conversions. Really all I need is the ftp code 226, it would be a little slower, but I could read characters and build the first few characters in each line using a separate subloop or even build a a small vb app to do it using the same logic.
I was just hoping for a magic bullett.
Sigh,

IP:

Rex R
Member
posted February 21, 2003 03:43 AM            
heheh qbasic reads durn near any file as text and thats what I were using on my 486 (well, and my p2) in this instace I would look for ascii 13,10 (carrage return line feed) followed by 50, 50, 54 (think I left out a line, yep I did, a=asc(a$) then do the conditional statment) if a...
what can I say its been a while since I did any programming in qbasic...and the only other 'programming' is in the tm language

IP: