SELECT S.StoreId, I.ItemId
   ,COALESCE(X.Qty, 0) AS Qty
FROM Stores S
   CROSS JOIN Item I
   LEFT JOIN StoreInv X
    ...

More coffee required.

If a transaction is going to be named I suspect it should be consistent.

BEGIN TRANSACTION Archive
ROLLBACK TRANSACTION NBMTArchive
COMMIT TRANSACTION NBMTArchive

• This reply was modified 1 year, 8 months ago by  Ken McKelvey.

A bit of a shot in the dark but do you have a...

• This reply was modified 1 year, 9 months ago by  Ken McKelvey.

This is not an issue but merely a nudge to developers to consider multi layer security. (What happens if someone can get through your firewall?)

You have three options:

1. Continue as...

If you do not like Powershell etc then maybe try putting the SP in an agent job and then allow...

If this is a production sysyem, and given the amount you have posted recently, it may be cheaper to bring in a consultant. If you are based in India maybe...

Have you made the poxy with sp_xp_cmdshell_proxy_account?

Personally I would not call BCP with xp_cmdshell.

In SSMS you could write code like a template and then press Cntl-Shft-M to fill in the values. Look at the template explorer under the view menu.

As you have not included any examples of the 7 day condition in the test data I am going to ignore it.

What I have done works with the test data...

This shows how to execute xp_cmdshell without being a sysadmin.

https://sqland.wordpress.com/2018/10/11/allow-a-user-to-execute-xp_cmdshell-without-being-a-sysadmin/

To avoid giving your user access to the master db, as above, you will have to look at giving the...

Good. Obviously in real life you will need to check for effects on indexes, permissions etc and sort them out.

Also, you probably should not be allowing these columns to be...

• This reply was modified 1 year, 9 months ago by  Ken McKelvey.

Use sys.computed_columns. Maybe something like:

IF NOT EXISTS
(
   SELECT 1
   FROM sys.objects O
      JOIN sys.schemas S
   ...

Can you use a sequence instead. A sequence can be CYCLEd.

I would be inclined to install and run these scripts first before getting involved with XEs:

https://www.brentozar.com/first-aid/