I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,
The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?
I've been asked to turn our single-user database system into a multi-usersystem. At present, each user has a copy of the MSDE on their desktopmachine and uses our program to access it. In future, we would like tocentralise our MSDE instance and allow multiple users to access it. Inorder to facilitate this, we are going to only allow one user write accessto the system at a time (I know, its a kludge, but the system was neverdesigned for multiple users in the first place).I have a single, simple question this being the case: can I update a single"read-only" bit field in a table of the database in order to flag to otherusers that the system is in read-only mode in a way that avoids concurrencyissues? ie. does an "UPDATE" query lock and unlock? ( I suspect the answeris yes! ). If anyone else has experience of these things, I would alsoappreciate some tips on how best to proceed.ThanksRobin
Can someone explain what happens when two users concurrently attempt tocreate a new record in a table with an autonumber primary key? For example,user 1 creates a new record and manipulates it within a transaction makinguse (perhaps) of the @@IDENTITY value when creating other, related records.Before this transaction is complete, user 2 creates a new record and doesthe same thing. Presumably they will both have the same @@IDENTITY? Ifthis is the case, how is it possible to manage such a situation?Thanks.
I am researching this and playing with using the syss.sysprocesses view and counting the dbid column. I would most likely want to store this information in a table with a date/time stamp because we really want to track our concurrent users during the day for awhile. Do you have any neat tricks in getting this done? Is there a DMV or Information_Schema view that could be used to accomplish this task? I found the following code on the internet:
SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections, loginame AS LoginName, nt_domain AS NT_Domain, nt_username AS NT_UserName, hostname AS HostName FROM sys.sysprocesses
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.Â
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
If these are all used up, and a 26th connection comes in. What happens? is the connection rejected? or does it sit in a queue, waiting to be given a connection?
hi i hv developed an application that runs on the local windows system and interfaces with the MSSQL 7.0 database server using ODBC DSN Connections. This application will be deployed over the LAN with over 100 users.
The MSSQL 7.0 server has only 5 user licenses that limits the concurrent usage to 5 users.
I need a solution whereby all users can access the SQL server database without adding more licenses
I have been searching hi and low on the web to try and find out typically which version of sql server should be used based on the number of concurrent users you will be having.
I believe that Sql Server 2005 Express should only really be used for up to 10 concurrent users while I seem to remember that Standard edition supports up to approx. 33,000 connections.
Does anybody have this information to hand as I need to write a business case to justify the potential upgrade.
Hi,Is it possible to have many different clients conncecting to a web basedserver all using, simultaneously, the same DSN-less connection string heldin a server side include file?Many thanksRay Allison
MSDE will only allow 5 concurrent connections to ...
a particular database? a particular instance? or a particular server?
ie. if i have one application running on one instance of MSDE with 5 connections and i have another application running on another instance of MSDE with 5 connections. would i actually have 10 connections? **both instances on the same machine...
I need to know how many concurrent users are allowed to access the database at any point of time.Will it depend on the type of licence?Where do we set this parameter.Some users are complaining 'time out' problem.Thanks for any help!
I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records. To do testing we run that stored proc manually, also. Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.
What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently. (May be semapohres,mutex or something like that?)
(I am trying not to use a table to store whether the stored proc is running or not)
On my server running SQL Server 2000 (SP3). I frequently get thisinformational message in the Event Viewer log.17052 :This SQL Server has been optimized for 8 concurrent queries. This limit hasbeen exceeded by 2 queries and performance may be adversely affected.The application that I am running is a vendor application so I do not haveaccess to the code. I contacted the vendor, but they have never seen thisproblem and cannot recreate it. I checked the Microsoft Knowledge Base forhelp, but I came up empty. It seems like it should be a simpleconfiguration change.Any advice?Thanks,Jeff
I have a puzzle in my mind here. I will thank anyone who can solve my puzzle.
I am not familiar with SQL and its theories behind, so please bear with me if I am asking a stupid newbie question.
My puzzle is generally a problem of generating sequence numbers. The following SQL is only a stripped down version - it fetches the max number, add 1 to it and updates the table with the new number.
DECLARE @max int
SELECT @max = MAX(next_number) + 1 from sequence_numbers
UPDATE sequence_numbers SET next_number = @max WHERE next_number = @max
Now if user1 gets 100 and user2 also gets 100 and they both try to update the table, what would happen? I fear that the result would be 101.
One of my coworker thinks that adding 'WHERE next_number = @max' can solve the conflict - user2 will fail. His reasoning is like this:
After user1 updates the table, next_number would be 101 and user2's update will fail because his WHERE criteria is still 100.
But I think user2 still sees the old data (100) and still succeeds and thus both users update the table with number 101.
Hi and thank you in advance to whomever takes the time to read this entry. I will be as detailed as possible, so I apologize for the length. The information here relates to the automotive industry, but I don't believe that is a very important detail
The overall purpose of this query is to create a compressed set of the data that exists in the database. The de-normalized information is structured like the following (including made-up data):
Code Block PartNumber Make Model Year ------------------------------------------------------- 835100 ACURA INTEGRA 2004 835100 ACURA INTEGRA 2003 835100 ACURA INTEGRA 2001 835100 ACURA INTEGRA 2000 835100 FORD FOCUS 2002
There is any number of part numbers, makes, etc in this mix. The query that I currently use is utilizing MAX and MIN functions to determine year range, but as in the sample above, this does not take into account the fact that there could be a year skipped in the middle. I could probably use a cursor to do this, but would really like to stick to set logic if at all possible.
My existing query is:
Code Block SELECT DISTINCT cwi.PartNumber ,RTRIM(lv.Make) AS Make ,MIN(lv.Year) AS StartYear ,MAX(lv.Year) AS EndYear FROM CWIParts AS cwi INNER JOIN PartTypes AS pt ON cwi.PartTypeID = pt.PartTypeID INNER JOIN PartDetail AS pd ON cwi.PartNumber = pd.PartNumber INNER JOIN Status AS s ON pd.StatusCode = s.StatusCode INNER JOIN LegacyVehicle AS lv ON cwi.LegacyVehicleID = lv.LegacyVehicleID WHERE cwi.PartTypeID = 10 AND s.Status = 'Active' GROUP BY lv.Make ORDER BY cwi.PartNumber ASC ,cwi.Make ASC
In hopes to end up with a result set that has 1 row for each unique part-number, make, and consecutive year range relevant to the part-number and make. I will also need to add the functionality to add other attributes (such as Model and Liters), but those are equality based and can be passed in easily.
I had done most of this programatically in vb.net, but I would much rather push this logic back to SQL Server. Thank you all for any help that you can provide on this topic.
Hi, I read that there's a "tecnical" limit of 32767 concurrent connections for SQL 2005 Express, and that SQL 2005 Express uses only 1GB of memory. So, what's the number of practical concurrent connections SQL 2005 Express can handle? Is it 200 ,300, 0r 400...? How much memory is consumed by every connection? Thanks in Advance.
I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.
Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.
The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.
I was wondering if it was possible to run 2 queries at the same time,IE rather than runningexec query1 exec query2 and having to wait for query1 to finish before query2 started, just have them run at the same time (they're not conflicting).I want to do this in TSQL, not by opening another query window in vs2005.
Does anyone know how to change the number of conccurent connections on SQL Server 7. We have purchased 10 additional licenses and I need to change the concurrent users from 10 to 20. Any help would be appreciated. Thanks
Hi all !Does someone know how many concurrent connections 2005 Express canaccommodate ? The limit on MSDE was 5 before performance drops considerably.Thanks for your help.Sebastian
I need some rough figures for replication performance. Can anyone give an idea of the number of concurrent subscribers a single IIS server can safely handle before having to move to load balancing with multiple IIS servers.
I would estimate the average daily row changes to be in the region of 500 to 2500 per subscriber.
I have a table into which the inserts will be done by multiple users at the same time;
The table has a primary key, non clustered, on unique id; each insert will have unique id, which is a seqence generated number, so they are always different for different transactions.
Is this possible to set the settings of sql server in such a way, that these inserts are done at the same time i.e. during insert by one user that table is not locked so that other inserts can take place at the same time as well?
I have a package that has several data flows that run concurrently after some initial tasks and an initial data flow. I want transactions on each of the data flows and have set the transaction option to Required on the data flows (not on the package itself). I am also using checkpoint restart on the package. A couple things are happening.
1) the first data flow is successful and that releases the several that are waiting. Some of these complete OK but inevitably one or two will fail. The failing data flows will be different from run to run, sometimes one and sometimes two will fail. The error says:
Error: 0xC0202009 at Provider_NF_Code, Delete Provider_NF_Code [130]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
My hunch is that DTC is getting the transactions mixed up. I think it is committing one just after another data flow has already started work expecting the transaction to still be active. That would explain why the failing data flows are random. Plus, if I set the MaxConcurrentExecutables to 1 the entire package is successful. BUT, why have concurrent tasks if you can't run them concurrently.
2) when the package fails with the DTC problem I restart it with the checkpoint file. I was expecting the package to restart with the failed data flows. Instead, it restarts with the initial data flow (that all the other flows wait for in the package). This data flow has always been successful. It's as if the transactions I have put on the individual data flows are actually placed on a single virtual container that all of them are in, and when down stream data flows fail the entire data flow chain is rolled back and set to restart.
How can I get multiple concurrent data flows to run with transactions?
Why are successful data flows being restarted? Can I get just the failed tasks to restart?
