The problem isn't in the PS portion - it is in the SQL query.  You need to enclose the 'linked' server reference in the query - although I have no...

I don't see a unique index on the columns ExternalReference, RegionID and CreatedBy.  That means you can have multiple rows with those 3 columns - including multiple rows with the...

The proposed solutions seem to be a bit more code than is needed:

SELECT *
     , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
...

The proposed solutions seem to be a bit more code than is needed:

SELECT *
     , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
  FROM...

In your connection string - make sure you include the Encrypt option and/or pass in the option to trust the server certificate.  The newer drivers default this to true -...

An availability group is defined in SQL Server - so you can have a single windows cluster of xx nodes hosting as many instances of SQL Server that you need. ...

So either this is a very bad interview question - or there are missing details from the question.  There is no way you can answer the question correctly - so...

AG group is in synronus- automatic with windows failover setup

Can any one reply on this when primary is down not accessible, also automatic failover not occurred to secondary and...

The problem here is the $ in the PS script.  SQL Server Agent is attempting to use that as a token - and that is causing the failure.  The only...

Hi, yes that is my concern that BEGAN TRAN; and COMMIT TRAN; might not be improving things and could expose the query to holding a lock should something go...

I tried incorporating Jeffrey's suggestion, but the ancient VS version I have to work with keeps crashing in the expression builder.

So, I decided to use the hardcoded solution and...

Since all of the joins to the derived tables (selects in the from/join) - there is no benefit to even using the sub-queries.  Removing the sub-queries and refactoring to simple...

Well, hardcoding would be an option, though this would require some maintenance issues if customers decides to need to look further into the past.

The source system is an Axapta...

As a starting point - to insert into a table that has an IDENTITY column you MUST specify the column list excluding the IDENTITY column and cannot use SELECT *...

The issue with partitioning is that the archive table must reside in the same filegroup as the main table - in the same database.  If the goal is to move...