June 15, 2015 at 8:32 am
Hi,
I have a procedure which inside I call two other Procedures, the first one is updating table Purchase Order and the second one is updating an intermediary table afterPurchase Order update. I have an issue, the intermediary table is not always being updated. Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly? Should I use "with(nolock)" in the Purchase Order select ?
Thank you,
John
June 15, 2015 at 9:31 am
With nolock won't help you. Don't think you want to do a dirty read of your data. If you put some code in here we might figure out what the problem is.
June 15, 2015 at 12:08 pm
Did you try using GO?
Example:
First SP
GO
Second SP
June 15, 2015 at 12:23 pm
yb751 (6/15/2015)
Did you try using GO?Example:
First SPGO
Second SP
This will not work inside a procedure.
Can you post your code?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 15, 2015 at 12:38 pm
Michael L John (6/15/2015)
yb751 (6/15/2015)
Did you try using GO?Example:
First SPGO
Second SP
This will not work inside a procedure.
Can you post your code?
Darn...missed that in the OP
June 15, 2015 at 2:21 pm
j.grimanis (6/15/2015)
Hi,I have a procedure which inside I call two other Procedures, the first one is updating table Purchase Order and the second one is updating an intermediary table afterPurchase Order update. I have an issue, the intermediary table is not always being updated. Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly? Should I use "with(nolock)" in the Purchase Order select ?
Thank you,
John
SQL statements will run one after the other, they don't overlap, not even when calling different stored procedures. You need to review the code and be sure that it's not a multiple connections problem. Don't use nolock hints, unless you want unreliable results.
You might want to use explicit transactions to understand what's happenning.
June 15, 2015 at 6:26 pm
Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly?
It's certainly possible, particularly if the code has ROLLBACK statements in it that might be used, such as in a CATCH block.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 16, 2015 at 9:06 am
The second procedure( which does not work correct some times) is being executed after a "Commit Transaction" inside the first proc. Is there a possibility that rows are still in commit process (multiple users call the first procedure ) and when the second procedure is running, the respective tables have not the updated data? If this happens, how should I solve it...?
June 16, 2015 at 9:10 am
No, an explicit COMMIT will lock in the transactions.
But if an explicit BEGIN TRANS was issued and either:
1) a COMMIT has not yet been issued for that trans
or
2) a ROLLBACK has been issued
then those changes would not be visible to later queries (unless you were using NOLOCK).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 16, 2015 at 9:10 am
j.grimanis (6/16/2015)
The second procedure( which does not work correct some times) is being executed after a "Commit Transaction" inside the first proc. Is there a possibility that rows are still in commit process (multiple users call the first procedure ) and when the second procedure is running, the respective tables have not the updated data? If this happens, how should I solve it...?
No, that's not a possibility. The second procedure won't start if the first hasn't completed.
June 16, 2015 at 9:13 am
Why don't you call the 2nd stored procedure before committing the transaction?
Are you using in the 2nd stored procedure some identity column values obtained in the first stored procedure? (identity - scope_identity() mistake)
June 16, 2015 at 9:15 am
j.grimanis (6/15/2015)
Hi,I have a procedure which inside I call two other Procedures, the first one is updating table Purchase Order and the second one is updating an intermediary table afterPurchase Order update. I have an issue, the intermediary table is not always being updated. Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly? Should I use "with(nolock)" in the Purchase Order select ?
Thank you,
John
Look how many guesses you have, John. How about showing your code so folks don't have to guess?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2015 at 10:02 am
ChrisM@Work (6/16/2015)
j.grimanis (6/15/2015)
Hi,I have a procedure which inside I call two other Procedures, the first one is updating table Purchase Order and the second one is updating an intermediary table afterPurchase Order update. I have an issue, the intermediary table is not always being updated. Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly? Should I use "with(nolock)" in the Purchase Order select ?
Thank you,
John
Look how many guesses you have, John. How about showing your code so folks don't have to guess?
That's the bottom line here. Without the code, everything's a guess. We can't see what you see unless you post it.
June 16, 2015 at 10:27 am
Ed Wagner (6/16/2015)
ChrisM@Work (6/16/2015)
j.grimanis (6/15/2015)
Hi,I have a procedure which inside I call two other Procedures, the first one is updating table Purchase Order and the second one is updating an intermediary table afterPurchase Order update. I have an issue, the intermediary table is not always being updated. Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly? Should I use "with(nolock)" in the Purchase Order select ?
Thank you,
John
Look how many guesses you have, John. How about showing your code so folks don't have to guess?
That's the bottom line here. Without the code, everything's a guess. We can't see what you see unless you post it.
Are you saying that you don't have a crystal ball? :w00t: :hehe:
June 16, 2015 at 10:41 am
Luis Cazares (6/16/2015)
Ed Wagner (6/16/2015)
ChrisM@Work (6/16/2015)
j.grimanis (6/15/2015)
Hi,I have a procedure which inside I call two other Procedures, the first one is updating table Purchase Order and the second one is updating an intermediary table afterPurchase Order update. I have an issue, the intermediary table is not always being updated. Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly? Should I use "with(nolock)" in the Purchase Order select ?
Thank you,
John
Look how many guesses you have, John. How about showing your code so folks don't have to guess?
That's the bottom line here. Without the code, everything's a guess. We can't see what you see unless you post it.
Are you saying that you don't have a crystal ball? :w00t: :hehe:
Yes, I shipped it out two weeks ago. 😉
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply