September 24, 2001 at 1:55 pm
Hello.
I have a COM+ application running on a Win2K sp2 box that executes a series of large jobs on demand. The application begins execution of each job by executing a MAIN stored proc on a SQL Server 7.0 DB, that in turn executes a series of stored procedures on 2 linked SQL servers. After the MAIN stored proc completes, the Data tier of the COM+ application sets a transaction vote and returns back to the business tier, which sets the final tx vote.
I'm having a problem with the small steps inside each MAIN procedure. Every so often, I receive the following error:
Error Description: -2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
I receive this error before the global timeout value (global timeout is 1 hr, i get this error in 15 min).
Also, it has proved difficult for me to repeat this error with any regularity. Example: The batch works with one set of paramters, but the same batch fails with a different set of parameters. When i use the word "batch" i'm referring to the job begun by the COM+ component, executed by Stored procs, and returned to COM+ to set tx votes.
Also, the MAIN stored procedure always works fine with any set of paramters, if it is run through MS Query Analyzer, and not through the COM+ App.
finally, I've received this error number many times with different (yet equally ambigious) error messages.
Help!
-
Matthew mamet
Matthew Mamet
September 24, 2001 at 4:08 pm
Have you looked at this article:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q260895
Are you running the most recent MDAC on your COM+ box and the latest SP on our SQL box? Are you logging everything so you can make sure it's not a syntax error (embedded single quote or something equally bizarre)?
Andy
September 25, 2001 at 6:54 am
nod.
i'm using "set xact_abort on/off"
using sql server profiler, i have not been able to find any sql syntax errors.
this error is such a pain!
Matthew Mamet
September 25, 2001 at 9:34 am
I usually see this error when my T-SQL code does something "illegal" in SQL. Like a divide by 0 (thought I think that actually throws a reasonable error).
Recheck the SQL code (or post it) with the parameters and be sure no errors can be thrown.
Steve Jones
September 25, 2001 at 12:37 pm
Maybe rather than post all the code (there are quite a few procs), I'll narrow my question down a bit.
When you're inside a transaction that is being governed by MTS/DTC, and that transaction must connect to more than 1 SQL Server to complete its task, which is "best" and why?
1. Simply connect to the linked server by fully qualified name.
Example: Select * FROM MyLinkedServer.MyDB.dbo.MyTable
2. Use OpenQuery.
Example: Select * from OPENQUERY(MyLinkedServer, "Select * FROM MyDB.dbo.MyTable")
3. Use OpenRowset.
Example: Select * FROM OPENROWSET('SQLOLEDB', 'MyLinkedServer'; 'MyUID'; 'MyPass',
"SELECT * FROM MyDB.dbo.MyTable")
Do any of these 3 methods work any better under an MTS transaction than the other?
-
Matthew
Matthew Mamet
September 25, 2001 at 3:44 pm
You're outside my experience at that point. I find linked servers incredibly useful and I would guess (not tested) that there is a little less overhead since your setup is already done ahead of time. I would still expect it to either work or fail consistently.
I agree with Steve that there is a good possibility that something is wrong with the syntax or the data on rare occasion. Could it be a deadlock even?
Only way I know to really resolve is log the hell out of everything, wait for it to happen and try to sort through it. You need to know how far it got and what was happening when it failed. Might also want to profile your TSQL activity on the server in parallel.
Sorry can't help more than that. I'm guessing you've checked MSDN and Dejanews/Google already?
Andy
September 27, 2001 at 12:00 pm
ok, i solved my problem. here's what was going on (if anyone's still interested! :P)
there was actually two errors.
the first error was a simple pk_violation error occuring somewhere deep within my procedure.
the error was being reported back to my COM+ application, and the component dutifully raised the error through its error handler -- where a 2nd bug was lurking in wait.
the ADO Connection object was being set to nothing before the error collection could be retrieved out of it.
for any of you who know about VB COM and ADO, there are 2 error objects, the VB one, and the ADO one.
soooo, to make a long story short - that error number (-2147217900) is an error number that's attempting to tell you that MORE THAN ONE error occurred, and you should loop through the ADO connection's error object to get the details.
WHEW!
Matthew Mamet
September 27, 2001 at 12:33 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply