Could not find object '[databasename].objectname' or you do not have permission. [SQLSTATE 42000] (Error 15165)

  • Hi All,

    I have an sql agent job which goes through some of the stored procedures on diffrent databases, the service account has a sysadmin permission on the server but the jobs goes through and find some of the stored procedures and it is fine until half way through the process and throws the following error:

    "Could not find object '[databasename].objectname' or you do not have permission. [SQLSTATE 42000] (Error 15165)" which doesn't make sense as it is not a permission issue 

    Has anyone had the same issue and know what the resolution is please?

    Thank you in advance!

  • tt-615680 - Wednesday, July 18, 2018 8:47 AM

    Hi All,

    I have an sql agent job which goes through some of the stored procedures on diffrent databases, the service account has a sysadmin permission on the server but the jobs goes through and find some of the stored procedures and it is fine until half way through the process and throws the following error:

    "Could not find object '[databasename].objectname' or you do not have permission. [SQLSTATE 42000] (Error 15165)" which doesn't make sense as it is not a permission issue 

    Has anyone had the same issue and know what the resolution is please?

    Thank you in advance!

    So how and what exactly is this agent job doing?  From what you have posted I have no idea where to even start.
    Remember, we can't see what you see so you really need to provide more information.

  • Lynn Pettis - Wednesday, July 18, 2018 8:56 AM

    So how and what exactly is this agent job doing?  From what you have posted I have no idea where to even start.
    Remember, we can't see what you see so you really need to provide more information.

    Thank you for your reply!

    The code is trying to find the processes which take too log to execute.
    here is some of the code .....
    SELECT TOP 100 [Runs]
        , [Avg time]
        , [Last time]
        , [Time Deviation]
        , [% Time Deviation]
        , [Last IO]
        , [Avg IO]
        , [Last IO] - [Avg IO] AS [IO Deviation]
        , [DatabaseName]
    INTO #temp2
    FROM #temp1
    ORDER BY [% Time Deviation] DESC
       
    SELECT DISTINCT
        ' EXEC sp_recompile ' + '''' + '[' + [DatabaseName] + '].'
        + [mydb].dbo.MyProc([SomeQuery]) + '''' AS recompileRoutineSQL
    INTO #temp3
    FROM #temp2
    WHERE [DatabaseName] NOT IN ('master', 'msdb', 'model','tempdb')

    DECLARE @RecompilationSQL NVARCHAR(MAX)
    SET @RecompilationSQL = ''

    SELECT @RecompilationSQL = @RecompilationSQL
                    + recompileRoutineSQL + CHAR(10)
    FROM #RecompileQuery
    WHERE recompileRoutineSQL IS NOT NULL

    DECLARE @StartOffset INT
    DECLARE @Length INT
    SET @StartOffset = 0
    SET @Length = 4000

    WHILE (@StartOffset < LEN(@RecompilationSQL))
    BEGIN
        PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
        SET @StartOffset = @StartOffset + @Length
    END

    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)

    EXECUTE sp_executesql @RecompilationSQL

  • What does this function do:  [MyDB].dbo.MyProc.
    Where does this column come from: [SomeQuery].

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply