Problems with EXECUTE SQL Task
-
Iam Looking for some help with a weird situation iam seeing when i use the Execute SQL Task.
Iam using this task to execute a stored procedure. The stored procedure itself uses cursors within and makes calls to several other stored procs. Normally, this stored proc would take atleast 2-3 hours to complete.
Now for some reason on the Production Database, this Execute SQL task is completing within a few seconds and the package moving on to the next task. What iam seeing is that control is existing out of the the stored procedure prematurely much before its done is job. And this behaviour is happening only this production database, on other databases it works fine.
And the best part is - If call the same stored proc from Query Analyser, the SP runs its full duration (2-3 hours) and completes successfully. Only when i excute this SP using a DTS task, that i see this weird behaviour. So,iam suspecting its something to do with the DTS package that is causing this type of behaviour.
Is there something I can with DTS to make sure that the stored proc executes till its completion and not exit prematurely?
At present i have following settings for the DTS execute SQL Task :
Task takes parameters
Command Time out is 0 (indefinite)
Step commits transaction on successful completion
Step rollsback transaction on failure
Step closes connection on completion
Is there anything else i can do? Any help would be appreciated.
Thanks
-
"And this behaviour is happening only this production database, on other databases it works fine."
This sorta rules out DTS and your stored procedures doesn't it? I'd start off by double-checking the settings between the one that works and the one that doesn't.
--------------------
Colt 45 - the original point and click interface
-
Rajesh,
This may be cecause of the account that the task is being run under. If this account doesn't have the correct permissions to call the stored proc or access to data objects called by the procedure it will fail. It is also possible the proc hasa clause in to to return if the caller doesn't belong to the correct server role ie
-- only members of the 'securityadmin' role can run this stored procedure
IF ( NOT (IS_SRVROLEMEMBER('securityadmin') = 1) )
BEGIN
RAISERROR(15003, -1, -1, 'securityadmin')
RETURN
END
Trying logining into a windows session using the nt account that the sql server agent runs under, connect to query analyser using windows authentication and run the stoed proc from there - this will let you know if it is this account that has the problems. Also if you are calling this process from a job make sure the owner field in the general tab of the job is set to an account with the correct permissions
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply