I have a database in sql server 2000. In this i have used identity(seed,increment) property so that unique ids may be available. Now if requests from diffrent users arrives at same time on the server, will there be a conflict because of ids?
View 3 RepliesI'm using Java to connect to a SQL Server 2000 database. I connect using the Driver Manager with Sun's odbc driver ( sun.jdbc.odbc.JdbcOdbcDriver ) or I can use the jdbc driver provided by Microsoft (com.microsoft.jdbc.sqlserver.SQLServerDriver)
The Java application makes 1 Connection.
Within the database there exists a stored procedure that updates 2 Tables. The tables have a fixed number of rows that get updated continuously by calls to this stored procedure.
The Java application has a thread pool of 15 threads that create 15 CallableStatements (1 per thread) using the same instance of the Connection object.
According the the Microsoft JDBC driver docs, 1 Connection with multiple calls to the Callable statements is how it's supposed to be done. The following is an excerpt from Microsoft's "SQL Server 2000 Driver for JDBC User’s Guide and Reference" (page 86) regarding Connection Managment:
Managing Connections
Connection management is important to application performance. Optimize your application by connecting once and using multiple statement objects, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection.
This is precisely what I'm doing, but I do not know if the the stored procedures are being run concurrently, the documentation does not tell me.
So my question: What is happening inside SQL Server 2000?
We have two processors on which sql server was installed.
SMP Concurrency opion is -1. and process priority is 0.
The computer is dedicated to Sql server.
with the same setup, has any body faces any problem....?
some times suddenly system hangs... sql sever takes 99% cpu usage ...
Shall I run the SQL Executive, SQL Performance Monitor with this setup...?
will it afect the system...?
[ this kind of setup - those you experienced.. ]
please reply immediately....
thanks
Wincy
We recently had an issue with SQL Server's performance. We have a server with multiple databases that are accessed by several different applications. When a query was issued to one table on a database (40 million rows), it brought the entire server to its knees. This impacted the other application accessing the database.
The query issued was on a varchar column using a like in the where clause. An index did exists on the table but because of the like clause it didn't want to use the index and proceeded to do a table scan. I understand that tablescans are going to have to happen no matter what sometimes, but why did it hurt the entire server's performance. Any ideas? I'm open to all suggestions. I might be the one doing something wrong and appreciate any advise.
TIA,
AJ
I'm currently looking at a multi-user app that has an Access 97 frontend and an SQL2K backend. Proposed changes to the system mean that the user concurrency count is likely to increase dramatically, and I'm wanting to know if there are any limits to the concurrency for this architecture.
Any experiences/help appreciated.
Account getting wrongly updated due to concurrencyWe are using Sql 2014 and storing data in customer_account table for customer account details however we are experiencing wrong value insertion during concurrency ..pls find the code
Declare @date datetime2(7)
Declare @InsDate datetime2(7)
SELECT TOP 1 @Amountremaining =Remaining ,@Date=Datetime                                                           Â
 FROM <customertaccounttable>                                                             Â
