Toad World® Forums

Invalid Error message on SP Compile.


#1

I am getting an error message “SQL Server Database Error: ‘CREATE/ALTER
PROCEDURE’ must be the first statement in a query batch.” when I execute a SP
compile in Toad. The Create statement is the first in a batch but also, opening
the same script in MS works fine. Granted there are errors in my SP but I don’t
get that error. I think it might be a case of Toad getting into a bad state
somehow but I wouldn’t know how to find what specific flags/state was out of
whack.

Tim


#2

It is hard to tell what is going wrong in your case without seeing the script
you’re running. Is it the latest beta you’re using? Did you try the
Query Analyzer? If Toad somehow modifies the script then comparing a source
script with the one that produces the error can give some hints about
what’s going wrong.

Igor.


#3

This is not an issue… Igor is right without seeing your code it will be hard to
know…

Personally, I am not having such problems and I do some really strange thing
before I create the procedure and definitely before I execute the code.

So something else is at play here if you are having an issue.

There should be no issues between version either, I have several versions of
Toad-ss Production for which I test against.

Please make sure before you start your procedure look basically like the
following, The Hank specific drop code is something new I am starting to do as
part of in-house standards.

Below is a non working sample of a procedure, which should help you figure out
your error.

use Admin

go

IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N
‘[dbo].[PROC_ AVI_PII_NONPROD_ CLEANSE]’ ) AND type in (N ‘P’ , N ‘PC’ ))

BEGIN

DROP PROCEDURE [dbo].[PROC_ AVI_PII_NONPROD_ CLEANSE] ;

Print ( ‘procedure PROC_AVI_PII_ NONPROD_CLEANSE - dropped’ )

end

else

Print ( ‘procedure PROC_AVI_PII_ NONPROD_CLEANSE - Not Found’ )

GO

/*********** Procedure to follow *********/

SET ANSI_NULLS ON ;

GO

SET QUOTED_IDENTIFIER ON ;

GO

CREATE PROCEDURE [dbo].[PROC_ AVI_PII_NONPROD_ CLEANSE]

(

@P_DBName varchar( 50 ) = ‘BOGUS’

)

WITH

EXECUTE AS CALLER

AS

begin – The beginning of the SP

/***

‘*********** ***’

Hank Freeman

Database Administrator

Email: hfreeman@msn. com

************ Start Here ************ *

***/

if Upper ( @P_DBName ) = ‘BOGUS’

begin

print ( ‘** Incorrect DBname entered ! - Terminating Store Procedure. **’ )

goto endit

end

begin

Declaritives:

begin

declare

@var_VISA_CCNumb varchar( 50 )

, @var_Visa_CCSalt varchar( 20 )

, @var_SERVER_ NAME VARCHAR( 25 )

, @var_TABLE_NAME VARCHAR( 50 )

, @var_DBName nvarchar( 50 )

, @DynSQL nvarchar( 2000 )

, @var_tbl_bkup varchar( 200 )

, @var_Begin_Timestam p datetime

, @var_End_TimeStamp datetime

, @var_runtime varchar( 100 )

end

set @var_DBNAME = @P_DBName

– SET @var_SERVER_ NAME = (SELECT @@SERVERNAME)

SET @var_SERVER_ NAME = ( SELECT CONVERT (VARchar( 25 ), SERVERPROPERTY (
‘servername’ ))) set @var_ExpirationYear = cast ( datepart (YYYY, getdate ())

  • 5 as char ( 4 ))

set @var_DepositCCExpDa te = ‘01’ + @var_ExpirationYear

–end of delcaritives

Print ( ’ Table paymentgatewaylog is present !!’ )

/*********** **/

/***create backup for the DDCC table **********/

/*********** **/

Set @var_bkup_tableID = ‘[’ + @var_SERVER_ NAME + ‘_’ + @var_DBName

  • ‘_’ + @var_TABLE_NAME + ‘Clientid

  • cast ( @var_Clientid as varchar( 6 )) + ‘_’

  • Cast (( select Administration_ SQL01.dbo. FN_Convert_ Date_Time_ to_STR(
    getdate ())) as varchar( 15 )) + ‘]’

set @var_tbl_bkup = 'select * into Administration_ SQL01.dbo. ’

  • @var_bkup_tableID

  • ’ from ’ + @var_DBName + ‘.dbo.’ + @var_TABLE_NAME + ‘’

  • ’ where clientid = ’ + cast ( @var_Clientid as varchar( 6 )) + ‘’

select @var_tbl_bkup

if @var_tbl_bkup is null

begin

