Sproc calling Sproc

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Sproc calling Sproc

Sproc calling Sproc

Rate Topic

Display Mode

Topic Options

Author

Message

Bill Holt

Posted Friday, September 28, 2007 4:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:48 AM
Points: 39, Visits: 18

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

Post #404260

Greg Snidow

Posted Friday, September 28, 2007 5:25 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 1,561, Visits: 2,315

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.

Post #404265

Bill Holt

Posted Saturday, September 29, 2007 4:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:48 AM
Points: 39, Visits: 18

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

Post #404337

Ninja's_RGR'us

Posted Saturday, September 29, 2007 4:54 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 21,357, Visits: 9,537

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.

Post #404341

Jeff Moden

Posted Saturday, September 29, 2007 10:03 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:20 PM
Points: 32,910, Visits: 26,799

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #404589

Bill Holt

Posted Sunday, September 30, 2007 6:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:48 AM
Points: 39, Visits: 18

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.

Post #404658

Ninja's_RGR'us

Posted Sunday, September 30, 2007 8:50 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 21,357, Visits: 9,537

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?

Post #404688

Bill Holt

Posted Sunday, September 30, 2007 11:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:48 AM
Points: 39, Visits: 18

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.

Post #404727

Ninja's_RGR'us

Posted Sunday, September 30, 2007 11:48 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 21,357, Visits: 9,537

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.

Post #404740

Jeff Moden

Posted Tuesday, October 02, 2007 10:35 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:20 PM
Points: 32,910, Visits: 26,799

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

Post #406029

« Prev Topic | Next Topic »

Permissions