Showing posts with label application. Show all posts

Thursday, March 29, 2012

configure IIS in SQLXML support

Hi,
I have to create a B2B application in c#.Net and use SQLXML and use
Annotated schemas to get output as XML.
I have no background in any of these. First of all i have to set up
the environment. So I installed win XP pro ,SQL Server 2000 and tried
to configure IIS in SQLXML but when I give the physical path to the
virtual directory say Northwind, as C:\Inetpub\Wwwroot\Northwind,
it says the physical path is not valid. what is the problem i am
having? What should i do? when I navigate in browse and look for the
folders Inetpub and Wwwroot I see nothing there other than some
picture Icons. Can someone help me please...
thanks,
-shubha
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/XML-configur...ict235374.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=816596
Did you create the Northwind directory under wwwroot?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"vanilla" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_816596_2a8025c990fe19afdddaea35d5531d23@.dbf orumz.com...
> Hi,
> I have to create a B2B application in c#.Net and use SQLXML and use
> Annotated schemas to get output as XML.
> I have no background in any of these. First of all i have to set up
> the environment. So I installed win XP pro ,SQL Server 2000 and tried
> to configure IIS in SQLXML but when I give the physical path to the
> virtual directory say Northwind, as C:\Inetpub\Wwwroot\Northwind,
> it says the physical path is not valid. what is the problem i am
> having? What should i do? when I navigate in browse and look for the
> folders Inetpub and Wwwroot I see nothing there other than some
> picture Icons. Can someone help me please...
> thanks,
> -shubha
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/XML-configur...ict235374.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=816596
sql sql

Tuesday, March 27, 2012

Configuration of SQL Server 2000 Instance

OK.

What I ask about is

how to configure a SQL Server 2000 Instance that allow different copies of my Application to access it

Simply (" my Application with users on LAN and ONLY ONE SQL Server 2000 is installed on a machine that connected with this LAN ")

How I can connect to this SQL Server AND What is needed about any specific configuration options

and If So , How I can deploy these configurations when distributing my Application

- I mean SQL Server configuration-

Thanks more for help

please any answer

on the server check "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe" to make sure network protocols are enabled.

on the workstations check: C:\WINDOWS\system32\cliconfg.exe

Configuration Help

I am running 2005 reporting services and 2005 visual studio. I have a
c# .net web application located in the default web directory in IIS
that uses forms authentication, but that authentication is to active
directory using an ldap call inside the code. We set it up that way so
the user can log in to a normal page instead of the windows pop-up
login. I have reporting services running inside the web site directory.
It is setup to use windows authentication. I have the user login into
the web site and then when they go to run a report a windows login box
appears and they have to login again. Does anyone know how I can
authenticate to reporting services in the background so the user does
not see the windows pop-up login.
Thanks in advance,
JasonWrite a FormsAuthentication extension to MSRS.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
When you app hits LDAP, you can also authenticate against MSRS and grab
the auth cookies for MSRS. Your other options is to hijacks the
ASP.NET pipeline when a user requests an MSRS report and perform the
auth on demand.
Andy Pottersql sql

Configuration for SQL 2000

Hi,

What configuration steps should I do to enable my .NET web application to access my SQL 2000 database? I have SQL Server 2000 SP3, SQL 2005 Express, SQL 2005 and VS 2005 installed on the same machine. Is it anything to do with the NT Authority\Network Service or ASPNET account that I need to allow access to or what? Kinda lost here... been banging my server for the past week.

Cheers!

There are several options. Do you want to run the SQL Server in Mixed Mode ? DO you want to use impersonation ? DO you want to impersoante the user at the SQL Server ?

-Jens Suessmeyer.

|||

That is also my next question. What I would like is for annonymous access to view the data taken from the database. Only authorized personnel can log in through Forms Authentication to Insert, Update or Delete the database.

I believe this is a simple implementation but I can't seem to get the configuration right. The database works if I put it as a local database but that defeats the purpose of the implementation.

I got this error which I believe is very common

What can I set in IIS 6.0, Compiter Management and SQL 2000?:

Server Error in '/' Application.

Runtime Error

Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="Off"/> </system.web> </configuration>

Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.

<!-- Web.Config Configuration File --> <configuration> <system.web> <customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/> </system.web> </configuration>

|||

Anyway after some tweaking here and there, I managed to get it to work properly. Added NT Authority\Local Service account to SQL 2000 User Login, which will categorize it as the public grp. After that checking the tables this group can operate Select instruction on.

This is probably not the conventional way, so I would like to know what the generic workable implementation should be (otherwise called the industrial standard).

Cheers!

Configuration for Configuration Package? Is this Possible?

Hi Guys,

Here's the current scenario:

I am creating a windows application to manage my ssis packages.

My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.

However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.

The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.

Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.

Thanks,

kervy

There are a number of ways. I would store the connection string of the connection manager in an XML configuration file.

-Jamie

|||

it's not the connection string of the connection manager, but the connection to the package configuration table,

currently my package configurations are looking in the "SSIS Configurations" table.. but I want my application to let the dtsx switch to another configuration table named "SSIS Configurations2" both have the same schema but different values. is there a "configuration" property for the configuration table connection?

|||

Ah OK. Well I've just looked at the setup screen for SQL Server configs for the first time in my life and seen that you can store the config location in an environment variable. I don't know exactly what it does but have you explored that? BOL should contain details to help you.

-Jamie

Sunday, March 11, 2012

Conditional query criteria in Access 2003 with SQL Server BE

