For starters, I am curious what the original memory setting is.

I am also curious as to how long this stored procedure has been in place, and how long the server...

If you are using Acess for the application side, why don't you pull the original dataset into Access and work to refine that instead of trying to track a temp...

Same result. I tried looking for the ONLINE description yesterday, and I still get the error with your slightly different version.

I do like the dbid > 4 part. ...

I agree. If you backup to a remote drive, backups will fail if the drive has connections or hardware issues.

Try pressing F5 to refresh the Service Manager or Configuration Manager screen.

The additional condition is perfectly acceptible.

I can see why the Balance cannot be self incrementing. However, what is the business rule that keeps you from having a sequence self increment?

On the Balance column, do...

Using Jared's method sounds like a good idea if you are trying to do the replication in real-time.

Do be sure and place the operations inside transactions. If the databases...

Does this syncronization need to be live or once per day?

Since you asked. A Linked Server is more secure because the credentials are stored within the object and not a parameter of the OPENROWSET.

I would try both and compare the time to return the results.

Go with the best overall. You may be surprised as to which works best for each different scenario.

What else is running on the production system at night? It is not unusual to have jobs scheduled to perform the heavy-lifting type maintenance over night. If this...

Here is the BOL link for OPENROWSET.

Basically, it is only recommended for adhoc queries to a remote server that is not a linked server. This creates a new connection...

One thing you may want to try is to drop the table indexes before the load and then add them back in when complete. If the tables have multiple...

Creating new indexes can have some negative impact when adding or updating data in the table. Often times this is negligible.

As for SQL Server offering impact estimates on specific...