Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

Displaying SQL Server MESSAGE text through VB 6 app

When using the Query Analyzer in SQL Server, the MESSAGES tab will display various data about the transaction you just ran...I would like to have my program trap that data...for example, if I run a backup through my VB code, I'd like to have the MESSAGES data returned to my program for analysis...how can I make this happen?  Thanks!
0
Silvey
Asked:
Silvey
  • 5
  • 3
  • 2
  • +3
1 Solution
 
prakashbitraCommented:
In Sql Stored Procedures use raise error method which will passes to ur form..
0
 
SilveyAuthor Commented:
That will display errors, however, I want any and all data that appears under the MESSAGE tab to be returned to my application...
0
 
daveherrmannCommented:
Here's what I use in vbscript, its just about the same in vb

Dave

dim DbConn
     Set DbConn = Server.CreateObject("ADODB.Connection")
     DbConn.ConnectionString = Session.Contents("ConnString")
     DbConn.Open
     Dim DbCmd
     Set DbCmd = Server.CreateObject("ADODB.Command")
     DbCmd.CommandText = "AddAttribute"
     DbCmd.CommandType = adCmdStoredProc
     DbCmd.CommandTimeout = 15
     DbCmd.ActiveConnection = DbConn
     
     on error resume next
         
         
     DbCmd.Parameters.Append DbCmd.CreateParameter("@ReportId",adInteger,adParamInput,, reportid)
     DbCmd.Parameters.Append DbCmd.CreateParameter("@LookUpId",adInteger,adParamInput,, Request.Form("lid"& i))
     DbCmd.Parameters.Append DbCmd.CreateParameter("@Checked",adVarChar,adParamInput,50, checked)
     DbCmd.Execute
     
         
     if err <> 0 then
          Response.Write err.Description
          Response.End
     end if
     
     if dbconn.Errors.Count > 0 then
          Response.Write "<b>The following error(s) have occurred</b><br><br>"
          for intLoop = 0 to DbConn.Errors.Count - 1
               Response.Write "<b>Error number: </b>"
               Response.Write DbConn.Errors(intLoop).Number
               Response.Write " <b>Error Description: </b>"
               Response.Write DbConn.Errors(intLoop).Description
               Response.Write "<br>"
          next
               DbConn.Errors.Clear
               Response.Write "<br><b>End of error(s)</b>"
               set DbCmd = nothing
               set Rs = nothing
               set DbConn = nothing
               Response.End
     end if
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SilveyAuthor Commented:
This will only display errors, correct?  What about other data in the messages window that's not an error?
0
 
daveherrmannCommented:
Yes it will only display errors.  The other messages (like number of rows affected) will not and to my knowledge there is no way of getting them.
0
 
SilveyAuthor Commented:
I can get errors to display with no problem...but what I'm looking for is something that will display EVERYTHING that appears on the MESSAGE tab...rows affected, messages that appear during a backup, etc...everything...
0
 
daveherrmannCommented:
The good news, is it exists.  The bad news is it's in C.  See Below from books online

Error and Message Handling
DB-Library for C applications deal with errors and messages: Messages generated by DB-Library for C functions are called errors and those from Microsoft? SQL Server? 2000 are known as messages. SQL Server can return both informational and error messages to the application. In addition, DB-Library for C has its own set of possible warnings and errors. A list of SQL Server messages appears in the sysmessages table. For more information about a list of DB-Library for C errors, see Error Messages.

DB-Library for C easily centralizes error and message handling. With dbmsghandle and dberrhandle, you can install your own message- and error-handling functions. When a message or error occurs, DB-Library for C automatically calls the appropriate user-supplied function, providing information to that function on the nature of the message or error, thus the error- and message-handling logic can be assigned to two functions in your application.

0
 
appariCommented:
yes it is possible but need to use SQL DMO, you can do it in VB itself.

use the method ExecuteWithResultsAndMessages to capture both results and messages.

here is the description of ExecuteWithResultsAndMessages method:

ExecuteWithResultsAndMessages Method (SQL-DMO)
The ExecuteWithResultsAndMessages method executes a Transact-SQL command batch returning batch result sets in a QueryResults object and capturing messages raised as part of command batch execution.

Applies To
Database Object RemoteServer Object
LinkedServer Object SQLServer Object


Syntax
object.ExecuteWithResultsAndMessages( Command , Messages , [ Length ] )
as QueryResults

Part Description
object Expression that evaluates to an object in the Applies To list.
Command String specifying a Transact-SQL or provider-specific command batch.
Messages String used to return message output.
Length Optional. A long integer indicating the statement batch length.


hope this helps.

Returns
A QueryResults object containing command batch results. The method fills the string specified by the Messages argument with message returns, if any are generated by batch execution.

Remarks
For Microsoft? SQL Server?, error severity indicates the degree of an error condition. Some errors are severe enough to terminate statement execution prematurely. Any error with a severity of 10 or higher is returned to the SQL-DMO application through normal error handling.

More benign errors indicate that statement execution succeeded, but that success was conditional. Success-with-information errors, called messages, are SQL Server errors with a severity of less than 10. Some Transact-SQL statements, such as the PRINT statement, do not generate result sets, using messages for their return value.

The ExecuteWithResultsAndMessages method implements command batch execution for a SQL-DMO application, allowing the application to capture success-with-information errors or other information transmitted as messages.


--------------------------------------------------------------------------------


0
 
LunchyCommented:
Silvey, it is to your advantage to reject an answer that does not meet your qualifications.

Lunchy
Friendly Neighbourhood Community Support Moderator
0
 
MoondancerCommented:
If this proposed answer has helped you, please accept it, grade it and close this question.  If it did not, reject it and add comments as to your progress and what else is needed.  If no action is taken here by next week, we'll need to decide this outcome.  Expert input always welcome as well.

Thanks,
Moondancer
Community Support Moderator @ Experts Exchange
0
 
SilveyAuthor Commented:
I'm sorry, I was unaware that this question was still open, as I rejected it shortly after it was posted.  I've had numerous problems with logging in to Expert's Exchange, responding to proposed answers, etc.  This answer did not work for me, as it did not provide the same information as what appears in the MESSAGES tab.  I apologize for any inconvenience this may have caused.
0
 
SilveyAuthor Commented:
The proposed answers did not provide what I was looking for - they only display errors and not the data that appears in the MESSAGES tab of the Query Analyzer.  I scrapped my plans for displaying these messages, and did something completely different in this project.  As a result, I would like to delete this question as it is no longer valid.
0
 
MoondancerCommented:
Rather than deleting this, I have REFUNDED your points and will move this to close it to our PAQ at zero points so that you can see the comments/results of actions taken.  Also, there is value here, even though you personally chose not to award anyone here, and others may benefit.  Please check the WHAT'S NEW link on the left, since this site is undergoing a transition and many roadblocks have been resolved.

If any expert here feels this action to be unfair, please comment and we'll try and make things right.  Thank you all for stepping in and working to find the ideal solution here.

Moondancer
Community Support Moderator @ Experts Exchange

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now