Print ( 'Error: The variable @var_tbl_bkup is null - script terminated… ’ )

select 'Error: The variable @var_tbl_bkup is null - script terminated… ’ as
‘Error Message’

goto endofit

end

else

exec ( @var_tbl_bkup )

/**** 1000 extra line of code goes here ***/

end

endofit:

Begin

Print ‘PCI cleanse finished.’

end

end – end of procedure

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary
image002.jpeg


#4

This is not an issue… Igor is right without seeing your code it will be hard to
know…

Personally, I am not having such problems and I do some really strange thing
before I create the procedure and definitely before I execute the code.

So something else is at play here if you are having an issue.

There should be no issues between version either, I have several versions of
Toad-ss Production for which I test against.

Please make sure before you start your procedure look basically like the
following, The Hank specific drop code is something new I am starting to do as
part of in-house standards.

Below is a non working sample of a procedure, which should help you figure out
your error.

use Admin

go

IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N
‘[dbo].[PROC_ AVI_PII_NONPROD_ CLEANSE]’ ) AND type in (N ‘P’ , N ‘PC’ ))

BEGIN

DROP PROCEDURE [dbo].[PROC_ AVI_PII_NONPROD_ CLEANSE] ;

Print ( ‘procedure PROC_AVI_PII_ NONPROD_CLEANSE - dropped’ )

end

else

Print ( ‘procedure PROC_AVI_PII_ NONPROD_CLEANSE - Not Found’ )

GO

/*********** Procedure to follow *********/

SET ANSI_NULLS ON ;

GO

SET QUOTED_IDENTIFIER ON ;

GO

CREATE PROCEDURE [dbo].[PROC_ AVI_PII_NONPROD_ CLEANSE]

(

@P_DBName varchar( 50 ) = ‘BOGUS’

)

WITH

EXECUTE AS CALLER

AS

begin – The beginning of the SP

/***

‘*********** ***’

Hank Freeman

Database Administrator

Email: hfreeman@msn. com

************ Start Here ************ *

***/

if Upper ( @P_DBName ) = ‘BOGUS’

begin

print ( ‘** Incorrect DBname entered ! - Terminating Store Procedure. **’ )

goto endit

end

begin

Declaritives:

begin

declare

@var_VISA_CCNumb varchar( 50 )

, @var_Visa_CCSalt varchar( 20 )

, @var_SERVER_ NAME VARCHAR( 25 )

, @var_TABLE_NAME VARCHAR( 50 )

, @var_DBName nvarchar( 50 )

, @DynSQL nvarchar( 2000 )

, @var_tbl_bkup varchar( 200 )

, @var_Begin_Timestam p datetime

, @var_End_TimeStamp datetime

, @var_runtime varchar( 100 )

end

set @var_DBNAME = @P_DBName

– SET @var_SERVER_ NAME = (SELECT @@SERVERNAME)

SET @var_SERVER_ NAME = ( SELECT CONVERT (VARchar( 25 ), SERVERPROPERTY (
‘servername’ ))) set @var_ExpirationYear = cast ( datepart (YYYY, getdate ())

  • 5 as char ( 4 ))

set @var_DepositCCExpDa te = ‘01’ + @var_ExpirationYear

–end of delcaritives

Print ( ’ Table paymentgatewaylog is present !!’ )

/*********** **/

/***create backup for the DDCC table **********/

/*********** **/

Set @var_bkup_tableID = ‘[’ + @var_SERVER_ NAME + ‘_’ + @var_DBName

  • ‘_’ + @var_TABLE_NAME + ‘Clientid

  • cast ( @var_Clientid as varchar( 6 )) + ‘_’

  • Cast (( select Administration_ SQL01.dbo. FN_Convert_ Date_Time_ to_STR(
    getdate ())) as varchar( 15 )) + ‘]’

set @var_tbl_bkup = 'select * into Administration_ SQL01.dbo. ’

  • @var_bkup_tableID

  • ’ from ’ + @var_DBName + ‘.dbo.’ + @var_TABLE_NAME + ‘’

  • ’ where clientid = ’ + cast ( @var_Clientid as varchar( 6 )) + ‘’

select @var_tbl_bkup

if @var_tbl_bkup is null

begin

Print ( 'Error: The variable @var_tbl_bkup is null - script terminated… ’ )

select 'Error: The variable @var_tbl_bkup is null - script terminated… ’ as
‘Error Message’

goto endofit

end

else

exec ( @var_tbl_bkup )

/**** 1000 extra line of code goes here ***/

end

endofit:

Begin

Print ‘PCI cleanse finished.’

end

end – end of procedure

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary
image001.gif