Someone else editing record ? Only me on the server ?

[Ryan]

I have some software (written in Delphi 5) which has been working for
several months without a problem.

I have been given a copy of the database on our development server
(SQL 7) and have pointed the software to this. So, nothing has changed
with the software. In theory the servers should be the same.

However, if I try to make a small change to any record (as I would on
the 'live' system), I get an error stating that another user is
editing the record. Now, I'm the only person with access to this
server. So am I stopping myself I wonder ?

If I create an ODBC connection to the database through Access 2000, I
get the same error. I feel I can reasonably assume that the software
is not at fault.

I'm sure it's something SQL based, whether it's an error or
configuration difference, but I'm stuck.

I've checked who is locking/blocking, and yes it will show me as
having the record open, but this is me attempting to edit the record
so I would expect this.

I can edit the table directly in SQL, but it takes 30 seconds or more,
freezes EM and then allows the change. Table info below :

if exists (select * from sysobjects where id =
object_id(N'[dbo].[PostReceived]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[PostReceived]
GO

CREATE TABLE [dbo].[PostReceived] (
[PostID] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (100) NULL ,
[ClientsName] [varchar] (100) NULL ,
[DateReceived] [datetime] NULL ,
[EnteredBy] [varchar] (100) NULL ,
[AssignedTo] [varchar] (100) NULL ,
[DateAssignedTo] [datetime] NULL ,
[Adviser] [varchar] (100) NULL ,
[TargetDate] [datetime] NULL ,
[CompletionDate] [datetime] NULL ,
[Completed] [bit] NULL ,
[KeyAccount] [varchar] (100) NULL ,
[Notes] [text] NULL ,
[Specific1] [varchar] (20) NULL ,
[Specific2] [varchar] (20) NULL ,
[Specific3] [varchar] (20) NULL ,
[Specific4] [varchar] (20) NULL ,
[Specific5] [varchar] (20) NULL ,
[ToDelete] [bit] NULL ,
[EnterUser] [varchar] (20) NULL ,
[Returned] [bit] NULL ,
[ReturnDate] [datetime] NULL ,
[ReturnReason] [varchar] (87) NULL ,
[PrintAdviser] [bit] NULL ,
[EmailAdviser] [bit] NULL ,
[EmailSM] [bit] NULL ,
[EmailRegionMana ger] [bit] NULL ,
[ReturnText] [varchar] (150) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

(Yes I know about using Identity in the key, but it's on my list to
change before anyone says anything).

[Erland Sommarskog]

Re: Someone else editing record ? Only me on the server ?

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> I have some software (written in Delphi 5) which has been working for
> several months without a problem.[/color]

Has it? Haven't you been posting about it all over again and again? :-)
[color=blue]
> However, if I try to make a small change to any record (as I would on
> the 'live' system), I get an error stating that another user is
> editing the record. Now, I'm the only person with access to this
> server. So am I stopping myself I wonder ?
>
> If I create an ODBC connection to the database through Access 2000, I
> get the same error. I feel I can reasonably assume that the software
> is not at fault.
>
> I'm sure it's something SQL based, whether it's an error or
> configuration difference, but I'm stuck.[/color]

So what is the exact error message you get with ODBC and Access? Including
bracketed stuff like [Microsoft SQL Server Driver] and that?

This is usually a client thing, unless you have a trigger on the table.
SQL Server is certainly not producing a message like that out of the blue.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Someone else editing record ? Only me on the server ?

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> Access Error :
>
> "This record has been changed by another user since you started
> editing it. It you save the record, you will overwrite the changes the
> other used made.
>
> Copying the changes to the clipboard will let you look at the values
> the other user entered, and then paste your changes back in if you
> decide to make changes."
>
> Options to copy to clipboard or drop changes are shown. The error was
> thrown trying to edit the record in a linked table.[/color]

So that is an error message from Access, and since I don't know Access
I have no idea how Access arrives at this conclusion, but I can tell
you that it is not SQL Server that whispers in it is ear.

I can think of two ways to find out:

1) Use the SQL Server Profiler, to see what Access sends to SQL Server.
2) Ask in an Access newsgroup.
[color=blue]
> Delphi Error :
>
> "Project WorkMan.exe raised exception class EDBEngineError with
> message 'Couldn't perform the edit because another user changed the
> record.'. Process stopped. Use Step or Run to continue."[/color]

Same thing applies here. Save for the recommendation of an Access newsgroup.

If I am to guess something, Access and Delphi both sets up some server-
side cursors, and then both manages to get lost somewhere when they are
trying to implement optimistic locking.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Someone else editing record ? Only me on the server ?

Erland,

Thanks for your help on this. I managed to find the answer in the
Access forum, albeit pointing to a change I needed to make in SQL.

It's the bit fields that cause the problem. Specifically how ODBC
handles these with NULL values. Part of what I was doing (stupidly)
was adding a bit field into the structure of the table in question and
not setting the values to 0 and not setting the default.

I'd got it in my notes, so assumed I'd done it so didn't bother
checking. It may have helped if I'd mentioned this, but somehow forgot
all about it. An easy oversight. Mental note to self - look for the
easy answer, not the complex one :-) 'Occams Razor' springs to mind
for any brain boxes reading this. (do a search for it if you don't
know what I mean).

