Board index » delphi » Unable to write MS-SQL Stored Proc via BDE/ODBC

Unable to write MS-SQL Stored Proc via BDE/ODBC

I have the following stored procedure (out of the MS-SQL 6.5 manual):

CREATE PROCEDURE Update_All_Stats
AS
/* run UPDATE STATISTICS on all tables of the database */
  DECLARE @tablename varchar(32)
  DECLARE @tablename_header varchar(75)
  DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE
type='U'
  OPEN tnames_cursor
  FETCH NEXT FROM tnames_cursor INTO @tablename
  WHILE (@@fetch_status<>-1)
  BEGIN
    IF (@@fetch_status<>-2)
    BEGIN
      SELECT @tablename_header = "Updating "+RTRIM(UPPER(@tablename))
      EXEC ("UPDATE STATISTICS "+@tablename)
    END
    FETCH NEXT FROM tnames_cursor INTO @tablename
  END
  DEALLOCATE tnames_cursor
RETURN

If I enter this via the ISQL/w against a MS-SQL Server 6.5, it runs
fine. If I try to do the same thing via a TQuery (TQuery.ExecSQL) from
inside Delphi, I get an ODBC-error error stating that there is an
error in line 14 with "UPDATE STATISTICS". This behaviour does not
occur with the native MSSQL driver from borland. Is this a general
ODBC-error or is there a way around this problem? I'm using ODBC 3
with Microsoft's SQL Server driver 2.65.0213

Roland Kisseleff
Sowatec AG, Switzerland

 

Re:Unable to write MS-SQL Stored Proc via BDE/ODBC


Quote
Roland Kisseleff wrote:
> I have the following stored procedure (out of the MS-SQL 6.5 manual):

> CREATE PROCEDURE Update_All_Stats
> AS
> /* run UPDATE STATISTICS on all tables of the database */
>   DECLARE @tablename varchar(32)
>   DECLARE @tablename_header varchar(75)
>   DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE
> type='U'
>   OPEN tnames_cursor
>   FETCH NEXT FROM tnames_cursor INTO @tablename
>   WHILE (@@fetch_status<>-1)
>   BEGIN
>     IF (@@fetch_status<>-2)
>     BEGIN
>       SELECT @tablename_header = "Updating "+RTRIM(UPPER(@tablename))
>       EXEC ("UPDATE STATISTICS "+@tablename)
>     END
>     FETCH NEXT FROM tnames_cursor INTO @tablename
>   END
>   DEALLOCATE tnames_cursor
> RETURN

> If I enter this via the ISQL/w against a MS-SQL Server 6.5, it runs
> fine. If I try to do the same thing via a TQuery (TQuery.ExecSQL) from

> inside Delphi, I get an ODBC-error error stating that there is an
> error in line 14 with "UPDATE STATISTICS". This behaviour does not
> occur with the native MSSQL driver from borland. Is this a general
> ODBC-error or is there a way around this problem? I'm using ODBC 3
> with Microsoft's SQL Server driver 2.65.0213

> Roland Kisseleff
> Sowatec AG, Switzerland

Roland,

You have to use a "TStoredProc" component instead of a "TQuery"
component to execute a stored procedure.  Also, since this stored
procedure does not return a result set, you have to use the "ExecProc"
method instead of the "Open" method.

Mark

Other Threads