Hello:
I just upsized an Access 2003 application to a SQL Server 2000 back end.
There is a query that worked fine before the upsize, but does not work now.
The purpose of the query is to give me a list of employees that are
scheduled to work today. The employee master row has 7 checkboxes for the
days of the week.
Here is the SQL for the query. Can someone tell me how to make this work
with the SQL Server BE?
SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
FROM EmployeeQry
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
(((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
(((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
(((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
(((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
(((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
(((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
Thanks
Steve
John:
Thanks for the reply. Sorry ... the query returns no rows, when in fact
there are many for each day of the week. I tried your suggestions ...
comments under each suggestion.
Steve
"John Spencer" wrote:

> I would try:
> Adding to the IIF statement.
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True,False)))
>
I tried this with no difference

> Or even simpler, drop the IIF statement
> EmployeeQry.Tuesday= (Format(Date(),"dddd")='Tuesday')
>
This returned all rows of the table.

> IF that still fails then try reversing the logic of the test. Just in
> case SQL server is storing the data as 1 (True) and 0 (False).
> EmployeeQry.Tuesday =IIf(Format(Date(),"dddd")<>'Tuesday',False, True)
>
Didn't work either.

> By the way "..., but does not work now." is not very descriptive of the
> problem. Does that mean, you get the wrong results, no results, an
> error, different results than expected at times, a syntax error or ...?
> Please try to be a bit more specific in describing the problem.
> '================================================= ===
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '================================================= ===
>
> Steve Happ wrote:
>
|||What type is EmployeeQry.Monday on SQL Server?
It could be as John alluded to that
for SQL Server
True = 1
False = 0
for Access
True = -1
False = 0
I might misunderstand, but you try "1"
instead of "True"
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
"Steve Happ" wrote:
> I just upsized an Access 2003 application to a SQL Server 2000 back end.
> There is a query that worked fine before the upsize, but does not work
> now.
> The purpose of the query is to give me a list of employees that are
> scheduled to work today. The employee master row has 7 checkboxes for the
> days of the week.
> Here is the SQL for the query. Can someone tell me how to make this work
> with the SQL Server BE?
> SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
> EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
> EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
> EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
> FROM EmployeeQry
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
> (((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
> (((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
> (((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
> (((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
> (((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
>
> Thanks
> Steve
|||or
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,Null))) OR
the point being that if Monday = "True"
on SQL, then its value is 1
which will never be equal to Access "True"
which is equal to -1
"Gary Walter" wrote:
> What type is EmployeeQry.Monday on SQL Server?
> It could be as John alluded to that
> for SQL Server
> True = 1
> False = 0
> for Access
> True = -1
> False = 0
> I might misunderstand, but you try "1"
> instead of "True"
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
>
> "Steve Happ" wrote:
>

Sunday, February 19, 2012

Concurrent User limitation?

Hi folks -
I have an application that runs a SQL database. I am using the MSDE to
access it. Client machine has the proprietary software and MDAC v 2.8
installed on a Win2KPro workstation that is fully patched. The support team
for my proprietary application is telling me that using MSDE instead of SQL
Server will give me a concurrent user limit of around 10 or 11.
I've been running this application for almost a year and have not run into
this until I recently instituted Group Policy settings (although I don't see
where the changes to Group Policy would have a bearing).
Sometimes the workstations can access the application, other times within
the same day, they receive an error stating that "the server does not exist
or access is denied", yet they can restart and get right in.
Does anyone know if there IS a concurrent user limitation using MSDE to
access a SQL application?
Thanks.
Deb Landrum, AAM, AIT
Insurance Alliance, Houston TX
HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
On Tue, 6 Sep 2005 14:29:13 -0500, Deb Landrum wrote:
(snip)
>Does anyone know if there IS a concurrent user limitation using MSDE to
>access a SQL application?
Hi Deb,
Well - sort of.
There is no real limit to the concurrent user connections, but there is
a limit to the number of concurrent workloads. If a connected user
spends most time entering data on the form, and only a fraction of the
total time actually executing queries, you can serve quite a few users
without ever running into problems. On the other hand, one single user
can spawn multiple concurrent workloads at once (though it's not
trivial).
The limit on concurrent workloads is set to 8 workloads, but since SQL
Server needs 3 workloads for it's internal processes, there are 5 left
for the users. Once you exceed the workload limit, you won;t get any
errors, but the overall performance of all workloads will be reduced.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
Deb Landrum wrote:
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE
> to access it. Client machine has the proprietary software and MDAC v
> 2.8 installed on a Win2KPro workstation that is fully patched. The
> support team for my proprietary application is telling me that using
> MSDE instead of SQL Server will give me a concurrent user limit of
> around 10 or 11.
> I've been running this application for almost a year and have not run
> into this until I recently instituted Group Policy settings (although
> I don't see where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times
> within the same day, they receive an error stating that "the server
> does not exist or access is denied", yet they can restart and get
> right in.
> Does anyone know if there IS a concurrent user limitation using MSDE
> to access a SQL application?
there's no upper limit but 32767 connections to an MSDE instance but, a
built-in Workload Governor will kick in when 8 concurrent workloads of a
certain type are running... this will result in a linear delay applied to
all concurrent batches in the I/O activities.. when the number decreases
under the 8 limit, the governor will go sleeping again, waiting for next
"overflow" to kick in again... that does not mean 8 concurrent users is the
maximum allowed without the governor to kick in, as usually lot of
connections will be "sleeping", just waiting for user intervention to do
something... Microsoft released a paper "estimating" a magic number of 25
concurrent users before the overhead will be noticeable to end users, but
this just is a "magic number"... it really depend on the application type,
access mode, activities against the data, data load and so on... as you may
understand, there's no actual documentation about this governor but the
papers as
http://msdn.microsoft.com/library/?u...asp?frame=true
...
but I think this is another story, as the exception you get is not related
to all this matter...
this error is a generic MDAC related exception, as you can see reading
http://support.microsoft.com/default...06&Product=sql ,
and probably depending on problem in your LAN...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi
To add what Hugo and Andrea have commented on, there is a limitation at OS
level. You are running w2k Professional which is a workstation product.
Currently the workstation products have a limitation of 10 simultaneous
connections from other machines. In effect, on your platform, you are
limited to 10 connections to MSDE. This limitation is in place to ensure
that workstation products, which are not optimized to be application or
file share servers, are not used as servers.
If you install MSDE on a server product, this limitation goes away.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
> team
> for my proprietary application is telling me that using MSDE instead of
> SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
> see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
> exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||Thanks to all for your input - the MSDE is on a server product, and, the
resolution was actually an issue within the SQL application that we are
running. For whatever reason, although the server name was properly and
correctly shown for the workstations to access it, the problematic ones were
receiving the error. However, by substituting the IP address of the server
for the actual server name, those same workstations were once again able to
access the application.
I don't know if this would be a particular weakness in the proprietary
application that we're using or not, but, again, Thank You to all for
replying.
Deb
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
team
> for my proprietary application is telling me that using MSDE instead of
SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||hi Deb,
Deb Landrum wrote:
> Thanks to all for your input - the MSDE is on a server product, and,
> the resolution was actually an issue within the SQL application that
> we are running. For whatever reason, although the server name was
> properly and correctly shown for the workstations to access it, the
> problematic ones were receiving the error. However, by substituting
> the IP address of the server for the actual server name, those same
> workstations were once again able to access the application.
> I don't know if this would be a particular weakness in the proprietary
> application that we're using or not, but, again, Thank You to all for
> replying.
you probably have a Name resolution problem in your LAN... let your Net
admin gain his money :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Concurrent select is locked while another transaction executes Ins

Our application need to be able to concurrently insert and select from the
same table irrespective of the data.
But, the following issue has been found in the sql server 2000.
Initially , the insert statements (around 1000 rows) for a table is executed
in a transaction inside a stored procedure.
like:
"Insert into Test (pkey,field1,field2,strfield) values
(1400,1144,12025,'test sp insert 2')"
and simultaneously a select statement to retrieve all records from the same
table is executed in the query analyzer. Then the select statement waits
until the insert operation completes.
"select * from Test"
But, if a select statement that filters the data based on the index fields
is used, then it executes without delay.
select * from test where pkey=2000 and field1=1111
(Index: pkey and field1 combination) Suppose if the filter condition
includes the data that is being inserted, then also it is blocked.
In SQL Server 2000, whether it is possible to avoid the blocking of select
statement while insert.
Note that the insert statement is executed in the default isolation level of
sql server 2000.
In MS Access, concurrent inserts and select works without any issue.
Table and Index references:
CREATE TABLE [dbo].[Test] (
[pkey] [bigint] NOT NULL ,
[field1] [bigint] NOT NULL ,
[field2] [int] NULL ,
[strfield] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_Test] ON [dbo].[Test]([field1], [pkey]) ON [PRIMARY]Baskar
http://www.sql-server-performance.com/blocking.asp
http://www.sql-server-performance.c...ucing_locks.asp
"Baskar" <Baskar@.discussions.microsoft.com> wrote in message
news:880BF5FF-E31F-4BA2-95B1-CDDE6C8616C8@.microsoft.com...
> Our application need to be able to concurrently insert and select from the
> same table irrespective of the data.
> But, the following issue has been found in the sql server 2000.
> Initially , the insert statements (around 1000 rows) for a table is
> executed
> in a transaction inside a stored procedure.
> like:
> "Insert into Test (pkey,field1,field2,strfield) values
> (1400,1144,12025,'test sp insert 2')"
> and simultaneously a select statement to retrieve all records from the
> same
> table is executed in the query analyzer. Then the select statement waits
> until the insert operation completes.
> "select * from Test"
> But, if a select statement that filters the data based on the index fields
> is used, then it executes without delay.
> select * from test where pkey=2000 and field1=1111
> (Index: pkey and field1 combination) Suppose if the filter condition
> includes the data that is being inserted, then also it is blocked.
> In SQL Server 2000, whether it is possible to avoid the blocking of select
> statement while insert.
> Note that the insert statement is executed in the default isolation level
> of
> sql server 2000.
> In MS Access, concurrent inserts and select works without any issue.
> Table and Index references:
> CREATE TABLE [dbo].[Test] (
> [pkey] [bigint] NOT NULL ,
> [field1] [bigint] NOT NULL ,
> [field2] [int] NULL ,
> [strfield] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_Test] ON [dbo].[Test]([field1], [pkey]) ON [PRIMARY]
>
>
>

Friday, February 17, 2012

concurrent connection limits

I'm new using SQL 2005 Express

I'm planning to use SQL Server Express 2005 for a asp net web application for a video rental store.

Is there a concurrent user conection limit that I should care?

TIA

Gerardo

You can find more information here.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Concurrent access problem...

Hi All
I am developing portfolio management application. In this application i
am fetching data from database and populate to dataset and that dataset
is getting binded to the datagrid... it is multiuse application and one
portfolio is getting accessed by more than one user. somehow i want to
make sure that one transaction can be updated by only one person. no
more than one person should be allowed to update one transaction... can
anyone suggest me good approach to solve this problem
thanks
Deep OceanOne simple approach would be to have a timestamp or datetime column in the
table .
Between the Retrieval and Save of the data - check for the datetime column
to have same value, which mean no other user has updated the record. If the
column values are not same between retrieval and save then return an error
message to the user that the record has already been updated by some other
user and user need to refresh the screen to do a frech update.
"Deep Silent Ocean" wrote:

> Hi All
>
> I am developing portfolio management application. In this application i
> am fetching data from database and populate to dataset and that dataset
> is getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no
> more than one person should be allowed to update one transaction... can
> anyone suggest me good approach to solve this problem
> thanks
> Deep Ocean
>|||Is this a web application using a disconnected ADO.NET dataset or a client
server type application using a connected ADO recordset?
For ADO recordsets bound to a grid (ex: Visual Basic or MS Office), it is a
matter of specifying the LockType property of the recordset to optimistic or
pessimistic.
http://msdn.microsoft.com/library/d.../>
ocktype.asp
http://msdn.microsoft.com/library/d...ocktypeenum.asp
http://msdn.microsoft.com/library/d...
ursortypex.asp
In web application, data changes are made to an ADO.NET database indirectly
via a disconnected dataset, so the issues are different. Here is an
excellent article on handling concurrency issues in a web application.
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
Pessimistic locking can sometimes inadvertently prevent users from
performing otherwise harmless operations, so I favor the method of using a
timestamp column and then giving the user the flexibility of choosing
whether or not to save their changes over another edit. However, this should
be rarely needed. It's also more of a business process issue than a
technical issue. Generally speaking, an account, invoice, portfolio, etc.
should be assigned to a specific account manager. It would seem confusing to
have multiple people working the same customer, but perhaps you mean
something different by "portfolio".
"Deep Silent Ocean" <ocean.indian@.gmail.com> wrote in message
news:O2tvKf9vFHA.908@.tk2msftngp13.phx.gbl...
> Hi All
>
> I am developing portfolio management application. In this application i am
> fetching data from database and populate to dataset and that dataset is
> getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no more
> than one person should be allowed to update one transaction... can anyone
> suggest me good approach to solve this problem
> thanks
> Deep Ocean

Concurrent Access

Hi,
I have been asked to give a realistic number of concurrent access to do the
stress test to the sql server for our application. I used sp_who2, and
there are about 187 rows in the result pane. However, most of them has"Sleep"
status. 187 concurrent access is a lot. Am I using the right method? Have you
done stress test on your sql server? What is the number of concurrent access
that you put on the server during the test?
Thanks in advance!
K
We have a couple hundred workstations and just over 1000 database
connections.
Keith
"K" <K@.discussions.microsoft.com> wrote in message
news:562243D0-BF68-45BB-A7FA-F4760A79DEB7@.microsoft.com...
> Hi,
> I have been asked to give a realistic number of concurrent access to do
> the
> stress test to the sql server for our application. I used sp_who2, and
> there are about 187 rows in the result pane. However, most of them
> has"Sleep"
> status. 187 concurrent access is a lot. Am I using the right method? Have
> you
> done stress test on your sql server? What is the number of concurrent
> access
> that you put on the server during the test?
> Thanks in advance!
> K
>

Concurrent Access

Hi,
I have been asked to give a realistic number of concurrent access to do the
stress test to the sql server for our application. I used sp_who2, and
there are about 187 rows in the result pane. However, most of them has"Sleep"
status. 187 concurrent access is a lot. Am I using the right method? Have you
done stress test on your sql server? What is the number of concurrent access
that you put on the server during the test?
Thanks in advance!
KWe have a couple hundred workstations and just over 1000 database
connections.
--
Keith
"K" <K@.discussions.microsoft.com> wrote in message
news:562243D0-BF68-45BB-A7FA-F4760A79DEB7@.microsoft.com...
> Hi,
> I have been asked to give a realistic number of concurrent access to do
> the
> stress test to the sql server for our application. I used sp_who2, and
> there are about 187 rows in the result pane. However, most of them
> has"Sleep"
> status. 187 concurrent access is a lot. Am I using the right method? Have
> you
> done stress test on your sql server? What is the number of concurrent
> access
> that you put on the server during the test?
> Thanks in advance!
> K
>

Concurrent Access

Hi,
I have been asked to give a realistic number of concurrent access to do the
stress test to the sql server for our application. I used sp_who2, and
there are about 187 rows in the result pane. However, most of them has"Sleep
"
status. 187 concurrent access is a lot. Am I using the right method? Have yo
u
done stress test on your sql server? What is the number of concurrent access
that you put on the server during the test?
Thanks in advance!
KWe have a couple hundred workstations and just over 1000 database
connections.
Keith
"K" <K@.discussions.microsoft.com> wrote in message
news:562243D0-BF68-45BB-A7FA-F4760A79DEB7@.microsoft.com...
> Hi,
> I have been asked to give a realistic number of concurrent access to do
> the
> stress test to the sql server for our application. I used sp_who2, and
> there are about 187 rows in the result pane. However, most of them
> has"Sleep"
> status. 187 concurrent access is a lot. Am I using the right method? Have
> you
> done stress test on your sql server? What is the number of concurrent
> access
> that you put on the server during the test?
> Thanks in advance!
> K
>

Concurrency violation adding records Why?

Hi,
I'm getting periodic concurrency violations while adding new records, can
someone please tell me how to resolve this. I have an application that is
running multi-user, approx 60+ users and for some reason my application is
throwing a concurrency violation when adding a new record, I have a
bindingsource that is bound to several fields, I then issue a EndEdit()
followed by an update. 95% of the time it works and the other 5% I get a
concurrency error.. This is very frustrating. the dataset being updated
contains an identity field which is an int, I set this identity field using
the datasets autoincrement feature and autostep -1. My insert statement
does NOT contain the identity field.
Thanks,If your clustered index column is going down by 1 (increment set to negative
one) then you do realize that SQL Server has to reorganize the entire table
after every single insert? If this is the case it might be at least
contributing to/exacerbating your issue.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm getting periodic concurrency violations while adding new records, can
> someone please tell me how to resolve this. I have an application that
> is running multi-user, approx 60+ users and for some reason my application
> is throwing a concurrency violation when adding a new record, I have a
> bindingsource that is bound to several fields, I then issue a EndEdit()
> followed by an update. 95% of the time it works and the other 5% I get a
> concurrency error.. This is very frustrating. the dataset being updated
> contains an identity field which is an int, I set this identity field
> using the datasets autoincrement feature and autostep -1. My insert
> statement does NOT contain the identity field.
>
> Thanks,
>|||the identity column never makes it to the sql, its only their to satisfy my
datatset. when the update method of my tableadapter is called it has no
mention of the identity column.
Do you have any other ideas? thank you for your help.
"Mike C#" <xyz@.xyz.com> wrote in messa ge
news:%234qPSXKKIHA.5468@.TK2MSFTNGP05.phx.gbl...
> If your clustered index column is going down by 1 (increment set to
> negative one) then you do realize that SQL Server has to reorganize the
> entire table after every single insert? If this is the case it might be
> at least contributing to/exacerbating your issue.
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
>|||I'm going to make several assumptions.
1) You use identity keys in the database.
2) You have figured out how to get the keys back to replace negative keys in
your dataset that existed prior to update.
3) You are using timestamps for concurrency (that's a big assumption).
4) You process the dataset updates in chunks using the
dataset.GetChanges(Deleted | Modified | Added)
5) You process the deletes first up the hierarchy. Then, you process the
adds down the parent chain. Finally, you process the modifies.
Let's say the user modified the parent row and added new child rows.
Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets the
added rows as you would expect. It also includes any modified parents. If
you do a DataAdapter.Update(dsAdded.table), it will insert the new child
rows AND will also update the parent row if it had been modified. Fine so
far. Here is what happens next. You do the
DataAdapter.Update(dsModified.table). Bingo - concurrency violation because
the timestamp in the parent row has already changed.
Sample SQL:
INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [D
escription],
[LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SELE
CT
AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.Access
Flag,
[Description] = @.Description, [LastUpdated] = getutcdate(), [Las
tUpdatedBy]
= @.LastUpdatedBy WHERE (([AccessFlagLookupID] =
@.Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
(AccessFlagLookupID = @.AccessFlagLookupID)
That "(CAST(TS AS INT) = @.Original_TS));" works with the first update with
the added rows (modified parent) but fails when it gets updated again when
the modified rows get processed.
The way I found this was through getting unexpected concurrency errors.
Stepping through the code and monitoring changes in the database timestamp
values spotted the problem.
Nasty.|||Hi,
-1- I have winform that contains some textboxes, comboboxes etc these are da
tabound to a BindingSource
-2- if the user decides to add a new record I call BindingSource.AddNew()
-2- The BindingSource is bound to my dataset.
-4- when the user clicks on the Save Record button my function looks like th
e following:
try
{
this.CustomerBindingSource.EndEdit();
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy
= "XXX";
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLas
tEdited = DateTime.Now;
int ncount = this.customersTableAdapter.Update(this.dataSetCustomers1.Custom
ers);
}
catch (DBConcurrencyException oEx )
{
MessageBox.Show("Concurrency Erro: " + oEx.Message);
}
catch (System.Exception ex)
{
MessageBox.Show("Other Error: " + ex.Message);
}
-5- The Dataset that is bound to the BindingSource has an identity field whi
ch is an int and is also the primary key on the table, The insert command t
hat is generated by the vs2005 designer does NOT contain the identy field bu
t does a select after the insert inorder to repopulate the dataset with the
new id as send below:
INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.D
ate, @.ContactLastName);
SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID =
SCOPE_IDENTITY())
-6- I am getting some concurrency violations with updates but I am also gett
ing the odd concurrency violation with Adding new records is this possible?
also it appeard that for some reason today the update command was also not u
pdating some records down to the database, however then the app was restarte
d and for some reason it started to work..
Any help would be greatly appreciated.
Thanks,
"Jim Rand" <jimrand@.ix.netcom.com> wrote in message news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.g
bl...
> I'm going to make several assumptions.
>
> 1) You use identity keys in the database.
> 2) You have figured out how to get the keys back to replace negative keys
in
> your dataset that existed prior to update.
> 3) You are using timestamps for concurrency (that's a big assumption).
> 4) You process the dataset updates in chunks using the
> dataset.GetChanges(Deleted | Modified | Added)
> 5) You process the deletes first up the hierarchy. Then, you process the
> adds down the parent chain. Finally, you process the modifies.
>
> Let's say the user modified the parent row and added new child rows.
>
> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets th
e
> added rows as you would expect. It also includes any modified parents. If
> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
> rows AND will also update the parent row if it had been modified. Fine so
> far. Here is what happens next. You do the
> DataAdapter.Update(dsModified.table). Bingo - concurrency violation becaus
e
> the timestamp in the parent row has already changed.
>
> Sample SQL:
>
> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [
;Description],
> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SE
LECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
>
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.Acce
ssFlag,
> [Description] = @.Description, [LastUpdated] = getutcdate(), [L
astUpdatedBy]
> = @.LastUpdatedBy WHERE (([AccessFlagLookupID] =
> @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
> (AccessFlagLookupID = @.AccessFlagLookupID)
>
> That "(CAST(TS AS INT) = @.Original_TS));" works with the first update with
> the added rows (modified parent) but fails when it gets updated again when
> the modified rows get processed.
>
> The way I found this was through getting unexpected concurrency errors.
> Stepping through the code and monitoring changes in the database timestamp
> values spotted the problem.
>
> Nasty.
>
>|||Do you have more information concerning usage patterns? I.e., is there more
than one person attempting to update/add rows simultaneously? Or is this
all happening with only a single user? Also, what is your PK on the
Customers table? What happens if you wrap the INSERT INTO...SELECT in a
BEGIN TRANSACTION...COMMIT TRANSACTION?
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:ubpeJ5LKIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi,
-1- I have winform that contains some textboxes, comboboxes etc these are
databound to a BindingSource
-2- if the user decides to add a new record I call BindingSource.AddNew()
-2- The BindingSource is bound to my dataset.
-4- when the user clicks on the Save Record button my function looks like
the following:
try
{
this.CustomerBindingSource.EndEdit();
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy
=
"XXX";
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLas
tEdited
= DateTime.Now;
int ncount =
this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
}
catch (DBConcurrencyException oEx )
{
MessageBox.Show("Concurrency Erro: " + oEx.Message);
}
catch (System.Exception ex)
{
MessageBox.Show("Other Error: " + ex.Message);
}
-5- The Dataset that is bound to the BindingSource has an identity field
which is an int and is also the primary key on the table, The insert
command that is generated by the vs2005 designer does NOT contain the identy
field but does a select after the insert inorder to repopulate the dataset
with the new id as send below:
INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.D
ate,
@.ContactLastName);
SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID =
SCOPE_IDENTITY())
-6- I am getting some concurrency violations with updates but I am also
getting the odd concurrency violation with Adding new records is this
possible? also it appeard that for some reason today the update command was
also not updating some records down to the database, however then the app
was restarted and for some reason it started to work..
Any help would be greatly appreciated.
Thanks,
"Jim Rand" <jimrand@.ix.netcom.com> wrote in message
news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I'm going to make several assumptions.
> 1) You use identity keys in the database.
> 2) You have figured out how to get the keys back to replace negative keys
> in
> your dataset that existed prior to update.
> 3) You are using timestamps for concurrency (that's a big assumption).
> 4) You process the dataset updates in chunks using the
> dataset.GetChanges(Deleted | Modified | Added)
> 5) You process the deletes first up the hierarchy. Then, you process the
> adds down the parent chain. Finally, you process the modifies.
> Let's say the user modified the parent row and added new child rows.
> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
> the
> added rows as you would expect. It also includes any modified parents. If
> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
> rows AND will also update the parent row if it had been modified. Fine so
> far. Here is what happens next. You do the
> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
> because
> the timestamp in the parent row has already changed.
> Sample SQL:
> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [
;Description],
> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SE
LECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.Acce
ssFlag,
> [Description] = @.Description, [LastUpdated] = getutcdate(),
> [LastUpdatedBy]
> = @.LastUpdatedBy WHERE (([AccessFlagLookupID] =
> @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
> (AccessFlagLookupID = @.AccessFlagLookupID)
> That "(CAST(TS AS INT) = @.Original_TS));" works with the first update with
> the added rows (modified parent) but fails when it gets updated again when
> the modified rows get processed.
> The way I found this was through getting unexpected concurrency errors.
> Stepping through the code and monitoring changes in the database timestamp
> values spotted the problem.
> Nasty.
>|||Thank you, I will try using the BeginTransaction .. Commit Transaction,
yes this is a multi-user application, about 50+ users,
Thank you, I will report back.
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23lsjlpNKIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Do you have more information concerning usage patterns? I.e., is there
> more than one person attempting to update/add rows simultaneously? Or is
> this all happening with only a single user? Also, what is your PK on the
> Customers table? What happens if you wrap the INSERT INTO...SELECT in a
> BEGIN TRANSACTION...COMMIT TRANSACTION?
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:ubpeJ5LKIHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi,
> -1- I have winform that contains some textboxes, comboboxes etc these are
> databound to a BindingSource
> -2- if the user decides to add a new record I call BindingSource.AddNew()
> -2- The BindingSource is bound to my dataset.
> -4- when the user clicks on the Save Record button my function looks like
> the following:
> try
> {
> this.CustomerBindingSource.EndEdit();
> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].Added
By =
> "XXX";
> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateL
astEdited
> = DateTime.Now;
> int ncount =
> this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
> }
> catch (DBConcurrencyException oEx )
> {
> MessageBox.Show("Concurrency Erro: " + oEx.Message);
> }
> catch (System.Exception ex)
> {
> MessageBox.Show("Other Error: " + ex.Message);
> }
> -5- The Dataset that is bound to the BindingSource has an identity field
> which is an int and is also the primary key on the table, The insert
> command that is generated by the vs2005 designer does NOT contain the
> identy field but does a select after the insert inorder to repopulate the
> dataset with the new id as send below:
> INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (
@.Date,
> @.ContactLastName);
> SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
> = SCOPE_IDENTITY())
> -6- I am getting some concurrency violations with updates but I am also
> getting the odd concurrency violation with Adding new records is this
> possible? also it appeard that for some reason today the update command
> was also not updating some records down to the database, however then the
> app was restarted and for some reason it started to work..
> Any help would be greatly appreciated.
> Thanks,
>
> "Jim Rand" <jimrand@.ix.netcom.com> wrote in message
> news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
>|||This problem is occurring when 50+ users are attempting to add/update rows
simultaneously then; not just one user? That's what I'm trying to discover.
And yes, please try wrapping your inserts and updates in single transactions
so we can determine if that helps alleviate the problem for you.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:O0cFq5SKIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Thank you, I will try using the BeginTransaction .. Commit Transaction,
> yes this is a multi-user application, about 50+ users,
> Thank you, I will report back.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23lsjlpNKIHA.3400@.TK2MSFTNGP03.phx.gbl...
>

Concurrency violation adding records Why?

Hi,-1- I have winform that =contains some textboxes, comboboxes etc these are databound to a BindingSource-2- =if the user decides to add a new record I call BindingSource.AddNew()-2- =The BindingSource is bound to my dataset.-4- when the user clicks on the =Save Record button my function looks like the following:
try{
this.CustomerBindingSource.EndEdit();this.dataSetCustomers1.Customers[CustomerBindingSource.Position]=.AddedBy =3D "XXX";this.dataSetCustomers1.Customers[CustomerBindingSource.Position=].DateLastEdited =3D DateTime.Now;int ncount =3D this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);}=catch (DBConcurrencyException oEx ){MessageBox.Show("Concurrency Erro: =" + oEx.Message);}catch (System.Exception =ex){MessageBox.Show("Other Error: " + ex.Message);}-5- The Dataset that is bound to the =BindingSource has an identity field which is an int and is also the =primary key on the table, The insert command that is generated by the vs2005 =designer does NOT contain the identy field but does a select after the insert =inorder to repopulate the dataset with the new id as send below:
INSERT INTO [Customers] ([Date], =[ContactLastName], ) VALUES (@.Date, @.ContactLastName);SELECT CustomerID, Date, =ContactLastName FROM Customers WHERE (CustomerID =3D SCOPE_IDENTITY())
-6- I am getting some concurrency violations with updates but I =am also getting the odd concurrency violation with Adding new records is this =possible? also it appeard that for some reason today the update command was also =not updating some records down to the database, however then the app was =restarted and for some reason it started to work..
Any help would be greatly appreciated.
Thanks,"Jim Rand" wrote in =message news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...> I'm going to make =several assumptions.> > 1) You use identity keys in the =database.> 2) You have figured out how to get the keys back to replace negative =keys in > your dataset that existed prior to update.> 3) You are =using timestamps for concurrency (that's a big assumption).> 4) You =process the dataset updates in chunks using the > =dataset.GetChanges(Deleted | Modified | Added)> 5) You process the deletes first up the =hierarchy. Then, you process the > adds down the parent chain. Finally, you =process the modifies.> > Let's say the user modified the parent =row and added new child rows.> > Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets the > added rows as =you would expect. It also includes any modified parents. If > you do =a DataAdapter.Update(dsAdded.table), it will insert the new child > =rows AND will also update the parent row if it had been modified. Fine so => far. Here is what happens next. You do the > DataAdapter.Update(dsModified.table). Bingo - concurrency violation =because > the timestamp in the parent row has already changed.> => Sample SQL:> > INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description], > [LastUpdatedBy]) VALUES =(@.AccessFlag, @.Description, @.LastUpdatedBy);SELECT > AccessFlagLookupID, =CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup > WHERE =(AccessFlagLookupID =3D SCOPE_IDENTITY())> > UPDATE [AgencyNET].[AccessFlagLookup] =SET [AccessFlag] =3D @.AccessFlag, > [Description] =3D @.Description, =[LastUpdated] =3D getutcdate(), [LastUpdatedBy] > =3D @.LastUpdatedBy WHERE (([AccessFlagLookupID] =3D > @.Original_AccessFlagLookupID) AND =(CAST(TS AS INT) =3D @.Original_TS));SELECT > CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE > (AccessFlagLookupID =3D @.AccessFlagLookupID)> > That "(CAST(TS AS INT) =3D =@.Original_TS));" works with the first update with > the added rows (modified =parent) but fails when it gets updated again when > the modified rows get processed.> > The way I found this was through getting =unexpected concurrency errors. > Stepping through the code and monitoring =changes in the database timestamp > values spotted the problem.> => Nasty.> >

--=_NextPart_000_000B_01C82894.5AB596A0--|||Do you have more information concerning usage patterns? I.e., is there more
than one person attempting to update/add rows simultaneously? Or is this
all happening with only a single user? Also, what is your PK on the
Customers table? What happens if you wrap the INSERT INTO...SELECT in a
BEGIN TRANSACTION...COMMIT TRANSACTION?
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:ubpeJ5LKIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi,
-1- I have winform that contains some textboxes, comboboxes etc these are
databound to a BindingSource
-2- if the user decides to add a new record I call BindingSource.AddNew()
-2- The BindingSource is bound to my dataset.
-4- when the user clicks on the Save Record button my function looks like
the following:
try
{
this.CustomerBindingSource.EndEdit();
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy ="XXX";
this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
= DateTime.Now;
int ncount =this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
}
catch (DBConcurrencyException oEx )
{
MessageBox.Show("Concurrency Erro: " + oEx.Message);
}
catch (System.Exception ex)
{
MessageBox.Show("Other Error: " + ex.Message);
}
-5- The Dataset that is bound to the BindingSource has an identity field
which is an int and is also the primary key on the table, The insert
command that is generated by the vs2005 designer does NOT contain the identy
field but does a select after the insert inorder to repopulate the dataset
with the new id as send below:
INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.Date,
@.ContactLastName);
SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID =SCOPE_IDENTITY())
-6- I am getting some concurrency violations with updates but I am also
getting the odd concurrency violation with Adding new records is this
possible? also it appeard that for some reason today the update command was
also not updating some records down to the database, however then the app
was restarted and for some reason it started to work..
Any help would be greatly appreciated.
Thanks,
"Jim Rand" <jimrand@.ix.netcom.com> wrote in message
news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I'm going to make several assumptions.
> 1) You use identity keys in the database.
> 2) You have figured out how to get the keys back to replace negative keys
> in
> your dataset that existed prior to update.
> 3) You are using timestamps for concurrency (that's a big assumption).
> 4) You process the dataset updates in chunks using the
> dataset.GetChanges(Deleted | Modified | Added)
> 5) You process the deletes first up the hierarchy. Then, you process the
> adds down the parent chain. Finally, you process the modifies.
> Let's say the user modified the parent row and added new child rows.
> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
> the
> added rows as you would expect. It also includes any modified parents. If
> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
> rows AND will also update the parent row if it had been modified. Fine so
> far. Here is what happens next. You do the
> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
> because
> the timestamp in the parent row has already changed.
> Sample SQL:
> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SELECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.AccessFlag,
> [Description] = @.Description, [LastUpdated] = getutcdate(),
> [LastUpdatedBy]
> = @.LastUpdatedBy WHERE (([AccessFlagLookupID] => @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
> (AccessFlagLookupID = @.AccessFlagLookupID)
> That "(CAST(TS AS INT) = @.Original_TS));" works with the first update with
> the added rows (modified parent) but fails when it gets updated again when
> the modified rows get processed.
> The way I found this was through getting unexpected concurrency errors.
> Stepping through the code and monitoring changes in the database timestamp
> values spotted the problem.
> Nasty.
>|||Thank you, I will try using the BeginTransaction .. Commit Transaction,
yes this is a multi-user application, about 50+ users,
Thank you, I will report back.
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23lsjlpNKIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Do you have more information concerning usage patterns? I.e., is there
> more than one person attempting to update/add rows simultaneously? Or is
> this all happening with only a single user? Also, what is your PK on the
> Customers table? What happens if you wrap the INSERT INTO...SELECT in a
> BEGIN TRANSACTION...COMMIT TRANSACTION?
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:ubpeJ5LKIHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi,
> -1- I have winform that contains some textboxes, comboboxes etc these are
> databound to a BindingSource
> -2- if the user decides to add a new record I call BindingSource.AddNew()
> -2- The BindingSource is bound to my dataset.
> -4- when the user clicks on the Save Record button my function looks like
> the following:
> try
> {
> this.CustomerBindingSource.EndEdit();
> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy => "XXX";
> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
> = DateTime.Now;
> int ncount => this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
> }
> catch (DBConcurrencyException oEx )
> {
> MessageBox.Show("Concurrency Erro: " + oEx.Message);
> }
> catch (System.Exception ex)
> {
> MessageBox.Show("Other Error: " + ex.Message);
> }
> -5- The Dataset that is bound to the BindingSource has an identity field
> which is an int and is also the primary key on the table, The insert
> command that is generated by the vs2005 designer does NOT contain the
> identy field but does a select after the insert inorder to repopulate the
> dataset with the new id as send below:
> INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.Date,
> @.ContactLastName);
> SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
> = SCOPE_IDENTITY())
> -6- I am getting some concurrency violations with updates but I am also
> getting the odd concurrency violation with Adding new records is this
> possible? also it appeard that for some reason today the update command
> was also not updating some records down to the database, however then the
> app was restarted and for some reason it started to work..
> Any help would be greatly appreciated.
> Thanks,
>
> "Jim Rand" <jimrand@.ix.netcom.com> wrote in message
> news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I'm going to make several assumptions.
>> 1) You use identity keys in the database.
>> 2) You have figured out how to get the keys back to replace negative keys
>> in
>> your dataset that existed prior to update.
>> 3) You are using timestamps for concurrency (that's a big assumption).
>> 4) You process the dataset updates in chunks using the
>> dataset.GetChanges(Deleted | Modified | Added)
>> 5) You process the deletes first up the hierarchy. Then, you process the
>> adds down the parent chain. Finally, you process the modifies.
>> Let's say the user modified the parent row and added new child rows.
>> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
>> the
>> added rows as you would expect. It also includes any modified parents.
>> If
>> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
>> rows AND will also update the parent row if it had been modified. Fine so
>> far. Here is what happens next. You do the
>> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
>> because
>> the timestamp in the parent row has already changed.
>> Sample SQL:
>> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
>> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description,
>> @.LastUpdatedBy);SELECT
>> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
>> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
>> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.AccessFlag,
>> [Description] = @.Description, [LastUpdated] = getutcdate(),
>> [LastUpdatedBy]
>> = @.LastUpdatedBy WHERE (([AccessFlagLookupID] =>> @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) =>> @.Original_TS));SELECT
>> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
>> (AccessFlagLookupID = @.AccessFlagLookupID)
>> That "(CAST(TS AS INT) = @.Original_TS));" works with the first update
>> with
>> the added rows (modified parent) but fails when it gets updated again
>> when
>> the modified rows get processed.
>> The way I found this was through getting unexpected concurrency errors.
>> Stepping through the code and monitoring changes in the database
>> timestamp
>> values spotted the problem.
>> Nasty.
>>
>|||This problem is occurring when 50+ users are attempting to add/update rows
simultaneously then; not just one user? That's what I'm trying to discover.
And yes, please try wrapping your inserts and updates in single transactions
so we can determine if that helps alleviate the problem for you.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:O0cFq5SKIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Thank you, I will try using the BeginTransaction .. Commit Transaction,
> yes this is a multi-user application, about 50+ users,
> Thank you, I will report back.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23lsjlpNKIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Do you have more information concerning usage patterns? I.e., is there
>> more than one person attempting to update/add rows simultaneously? Or is
>> this all happening with only a single user? Also, what is your PK on the
>> Customers table? What happens if you wrap the INSERT INTO...SELECT in a
>> BEGIN TRANSACTION...COMMIT TRANSACTION?
>> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
>> news:ubpeJ5LKIHA.484@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> -1- I have winform that contains some textboxes, comboboxes etc these are
>> databound to a BindingSource
>> -2- if the user decides to add a new record I call BindingSource.AddNew()
>> -2- The BindingSource is bound to my dataset.
>> -4- when the user clicks on the Save Record button my function looks like
>> the following:
>> try
>> {
>> this.CustomerBindingSource.EndEdit();
>> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy
>> = "XXX";
>> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
>> = DateTime.Now;
>> int ncount =>> this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
>> }
>> catch (DBConcurrencyException oEx )
>> {
>> MessageBox.Show("Concurrency Erro: " + oEx.Message);
>> }
>> catch (System.Exception ex)
>> {
>> MessageBox.Show("Other Error: " + ex.Message);
>> }
>> -5- The Dataset that is bound to the BindingSource has an identity field
>> which is an int and is also the primary key on the table, The insert
>> command that is generated by the vs2005 designer does NOT contain the
>> identy field but does a select after the insert inorder to repopulate the
>> dataset with the new id as send below:
>> INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.Date,
>> @.ContactLastName);
>> SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
>> = SCOPE_IDENTITY())
>> -6- I am getting some concurrency violations with updates but I am also
>> getting the odd concurrency violation with Adding new records is this
>> possible? also it appeard that for some reason today the update command
>> was also not updating some records down to the database, however then the
>> app was restarted and for some reason it started to work..
>> Any help would be greatly appreciated.
>> Thanks,
>>
>> "Jim Rand" <jimrand@.ix.netcom.com> wrote in message
>> news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I'm going to make several assumptions.
>> 1) You use identity keys in the database.
>> 2) You have figured out how to get the keys back to replace negative
>> keys in
>> your dataset that existed prior to update.
>> 3) You are using timestamps for concurrency (that's a big assumption).
>> 4) You process the dataset updates in chunks using the
>> dataset.GetChanges(Deleted | Modified | Added)
>> 5) You process the deletes first up the hierarchy. Then, you process the
>> adds down the parent chain. Finally, you process the modifies.
>> Let's say the user modified the parent row and added new child rows.
>> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
>> the
>> added rows as you would expect. It also includes any modified parents.
>> If
>> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
>> rows AND will also update the parent row if it had been modified. Fine
>> so
>> far. Here is what happens next. You do the
>> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
>> because
>> the timestamp in the parent row has already changed.
>> Sample SQL:
>> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
>> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description,
>> @.LastUpdatedBy);SELECT
>> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM
>> AgencyNET.AccessFlagLookup
>> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
>> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.AccessFlag,
>> [Description] = @.Description, [LastUpdated] = getutcdate(),
>> [LastUpdatedBy]
>> = @.LastUpdatedBy WHERE (([AccessFlagLookupID] =>> @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) =>> @.Original_TS));SELECT
>> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
>> (AccessFlagLookupID = @.AccessFlagLookupID)
>> That "(CAST(TS AS INT) = @.Original_TS));" works with the first update
>> with
>> the added rows (modified parent) but fails when it gets updated again
>> when
>> the modified rows get processed.
>> The way I found this was through getting unexpected concurrency errors.
>> Stepping through the code and monitoring changes in the database
>> timestamp
>> values spotted the problem.
>> Nasty.
>>
>>
>

