We have two MS SQL 2008, databases. They are unlinked and on two separate servers. Due to security restrictions beyond my control - this situation exists.
Database "A" (Restricted) - contains many VIEWS that have the data we need. There are no tables. The vendor only provides us with selected datasets as VIEWS. We cannot change this. Every night this database schema and contents is updated at close of business, so we need to run a script/application to move the latest nightly copy into Database "B" that we can manipulate for reporting.
We need the data within these VIEWS moves into Database "B" (unrestricted, our server), and placed into matching TABLES with the same naming conventions and data types (referential integrity not required).
We're looking for a VB script / application that can be placed on our Windows machine and run nightly by scheduler. It should...
- Clear out Database "B".
- Copy the latest VIEW "schema" from Database "A" into matching TABLES in Database "B".
- Copy the contents from VIEWS in Database "A" and place into new matching TABLES in Database "B".
It must also have the following functionality...
- If the process is successful, send a text email with the results of the activity, "Copy Successful, # tables copied, # records" (something like that).
- If the process fails, send a text email with failure notice.
- The connection parameters for both databases (username, password, host, port, database, etc), should be editable either from an text config type file or within a user interface (whatever is cheapest, easiest).
- The notification email address for success or failure, also editable in the same text config file or user interface
The deliverable would be a working application along with the source code - but ideally we would like to continue working with you longer term as our needs for this may change in the future.