Â
[code]....
I have a db to manage the creation of invoice number designed for a web application.
My problem is how to manage the concurrency when the users need to create an invoice number.
Hi
I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this.
Below is the exception from my log file
The cursor type/concurrency combination is not supported.
com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source)
The following is the piece of code where the problem I'm assuming is happening, how can I correct it.
varStmt1 = varConnection.prepareStatement(varCitationSQL.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Have tried using both JDC v1.1 and 1.2 but of no use.
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as (
select 0 [iterations], 1e+0 [computed]
union all
select iterations+1,dbo.f(computed)
from where rCTE
where iterations < 30
)
select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
I need to use WHILE to avoid Cursor under certains conditions.
My SELECT statement is:
SELECT ref, ano, numberofyears ,nreint, naoreint,degress,
tabela, tax, taxamaxima,[evactual],
[evaldepact],[ereintact],nrregbt,[taxAmtAno]
FROM deprec
ORDER BY [ref] ASC
numberofyears= 100 /tax for exemple for a good where lifecycle is 4 years ,ex:
Tax = 25% Then 100/25 = 4 years
I see this WHILE script, but i need to run :
1. for each REF + Until years < 4 in this exemple, because i have goods years depend on Percent.
the WHILE script i see is:
DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @num_rows=@@ROWCOUNT
SET @cnt=0
WHILE @cnt<@num_rows
[Code] .....
My doubt is how to make the LOOP for each REF until Year < 4 (like my example)
This query below is giving product join for me, is there a way to avoid this?
SELECT DISTINCT a.RevID, indexdate, transadate
FROM temp1 AS a
INNER JOIN temp2 AS d ON transdate BETWEEN indexdate-60 AND indexdate+60
)
I am going to apply SQL ServicePack 3 in our production Server . Is it possible to avoid restart ?
View 2 Replies View RelatedHi folks!!
I am new to installation of SQL Server 2005..I wanted to know while selecting Service Account Screen why Avoid running SQL Server Agent as the Local System account.????
T.I.A
We are running a shopping mall in Korea and got a database including a table
of 4 million product prices, which is to be updated hourly basis. Updating 4million
records requires at least 10 minutes to complete. During the update, our shopping mall
exposed to customers does not respond quickly in fact very very slowly
and we investigated and found out that many tables of SQL database during the update were being
locked. As you know, site speed is top priority. We studied and found out that there are
two ways to avoid having locked tables during update, those are "read uncommitted" and
"snapshot" using the following lines.
set transaction Isolation level read uncommitted
set transaction Isolation level snapshot
We tried numerous times the above two lines and still find our tables being locked during update
and our customers are being disappointed.
My questions:
1. Is it possible at all in view of "the state of the art" to avoid having locked tables during update of 4 million records ?
2. if it is possible, would you please teach me like I am the beginner of database studies?
For your information, we are using 2005sql (64bit) in Windows 2003 (64bit).
Sincerely
Daeyeon Jo
Hi everybody,
i'll try to explain the problem with my bad english, so sorry.
In my firm i have a server (W2003 Server) with IIS 6.0 and SQL Server 2005 EE an it is joined in the main domain as all users and their workstation (window XP) are joined too.
The main role for this server is to be a web server and with IIS Manager I configured to verify user identities with Windows Integrated authentication without any anonymous access to web pages (ASP scripts).
I made a group called "Web Users" with read right on ASP scripts and his only one member is "Domain Users".
"Web Users" has of course rights to access this server from network.
This group is also a SQL Server login because it has rights to wiew an modify data at least in one db table trought ASP pages using ADO, but the problem is that they can also connect using ODBC.
Configuring SQL to accept only local connection i can avoid this, but as SQL Admin i can't connect too. So how is possible for web users interact with SQL only trought web pages ?
Thank you very much
using below query to raplace the string values (REPLACE abc with T1223), how to use the query without hard coding.
i want to store the values in another temp table and access in main query.
'abc', 'T1223',
'def', 'T456',
'ghi', 'T789',
'jkl', 'T1011',
'mno', 'T12'
select id,name,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,
'abc', 'T1223'),
'def', 'T456'),
'ghi', 'T789'),
'jkl', 'T1011'),
'mno', 'T12'))) New_id
from TAB
I am sharing one sql query and o/p:
select distinct  caseÂ
     when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
     when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
     when LastExecutionResult = '17023' then 'User cancelled installation'
     when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