Concurrency violation adding records Why?

Hi,
I'm getting periodic concurrency violations while adding new records, can
someone please tell me how to resolve this. I have an application that is
running multi-user, approx 60+ users and for some reason my application is
throwing a concurrency violation when adding a new record, I have a
bindingsource that is bound to several fields, I then issue a EndEdit()
followed by an update. 95% of the time it works and the other 5% I get a
concurrency error.. This is very frustrating. the dataset being updated
contains an identity field which is an int, I set this identity field using
the datasets autoincrement feature and autostep -1. My insert statement
does NOT contain the identity field.
Thanks,
If your clustered index column is going down by 1 (increment set to negative
one) then you do realize that SQL Server has to reorganize the entire table
after every single insert? If this is the case it might be at least
contributing to/exacerbating your issue.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm getting periodic concurrency violations while adding new records, can
> someone please tell me how to resolve this. I have an application that
> is running multi-user, approx 60+ users and for some reason my application
> is throwing a concurrency violation when adding a new record, I have a
> bindingsource that is bound to several fields, I then issue a EndEdit()
> followed by an update. 95% of the time it works and the other 5% I get a
> concurrency error.. This is very frustrating. the dataset being updated
> contains an identity field which is an int, I set this identity field
> using the datasets autoincrement feature and autostep -1. My insert
> statement does NOT contain the identity field.
>
> Thanks,
>
|||the identity column never makes it to the sql, its only their to satisfy my
datatset. when the update method of my tableadapter is called it has no
mention of the identity column.
Do you have any other ideas? thank you for your help.
"Mike C#" <xyz@.xyz.com> wrote in messa ge
news:%234qPSXKKIHA.5468@.TK2MSFTNGP05.phx.gbl...
> If your clustered index column is going down by 1 (increment set to
> negative one) then you do realize that SQL Server has to reorganize the
> entire table after every single insert? If this is the case it might be
> at least contributing to/exacerbating your issue.
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:%23qbtuTJKIHA.3516@.TK2MSFTNGP02.phx.gbl...
>
|||I'm going to make several assumptions.
1) You use identity keys in the database.
2) You have figured out how to get the keys back to replace negative keys in
your dataset that existed prior to update.
3) You are using timestamps for concurrency (that's a big assumption).
4) You process the dataset updates in chunks using the
dataset.GetChanges(Deleted | Modified | Added)
5) You process the deletes first up the hierarchy. Then, you process the
adds down the parent chain. Finally, you process the modifies.
Let's say the user modified the parent row and added new child rows.
Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets the
added rows as you would expect. It also includes any modified parents. If
you do a DataAdapter.Update(dsAdded.table), it will insert the new child
rows AND will also update the parent row if it had been modified. Fine so
far. Here is what happens next. You do the
DataAdapter.Update(dsModified.table). Bingo - concurrency violation because
the timestamp in the parent row has already changed.
Sample SQL:
INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
[LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SELECT
AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.AccessFlag,
[Description] = @.Description, [LastUpdated] = getutcdate(), [LastUpdatedBy]
= @.LastUpdatedBy WHERE (([AccessFlagLookupID] =
@.Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
(AccessFlagLookupID = @.AccessFlagLookupID)
That "(CAST(TS AS INT) = @.Original_TS));" works with the first update with
the added rows (modified parent) but fails when it gets updated again when
the modified rows get processed.
The way I found this was through getting unexpected concurrency errors.
Stepping through the code and monitoring changes in the database timestamp
values spotted the problem.
Nasty.
|||Hi,
-1- I have winform that contains some textboxes, comboboxes etc these are databound to a BindingSource
-2- if the user decides to add a new record I call BindingSource.AddNew()
-2- The BindingSource is bound to my dataset.
-4- when the user clicks on the Save Record button my function looks like the following:
try
{
this.CustomerBindingSource.EndEdit();
this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].AddedBy = "XXX";
this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].DateLastEdited = DateTime.Now;
int ncount = this.customersTableAdapter.Update(this.dataSetCust omers1.Customers);
}
catch (DBConcurrencyException oEx )
{
MessageBox.Show("Concurrency Erro: " + oEx.Message);
}
catch (System.Exception ex)
{
MessageBox.Show("Other Error: " + ex.Message);
}
-5- The Dataset that is bound to the BindingSource has an identity field which is an int and is also the primary key on the table, The insert command that is generated by the vs2005 designer does NOT contain the identy field but does a select after the insert inorder to repopulate the dataset with the new id as send below:
INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.Date, @.ContactLastName);
SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID = SCOPE_IDENTITY())
-6- I am getting some concurrency violations with updates but I am also getting the odd concurrency violation with Adding new records is this possible? also it appeard that for some reason today the update command was also not updating some records down to the database, however then the app was restarted and for some reason it started to work..
Any help would be greatly appreciated.
Thanks,
"Jim Rand" <jimrand@.ix.netcom.com> wrote in message news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I'm going to make several assumptions.
> 1) You use identity keys in the database.
> 2) You have figured out how to get the keys back to replace negative keys in
> your dataset that existed prior to update.
> 3) You are using timestamps for concurrency (that's a big assumption).
> 4) You process the dataset updates in chunks using the
> dataset.GetChanges(Deleted | Modified | Added)
> 5) You process the deletes first up the hierarchy. Then, you process the
> adds down the parent chain. Finally, you process the modifies.
> Let's say the user modified the parent row and added new child rows.
> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets the
> added rows as you would expect. It also includes any modified parents. If
> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
> rows AND will also update the parent row if it had been modified. Fine so
> far. Here is what happens next. You do the
> DataAdapter.Update(dsModified.table). Bingo - concurrency violation because
> the timestamp in the parent row has already changed.
> Sample SQL:
> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SELECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.AccessFlag,
> [Description] = @.Description, [LastUpdated] = getutcdate(), [LastUpdatedBy]
> = @.LastUpdatedBy WHERE (([AccessFlagLookupID] =
> @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
> (AccessFlagLookupID = @.AccessFlagLookupID)
> That "(CAST(TS AS INT) = @.Original_TS));" works with the first update with
> the added rows (modified parent) but fails when it gets updated again when
> the modified rows get processed.
> The way I found this was through getting unexpected concurrency errors.
> Stepping through the code and monitoring changes in the database timestamp
> values spotted the problem.
> Nasty.
>
|||Do you have more information concerning usage patterns? I.e., is there more
than one person attempting to update/add rows simultaneously? Or is this
all happening with only a single user? Also, what is your PK on the
Customers table? What happens if you wrap the INSERT INTO...SELECT in a
BEGIN TRANSACTION...COMMIT TRANSACTION?
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:ubpeJ5LKIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi,
-1- I have winform that contains some textboxes, comboboxes etc these are
databound to a BindingSource
-2- if the user decides to add a new record I call BindingSource.AddNew()
-2- The BindingSource is bound to my dataset.
-4- when the user clicks on the Save Record button my function looks like
the following:
try
{
this.CustomerBindingSource.EndEdit();
this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].AddedBy =
"XXX";
this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].DateLastEdited
= DateTime.Now;
int ncount =
this.customersTableAdapter.Update(this.dataSetCust omers1.Customers);
}
catch (DBConcurrencyException oEx )
{
MessageBox.Show("Concurrency Erro: " + oEx.Message);
}
catch (System.Exception ex)
{
MessageBox.Show("Other Error: " + ex.Message);
}
-5- The Dataset that is bound to the BindingSource has an identity field
which is an int and is also the primary key on the table, The insert
command that is generated by the vs2005 designer does NOT contain the identy
field but does a select after the insert inorder to repopulate the dataset
with the new id as send below:
INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.Date,
@.ContactLastName);
SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID =
SCOPE_IDENTITY())
-6- I am getting some concurrency violations with updates but I am also
getting the odd concurrency violation with Adding new records is this
possible? also it appeard that for some reason today the update command was
also not updating some records down to the database, however then the app
was restarted and for some reason it started to work..
Any help would be greatly appreciated.
Thanks,
"Jim Rand" <jimrand@.ix.netcom.com> wrote in message
news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
> I'm going to make several assumptions.
> 1) You use identity keys in the database.
> 2) You have figured out how to get the keys back to replace negative keys
> in
> your dataset that existed prior to update.
> 3) You are using timestamps for concurrency (that's a big assumption).
> 4) You process the dataset updates in chunks using the
> dataset.GetChanges(Deleted | Modified | Added)
> 5) You process the deletes first up the hierarchy. Then, you process the
> adds down the parent chain. Finally, you process the modifies.
> Let's say the user modified the parent row and added new child rows.
> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
> the
> added rows as you would expect. It also includes any modified parents. If
> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
> rows AND will also update the parent row if it had been modified. Fine so
> far. Here is what happens next. You do the
> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
> because
> the timestamp in the parent row has already changed.
> Sample SQL:
> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
> [LastUpdatedBy]) VALUES (@.AccessFlag, @.Description, @.LastUpdatedBy);SELECT
> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @.AccessFlag,
> [Description] = @.Description, [LastUpdated] = getutcdate(),
> [LastUpdatedBy]
> = @.LastUpdatedBy WHERE (([AccessFlagLookupID] =
> @.Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @.Original_TS));SELECT
> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
> (AccessFlagLookupID = @.AccessFlagLookupID)
> That "(CAST(TS AS INT) = @.Original_TS));" works with the first update with
> the added rows (modified parent) but fails when it gets updated again when
> the modified rows get processed.
> The way I found this was through getting unexpected concurrency errors.
> Stepping through the code and monitoring changes in the database timestamp
> values spotted the problem.
> Nasty.
>
|||Thank you, I will try using the BeginTransaction .. Commit Transaction,
yes this is a multi-user application, about 50+ users,
Thank you, I will report back.
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23lsjlpNKIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Do you have more information concerning usage patterns? I.e., is there
> more than one person attempting to update/add rows simultaneously? Or is
> this all happening with only a single user? Also, what is your PK on the
> Customers table? What happens if you wrap the INSERT INTO...SELECT in a
> BEGIN TRANSACTION...COMMIT TRANSACTION?
> "Rob Dob" <robdob20012002@.yahoo.com> wrote in message
> news:ubpeJ5LKIHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi,
> -1- I have winform that contains some textboxes, comboboxes etc these are
> databound to a BindingSource
> -2- if the user decides to add a new record I call BindingSource.AddNew()
> -2- The BindingSource is bound to my dataset.
> -4- when the user clicks on the Save Record button my function looks like
> the following:
> try
> {
> this.CustomerBindingSource.EndEdit();
> this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].AddedBy =
> "XXX";
> this.dataSetCustomers1.Customers[CustomerBindingSo urce.Position].DateLastEdited
> = DateTime.Now;
> int ncount =
> this.customersTableAdapter.Update(this.dataSetCust omers1.Customers);
> }
> catch (DBConcurrencyException oEx )
> {
> MessageBox.Show("Concurrency Erro: " + oEx.Message);
> }
> catch (System.Exception ex)
> {
> MessageBox.Show("Other Error: " + ex.Message);
> }
> -5- The Dataset that is bound to the BindingSource has an identity field
> which is an int and is also the primary key on the table, The insert
> command that is generated by the vs2005 designer does NOT contain the
> identy field but does a select after the insert inorder to repopulate the
> dataset with the new id as send below:
> INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@.Date,
> @.ContactLastName);
> SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
> = SCOPE_IDENTITY())
> -6- I am getting some concurrency violations with updates but I am also
> getting the odd concurrency violation with Adding new records is this
> possible? also it appeard that for some reason today the update command
> was also not updating some records down to the database, however then the
> app was restarted and for some reason it started to work..
> Any help would be greatly appreciated.
> Thanks,
>
> "Jim Rand" <jimrand@.ix.netcom.com> wrote in message
> news:uTgwKOLKIHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||This problem is occurring when 50+ users are attempting to add/update rows
simultaneously then; not just one user? That's what I'm trying to discover.
And yes, please try wrapping your inserts and updates in single transactions
so we can determine if that helps alleviate the problem for you.
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:O0cFq5SKIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Thank you, I will try using the BeginTransaction .. Commit Transaction,
> yes this is a multi-user application, about 50+ users,
> Thank you, I will report back.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:%23lsjlpNKIHA.3400@.TK2MSFTNGP03.phx.gbl...
>

Thursday, March 29, 2012

configure IIS in SQLXML support

Tuesday, March 27, 2012

Configuration of SQL Server 2000 Instance

Configuration Help

Configuration for SQL 2000

Server Error in '/' Application.

Runtime Error

Configuration for Configuration Package? Is this Possible?

Sunday, March 11, 2012

Conditional query criteria in Access 2003 with SQL Server BE

Sunday, February 19, 2012

Concurrent User limitation?

Concurrent select is locked while another transaction executes Ins

Friday, February 17, 2012

concurrent connection limits

Concurrent access problem...

Concurrent Access

Concurrent Access

Concurrent Access

Concurrency violation adding records Why?

Concurrency violation adding records Why?

Concurrency violation adding records Why?

Concatenating fields

Blog Archive

About Me