That is what is supposed to happen. 😉

When using PIVOT - you can only specify the columns that you actually are using. If you include additional columns, it causes the problem you are seeing.

So, the following...

You could use something like:

;With cteEmp (EmpNo, AddressLine, Dob, JoinDate, timestamp, rn)

As (

Select a.empno

,a.address

...

Sounds to me like this instance of SQL Server is in a cluster. If so, you need to add the other drives as dependencies for SQL Server in Cluster...

SSMS will not prompt you for a parameter like that. You need to modify this yourself every time you run the script.

You can use the DATEADD function to get...

Launch SQL Server Configuration Manager and change it there. That should set all appropriate permissions as needed.

When you say you are running the backups with administrator credentials - what does that mean?

Can you find out what user is running the SQL Server service and what user...

And one final update - adding a new non-clustered index on the ID column and making it the primary key fixed the problem. I could not reproduce the deadlock.

Removed...

I thought I would come back here and give you all an update. I was able to actually reproduce the same issue on a newly created table. I...

This really...

Here is a method that does not use a CTE - and will perform much better if there is an index on the StartDtTm column:

Select r.ResourceID

...

So, you have five rows being returned from the first CTE - then, you select all rows from the first CTE in the second CTE and are wondering why you...

I guess I am still not clear on what your issue is. If I use the following:

Use tempdb;

If object_id('tempdb..#TestReplace') Is Not Null

Begin;

...

Jerry - I would convert the column in the query itself. Using the temp table was just a way of showing what database was being defined from the replace...

Did you update statistics in 2005? Rebuild indexes? Update usage?

And finally, not related to performance - but did you run DBCC CHECKDB db WITH data_purity?