September 18, 2002 at 12:12 pm
Looking at Profiler, we are looking at
Audit Login
BEGIN TRAN
Audit Logout
Error: Commit doesn't have a corresponding Begin Tran.
The logout (session/connection disconnect) apparently resets @@TRANCOUNT to 0. This causes our commit to fail.
We have looked long and hard at how to keep SQL Server from automatically logging us in and out of a session like that. We're trying a couple of solutions, but any hints would be welcome.
Steve
Steve Miller
September 18, 2002 at 2:37 pm
What cursor location/model you using? Have you considered just doing the transaction in the stored proc and not using ADO to handle it?
Andy
September 18, 2002 at 3:46 pm
We have a C++ front end. We're using OLE DB for the connection.
I wanted to do the code in a stored proc, but my colleagues are telling me it would be difficult to pull the code out of C++. Something about too many paramaters need from C. <sigh>
Steve
Steve Miller
September 18, 2002 at 8:15 pm
Ah well. Still, no reason you cant make it work using ADO. You'll have to correlate statements in Profiler with whats happening in your code, see why the connection is getting closed. Can you do a wider capture, lets see what else is going on?
Andy
September 19, 2002 at 7:42 am
Our problem looks much like the one reported in KB 191268. It gives no workaround. I don't how we have a server side query going on, but everything else matches.
We have worked with Profiler. We know that at some point SQL Server decides to isolate each query with its own connection. The BEGIN TRAN is one of these commands. The connection dedicated to the BEGIN TRAN ends, so when the COMMIT comes wandering along, isolated in its own connection, there's no BEGIN TRAN left.
We tried taking the BEGIN TRAN out, but ran into a separate, possibily related OLE DB bug. It's enough to make a coder start yelling.
As for your comment, "no reason you cant make it work using ADO", what do you mean? While familiar with RDMSs, I'm still new to this noise of trying to get a front end to work with SQL Server.
Thanks,
Steve
Steve Miller
September 19, 2002 at 10:55 am
I just meant that while I recommend you do your transactions either in TSQL directly (or maybe COM+ if that is a better fit) you can make the support for transactions built into ADO work. If you want to post code we'll look it over to see if we see anything. Other than that I recommend you leave out the transactions until you have everything working, then add them in, that way you only work one issue at a time.
Andy
September 20, 2002 at 4:46 am
If you are trying to execute all this in the connection you cannot as only one execution can be defined. However consider using a stored procedure as you execution statement. You don't have to define as a stored procedure perse. Take a look at this example and see how I accomplish it. (note: some stuff may not apply to you and you should be aware this is written for a multithreaded environment with event logging. Also, I this is my first C++ app so if I don't conform to standards it is because I am also learning it as I go.)
void SQLProc::InsertEvent(CString CCID, CString Agent, int Duration, CString PriState, CString SecState, CString AppID, int TimeOf)
{
if (!m_bSQLState)
return;
CEventLog eventLog;
char DurState[100];
_itoa(Duration, DurState, 10);
time_t lTime = TimeOf;
struct tm *aTime;
// Get current server time.
aTime = localtime(&lTime);
CString StartTime = asctime (aTime);
StartTime = StartTime.Right(StartTime.GetLength() - 4);
StartTime = StartTime.Left(StartTime.GetLength() - 1);
// Build query string
_bstr_t sqlQuery = "ip_InsertAgentData " + CCID + ", " + Agent + ", " + (LPCSTR) DurState + ", '" + PriState + "', '" + SecState + "', 0, '" + StartTime + "'";
try
{
m_pConnection->Execute(sqlQuery,NULL,NULL); // Execute out query string on our defined connection.
}
catch(_com_error &e)
{
m_bSQLState = FALSE;// Stop other processes from inserting data until reconnect,
// also this avoids multiple event log messages for SQL down.
// Notify the Event Log of errors if any.
_bstr_t bstrErrorMsg;
bstrErrorMsg = "Location: SQLProc InsertEvent\nSource: " + e.Source() + "\nDescription: " + e.Description();
m_eventLog.LogEvent(EVENTLOG_ERROR_TYPE, MSG_ERROR_1, bstrErrorMsg.operator char *());
m_pConnection->Close(); //Close the conection.
int nRetry = 0;//Counter for SQL Connect try.
do
{
m_bSQLState = SQLConn(); //Try to connetc to SQL Server
nRetry++; //Increment number of tries.
//Check attempt, if fail wait 5 seconds to give SQL chance to start and reattempt.
if(!m_bSQLState)
Sleep(5000);
}
while (!m_bSQLState && nRetry < 13);
// If after a minute the connection does not restore then data is not going to be logged until corrected.
if (!m_bSQLState)
m_eventLog.LogEvent(EVENTLOG_ERROR_TYPE, MSG_ERROR_1, "Unable to connect to SQL Server, data is not being logged.");
}
}
Hope it helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 20, 2002 at 8:07 am
Thanks for the effort. I'm kinda impressed that you would use this problem to try your first C++ app on. I have C++ coders all around here, and I've forwarded your message to them to look at.
In relation to KB Q191268, (ms-help://MS.VSCC/MS.MSDNVS/kboledb/Source/oledb/q191268.htm)I went through all our code, looking for open cursors that were not closed. I found four of them. Closing and deallocating them didn't have any affect on the bug I'm working on. <sigh>
Profiler shows isolation of each bit of SQL code after execution of one particular stored procedure. It uses a temp table and dynamic SQL. Nothing should be out of the ordinary there, but I'm going to take a look at that this morning.
Steve
Steve Miller
September 23, 2002 at 4:19 am
Thanks, this is a working piece of code from my first app so your guys should be able to use. The app which I am finishing is far larger and more complex than this with a server and gui client implementation. The snippet is pretty generic thou and has worked great for months in testing what I have completed so far. Fortunately I am nearly thru this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 23, 2002 at 7:34 am
The problem was in the C++ code! Ha!
Steve
Steve Miller
September 24, 2002 at 4:11 am
So you got your day. Glad problem was found. Exactly what was it in case someone else runs into? (Especially me)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 24, 2002 at 8:54 am
Well, I suppose I "got my day". I had originally thought the problem was on the front end, but was convinced otherwise. It was a relief knowing otherwise.
I can't answer the question what it was. It had to do with how our C++ code was working with the COM interface to SQL Server. The guys I work with are smarter than I am, so I took his word for it. Sorry I can't tell you more. We're chasing a real bad memory problem at the moment.
Steve
Steve Miller
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply