none
Syntax error in TextHeader of StoredProcedure is there a solution for this bug....

Answers

  • You have nested comments before your ALTER PROC, which is not possible in TSQL.  Change your header to:

    /*  ------------------------------------------------------------------------------  
    Detailed Description:  airsp_ValidateArea_China   
    Dependencies:    dbo.airsp_IsValidGeocode 
    stored procedure no no @fltLatitude 
    dbo.airsp_strcmpExcludeLeadingZeros stored procedure no no @str1   
    Called BY:    dbo.airsp_ValidateArea stored procedure   
    *******************************************************************************
    airsp_ValidateArea_China
    China data will be validated using a non-standard approach.
    Although we don't model at the Chinese postal codes level, postal codes may be the only data that the customer has.
    If the customer supplies a postal code, we will map the postal code to the correct county code.
    The original postal code will not be overwritten.
    For some counties, we don't have every single postal code in tblAreaExternal. Some of the postal codes
    (the ones with trailing zeroes) are indicative of a range of values.  For example, the postal code 056600
    matches any postal code in the range of 056600 to 056699
    In order to find the correct county based on the customer supplied postal code, we will try up to 5 logical matches
    (this is done with a single query)
    The Matching Logic:
    Try to match postal code using all 6 digits.
    If no match found, match left 5 digits of the supplied postal code to tblAreaExternal records with 1 trailing 0 (zero)
    If no match found, match left 4 digits of the supplied postal code to tblAreaExternal records with 2 trailing 0's
    If no match found, match left 3 digits of the supplied postal code to tblAreaExternal records with 3 trailing 0's
    If no match found, match left 2 digits of the supplied postal code to tblAreaExternal records with 4 trailing 0's
    If no postal code is supplied, we will use the usual method for validating the data.
    Created by Mark Aengenheyster on 8/22/2005
    Air Worldwide Corp
    *******************************************************************************
    --   -------------------------------------------------------------------------------------- 
    --  Task | Date Modified || Change Summary 
    -------------------------------------------------------------------------------------- 
    --       | 6/12/2007     || added comments and code to backfill l2 l3 values 
    --       | 6/13/2007     || chanage the call to getareanames2 to return both  
    --       |               || name and briefname 
    --       | 7/12/2007     || Add 1012 SchemePath 
    --       | 8/8/2007      || Added @DEBUG and corrected problem with name values 
    --       |               || not returing when value is null string not a null     | 9/26/2007  Always perserve user provided LAT/LONG values   
    schemepath  Province  County Lat Long 
    1038                         X    X 
    1038                  X      X    X            replace County if not correct based on LAT LONG 
    1012        X                X    X            replace Province based on LAT LONG 
    1038        X         X      X    X            replace County and Province Based on LAT LONG     
    step 1 convert names  step2 verify SchemePath based on codes provided IF Province is provided scheme path and no County then 1012 else 1038 
    step 3 if lat long not 0 or null find closest area use codes returned else current process.   
    */   

    ALTER PROC.......
    Wednesday, October 24, 2007 7:19 PM
    Moderator

All replies

  • Please post the code of your stored procedure to the forum.

     

    Chris

    Tuesday, October 23, 2007 1:41 PM
  • Can you post the code of the procedure?

    Tuesday, October 23, 2007 1:42 PM
  • /*  ------------------------------------------------------------------------------  
    Code has been removed*/

    Wednesday, October 24, 2007 3:13 PM
  • I wonder if this is to do with your /* and */ comment markers.

     

    Try deleting from your script the two characters that I've highlighted below in red, then try re-creating and scripting your stored proc.

     

    Chris

     

     

     

     

    /*  ------------------------------------------------------------------------------  
    Detailed Description:  airsp_ValidateArea_China   
    Dependencies:    dbo.airsp_IsValidGeocode 
    stored procedure no no @fltLatitude 
    dbo.airsp_strcmpExcludeLeadingZeros stored procedure no no @str1   
    Called BY:    dbo.airsp_ValidateArea stored procedure   
    /*******************************************************************************
    airsp_ValidateArea_China
    China data will be validated using a non-standard approach.
    Although we don't model at the Chinese postal codes level, postal codes may be the only data that the customer has.
    If the customer supplies a postal code, we will map the postal code to the correct county code.
    The original postal code will not be overwritten.
    For some counties, we don't have every single postal code in tblAreaExternal. Some of the postal codes
    (the ones with trailing zeroes) are indicative of a range of values.  For example, the postal code 056600
    matches any postal code in the range of 056600 to 056699
    In order to find the correct county based on the customer supplied postal code, we will try up to 5 logical matches
    (this is done with a single query)
    The Matching Logic:
    Try to match postal code using all 6 digits.
    If no match found, match left 5 digits of the supplied postal code to tblAreaExternal records with 1 trailing 0 (zero)
    If no match found, match left 4 digits of the supplied postal code to tblAreaExternal records with 2 trailing 0's
    If no match found, match left 3 digits of the supplied postal code to tblAreaExternal records with 3 trailing 0's
    If no match found, match left 2 digits of the supplied postal code to tblAreaExternal records with 4 trailing 0's
    If no postal code is supplied, we will use the usual method for validating the data.
    Created by Mark Aengenheyster on 8/22/2005
    Air Worldwide Corp
    *******************************************************************************/
    --   -------------------------------------------------------------------------------------- 
    --  Task | Date Modified || Change Summary 
    -------------------------------------------------------------------------------------- 
    --       | 6/12/2007     || added comments and code to backfill l2 l3 values 
    --       | 6/13/2007     || chanage the call to getareanames2 to return both  
    --       |               || name and briefname 
    --       | 7/12/2007     || Add 1012 SchemePath 
    --       | 8/8/2007      || Added @DEBUG and corrected problem with name values 
    --       |               || not returing when value is null string not a null     | 9/26/2007  Always perserve user provided LAT/LONG values   
    schemepath  Province  County Lat Long 
    1038                         X    X 
    1038                  X      X    X            replace County if not correct based on LAT LONG 
    1012        X                X    X            replace Province based on LAT LONG 
    1038        X         X      X    X            replace County and Province Based on LAT LONG     
    step 1 convert names  step2 verify SchemePath based on codes provided IF Province is provided scheme path and no County then 1012 else 1038 
    step 3 if lat long not 0 or null find closest area use codes returned else current process.   
    */

    Wednesday, October 24, 2007 6:09 PM
  • You have nested comments before your ALTER PROC, which is not possible in TSQL.  Change your header to:

    /*  ------------------------------------------------------------------------------  
    Detailed Description:  airsp_ValidateArea_China   
    Dependencies:    dbo.airsp_IsValidGeocode 
    stored procedure no no @fltLatitude 
    dbo.airsp_strcmpExcludeLeadingZeros stored procedure no no @str1   
    Called BY:    dbo.airsp_ValidateArea stored procedure   
    *******************************************************************************
    airsp_ValidateArea_China
    China data will be validated using a non-standard approach.
    Although we don't model at the Chinese postal codes level, postal codes may be the only data that the customer has.
    If the customer supplies a postal code, we will map the postal code to the correct county code.
    The original postal code will not be overwritten.
    For some counties, we don't have every single postal code in tblAreaExternal. Some of the postal codes
    (the ones with trailing zeroes) are indicative of a range of values.  For example, the postal code 056600
    matches any postal code in the range of 056600 to 056699
    In order to find the correct county based on the customer supplied postal code, we will try up to 5 logical matches
    (this is done with a single query)
    The Matching Logic:
    Try to match postal code using all 6 digits.
    If no match found, match left 5 digits of the supplied postal code to tblAreaExternal records with 1 trailing 0 (zero)
    If no match found, match left 4 digits of the supplied postal code to tblAreaExternal records with 2 trailing 0's
    If no match found, match left 3 digits of the supplied postal code to tblAreaExternal records with 3 trailing 0's
    If no match found, match left 2 digits of the supplied postal code to tblAreaExternal records with 4 trailing 0's
    If no postal code is supplied, we will use the usual method for validating the data.
    Created by Mark Aengenheyster on 8/22/2005
    Air Worldwide Corp
    *******************************************************************************
    --   -------------------------------------------------------------------------------------- 
    --  Task | Date Modified || Change Summary 
    -------------------------------------------------------------------------------------- 
    --       | 6/12/2007     || added comments and code to backfill l2 l3 values 
    --       | 6/13/2007     || chanage the call to getareanames2 to return both  
    --       |               || name and briefname 
    --       | 7/12/2007     || Add 1012 SchemePath 
    --       | 8/8/2007      || Added @DEBUG and corrected problem with name values 
    --       |               || not returing when value is null string not a null     | 9/26/2007  Always perserve user provided LAT/LONG values   
    schemepath  Province  County Lat Long 
    1038                         X    X 
    1038                  X      X    X            replace County if not correct based on LAT LONG 
    1012        X                X    X            replace Province based on LAT LONG 
    1038        X         X      X    X            replace County and Province Based on LAT LONG     
    step 1 convert names  step2 verify SchemePath based on codes provided IF Province is provided scheme path and no County then 1012 else 1038 
    step 3 if lat long not 0 or null find closest area use codes returned else current process.   
    */   

    ALTER PROC.......
    Wednesday, October 24, 2007 7:19 PM
    Moderator
  • Try the below solution, it worked out for me.

    http://blog.wassupy.com/2011/02/recovering-from-sql-server-error-syntax.html


    From Harish

    Tuesday, November 19, 2013 4:56 PM
  • I had a similar problem where I got the error message when I attempted to modify a previously-created stored procedure.  I was unable to reopen the stored procedure and thought that I had just lost 3 months' work.  I was able to recover the stored procedure (splocal_CalculateNeedsList) by executing:

    SELECT

    ssm.definition

    FROM

    gbdb.sys.sql_modulesssm


    WHERE

    ssm.definitionLIKE'%spLocal_CalculateNeedsList%'

    I copied the ssm.definition to a new query window, then found the problem and re-executed the stored procedure.  In my case, this was the error:

    /*

    USE GBDB

    DECLARE @intErrorCode INT,

    @vchErrorMessage VARCHAR(25),

    @RunReport INT = 1

    IF (@RunReport = 1)

    BEGIN

    IF (OBJECT_ID('tempdb.dbo.#tblspLocal_CalculateNeedsList') IS NOT NULL)

    BEGIN

    DROP TABLE #tblspLocal_CalculateNeedsList

    END

    CREATE TABLE #tblspLocal_CalculateNeedsList (

    Id INT,

    ReportSection INT,

    /*

    Section 1 - Grinds Needed

    */

    */

    Once I replaced the

    /*

    Section 1 - Grinds Needed

    */

    with

    -- Section 1 - Grinds Needed

    The problem was solved.


     

    Thursday, December 6, 2018 7:24 PM