Transact SQL :: Deadlocks On Server From Select Statements?
Feb 13, 2014
I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed
CREATE TABLE [dbo].[CMS_LOCKS7](
[PARENTID] [int] NOT NU, --we have a non clustered index on this column
[CHILDID] [int] NOT NULL, --we have a non clustered index on this column
[ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column
[ORDINAL] [int] NULL,-- we have a non clustered index on this column
I am working on an app that getting quite a few deadlocks due to delete statements. I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as begin Declare @ret int; Declare @EnterVolumeTime int; Declare @ExitVolumeTime int; Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time.The table has clustered index on ID column
----session 1 -------- begin transaction select * from a1
update a1 set id = 22 where id = 2
----session 2 -------- begin transaction select * from a1
update a1 set id = 22 where id = 2
Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock.so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation.
I need to breakdown some information with just initials (sometimes 2, sometimes 3 if the initials are already used) SQL Server throws the error of:
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
What should I alter, or how should I write this query so I can use all of the needed case statements? (their are actually about 24 when statements, but this is just to get a working example to display)
Select case when employee_name Like 'Jorge Jones' Then 'JJ' when employee_name Like 'Mike Mikes' Then 'MM' when employee_name Like 'Albert Alvarez' Then 'AA' when employee_name Like 'Hernandez-Sotata' Then 'HS'
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE WHEN [AddressType] = 'M' THEN [MailingAddress].[Address1] [MailingAddress].[Address2] [MailingAddress].[City] [MailingAddress].[State] [MailingAddress].[Zipcode] WHEN [AddressType] = 'D' THEN [DefaultAddress].[Address1] [DefaultAddress].[Address2] [DefaultAddress].[City] [DefaultAddress].[State] [DefaultAddress].[Zipcode]
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
STEP1: CREATE TABLE Trace(Statement VARCHAR(MAX)) INSERT INTO Trace VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted from History'), ('Insert into Account Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.
What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.
This is an example of the table:
UpdateColumn Old Value New Value ReceivedDate 7/8/2015 5:00:00 AM ReceivedDate 7/8/2015 12:00:00 AM 7/9/2015 5:00:00 AM ReceivedDate 7/9/2015 12:00:00 AM
So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying
This is the query:
CASE UpdateColumn ... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ... I tried adding CASE UpdateColumn ... WHEN 'ReceivedDate' IS NOT NULL THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ...
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
Hello, how can i merge together several select statements? I have something like this: CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR) AND MPI.CREATE_DT>=20070101 ORDER BY MPI.CREATE_DT
SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD FROM PATIENT, PHYSICIAN WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR
I have a table that list Canadian provinces and American States it looks something like this:
ID | ProvState
Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.
I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.
SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState UNION SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState
Arnie and All others. Thanks for your help. The previous case became lenghty and then just mixed up a lot.
To make it easy I have created two temp tables and wrote to test select statement .
You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement.
--Selected record must RaType='b' -- PlanID='H321' -- Gender='0' -- --And not to include in select if any one of these meets: -- Hosp='1' in other words it has to be 0 -- ESRD='1' or Rafctor Type in ('g','f') in other words ESRD should be 0 and rafctorType in ('h','i') -- Dod is not null in other words Dod has to be null --
--copy from here
GO Create table #MyTable
( RowID int IDENTITY, RD varchar(10), RAType varchar(5), History varchar(15) )
INSERT INTO #MyTable VALUES ( '1', 'A', '1111' ) INSERT INTO #MyTable VALUES ( '2', 'S','2222' ) INSERT INTO #MyTable VALUES ( '3', 'D', '2345') INSERT INTO #MyTable VALUES ( '4', 'I2','1234' ) INSERT INTO #MyTable VALUES ( '5', 'C','3333' ) INSERT INTO #MyTable VALUES ( '1', 'B','4444' ) INSERT INTO #MyTable VALUES ( '2', 'X','5555' ) INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666') GO
Go Create Table #MYTable2
( RowID int IDentity, RD varchar(10), RaType varchar(5), History varchar(15), PlanID varchar(6), Hosp varchar(2), ESRD varchar(2), RafctorType varchar(3), gender varchar(5), dod varchar (5) NULL
Select #MYtable2.History from #MYTable2 INNER JOIN #mytable on #myTable2.History=#mytable.history Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL
Select #Mytable2.History from #MyTable2 INNER JOIN #mytable on #mytable2.History=#mytable.history where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL)
Hi Everyone, Can you please let me know what is wrong with the following code? I run the following code using path: http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin public void searchResultsWithClinic() { SqlConnection myConnection; string conString; conString = ConfigurationManager.AppSettings["calendarString"]; myConnection = new SqlConnection(conString); String cmdStr1, cmdStr2, cmdStr3; cmdStr1 = "SELECT sb_clinic_name FROM sb_client_and_clinic WHERE sb_client_id = '" + Request.Params.Get("LoginID") + "'"; cmdStr2 = "SELECT sb_client_id FROM sb_client_and_clinic WHERE sb_clinic_name = '" + cmdStr1 + "'"; SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection); DataSet DS = new DataSet(); myCommand.Fill(DS, "SearchPaymentResults"); repeaterSearchPaymentResults.DataSource = DS; repeaterSearchPaymentResults.DataBind(); myConnection.Close(); }
Incorrect syntax near 'admin'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'admin'.Source Error:
Line 90: SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection); Line 91: DataSet DS = new DataSet(); Line 92: myCommand.Fill(DS, "SearchPaymentResults"); Line 93: Line 94: repeaterSearchPaymentResults.DataSource = DS;Source File: d:Inetpubwwwrootjimmymay_30th_2006vcalendar_cssb_SearchPaymentResults.ascx.cs Line: 92 --------------------------------------------------------------------------------- Please note that the 'admin' in the error message comes from http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin thanks,May
I have a SP returning the following result The select statement for this is
Code:
SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount FROM dbo.Test2 INNER JOIN dbo.test3 INNER JOIN dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId) GROUP BY dbo.TEST1.[OFFICE NAME] ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is
Code:
Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks
I only want to count the titleids that are on loan can I join these two statements or write the stored procedure a different way? I hope this makes sense. select count(libraryrequest.titleid) as [Presently on Loan], libraryrequest.titleid, media.[description]as Media from libraryrequestjoin titles on titles.titleid = libraryrequest.titleidjoin resources on resources.titleid = titles.titleidjoin media on media.mediaid = resources.mediaidgroup by libraryrequest.titleid , media.[description] select distinct requestors.Requestorid, titles.title, resources.quantityowned,requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip, libraryrequest.shipdate,libraryrequest.duedate, libraryrequest.returndate from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.requestorid = Requestors.requestoridjoin resources on resources.titleid = titles.titleid
how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS' Code1.... SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE1')
Code2.....
SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE2')
I am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints' FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID WHERE M.EMAIL = @UserEmail
Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this.
SELECT column1 FROM table1 WHERE column2 = (select column2 from table1 where column3 = 14)
Hi guys and gals, I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says: 'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.' My Code is below: //Start of sql CREATE PROCEDURE ADMIN_GetSingleUsers( @userID int) AS DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE, Cast(Users.Active as varchar(50)) as Active, Cast(Users.Approved as varchar(50)) as Approved, Users.Unit_ID As usersUnitID, * From TITLE, Users WHERE User_ID = @userID AND TITLE.TITLE_ID = Users.Title_ID ) Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL //End of sql Can you point to what I am doing wrong? Thanks in advance!
I am having problems using IF statements. In fact, I don't even know if it is possible to use "IF" in a SELECT statement. Here's my dilemma:
For each employee in my database, I have a numeric rating. I want to assign the rating to a category (green, yellow, red), based on the range the rating falls into.
My data currently looks like this: EmpName Rating employeeX 100 EmployeeY 85 EmployeeZ 25
After the magic of SQL, I want the data to end up looking like this (actually, my boss wants it like this):
EmpName Green Yellow Red employeeX 100 EmployeeY 85 EmployeeZ 25
The numeric rating would be inserted into the appropriate column, based on these ranges: 95 to 100 = green 80 to 94 = yellow < 80 = red
I thought I could use a CASE statement, but I just learned that I can't do comparisons (I'm obviously a beginner). And I can't figure out how to use an If statement (or IIF).
