MDAC 2.6 and SQL Server 2000

I have been working on a VB6 app that talks to a SQL Server 2000 (SP2) engine via OLEDB. All works well with the exception of one transaction. The particular a transaction updates two tables via stored procs and then is either committed or rolled back.

I have an weird error where by when the transaction calls the second stored proc the data from the first stored proc is gone. I have put a break point in VB before the second stored proc is executed and the data exists but after execution of the second stored proc the data is gone.

The issue gets more weird becuase the app runs fine on a Win 2k machine with MDAC 2.5 but not with MDAC 2.6. I have other transactions that update via two stored procs but they are fine. I tried the app under MDAC 2.7 and that doesn't work either.

I have no rollbacks or commits in the stored procs. Also worth noting that if I am only upadting the second table by adding 5 rows for example, only the last row will be written.

Any suggestions would be most welcome.RE: Q1 Any suggestions would be most welcome.

A1 Are you quite certain you have tested your applications and results using the same MDAC levels accross the server, and the clients being tested (each time)? (There is an executable utility available to check and verify MDAC version levels.) Some symptoms sound like they may result from isolation level issues, while others sound quite peculiar given the data as presented.|||What is the "weird error" you are receiving ? The data that exists and then is gone - is it stored in a table and then "rolled back" out of the table ? I have attached ms component checker if you are concerned about mdac. You can either extract it or rename is to *.exe and it will extract automatically ( I have to a zip extension on it because exe's are not uploadable).|||A possible simple explanation for the "weird error" as described, is that it might be nothing more than an ordinary dirty read (made before a successful commit). The "when upadting the second table by adding 5 rows for example, only the last row will be written" issue is more peculiar. I've sometimes seen such kinds of things in procedures that use cursors (when "unexpected" conditions exist) and / or errors are not handled well.|||Is each stored procedure doing a commit or rollback (within the stored procedure) ? Can you post the stored procedures ( or a simpler version of what you are doing ) and the vb code ?|||The more I thought about your problem, I remembered a similar situation a couple of weeks ago - take a look at the following post and see if it fits:

post (http://dbforums.com/showthread.php?s=&threadid=554748)|||Hi.
Firstly, thankyou for your help. For reference, I am developing on a MDAC 2.6 machine running windows 2k, sp2, but I have referenced mdac 2.5 in the VB project references. The SQL server is running MDAC 2.6.
I can confirm that the stored procedures do not have any rollbacks, this is all handled by the VB code. The stored proc is called once for each row that is to be updated, as the VB code searches through a grid row by row. If any changes are detected on a row then the stored proc is called to perform the update.Once all rows have been searched and updated (if necessary) then the commit is called. In the event of an error then a rollback is called.
Thanks for your help!|||I would run sql profiler for both the successful and unsuccessful transactions - then look at the two and see what is different. If you don't see an answer within these results, please post the profiler output.|||RE: "I can confirm that the stored procedures do not have any rollbacks, this is all handled by the VB code. The stored proc is called once for each row that is to be updated, as the VB code searches through a grid row by row. If any changes are detected on a row then the stored proc is called to perform the update.Once all rows have been searched and updated (if necessary) then the commit is called. In the event of an error then a rollback is called."

Q1 I'm guessing "this is all handled by the VB code" means the ADO, ODBC, etc., API is making the actual transaction syntax on the Sql Server. That could account for the apparent MDAC related differences you are seeing. The rnealejr suggestion to run sql profiler traces for both the successful and unsuccessful transactions and compare them in minute detail is a very good one.

Q2 Just to clarify, this sounds like a description of a series of transactions (one for each updated row), collectively nested in a single transaction for the entire grid. Is that correct?|||Hi.
I have finally managed to make some progress on this matter. I ran the profiler, with a trace on two different situations, i.e. one which works OK and one which doesn't. I kept things simple and simply traced 1 - a deletion of 1 row, on its own, and 2 - a deletion of 2 rows, which rolls back the first deletion so that only the last worked on is actually committed (deleted). I am not sure how to post the trace files on here properly (forgive me, I am new to the profiler) but the text output is:-

Firstly for the deletion of one row which works..
---
set implicit_transactions on (Visual Basic)
---
declare @.P1 varchar(255) (Visual Basic)
set @.P1='OK'
exec UOM_CHANGE @.P1 output, 1, '108', '108', 'tracetest', 'TRC', '12/31/3000'
select @.P1
---
IF @.@.TRANCOUNT > 0 COMMIT TRAN (Visual Basic)
---

Now secondly the deletion of 2 rows which doesnt work so well....
---
set implicit_transactions on (Visual Basic)
---
declare @.P1 varchar(255) (Visual Basic)
set @.P1='OK'
exec UOM_CHANGE @.P1 output, 1, '108', '108', 'trace test 1', 'TRC1', '12/31/3000'
select @.P1
---
SET NO_BROWSETABLE ON (Visual Basic) *
---
SET FMTONLY ON EXEC UOM_CHANGE ' ',0,' ',' ',' ',' ',' ' SET FMTONLY OFF (Visual Basic) *
---
set fmtonly off (Visual Basic) *
---
SET NO_BROWSETABLE OFF (Visual Basic) *
---
declare @.P1 varchar(255) (Visual Basic)
set @.P1='OK'
exec UOM_CHANGE @.P1 output, 1, '109', '109', 'trace test 2', 'TRC2', '12/31/3000'
select @.P1
---
IF @.@.TRANCOUNT > 0 COMMIT TRAN (Visual Basic)
---

Now then, the only difference between the two (apart from of course that the stored proc is called twice in the second trace) are the four operations that I have highlighted above with an asterisk. From reading MSDN it would seem that there is a known bug that a patch is available for, so I will need to look into this further.
Please see this article, ref Q281633

http://support.microsoft.com/default.aspx?scid=KB;en-us;q281633

Our customers build is MDAC 2.5 so we must code to their requirements, but possible the MDAC 2.6 service pack may work. Watch this space!
Thanks for your help, its proven very useful.|||Bingo ! Are you running the original version of mdac 2.6 (no service packs) ? If so, then you hit the nail on the head. Actually, I ran into this problem a few months ago - once I saw your profiler with the set fmtonly - it reminded me.sql

MCDBA hot or not?

I've been working with SQL for close to a year now and I'm wondering if its worth the time and money to become certified. Are DBA's still on the up and rising or another job that will be outsourced to India?Anyone???|||I'm curious of the same thing. I have been working with SQL for a little over a year. I'm wondering if it's still on the up and rising and also if it pays financially. I've heard both good and bad so far. :confused:|||While some DBA work could be done remotely from offshore contractors, most companies want at least a few critical support people on-site. DBA's and network engineers seem very stable positions at this point. The developer DBA positions seem to be moving offshore with the programming staff. Make sure you are a production DBA with skills in data warehousing and you will have a career in the USA for the next ten years.

The MCDBA certification might make the difference if your company is deciding which positions to outsource. If I was making that decision, I would keep the MCDBA's with five years experience and outsource those with less credentials.

Just my opinion but I have read similar sentiments in the IT trade publications...

maximum number of tables in a query (260) was exceeded

I am getting the following error message from a view I have created. I
would think it was a design issue but it appeared to be working at one time.
When it was reopened I received the following error:
--Could not allocate ancillary table for view or function resolution. The
maximum number of tables in a query (260) was exceeded.
Any ideas on how to correct this? Thanks.
DonHi
If there are 260 tables in the query then you will need to think about the
design. You may have subsequently changed a view to include more tables it
was working previously.
John
<dbj> wrote in message news:uj39oq$UFHA.3188@.TK2MSFTNGP09.phx.gbl...
>I am getting the following error message from a view I have created. I
>would think it was a design issue but it appeared to be working at one
>time. When it was reopened I received the following error:
> --Could not allocate ancillary table for view or function resolution.
> The maximum number of tables in a query (260) was exceeded.
> Any ideas on how to correct this? Thanks.
> Don
>

max_errors in bcp

Can any one help on this ?
I have set the -m parameter for bcp for importing but its
not working.
C:\>bcp TestDB.dbo.TestTable in E:\DataFile.txt -w -m1000
-eE:\Error.txt -SServerName -Usa -P
ThanksWhat exactly is the problem? Couyld you be more specific?
Tengiz Khartishvili
SQL Server Storage Engine
This posting is provided "as is" with no warranties and confers no rights.
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:11a5401c3f544$0570b6c0$a401280a@.phx
.gbl...
> Can any one help on this ?
> I have set the -m parameter for bcp for importing but its
> not working.
> C:\>bcp TestDB.dbo.TestTable in E:\DataFile.txt -w -m1000
> -eE:\Error.txt -SServerName -Usa -P
>
> Thanks
>

max worker threads option

Hi,
I am working on a SQL server and the "Maximum worker threads" option is set
to zero. That seems strange to me.
What does that means? Does zero have special meaning for "Maximum worker
threads" option?
Thank you,
AlanA.M wrote:
> I am working on a SQL server and the "Maximum worker threads" option is se
t
> to zero. That seems strange to me.
> What does that means? Does zero have special meaning for "Maximum worker
> threads" option?
The default setting is 255, so how yours ended up at 0 seems odd. For
more information look at "max worker thread Option" in Books Online.
http://msdn.microsoft.com/library/e...config_09wu.asp
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Thank you for reply.
I already went through books online. I haven't see any place explain the
meaning of the value zero for "Maximum worker threads". That is what I am
looking for.
Alan
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:OPyv$g4DFHA.576@.TK2MSFTNGP15.phx.gbl...
> A.M wrote:
set
> The default setting is 255, so how yours ended up at 0 seems odd. For
> more information look at "max worker thread Option" in Books Online.
> http://msdn.microsoft.com/library/e...config_09wu.asp
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||A.M wrote:
> I already went through books online. I haven't see any place explain the
> meaning of the value zero for "Maximum worker threads". That is what I am
> looking for.
Glad to hear, so used to people not checking there first. It looks like
the values are only valid from 32-32767. So my guess would be that if it
is zero it defaults to either 32 or 255.
I would definitely be interested to know how it got set to 0, I would
think that there would be something internal to SQL to prevent this. And
also what it is actually using inside SQL.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/