[Code] .....
The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)
Comment Machine Name
Application will be Installed once machine is Online 4
Application will be Installed once machine is Online 12
Application will be Installed once machine is Online 42
Application will be Installed once machine is Online 120
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
I need o/p like below:in single line
Application will be Installed once machine is Online 178
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
Hi,
We are using Microsoft Reporting Service 2005 to develop reports and we are accessing these reports through a J2EE application.
The front end is implemented using Tapestry and we using JBoss as our applicaiton server.
We are using Shared Data Sources for the reports and we set its data source type to SQL Server Analysis Services.
In the credential tab, by default "Use Windows authentication" is selected. All other options are disabled.
When I access my reportserver through my web application, I am always prompted for a windows login and password.
How can I avoid being shown the windows login prompt, since our web application will be used by several users and we do not want the users to type in a username/password everytime they want to access our reports.
Please suggest me solution for this scenario.
Thanks in advance!
I have a script that resolve's data into xml like this, ex:
<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS
Hi everybody,I need to understand how concurrency excatly
work in asp.net. For example, I'm confused what happens if two users at
the same time try to access the same record in a table or even the same
variable. Do ASP.NET handle this , I mean by locking one user and
letting the other to have access OR it's up to the programmer to write
some code to lock shared resources such as database , objects and
variables?If it's up to the programmer to do this task, I appreciate if you can show me an example that clarifies that.Thank you
Following on from a thread I started about "concurrency" (real-time-ishsystem), I thought I would play about to see if I could easily adapt my datamodel to take account of potential multi-user write conflicts. So, I wouldappreciate you checking my logic/reasoning to see if this kind of thingwill work. Below I have a stored procedure that will simply delete a givenrecord from a given table. I have appended a "_Written" counter to thecolumns of the table. Every time the record is written, the counter isincremented. Clients store the current _Written count in their objects andpass this in to any write procedure executed.The procedure explicitly checks the _Written count within the transaction tosee if it agress with the written count passed in by the client. If it doesnot, the client throws an error. Note I am explicitly checking the_Written count precisely so I can determine exactly why this operation mightfail, rather than checking @@ROWCOUNT after an update.Thanks.RobinCREATE PROCEDURE dbo.proc_DS_Remove_DataSet@_In_ID INTEGER,@_In_Written INTEGERASDECLARE @Error INTEGERDECLARE @WRITTEN INTEGERBEGIN TRANSACTIONSET @Error = @@ERRORIF @Error = 0BEGINSELECT @WRITTEN = _Written FROM MyTable WHERE ID = @_In_IDSET @Error = @@ERRORIF @WRITTEN <> @_In_WrittenBEGINRAISERROR ('10', 16, 1)SET @Error = @@ERRORENDENDIF @Error = 0BEGINDELETE FROM MyTable WHERE ID = @_In_IDSET @Error = @@ERRORENDIF @Error = 0COMMIT TRANSACTIONELSEROLLBACK TRANSACTIONRETURN @Error
View 5 Replies View RelatedDo single commands (or stored procedures) execute concurrently, or they are executed one by one. How do you perform a lock during the execution of a command (or stored procedure).
View 3 Replies View RelatedI have a user object that is stored in the session for each user but what if an administrator updates a certain user and I want to reflect the update to the user if they are logged in?One possible way of solving this is:Each time the user goes to a page, check the user table and compare the timestamp. That would mean if 30 users refresh the page..the db would hit 30 times lol. I don't think that would scale very well.Any ideas on how to solve this?
View 5 Replies View RelatedI have a table where I count how many emails of a given type are sent out each day. This incrementing is wrapped in a sproc that either inserts a new row, or updates the existing row. The column that counts the value is named Count of type INT.
Below is the sproc, seems like a straightforward thing. However, I'm seeing email counts higher than they should be when there's a high number of concurrent executions of the sproc. I'm pretty sure it's not a problem in the calling code, so I'm wondering about the UPDATE statement, since it updates a column based on the value of the column. I would think this should work since it's wrapped in a SERIALIZABLE transaction, anybody have further insight?
SQL Server 2005 by the way.
Sean
CREATE PROCEDURE [dbo].[IncrementEmailCounter]( @siteId SMALLINT, @messageType VARCHAR(20), @day SMALLDATETIME) ASBEGIN SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION
IF (SELECT COUNT(*) FROM EmailCount WHERE SiteId = @siteId AND MessageType = @messageType AND [Day] = @day) = 0 INSERT INTO EmailCount (SiteId, MessageType, [Day], [Count]) VALUES (@siteId, @messageType, @day, 1) ELSE UPDATE EmailCount SET [Count] = [Count] + 1 WHERE SiteId = @siteId AND MessageType = @messageType AND [Day] = @day
COMMIT TRANSACTION SET TRANSACTION ISOLATION LEVEL READ COMMITTEDEND
I'm wondering whether the following code would work if users are RAPIDLY registering (assumption) WITH the same username.public bool UsernamExists(string username)
{
string sql = "SELECT true FROM [users] WEHRE username = @username;";
return Convert.ToBoolean(comm.ExecuteScalar());
}
public bool Signup(User user)
{
bool usernameExists = UsernameExists(user.Username);
if( usernameExists ) return false;
//update or insert sql for user etc blah blah
} If two users try to signup AT THE VERY SAME TIME (DOWN TO THE NANOSECOND), would this technique work? Do I have to wrap it in a transaction, stored procedure?? Thanks.
Hi,I'm trying to implement Optimistic Concurrency in asp 2 but so far it has caused me nothing but problems.First, when doing an UPDATE I tried to use the primary key & a timestamp field which I had in SQL Express.. VS 2005 generated the stored procedures fine however when it came to the actual updating I think there was a problem with the conversion of the timestamp field when it was being stored in a text box (in a FormView control). So.. as a result that failed. And also I checked sooo many places online and haven't been able to find any examples of code where a timestamp was used with success in asp2.Next, I got ride of the timestamp type (in SQL Express database) and used a datetime and then.. I just implemented Optimistic Concurrency by passing in ALL the values (ie all the original values) like is proposed http://www.asp.net/learn/dataaccess/tutorial21vb.aspx?tabid=63 . This... works however I really do not want to have to pass in ALL these values (ie original and new).Ideally I would like to be able to use the primary key & the datetime field to handle the Optimistic Concurrency checks where only the original values of both those fields are passed back into the stored procedure. Now.. I tried this as well, but I kept getting an error that suggests that (for some reason) the FormView or DataSource is passing ALL the values (original & new) into the dataset as opposed to only the original primary key & datetime fields & the new set of values.Can ANYONE offer any help? I really would like not to have to pass in all these values.Thanks in advance!
View 6 Replies View RelatedHi! I'm building a web application with ASP.NET, and using MS SQL 2000 for my database server.
How should I do to guarantee the integrity of the data in spite of the concurrent access? Meaning... how can I make sure that more than 1 user can update 1 table at the same time, while no error will occur? Do I need to add some codes at my aspx file? Or do I need to do something to my database? Or do I not have to worry about it?
Thank you.
Can anyone help with concurrency issues. Small network and only one person at a time can log into the database.
It was originally written in MS Access and converted to SQL 7.0 with a VB front end.
Thanks,
Rick
Hello,
SQL Server6.5 is installed on a dual processor computer. So can I make use of dual processors by setting SMP concurrency to -1 or 2.
I tried setting these values but failed to do so. Server is running at setting 1 always, irrespective of configured value.
Any suggestion???
Srini
Is there any way to get the sample below working so that both "threads" are guaranteed to get unique and incrementing values?
I'm suspecting the answer is no. You can use transactions on completely database oriented operations that read/write to a database and complete. But there aren't complete synchronization controls for operations like below that try to return a value to an outside process.
IF OBJECT_ID('SimpleTable') IS NOT NULL
DROP TABLE SimpleTable
CREATE TABLE SimpleTable (
A INTEGER
)
INSERT INTO SimpleTable (A) VALUES (1)
-- Run in one window
DECLARE @value INTEGER
BEGIN TRANSACTION
SELECT TOP 1 @value = A FROM SimpleTable
WAITFOR DELAY '00:00:05'
UPDATE SimpleTable SET A = @value + 1
COMMIT TRANSACTION
SELECT @value
SELECT A FROM SimpleTable
-- Run in a second window
DECLARE @value INTEGER
BEGIN TRANSACTION
SELECT TOP 1 @value = A FROM SimpleTable
UPDATE SimpleTable SET A = @value + 1
COMMIT TRANSACTION
SELECT @value
SELECT A FROM SimpleTable
Suppose process A is updating record #1 in table T.
By default, can other processes read record #1 while the updating is in progress ??
If the answer is Yes, then which value can they see - the old one or the new one ?
Thank you in advance.
Did I understand correctly the Pesimistic access:
When we choose pessimistic access
-we lock all the rows of the table. right.
-other users still can access the table if they specify read only mode meaning if their intention is to only read the data and not modify it. am I right
Thanks for your help.
In a previous post, the theme of cursors and concurrency was touched as a secondary subject. I have a specific question about it as the primary one:
if we have
--------
create proc myProc
as
declare cursor for
select * from mytable
go
----------
if two or more clients(webpages for example)
execute myProc concurrently will the cursor be safe ? or would I have to make special arrangements, there are a couple of procs (that use cursors)that somebody else did and would not like to modify but we want to make the procs web available,
thank you