If XACT_ABORT is ON, an error will cause the whole transaction to be rolled back; if it's OFF, only the statement being executed will be rolled back.  When XACT_ABORT is...

I feel your pain.  Presumably you're the DBA?  If you don't get to make...

Better still, from an administrative point of view, would be to create a role and add users to it as and when required.

John

Why can't you use a trigger?  You could investigate SQL Audit instead - I think that would deliver what you're looking for.

John

A SQL audit needn't be too much of a burden.  Set up your filter carefully, limit the file size and the number of files, and import regularly into a database...

No, I think Full recovery mode is mandatory - it says here.  Dealing with transaction log backups isn't too much extra admin, although I suppose you will need a...

The query you posted returns the lowest person_id for every first_name - so you'd get the John with the lowest person_id, the Wally with the lowest person_id, and so on. ...

You can use SELECT TOP 1 with and ORDER BY person_id clause.

John

Linked servers, CMS, OPENROWSET, Policies, SSRS, SSIS - each has its advantages and its limitations and could form the whole solution or part of it.  You'll need to figure out...

No.  I'm not here to do your (home)work for you.  I've given you a link to get you going, and Des has also provided you with a query.  Use both...

Sam

Yes, you can help with execution plans, code smells (of which I've found many in vendor code), deadlock analysis, table and index designs, and so on.  However, if you...

Do you mean three columns at the same time?  Yes, you can update as many as you like - provided (I think) they're all in the same table.

https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#UpdateExamples

John

You have posted your own script suggestion.  Does it work?  If not, what is the problem - does it give an error message, does it not do what you intend...

I think SUM(...) OVER(...) is the way to go, with the columns that are to be grouped by in your PARTITION BY clause.

John

Presumably this will work:

COALESCE ('L' + LocationNo, 'A' + AsstNo) AS LocOrAsset

I use COALESCE instead of ISNULL - but that's just my preference.  You will need to...

• This reply was modified 6 years, 7 months ago by John Mitchell-245523.