Solved

Displaying SQL Server MESSAGE text through VB 6 app

Posted on 2001-09-05
13
463 Views
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!
0
Comment
Question by:Silvey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 1

Expert Comment

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

Author Comment

by:Silvey
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...
0
 
LVL 2

Expert Comment

by:daveherrmann
ID: 6458669
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

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

Expert Comment

by:daveherrmann
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.
0
 

Author Comment

by:Silvey
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...
0
 
LVL 2

Expert Comment

by:daveherrmann
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.

0
 
LVL 39

Expert Comment

by:appari
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


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
 
LVL 2

Expert Comment

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

Lunchy
Friendly Neighbourhood Community Support Moderator
0
 
LVL 1

Expert Comment

by:Moondancer
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.

Thanks,
Moondancer
Community Support Moderator @ Experts Exchange
0
 

Author Comment

by:Silvey
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.
0
 

Author Comment

by:Silvey
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.
0
 
LVL 1

Accepted Solution

by:
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.

Moondancer
Community Support Moderator @ Experts Exchange

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

726 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