Please provide sample DDL and INSERT statements for the above structure and you'll have a working solution very quickly.

I'd suggest that your 'real table' contains a row with a missing semicolon.

Or is this just a blog post?

Here's another way which is slightly more robust (because IsNumeric() can return unexpected results).

SELECT d.name
FROM sys.databases d
WHERE d.database_id > 4
      AND PATINDEX('%[0-9][0-9]',...

You are using command line sqlcmd, so SSMS is not relevant here.

Sure.

Can other people make changes to it?

Trying to establish whether the problem is with the table itself, or with your Access instance.

Replace

DATEPART("ww" ,GETDATE())

with

DATEPART( "ww", dateadd("d",-7,getdate() ))

I don't like this design much, but here's an idea that might help.

DROP TABLE IF EXISTS #Ops;

CREATE TABLE #Ops
(
    ProductId INT
   ,PriorityOrder...

Something like this:

INSERT INTO Archive_table (<column_list>)
  SELECT (<column_list>)
  FROM TableA AS A
    INNER JOIN TableB AS B
    ...

By 'Reports', are you referring to SSRS reports?

When you say 'few', do you mean that some reports are OK and others are not?

Do you get any errors? If so, please...

But how will you coordinate the synonym switch with what the Java app is doing?

Thom, if that's the case, you are right.

But for the examples provided, I can't see how it's required (excepting, possibly, the change in cost price – though any half-decent application...

I mean by running one or more queries which look for validation issues & passing the results of those queries to sp_send_dbmail.

There are many ways of doing it. Here...

I'm not a fan of triggers, so my initial thought would be to create a SQL Agent job which runs periodically (hourly? daily?), looking at all rows in the table...

• Create a variable expression to hold the entire SQL statement.
• Add the lookup to the dataflow.
• At the Control Flow level, get properties for the dataflow and go into...