Solved

SQL Messages from a Executing Stored Procedure in Delphi

Posted on 2004-09-08
3
1,043 Views
Last Modified: 2012-06-21
I have a large Stored Procedure in Microsoft SQL Server 2000 which is fired from a Delphi 7 App.

Is there a way of getting the messages, Like what is displayed in the Microsoft SQL Query Analyzer messages panel, in real time.

Becuase of the long execution time of the procedure it would be great to get the feedback as to the procedure is actually doing something

Cheers
Darren



0
Comment
Question by:dhnkley
3 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12013180
I am not sure you can implement what you want. One possible solution is to create e temporary table, for e.g.:

CREATE TABLE PROC_TMP_RESULT
(
  MSG VARCHAR(255)
)

from inside your procedure, while it is being executed, you can make some INSERT into this table and read the message from the table...
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12013280
If you use ADO and want to catch "print" statements, you can you the solution I gave here:

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20961933.html

The TADOConnectio.OnInfoMessage event.

Regards Jacco
0
 
LVL 13

Accepted Solution

by:
BlackTigerX earned 500 total points
ID: 12018598
The only way I have been able to make this work is by:

drop a connection and a stored procedure, hook them up to the server and stored procedure
set the CursorLocation in both to clUseServer

add an event handler for the ADOConnection OnInfoMessage event

procedure TForm1.ADOConnection1InfoMessage(Connection: TADOConnection;
  const Error: Error; var EventStatus: TEventStatus);
begin
  Memo1.Lines.Add(Error.Description); //this is the print statement of your stored procedure
  EventStatus:=esCancel
end;

add code to execute the procedure

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOStoredProc1.Open
end;

is VERY important to use OPEN instead of ExecProc method, ExecProc will only return the first print statement of your stored procedure

my stored procedure looks like this:

create procedure test
as
print 'starting'
print 'ending'
select null --This line is just necessary so my Delphi code doesn't trow an exception saying that the procedure doesn't return a dataset when I used the Open method

here's the full unit:
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOStoredProc1: TADOStoredProc;
    Memo1: TMemo;
    Button1: TButton;
    procedure ADOConnection1InfoMessage(Connection: TADOConnection;
      const Error: Error; var EventStatus: TEventStatus);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ADOConnection1InfoMessage(Connection: TADOConnection;
  const Error: Error; var EventStatus: TEventStatus);
begin
  Memo1.Lines.Add(Error.Description);
  EventStatus:=esCancel
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOStoredProc1.Open
end;

end.


and the form Code:

object Form1: TForm1
  Left = 192
  Top = 123
  Width = 363
  Height = 332
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Memo1: TMemo
    Left = 8
    Top = 112
    Width = 313
    Height = 177
    Lines.Strings = (
      'Memo1')
    TabOrder = 0
  end
  object Button1: TButton
    Left = 104
    Top = 72
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 1
    OnClick = Button1Click
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initia' +
      'l Catalog=AirGroup;Data Source=acs-db'
    CursorLocation = clUseServer
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    OnInfoMessage = ADOConnection1InfoMessage
    Left = 16
    Top = 16
  end
  object ADOStoredProc1: TADOStoredProc
    Connection = ADOConnection1
    CursorLocation = clUseServer
    ProcedureName = 'test;1'
    Parameters = <>
    Left = 16
    Top = 64
  end
end

pretty lame way of doing it... Microsoft ADO has some bugs (they are aware of them) that's why you need to do it this way to get those messages
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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