Sounds like maybe there's an explicit DENY in place.

When a login is sysadmin the DENY won't affect anything because security checks are bypassed.

Try running this in the database in question:

SELECT...

Eric M Russell (4/25/2016)

---

davidlester.home (4/25/2016)

---

While I can give the DDL for this, I don't think that this is a normal issue. I am good myself at performance tuning. It seems...

Ah, my condolences.

There's a really old and still active connect item about this limitation.

You can at least upvote it and hope. 🙂

https://connect.microsoft.com/SQLServer/fedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance

I thought I remembered there being some ugly ways...

That's because the non-clustered index was created as partitioned (that is the default behavior when you create an index on a partitioned table).

With it partitioned on eventlogID, that means it...

The problem in this case is the compile time.

If you look at the plan, you'll see that optimization timed out, and the compile time and CPU are both just over...

Another way to do unpivots is with CROSS APPLY.

I generally prefer doing it that way.

Here's the equivalent query in that form:

SELECT ID,

Product,

...

I'd check to make sure it doesn't have auto close on.

That could make sense of the sequence.

VB code runs, take a while, session closes, database auto closes.

SSMS session executes stored...

RAND() is a function. You need those parentheses.

Also note that if you create such a column and insert multiple rows in the same statement, they will all have the same...

Yes.

Under the job step properties, in the advanced section, there's a spot for Retry Attempts and Retry Interval which do exactly that.

Cheers!

That is true, but I think the OP may be asking when many lower-level locks are escalated to a higher-level lock (partition or object), whether those lower-level locks that are...

Ah, ok, as I read it that makes more sense.

The INSERT only has an S lock.

The REBUILD has the U lock and is trying to convert it to an...

That's why I'd like to see the full deadlock graph, if possible. Just from this information the only thing I could guess is that it's run in a transaction and...

sysschobjs is a base table that lies behind sys.objects, sys.indexes, and a bunch of other system views, so all the references to sys.objects, sys.indexes and such are why that INSERT...

wrightyrx7 (4/21/2016)

---

John Mitchell-245523 (4/21/2016)

---

Something like this...

WITH Departments AS (

SELECT *, d.DepartmentName,

CASE WHEN p.PDT_ORG_4_CDA LIKE 'X%'

THEN p.PDT_ORG_5_CDA

ELSE p.PDT_ORG_4_CDA

END AS DeptCode

)

From stage_chris21.PSDET p

INNER JOIN Departments d

ON p.[DeptCode] = d.DepartmentCode

... or this:

SELECT...

Pher (4/21/2016)

---

Using SQL 2012

-- Msg 195, Level 15, State 10, Line 5

-- 'NVARCHAR' is not a recognized built-in function name.

-- The TRY_Convert function requires 3 argument(s).

SELECT TRY_CONVERT(NVARCHAR(1), 234523)

Did I miss...