MDAC 2.8 Issues?

Most of our Windows 98 SE workstations that are running
MDAC 2.8 have an issue when posting a transaction against
a SQL Server 2000 SP2 database. OLE DB Provider for SQL
Server is the database connector and the application
making the call is coded in Visual Basic 6.1.

The client sees this error returned as an "Runtime 6 -
Overflow Error" on the Visual Basic end as reported by the
OLE DB Provider. Below is a snippet as taken from the SQL
Server Profiler trace:

declare @.P1 int
set @.P1=180150062
declare @.P2 int
set @.P2=2
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT TblJobs.[Control
Status Code], TblJobs.[Sales Date], TblJobs.[Promise
Date], TblCust.Last, TblCust.First, TblCust.MI, TblCust.
[Home Phone] FROM TblCust RIGHT JOIN TblJobs ON TblCust.
[Customer ID] = TblJobs.[Customer ID] WHERE TblJobs.
[Control Number] = 00801328', @.P2 output, @.P3 output, @.P4
output
select @.P1, @.P2, @.P3, @.P4

exec sp_cursorfetch 180150062, 32, 1, 1

exec sp_cursorclose 180150062

Error: 2809, Severity: 18, State: 1

Error: 16945, Severity: 16, State: 1

declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=229378
declare @.P3 int
set @.P3=294916
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'exec TblTransactions',
@.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4

Error: 2809, Severity: 18, State: 1

Error: 16945, Severity: 16, State: 1

Any suggestions? Would using a stored procedure benefit
any? Are there known issues with MDAC 2.8 operating in
this manner? When I rollback to MDAC 2.5 or 2.6 the
operation goes through successfully. I don't relish
rolling back a decent number of client workstations seeing
other MDAC-dependent applications work fine. Unfortunately
I don't have the VB source code to post, so I might be
grasping at straws by posting here.

Any help would be appreciated!Howdy

MDAC 2.8 should really be run against SQL 2000 SP3A - nothing less.
You are running SQL 2000 SP2.

Seems to be lots of problems with SQL 2000 and MDAC & VB ( 6 & .NET )

Cheers,

SG|||I just tested this out by upgrading my local MSDE to sp3a. Restarted and tried the same transaction type and it failed just the same. I really am dreading rolling back all of these workstations, especially since just this one custom app is the one giving me trouble. Wish I myself was a VB programmer so I could dig into things for myself!

Can't imagine something as simple as trying to append a row to an existing set could offer problems. And if MDAC 2.8 is the culprit (since rolling back seems to fix things) I can't imagine no one else piping up about odd MADC 2.8 behavior. Oh well...*sigh*|||Howdy,

It might be worth trying setting nocount on in the sprocs to see what that does...

It may be the "chatter" that the app produces may be causing buffer overflows perhaps. I found setting nocount to on helps with some apps

Cheers,

SG.

Max value of column in DB

Hey everyone!
Ok, I'm not exactly sure this is an easy task so I hope im posting this in the right area.
What I have is a column in my SQL Server database that is just an ID number.
I had previously used Access and being SQL Server noob, I had no idea if it had a property type like Access' "AutoNumber". I just made it an integer
What I want to do though now, is assign ID's automatically to each new record by making it 1 above the most recently added one. There are two ways I've thought I can be helped:
1) Is there code to find the maximum value of a column in a database, return the value, and then add 1 to it?
or
2.) Can someone give me information about how I could use a feature like Access' AutoNumber in SQL Server?
THANKS GUYS FOR ALL YOUR HELP!In SQL server, you can make the column data type as integer and set identity = yes, identity Seed = 1 and identity Increment = 1 in Columns tab.|||IDENTITY in SQL Server is a property like Access Autonumber. Run a search for IDENTITY and IDENTITY Scope, IDENT_SEED, IDENT_INCR and SET IDENTITY_INSERT option and System varaible IDENTITYCOL in the BOL(books online) to get started. Hope this helps.|||

Worked out perfect, exactly what I was looking for!

Thanks a ton guys!