Go Premium for a chance to win a PS4. Enter to Win


Displaying SQL Server MESSAGE text through VB 6 app

Posted on 2001-09-05
Medium Priority
Last Modified: 2008-03-10
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!
Question by:Silvey
  • 5
  • 3
  • 2
  • +3

Expert Comment

ID: 6458197
In Sql Stored Procedures use raise error method which will passes to ur form..

Author Comment

ID: 6458214
That will display errors, however, I want any and all data that appears under the MESSAGE tab to be returned to my application...

Expert Comment

ID: 6458669
Here's what I use in vbscript, its just about the same in vb


dim DbConn
     Set DbConn = Server.CreateObject("ADODB.Connection")
     DbConn.ConnectionString = Session.Contents("ConnString")
     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)
     if err <> 0 then
          Response.Write err.Description
     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>"
               Response.Write "<br><b>End of error(s)</b>"
               set DbCmd = nothing
               set Rs = nothing
               set DbConn = nothing
     end if
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 6458677
This will only display errors, correct?  What about other data in the messages window that's not an error?

Expert Comment

ID: 6458695
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.

Author Comment

ID: 6458700
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...

Expert Comment

ID: 6458726
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.

LVL 39

Expert Comment

ID: 6459397
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

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.

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.

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.



Expert Comment

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

Friendly Neighbourhood Community Support Moderator

Expert Comment

ID: 6697157
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.

Community Support Moderator @ Experts Exchange

Author Comment

ID: 6697260
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.

Author Comment

ID: 6697265
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.

Accepted Solution

Moondancer earned 0 total points
ID: 6706061
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.

Community Support Moderator @ Experts Exchange


Featured Post


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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question