So to fix it I did...

1) Ran an UPDATE query, setting all NULL bit fields to 0.

2) On the SQL Server side, set a default value of 0 for the BIT
fields. (or, on
the Access/Delphi side, make sure you provide the value even if
editing an existing record with a NULL value)

Funnily enough it works. Strange how that happens isn't it :-). I'll
let myself out quietly.......

Ryan

[Erland Sommarskog]

Re: Someone else editing record ? Only me on the server ?

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> It's the bit fields that cause the problem. Specifically how ODBC
> handles these with NULL values. Part of what I was doing (stupidly)
> was adding a bit field into the structure of the table in question and
> not setting the values to 0 and not setting the default.[/color]

That's probably one of the things you could have seen if you had used
the Profiler.
[color=blue]
> 1) Ran an UPDATE query, setting all NULL bit fields to 0.
>
> 2) On the SQL Server side, set a default value of 0 for the BIT
> fields. (or, on
> the Access/Delphi side, make sure you provide the value even if
> editing an existing record with a NULL value)[/color]

Going back and looking your table definition, I notice that all columns
but the primary key is nullable. I don't think this is a good design.
Bit columns should normally be NOT NULL - unless there really is a need
for three-valued logic. And I find it difficult to believe that
business rules permit ClientsName and DateReceived to be NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Someone else editing record ? Only me on the server ?

Yep, something on my list to sort out. Thanks for the advice.

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns952FEF 390378CYazorman @127.0.0.1>...[color=blue]
> Ryan (ryanofford@hot mail.com) writes:[color=green]
> > It's the bit fields that cause the problem. Specifically how ODBC
> > handles these with NULL values. Part of what I was doing (stupidly)
> > was adding a bit field into the structure of the table in question and
> > not setting the values to 0 and not setting the default.[/color]
>
> That's probably one of the things you could have seen if you had used
> the Profiler.
>[color=green]
> > 1) Ran an UPDATE query, setting all NULL bit fields to 0.
> >
> > 2) On the SQL Server side, set a default value of 0 for the BIT
> > fields. (or, on
> > the Access/Delphi side, make sure you provide the value even if
> > editing an existing record with a NULL value)[/color]
>
> Going back and looking your table definition, I notice that all columns
> but the primary key is nullable. I don't think this is a good design.
> Bit columns should normally be NOT NULL - unless there really is a need
> for three-valued logic. And I find it difficult to believe that
> business rules permit ClientsName and DateReceived to be NULL.[/color]