Board index » delphi » Can't find Stored Procedures when moving from MSSQL 6.5 to 7

Can't find Stored Procedures when moving from MSSQL 6.5 to 7

I'm, currently using D4 CS SP3 and NT 4 SP4. I have several Delphi apps that
connect to MSSQL 6.5 SP5 databases using BDE/SQL Links and Native Drivers
(5.01). When I attempted to upgrade MSSQL to 7.0, mysterious errors occurred
in my delphi apps, the most common being "Could not find stored procedure
'xxx'", with some different values for xxx. However, many procedures worked
normally. Anyone know whats wrong? (ODBC doesn't help either).

/John Ray

 

Re:Can't find Stored Procedures when moving from MSSQL 6.5 to 7


Quote
John Ray wrote:

> I'm, currently using D4 CS SP3 and NT 4 SP4. I have several Delphi apps that
> connect to MSSQL 6.5 SP5 databases using BDE/SQL Links and Native Drivers
> (5.01). When I attempted to upgrade MSSQL to 7.0, mysterious errors occurred
> in my delphi apps, the most common being "Could not find stored procedure
> 'xxx'", with some different values for xxx. However, many procedures worked
> normally. Anyone know whats wrong? (ODBC doesn't help either).

> /John Ray

The problem is that MS SQL 7.0 eliminated the Sysobjects table so
procedure overloading in no longer supported.

In prior versions a ';1' at the end of the stored procedure name was
legal, it now has to be removed.

Either manually remove it, or use a method like this in your
datamodule's OnCreate() handler..

procedure strip_Overloading_Indicator_From_SPs( Component: TComponent );
var
  I, S: integer;
  SPN: string;
  Params: TParams;
begin
  Params := TParams.Create;
  TRY
  with Component do
  begin
    for I := 0 to ComponentCount - 1 do
    begin
      if Components[ I ] is TStoredProc then
      begin
        SPN := TStoredProc( Components[ I ] ).StoredProcName;
        S := pos( ';1', SPN );
        if S > 0 then
        begin
          Params.Assign( TStoredProc( Components[ I ] ).Params );
          Delete( SPN, S, 2 );
          TStoredProc( Components[ I ] ).StoredProcName := SPN;
          TStoredProc( Components[ I ] ).Params.Assign( Params );
        end;
      end;
    end;
  end;
  FINALLY
  Params.Free;
  END;
end;

HTH

--

                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Programmer / Analyst                    .
. TVisualBasic := Class(None)             .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) home (dot) com    .
-------------------------------------------

Other Threads