Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
abrown72
Starting Member
4 Posts |
 Posted - 2014-06-03 : 08:49:01
|
| Hey All,I'm just looking for some alternative ideas here. With shared webhosting I can not use MS DTS or SSIS for security reasons.I need to take inventory records from a local SQL 2005 server when updated and insert them into a remote shared webhost 2012 SQL server.Please, any recommendations would be great!Regards,Aaron |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-03 : 11:53:22
|
| Can you use replication? Last resort would be triggers, but they would need to be async.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
abrown72
Starting Member
4 Posts |
Posted - 2014-06-03 : 12:05:13
|
quote:
Originally posted by tkizer
Can you use replication? Last resort would be triggers, but they would need to be async.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
I can't use replication, the tables are have different data, the local database takes data from multiple tables and inserts into one remote table.I am wondering...The servers are linked, can I use an update trigger that executes a select stored procedure on the local database, and passes values to an insert stored procedure on the remote database?Is that my work-around for the shared web host not allowing ad hoc distributed queries, or access to SSIS? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-03 : 12:38:42
|
quote:
Originally posted by abrown72
I can't use replication, the tables are have different data, the local database takes data from multiple tables and inserts into one remote table.
You can still use replication for this. You would modify the replication stored procedures on the subscriber to do what you want.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-03 : 12:40:02
|
quote:
Originally posted by abrown72
[quote]Originally posted by tkizer
The servers are linked, can I use an update trigger that executes a select stored procedure on the local database, and passes values to an insert stored procedure on the remote database?
You could, but I wouldn't recommend it. The trigger participates in the transaction. All of that work to write data to a remote database will slow down the parent transaction. And what happens when the remote server is offline or the network is down?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Inserting Data From Local SQL to Remote Shared Web