CODEWITHSUNDEEP
sql-servervb.net
tastydew
tastydew

Reputation: 697

Retrieving only PRINT command from SQL Server procedure in VB.NET

I apologize if I am missing a few things here, I am still learning .NET.

I have a project I have been working on where I am restoring a database as well as transaction logs. After each transaction log I have a PRINT statement to output that the transaction log has been processed.

I have been using the OnInfoMessage function to get the messages out of what is done in SQL Server, but was looking to ONLY extract the PRINT command out of it and display it in a textbox / label / RichTextBox as sort of a status box that updates as it goes.

My procedure in SQL Server is as follows

....
WHILE @@FETCH_STATUS=0
BEGIN
  SET @sql = 'RESTORE LOG FCS FROM DISK = ''C:\Databases\'+@fileName     +'''  WITH NORECOVERY';
EXEC (@sql);
PRINT '===' +@sql;
FETCH NEXT FROM cFile INTO @fileName
END
CLOSE cFile
DEALLOCATE cFile

In VB.net I am able to get ALL messages to display in a log file.

Private Shared Sub OnInfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)

    Using LogFile As IO.StreamWriter = New IO.StreamWriter("C:\SDBT\worklog.ft", True)

        LogFile.WriteLine(e.Message)

    End Using
End Sub

This in turn gives me the following output...

Processed 10392 pages for database 'FCS', file 'FCS' on file 1.
Processed 2 pages for database 'FCS', file 'FCS_log' on file 1.
RESTORE DATABASE successfully processed 10394 pages in 2.652 seconds  (30.619 MB/sec).
Processed 0 pages for database 'FCS', file 'FCS' on file 1.
Processed 6 pages for database 'FCS', file 'FCS_log' on file 1.
RESTORE LOG successfully processed 6 pages in 0.061 seconds (0.720 MB/sec).
===RESTORE LOG FCS FROM DISK = 'C:\SDBT_TestDB\log_00001.trn'  WITH NORECOVERY
Processed 0 pages for database 'FCS', file 'FCS' on file 1.
Processed 2 pages for database 'FCS', file 'FCS_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.058 seconds (0.252 MB/sec).
===RESTORE LOG FCS FROM DISK = 'C:\SDBT_TestDB\log_00002.trn'  WITH NORECOVERY

Ideally I would like to only retrieve the lines preceded with "===" and output those to the user.

Is it possible to get the @sql variable value from SQL Server to VB.net or is there something built into .NET that I am missing?

Thanks in advance!

Upvotes: 1

Views: 291

Answers (1)

Stone
Stone

Reputation: 308

In your OnInfoMessage sub, check each line of text in e.Message to see if it starts with === and only write those lines.

    For Each line In e.Message.Split(vbNewLine)
        If(line.StartsWith("===")) Then
             LogFile.WriteLine(line)
        End If
    Next

Upvotes: 2

Related Questions