Ok, If you state there are no duplicates in the DAT_STAGE then thats fine, I'm always suspicious of data loaded from files as they can easily have duplicates in them,...

I cant see anything wrong with the query, thats why I asked if there were duplicated records in the staging table.

If you run

SELECT

lvl_nme

,ds_nme

,count(*)

From DAT_STAGE

Group by

lvl_nme

,ds_nme

Having count(*)>1

Do you...

Simple question have you checked the DATA_STAGE table for duplicated rows?

Curious,

Create Table #folder (FolderId int, FolderName varchar(100))

Create Table #Permissions (FolderId int, FolderName varchar(100),Permission varchar(100))

Insert into #Folder

Select 1 ,'Folder1'

Union Select 2 ,'Folder2'

Union Select 3 ,'Folder3'

Union Select 4 ,'Folder4'

Union Select 5...

You need to do an Outer Join, something like

Select FolderName

,COALESCE(Permission,'None') Permission

--,IsNull(Permission,'None') Permission --Alternate to COALESCE

From Folder f

LEFT OUTER JOIN [Permissions] p

on f.FolderId=p.FolderId

I would be very nervous about deleting records from a DW as the nature of them can mean that while its ok to delete from dimensions based on one set...

Either you dont have access to the Master db or more likley someone has done a clean up after mistakenly creating objects in the Master db and deleted the table...

Abu Dina (10/11/2012)

---

Jason-299789 (10/11/2012)

---

Does that work when itterating through a record set of server names, as I thought that the connections were set only when the package was loaded and...

Does that work when itterating through a record set of server names, as I thought that the connections were set only when the package was loaded and couldnt change during...

It depends on what you call a job. In SQL Agent Terms a Job is a single Item, though it may have multiple steps.

so You create a single Job,...

GilaMonster (10/11/2012)

---

Jason-299789 (10/11/2012)

---

One last question, was this always the case or is there a case to say that performance enhancements with the query engine over the last few revisions have...

GilaMonster (10/11/2012)

---

Jason-299789 (10/11/2012)

---

GilaMonster (10/11/2012)

---

IN is not an expensive operation. For matching rows, it's cheaper than join.

Moving a filter from a join to the where when you have an outer join...

GilaMonster (10/11/2012)

---

IN is not an expensive operation. For matching rows, it's cheaper than join.

Moving a filter from a join to the where when you have an outer join changes the...

In regards to the query wouldnt this run using a more optimal plan

;With Cte_Newcompany(NewCompanyId)

AS

(

SELECT CompanyID

FROM new.CompanyIndex AS CI

INNER JOIN UserData.dbo.CriteriaDistribution AS CD

ON CI.SegmentNo =[CD].[SegmentNo]

AND CI.[CriteriaID]...

I'm happy to have a look at the code you create and give it a sanity check.

In response to your question, you need to drop it by the constraint name,...