Post reply

stored procedure not running

  • November 24, 2022 at 11:55 am

    #4117546

    Created a stored procedure and logged in SSMS as Administrator and this user is exists in the Security > Logins

    I'm able to successfully execute the sp using T-SQL. e.g,

    declare @output_execution_id bigint
    exec dbo.int_Bill_Run @output_execution_id output

    Now, when I add it SQL server agent as a job I get errors - Executed as user: Domain\Administrator. The current security context is non-revertible. The "Revert" statement failed. [SQLSTATE 42000] (Error 15196). The step failed.

    Tried creating a user in Credentials and looked into Proxy accounts - but struggling.

    I've added all the relevant screenshots, can you help?

    Thank you

     

    Attachments:
    You must be logged in to view attached files.

  • November 24, 2022 at 12:49 pm

    #4117574

    Suggest you start by reading the documentation 😉

    😎

  • November 24, 2022 at 2:32 pm

    #4117600

    what is that proc doing?

    and what are you trying to accomplish (end target)

    as it resides on SSISDB I would suspect you are trying to start a package execution - and if that is the case you are in even bigger trouble as almost all of the SSIS catalog SP's do a lot of execute as/revert themselves.

  • November 24, 2022 at 3:39 pm

    #4117629

    So I installed sql server locally and deployed a dtsx package to the integration services catalog from VS. SSISDB database is already there, so thought I'd create the SP there.

    The aim for this is package to run once a month (unknown schedule as the day can be different each month), so the end user determines when to start this job.

    I've been looking a various ways to execute it. I can successfully execute a job of SSIS type using proxy account to the dtsx.

    Next looked at T-SQL calling a SP - also successful, then looked at putting the SP into a job (not sure if I need to) - this is where it errored.

    I suppose I should be asking, what is the best way to execute the package as a user? It needs to be executed outside of SSMS, so thinking of a command/batch file or windows app (one of colleagues should be able to create) which calls the SP or references the file location of the dtsx.

     

  • November 24, 2022 at 7:30 pm

    #4117697

    what will that package do?

    • Access file system (on another server or locally to the server - if remote it won't work)
    • access remote database (if so it will require hardcoded username/password to work on your case)
    • access local db (to SSIS server)
    • and importantly why SSIS and what exactly does the package do

  • November 25, 2022 at 7:29 am

    This was removed by the editor as SPAM

  • November 25, 2022 at 4:29 pm

    #4118028

    The package

    1. connects to a SQL server database to get current month data.
    2. pass a results of a variable (from 1) into an SSRS report defined in a script task (gets looped), this create excel files on one of the servers
    3. files are zipped, emailed and moved into a monthly folder.

    The above steps will eliminate the current manual processes.

    So everything should execute with one click of a button. I'm developing and testing locally before deploying to a prod environment. A user in another department will somehow execute this so permissions need to be looked at too.

    So I have a package that runs fine from the integration services catalog, next step is what method/tools to execute as another user.

  • November 25, 2022 at 6:03 pm

    #4118070

    I believe your only solution for this is to setup a SQL Agent job that executes the SSIS package (with a defined proxy) and introduce a SP to allow your users to kick off the job.

    see https://learn.microsoft.com/en-us/answers/questions/180861/grant-a-user-to-run-only-1-specific-sql-server-age.html on how to allow this job start option.

    another option it to have the job run on a schedule, multiple times a day, with a initial step that checks on a user db/table if the job is ready to be executed, and you setup a process that allows the users to update this table.

    your job would consist of 3 steps -

    1 - check if job should run - if not exit job (through an error but set job flow to quit with success)

    2 - execute ssis

    3 - update table flagging the job request as finished

  • November 25, 2022 at 11:08 pm

    #4118156

    With the understanding that I'm not even sure how to spell "SSIS", have you checked it to see if the stored procedure contains a "Revert" statement?

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 25, 2022 at 11:52 pm

    #4118162

    Jeff Moden wrote:

    With the understanding that I'm not even sure how to spell "SSIS", have you checked it to see if the stored procedure contains a "Revert" statement?

    all the SP's on SSIS catalog contain them - and if the one that the OP did also contains it that is why its messed up.

     

  • November 26, 2022 at 12:11 am

    #4118172

    frederico_fonseca wrote:

    Jeff Moden wrote:

    With the understanding that I'm not even sure how to spell "SSIS", have you checked it to see if the stored procedure contains a "Revert" statement?

    all the SP's on SSIS catalog contain them - and if the one that the OP did also contains it that is why its messed up.

    Wow!  Ok... thanks.  Like I said, the OP needs to check his stored procedure. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 10 (of 10 total)

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