Saturday, September 28, 2013

[SQL 2012] Lazy Spool - What is causing it ?

[SQL 2012] Lazy Spool - What is causing it ?

Lazy Spool - What is causing it ?

Posted: 27 Sep 2013 04:07 AM PDT

I removed the TOP, tried commenting out the WHERE, OR clauses, but cant seem to lose the Lazy Spool !Thanks for any help!USE [JobPortal9_10_13]GO/****** Object: StoredProcedure [dbo].[GetAllJobsSearchedDynamicQueryNew_Vikas_5_21_13] Script Date: 9/27/2013 11:56:57 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ============================================= -- -- Create date: <Create Date,5-7-13,> -- Description: <Description,[Get All Jobs Searched Structured SQL],> -- -- ============================================= ALTER Procedure [dbo].[GetAllJobsSearchedDynamicQueryNew_Vikas_5_21_13] -- Add the parameters for the stored procedure here @Title varchar(250), @CompanyID INT = NULL, @Industry int, @Industry2 int, @Industry3 int, @Date int, @JobTitle int, @JobType int, @Experience int, @Education int, @State int, @City int, @Salary int, @MaxSalary int, @fromRec int, @toRec int, @SortType VARCHAR(50),@SortOrder VARCHAR(10) AS IF @CompanyID < 1 SET @CompanyID = NULLDECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));DECLARE @C TABLE( ID int, CityID INT, StateID INT, Location VARCHAR(50))DECLARE @HasLocation BIT = 0IF (@CITY IS NOT NULL AND @CITY > 0) OR (@State IS NOT NULL AND @State > 0)BEGIN SET @HasLocation = 1 IF @City < 1 SELECT @City = NULL IF @State < 1 SELECT @State = NULL INSERT INTO @C SELECT ID, CityId, RegionId, ((SELECT TOP 1 NAME FROM Cities C WHERE C.ID = CityId) + ', ' + (SELECT TOP 1 NAME FROM Regions R WHERE R.ID = RegionID)) LOCATION FROM Companys WHERE (@City IS NULL OR CITYID = @City) AND (@State IS NULL OR REGIONID = @State);END;WITH CTE1 (RowID,id,CompanyID,title,contactperson,lastmodified,description, workexperience,jobtypeid,AcademicExperienceTypeId,workexperiencetypeid, industryid,industryid2,industryid3,salaryminid,salarymaxid, jobTitle, --city,state, --PostalCode, --name, positions,deadline)AS( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowID, id, CompanyID, title, ContactPerson, LastModified, description, isnull((select we.[Name] from workexperiencetypes we where we.id=workexperiencetypeid),'') as workexperience, jobtypeid,AcademicExperienceTypeId,workexperiencetypeid, industryid,industryid2,industryid3,salaryminid,salarymaxid, isnull((select jt.[Name] from jobTitles jt where jt.id=jobtypeid),'') as jobTitle, positions,deadline FROM EmploymentOpportunities WHERE (@Title IS NULL or title = @Title) and (@Industry = 0 OR industryid = @Industry) and (@Industry2 = 0 OR Industryid2 = @Industry2) and (@Industry3 = 0 OR Industryid3 = @Industry3) and (@Date = 0 OR lastmodified >= @Date) and lastmodified is not null and lastmodified > @ActualDate AND (@CompanyID IS NULL OR COMPANYID = @CompanyID) and (@JobTitle = 0 OR title = @JobTitle) and (@JobType = 0 OR jobtypeid = @JobType) and (@Experience = 0 OR workexperiencetypeid = @Experience) and (@Education = 0 OR academicexperiencetypeid = @Education) --and (@State = 0 OR c.RegionId = @State) --and (@City = 0 OR c.CityId = @City) and (@Salary = 0 OR SalaryMinID >= @Salary) and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary) AND (@HasLocation = 0 OR COMPANYID IN (SELECT ID FROM @C)))SELECT *, (SELECT MAX(RowID) FROM CTE1) TotalCount, isnull((select we.[Name] from workexperiencetypes we where we.id=workexperiencetypeid),'') as workexperience, isnull((select jot.[Name] from jobtypes jot where jot.id=jobtypeid),'') as jobtype, isnull((select edu.[Name] from Degree edu where edu.Id=AcademicExperienceTypeId),'') as education, isnull((select ind.[Name] from industries ind where ind.id=industryid),'') as industryname, isnull((select ind.[Name] from industries ind where ind.id=industryid2),'') as industryname2, isnull((select ind.[Name] from industries ind where ind.id=industryid3),'') as industryname3, isnull((select jt.[Name] from jobTitles jt where jt.id=jobtypeid),'') as jobTitle, City + ', ' + [State] + ', ' + PostalCode AS LocationFROM (SELECT ROW_NUMBER() OVER (ORDER BY --id) RowNumber, CASE WHEN @SortOrder = 'ASC' THEN CASE WHEN @SortType = 'LastModified' THEN CAST(LastModified AS VARCHAR(50)) WHEN @SortType = 'Location' THEN City WHEN @SortType = 'Title' THEN Title END END ASC, CASE WHEN @SortOrder = 'DESC' THEN CASE WHEN @SortType = 'LastModified' THEN cast(LastModified AS VARCHAR(50)) WHEN @SortType = 'Location' THEN City WHEN @SortType = 'Title' THEN Title END END DESC, id) RowNumber, * FROM (SELECT *,(SELECT TOP 1 NAME FROM Cities CT WHERE ID = (SELECT TOP 1 CO.CityId FROM COMPANYS CO WHERE CO.ID = CTE1.CompanyID)) City, (SELECT TOP 1 AbbreviatedName FROM Regions CT WHERE ID = (SELECT TOP 1 CO.RegionId FROM COMPANYS CO WHERE CO.ID = CTE1.CompanyID)) [State], (select top 1 PostalCode from companys co where co.id = cte1.CompanyID) PostalCode FROM CTE1) as p0)AS P1where RowNumber BETWEEN @fromRec AND @toRec OPTION(Maxdop 8)

Saving maintenance plans from prior versions using SSMS 2012 changes precedence constraint

Posted: 27 Sep 2013 09:19 AM PDT

I ran into strange behavior with SSMS 2012. Here's a test I did to verify it's a problem. I created a basic maintenance plan using SSMS 2005. I added a backup database task to backup the model database (as an example) to a folder that does not exist. I then added an execute t-sql statement task to send an email to me. I connected the backup task to the email task using the precedence constraint and set it to "failure" as well as a "logical or". I saved it and ran it to verify it will send me an email on failure. It did. I closed it.Next I opened up the maintenance plan using SSMS 2012 and set the reporting and logging to generate a file. I then saved the plan and closed it. I opened it again in SSMS 2005 to find the precedence constraint is now a "logical and". In SSMS 2012, it still shows as a "logical or". The reason I need the "or" is because my actual maintenance plans contain several tasks. They should be set to email us when any of the tasks fail, not all of them. When it's set to "and", we don't get an email when one of the tasks fails.Any ideas why this is happening? I have now learned (the hard way) to not edit or save maintenance plans in 2012 that were created in prior versions. I had the same problems with plans originally created in SQL 2000, 2008, and 2008 R2. Thanks!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog