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...

while it's slightly off topic, I worked with a  team that mixed the V model (kinda like waterfall) and agile.. it had an unfortunate nickname of "vagile"...

I recently migrated from Windows 2003 SBS running SQL 2005 Express along with SSMS 2005 Express. The new server is Windows 2008 R2 SP1 and has SQL 2008 R2...

I came...