Steve Jones - SSC Editor (4/26/2016)

---

Phil Parkin (4/26/2016)

---

As a general rule, it's better to put IsNulls inside Sums rather than outside. This avoids the warning message about NULL values being...

info.sqldbamail (4/26/2016)

---

Thanks for your answer, one last question i would like to know is, if SQL Server like to consume whole available memory then why it is not consuming whole...

I assume that the ID columns in PolicyAudit and Systems are unique? If so, you're only going to get one row because you're joining on them to the single-row...

Remove them first. Nobody wants orphaned users in their database. It shouldn't be too difficult to write a script that will take the login name, go to all...

I prefer the nuclear option - if the login is no longer required, get rid of it. If you have proper change control procedures, you'll have a rollback script...

If you're using SSIS, it's easy. Create a variable called SettingsQuery and set its value with an expression that changes according to what version it's running on. You...

If you've got all that additional memory in your server and it's not doing anything, then yes, increase your max server memory. Check it's not needed for anything else...

Did you read the error message?

Try using one of the other memory columns, such as physical_memory_kb. If you can't...

Leave it as it is. That's how it's supposed to work. SQL Server won't give back memory it has taken unless the operating system asks for it. ...

Do you have anything else on the server, other than SQL Server? What are the messages you're getting?

John

Well, I don't have the luxury of being able to see your tables, so I couldn't test it. But I think if you swap those table aliases over, or...

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 *, d.DepartmentName,

CASE WHEN...

You can't join on a column alias. Either wrap the whole SELECT statement in a CTE or subquery, or repeat the whole CASE expression in your join predicate.

John

Chris

You need to use a CASE expression.

John

If you look in the SQL errorlog, do you have failed login attempts for sa? If you look at the Properties of SQL Server Agent and go to the...