Database

The answer to my question was given to me by someone from Microsoft. The reason why this deadlock happens is that the optimiser is choosing the second index for this SQL: Update anyTable set anyColumn = anyColumn mak So, it puts a lock in order to do the update and then SQL server is putting an exclusive lock to the primary key for the update and release the first lock of the second index. At this time the second process is putting a lock for the second index and waits for the 1st process to finish in order to get an exclusive lock for the primary key. The 1st process now is trying to reupdate the same table and to put a lock for the second index but this is already locked by the 2nd process (who waits the 1st). So, the deadlock is inevitable.

There would be some examples on how to synchronize Sql Server Express/CE with Sql Server, so yes. Then again, these samples are built specifically to show some specific functionality. Means it is no silver bullet; you may want to print the documentation and set up a test-environment. Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)

Hah hah your screwed, you will now spend the next 8 months working around edge cases on excel. You are looking at one of the classic development nightmares. Using Excel as a datasource for a database application. If the format is stable you can map the cells to fields in your table, a REALLY tedious job that will need to be repeated every time some fool of a use inserts a row or column. Never underestimate the power of human stupidity RAH

Yes - much less of a headache! :) One thing to watch out for .. if in the future you need to change the schema of the database after you have rolled it out to users you will need to consider how to amend existing data... but by then you will have upgraded to .net :laugh:

I think what you are looking for is a way to emulate the sql row_number function within MySQL (or do you mean Oracle PL/SQL?) This article[^] shows a method which I've used successfully in the past ... example posted here in case the link dies SET @row_number = 0; SELECT (@row_number:=@row_number + 1) AS num, firstName, lastName FROM employees LIMIT 5; As an aside, using Max() to determine the "next" number when inserting rows doesn't always work well in multi-user environments. You should have a look at auto_increment[^] instead

indian143 wrote: ... give user the roles to access the application db_owner ... That's a very bad idea. The user should only have the permissions required by your application. I assume your application doesn't need to modify the structure of tables, views, stored procedures, etc.? And it doesn't need to modify the users and permissions? So why give it those permissions? "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Member 12069869 wrote: Can MySql handle such large data? Yes. Of course that doesn't mean your application can. Nor that you can't mess it up by failing to design correctly. Member 12069869 wrote: each row have about 20 fields/colums Things that matter - What is the maximum, average and variance for the row size (your data not db overhead.) - What is the realistic growth rate? Realistic means that, not pie in the sky sales/marketing claims. - What will the system be querying on most? Unless your growth rate is very big this is more relevant. - Can data be aged? Thus will the above queries be mostly for the last month, or last three months versus spanning the entire data store

There are 3103 records in the table. I forgot that IQueryable just returns a query, that is not executed until you use it. I ended up splitting it into 2 with the Page index because I didn't know how to write the expression in 1 shot. Not sure if it's kosher, but feedback will tell me I guess. Thanks Richard! :) Public Shared Function load_subscribers( _ ByVal pPageIndex As Integer, ByVal pPageSize As Integer, ByRef pResults As List(Of ws_subscribers_list)) As Integer Dim pValue As Integer = 0 Dim context As New hx5Context() If (pPageIndex = 1) Then pResults = \_ ( From cs In context.crm\_Subscribers Order By cs.Name Take (pPageSize) Select New ws\_subscribers\_list With { .SubscriberID = cs.SubscriberID, .CustomerID = cs.CustomerID, .Name = cs.Name, .EmailAddress = cs.EmailAddress, .Source = cs.Source, .Subscribe = cs.Subscribe, .DateCreated = cs.DateCreated, .Status = cs.Status } ).ToList() Else pResults = \_ ( From cs In context.crm\_Subscribers Order By cs.Name Skip ((pPageIndex - 1) \* pPageSize) Take (pPageSize) Select New ws\_subscribers\_list With { .SubscriberID = cs.SubscriberID, .CustomerID = cs.CustomerID, .Name = cs.Name, .EmailAddress = cs.EmailAddress, .Source = cs.Source, .Subscribe = cs.Subscribe, .DateCreated = cs.DateCreated, .Status = cs.Status } ).ToList() End If pValue = pResults.Count() Return pValue End Function

What is your question?

Afzaal Ahmad Zeeshan wrote: Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread. Almost. A deadlock[^] occurs when two or more competing threads are waiting for each other to release a lock. For example: Thread 1 locks X; Thread 2 locks Y; Thread 1 tries to acquire a lock on Y - waits for thread 2 to release; Thread 2 tries to acquire a lock on X - waits for thread 1 to release; The DBMS can usually detect the deadlock, and will kill the thread which has done the least work. NB: There's nothing wrong with trying to access something locked by another thread, so long as the current thread doesn't currently hold any locks which would prevent that thread from finishing. "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

This sort of business rule should be in your application not the database. Wrong tool for the job. Never underestimate the power of human stupidity RAH

Aliyu Usman wrote: WHEN EXAMS BETWEEN 40 AND 0 THEN 'F' You need to reverse this so that you have: BETWEEN 0 AND 40 “That which can be asserted without evidence, can be dismissed without evidence.” ― Christopher Hitchens

--[Spgetdata] 1,2,3 ALTER PROC [dbo].[Spgetdata] @Col1 INT, @Col2 INT, @Col3 INT AS BEGIN DECLARE @count INT DECLARE @currentColumn NVARCHAR(25) DECLARE @currentColumnVal NVARCHAR(25) SET @count = 1 SET @currentColumn = NULL SET @currentColumnVal = NULL CREATE TABLE #tempTable ( ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, Val1 INT, Val2 INT, Val3 INT ) INSERT #tempTable (Val1, Val2, Val3) VALUES(@Col1, @Col2, @Col3) SET @count = 1 WHILE @count <= 3 BEGIN SET @currentColumn = 'Val' + Cast(@count AS VARCHAR(25)) --SELECT @currentColumnVal = @currentColumn --FROM #tempTable DECLARE @Q NVARCHAR(Max) SET @Q = 'SELECT @currentColumnValX = ' + @currentColumn + ' FROM #tempTable' EXEC Sp_executesql @Q, N'@currentColumnValX varchar(500) Output', @currentColumnVal Output -- Do stuff -- Do more Stuff SET @count = @count + 1 END PRINT @currentColumnVal -- Just to see what the value of the last Column is END

I'm not that home in ASP, not a language I work with very often; you might have more success in the ASP.NET forum. Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)

Try this CREATE TABLE tblTest (ID INT, NAME VARCHAR(20),AGE INT) INSERT INTO tblTest VALUES(1,'AAA',22),(1,'AAA',22),(2,'BBB',33),(2,'BBB',33),(2,'BBB',33),(3,'CCC',44),(4,'DDD',55) --create an alternate table to store the duplicate records CREATE TABLE tblTestDuplicate (ID INT, NAME VARCHAR(20),AGE INT) -- insert those duplicate records to this new table INSERT INTO tblTestDuplicate SELECT * FROM tblTest GROUP BY ID,NAME,AGE HAVING COUNT(ID) > 1 --delete the duplicate records from the original table DELETE FROM tblTest WHERE ID IN (SELECT ID FROM tblTestDuplicate) --insert all the records into the original table INSERT INTO tblTest SELECT * FROM tblTestDuplicate --Project the new records SELECT * FROM tblTest ORDER BY ID --clean up DROP TABLE tblTestDuplicate DROP TABLE tblTest /* ID NAME AGE 1 AAA 22 2 BBB 33 3 CCC 44 4 DDD 55 */ Hope this helps

@samflexx, I have simulated your situation and have made a solution that I think matches your requirement. Since you said that total records belonging to each division the obvious idea that cropped up is to use the ROLLUP function. I am presenting the solution. Let us know if it has helped you ;WITH CTE AS( SELECT 1 AS EMPNUM ,'File1'AS FILENAME ,'Name1' AS EMPNAME ,'Division1' AS Division UNION ALL SELECT 2 AS EMPNUM ,'File2'AS FILENAME ,'Name2' AS EMPNAME ,'Division2' AS Division UNION ALL SELECT 3 AS EMPNUM ,'File12'AS FILENAME ,'Name12' AS EMPNAME ,'Division1' AS Division UNION ALL SELECT 4 AS EMPNUM ,'File14'AS FILENAME ,'Name14' AS EMPNAME ,'Division1' AS Division UNION ALL SELECT 5 AS EMPNUM ,'File21'AS FILENAME ,'Name21' AS EMPNAME ,'Division2' AS Division UNION ALL SELECT 6 AS EMPNUM ,'File3'AS FILENAME ,'Name3' AS EMPNAME ,'Division3' AS Division) -- table create and data insertion part -- Query starts SELECT X.* FROM( SELECT EMPNUM = ISNULL(CAST(t.EMPNUM AS VARCHAR(10)), ' ') ,FILENAME = ISNULL(CAST(t.FILENAME AS VARCHAR(10)), ' ') ,EMPNAME = ISNULL(CAST(t.EMPNAME AS VARCHAR(10)), ' ') ,Division = CASE WHEN t.Division IS NULL AND t.EMPNUM IS NULL THEN 'Total :-' WHEN t.EMPNUM IS NULL THEN 'Total ' + t.Division + ' Count :-' ELSE t.Division END ,DivisionCount = COUNT(t.Division) FROM CTE t GROUP BY ROLLUP(t.Division,t.EMPNUM,t.EMPNAME,t.FILENAME))X WHERE (LEN(X.FILENAME) > 0 AND LEN(X.EMPNAME) > 0) OR (LEN(X.FILENAME) = 0 AND LEN(X.EMPNAME) = 0 AND LEN(X.EMPNUM) = 0) The output EMPNUM FILENAME EMPNAME Division DivisionCount 1 File1 Name1 Division1 1 3 File12 Name12 Division1 1 4 File14 Name14 Division1 1 Total Division1 Count :- 3 2 File2 Name2 Division2 1 5 File21 Name21 Division2 1 Total Division2 Count :- 2 6 File3 Name3 Division3 1 Total Division3 Count :- 1 Total :- 6

:-O Wrong is evil and must be defeated. - Jeff Ello

Okies... Thanks for the information...Will take care... My Tech Blog : IT Developer Zone