Sproc calling Sproc

Question

Sproc calling Sproc

Bill Holt

Mr or Mrs. 500

Points: 527
More actions
September 28, 2007 at 4:45 pm

#118842

We have a need to insert rows from a temp table into a master table. There are also rows in the temp table that need to update rows in the master table. Two sprocs have been created, one to insert, the other to update. The sproc that inserts rows has an Exec statement that executes the sproc that performs the update. The call to the first sproc is being made by Ms Access that seems to be using Transactions to control the process.
My question is: If the second sproc (update) fails due to a timeout condition, are all the transactions rolled back in the first sproc (insert) because it calls the update sproc? Hope this is clear.
My plan is to separate these into two calls by Ms Access but I am wondering about the performance.
Thanks,
Bill

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

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

I would definately make two separate procedures, but that is just my opinion. If you are going to run them from Access I am pretty sure you can manually set the time until timeout under Tools>Options. I have found that if the code is good Access will not timeout, so if you are having timeout issues it might be a slow connection or some code that needs help, in which case you should post it and let these fine experts here look at it. I don't know how you are calling the procedure from Access, but you should have a command button on a form and do like this:

Private Sub cmdEXECUTE_OnClick()

Dim strSQL as String

strSQL = "EXECUTE Your_Procedure"

DoCmd.RunSQL strSQL

Exit Sub

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Bill Holt Mr or Mrs. 500 Points: 527 More actions · Answer 2

Greg,

We have several issues compounding the timeouts. The timeout in Access is set to 480 seconds but heavy processor usage, an intermittent memory error that we believe is the memory controller, a slow point-to-point connection, etc. I'm just trying to break this thing down as much as possible and it raised a question about whether or not Access would rollback all of the sproc actions even if one sproc finished its insert but called the update sproc.

A new server, relocation to eliminate the P2P, are all in the works.

Thanks for the reply.

Bill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

One thing you can do to aleviate this is call the proc. In the proc raise an custom error. Set that error to start a JOB. In that job start the long queries (can't have timeout errors there).

Then have access query the server every 30 secs to see if the process is over (assuming access needs to know when that happens). I usually just setup a flag in a flags table to see if the process is over. You can use the @@spid of the connection in the raiseerror to know which connection started this call. You'll need to make sure that that connection queries the correct spid value to check for status, but I'm sure you get the basic idea.

Let me know if you need more help with this approach.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

Transactions??? Why do you need transactions for this??? That's the problem....

--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)

Bill Holt Mr or Mrs. 500 Points: 527 More actions · Answer 5

Jeff,

I agree. It appears that when you use a pass-through query in Access it uses transactions. The reason I believe that is the case is because when the timeout occurs, none of the transactions in the query, either inserts or updates, have been committed.

Ninja,

Interesting concept. However, these timeouts are recking havoc on other processes that are running, causing them to timeout as well. It appears to be related to the fact that MsAccess is calling these sprocs OR that the sproc calling another sproc is exceeding the timeout value set in Access, which is 480.

Thanks guys.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Is there anyway you can run that process at night and build the reporting table once a day?

You could even maintain it once-twice per hour with the new data if needed. We're pretty much taking shots in the dark hee because we don't have all the details. Can you give us all the info?

Bill Holt Mr or Mrs. 500 Points: 527 More actions · Answer 7

This is a batch process to create credit card and ACH transactions. The transactions are derived from an account table and inserted into a temporary transaction table (tblTempTranactions, not a SQL #temp table) for validation purposes, and to create so other associated transaction information such as discount fees for credit cards. There may be 50,000 ACH or CC transactions and with the additional transactions that are needed, we end up with (assuming 50,000 transactions) between 100,000 and 150,000 transactions in this temporary table.

Once the batch is verified as accurate, these transactions need to be inserted into tblTransaction. In some cases we are reprocessing previously failed transactions and updates are performed to tblTransaction.

Two sprocs are used to do the inserts and updates. When the sproc is called that inserts the transactions, it in turn calls the sproc that performs the update. These sprocs are called as pass-through queries from an Access frontend. If the process timesout, no transactions are ever committed, either inserts or updates, leading me to assume that Access must be using Transactions to control the pass-through query. There are no Transactions statements in the sprocs. Correct me if I am wrong, but there is no default usage of Transactions in SQL Server.

Tomorrow I plan to separate the sprocs into two calls by Access and add in some logging to follow this process a little more closely.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I'd strongly advise to go with my asynchronous solution. That way access would only be used as a starting trigger, nothing more. Timeouts could never be an issue.

Also access could periodically poll the server to check if the batch is complete.

I understand the need for manual review, but once it's accepted, the users should only be notified of the results of the batch and nothing more.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 9

Bill,

I guess I don't understand why you are calling these procs as passthrough queries from Access... can't you schedule this as a job or run it through OSQL? That would alleviate a lot of the timeout issues you're having with Access because it wouldn't even be in the picture anymore...

--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)