Release Automation

Hi All,

When we execute the SQL script through tool we are getting below error massage and if we execute the script manually on database through Query analyzer IDE, its successful.

Fail to run script [\\gladevcontrol.dev.att.com\E$\inetpub\releasedocs\Test_RA_Deploy\20160727\iHosting\TestDBScript_072616.sql]: Incorrect syntax near 'ON'

Also Exactly we are not getting line number where it was giving error through CA RA Tool.

Can you share the DB type , RA version , RA action configuration and the  SQL file that cause this issue?

Thanks

Jacky

DB : MS sql server

RA version: 5.5.2

RA Action: Run SQL File on Microsoft(c) SQL Server

sql script:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spAENSiHosting_GMO_SuperUser_GetUserMaintenanceSearchResult]

    -- Add the parameters for the stored procedure here

    @attuid VARCHAR(6)

    , @groupid VARCHAR(10)

    , @clli VARCHAR(11)

    , @lastname VARCHAR(30)

    , @firstname VARCHAR(30)

    , @govtaccess VARCHAR(10)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    DECLARE @sql VARCHAR(2000), @where VARCHAR(1000)

    SET @sql = 'SELECT

        U.hrid,

        PN.firstname,

        PN.lastname,

        U.clli,

        U.groupid,

        G.groupname,

        U.GovernmentAccess,

        CONVERT(CHAR(21), U.lastupdateddatetime,100) AS lastupdateddatetime,

        ISNULL(PN1.firstname, ' + '''' + ' ' + '''' + ') + ' + '''' + ' ' + '''' + ' + ISNULL(PN1.lastname, ' + '''' + ' ' + '''' + ') AS lastupdatedby

    FROM TblAENSiHostingUsers U (NOLOCK)

    JOIN TblAENSiHostingGroups G (NOLOCK) ON U.groupid = G.groupid

    LEFT JOIN [GEOLINKDATASERVER].[ncsdata].[dbo].TblPostNames PN (NOLOCK) ON U.hrid = PN.hrid

    LEFT JOIN [GEOLINKDATASERVER].[ncsdata].[dbo].TblPostNames PN1 (NOLOCK) ON U.lastupdatedby = PN1.hrid '

    SET @where = ' WHERE 1=1 '

    IF ((@groupid != '') OR (@attuid != '') OR  (@clli != '') OR (@lastname != '') OR (@firstname != '') OR (@govtaccess != ''))

    BEGIN

        SET @sql = @sql

        --IF ((@groupid != '') AND (@where = ''))

        --    SET @where = @where + ' U.groupid = ' + '''' + @groupid + ''''

         IF (@groupid != '')

            SET @where = @where + ' AND U.groupid = ' + '''' + @groupid + ''''

        --IF ((@attuid != '') AND (@where = ''))

        --    SET @where = @where + ' U.hrid like ' + '''' + '%' + @attuid + '%' + ''''

         IF (@attuid != '')

            SET @where = @where + ' AND U.hrid like ' + '''' + '%' + @attuid + '%' + ''''

        --IF ((@clli != '') AND (@where = ''))

        --    SET @where = @where + ' U.clli like ' + '''' + '%' + @clli + '%' + ''''

         IF (@clli != '')

            SET @where = @where + ' AND U.clli like ' + '''' + '%' + @clli + '%' + ''''

        --IF ((@lastname != '') AND (@where = ''))

        --    SET @where = @where + ' PN.lastname like ' + '''' + '%' + @lastname + '%' + ''''

         IF (@lastname != '')

            SET @where = @where + ' AND PN.lastname like ' + '''' + '%' + @lastname + '%' + ''''

        --IF ((@firstname != '') AND (@where = ''))

        --    SET @where = @where + ' PN.firstname like ' + '''' + '%' + @firstname + '%' + ''''

         IF (@firstname != '')

            SET @where = @where + ' AND PN.firstname like ' + '''' + '%' + @firstname + '%' + ''''

        --IF ((@govtaccess != 'ALL') AND (@where = ''))

        --    SET @where = @where + ' U.GovernmentAccess = ' + '''' + @govtaccess + ''''

         IF (@govtaccess != 'ALL')

            SET @where = @where + ' AND U.GovernmentAccess = ' + '''' + @govtaccess + ''''

    END

    SET @sql = @sql + @where + ' ORDER BY PN.firstname '

    PRINT @sql

    EXEC(@sql)

END

The reason why your script failed has to do with the delimiters, you only have the "GO" value; this will make the action fail. Please modify the delimiter value to be just

Now change the delimiter to the value below, and your SQL script will execute.

thanks. it worked.