CODEWITHSUNDEEP
sqlsql-server
Sheen
Sheen

Reputation: 3441

SQL Server: Stored proc can execute well but at the end error "Cannot find the object"

all:

I've searched for a couple of hours now regarding problem I get. There are similar questions on Google and here, but none is same.

I have a simple stored procedure A, EXECUTE permission is correctly granted to my login. It basically populates some table with data. It is in same schema as my login user. I can EXECUTE it successfully, which means all data are populated expectedly. However, at the end of execution, the message window shows:

Msg 15151, Level 16, State 1, Procedure A, Line xxx Cannot find the object 'A', because it does not exist or you do not have permission.

The line number points to end of SP definition. I have no idea why this error message comes up and the SP seems no problem at all in terms of execution result.

EDIT:

The SP code is like below:

IF EXISTS (SELECT * FROM SysObjects WHERE id = OBJECT_ID('dbo.spA') AND type = 'P')
    DROP PROCEDURE dbo.spA
GO

CREATE PROCEDURE dbo.spA
    @ReportCCY as char(3) = 'GBP',
    @Date as date = null                  /* for regression testing */
AS
BEGIN

    if (@Date is null)
        select @Date = convert(varchar(20), max(VDate), 112) from JB H (nolock)

    declare @dbname as varchar(50)
    select @dbname = DB_NAME()
    exec DBA_admin.App.p_usr_Truncate @DB=@dbname, @Schema='dbo', @Table='ATable'

    insert into dbo.ATable
    select r.Value, I.id
    from Rates r
    join Inst I (nolock) on I.idCCyBase = r.idHold and r.ccyinto = @ReportCcy and r.dtDate = @Date
END

GRANT EXECUTE ON dbo.spA TO UserGrp
GO

Upvotes: 3

Views: 985

Answers (1)

ventik
ventik

Reputation: 887

You forgot 'GO' after 'END'. Now 'GRANT EXECUTE' is a part of your procedure.

Upvotes: 5

Related Questions