Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-04-26 : 08:28:37
|
| Mike writes "My Application is written by ColdFusion.It calls a stored Procedure sp_A and sp_A calls sp_B.While my application is running , I open up SQL profiler to see what is actually doing. I can see the 'TextDate' exec sp_B @UserAddress, @Crtd_UserAfter that, then I see 'SET IMPLICIT_TRANSACTIONS OFF'?? It looks like to me the sp_A is trying to call sp_B, but sp_B never run. I don't have any ideas what's going on. FYI: I can run sp_A in SQL analyzer without any problem (sp_A successfully calls sp_B and completed).Please help..ThanksMike" |
|
|
twhelan1
Yak Posting Veteran
71 Posts |
Posted - 2006-04-26 : 10:54:30
|
From BOLquote:
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:ALTER TABLE FETCH REVOKE CREATE GRANT SELECT DELETE INSERT TRUNCATE TABLE DROP OPEN UPDATE If the connection is already in an open transaction, the statements do not start a new transaction.Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.
Did you run profiler when you executed sp_A in Query Analyzer? What does sp_B do and what makes you believe it is never run?~Travis |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-04-26 : 10:57:57
|
| You won't see individual statements contained in an SP in profiler output, just the sp call itself. If you want to confirm that sp_b is being called from sp_a, call sp_a from query analyzer and have both SPs print some text or write to table. Anything that will tell you what statements were made, which code paths were taken, etc.Be One with the OptimizerTG |
|
|
|
mlawmlaw
Starting Member
1 Post |
Posted - 2006-04-26 : 11:59:49
|
| Yes, I do have some print 'texts' in sp_A and sp_B. When I was running sp_A from Query Analyzer, I did see the print 'texts' from sp_A and sp_B in profiler. I only see 'Set Implicit_transactions off' when I was running sp_A through coldfusion .cfm page. I am not sure if this is a coldfusion issue or something else.. please suggest me some ideas. Thanks |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-04-26 : 13:45:42
|
| Sql Server default is OFF so unless your cold fusion connection sets it to ON everything is fine. And if it does set it to ON then it is correctly turning it back to OFF. You should be aware if your CF connection is always setting this to ON. You can confirm the setting by doing a SELECT statement then looking at the value of @@trancount. It will be > 0 if implicit_transactions is set to ON.Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-26 : 13:56:30
|
quote:
Originally posted by TG
You won't see individual statements contained in an SP in profiler output, just the sp call itself.
You can see individual statements contained in a stored procedure in SQL Profiler. You have to add Stored Procedures\SP:StmtCompleted to the trace. It will tell you which stored procedure this individual statement is coming from by placing a comment at the beginning of the TextData column.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
Stored procedures "Set Implicit Time off"