0

I've a read-only access to a database server dbserver1. I need to store the result set generated from my query running on dbserver1 into another server of mine dbserver2. How should I go about doing that?

Also can I setup a trigger which will automatically copy new entries that will come in to the dbserver1 to dbserver2? Source and destination are both using Microsoft SQL server.

Following on that, I need to call a python script on a database trigger event. Any ideas on how could that be accomplished?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
90abyss
  • 7,037
  • 19
  • 63
  • 94
  • Use linked server and distributed query like `INSERT INTO tab... SELECT * FROM [linked_server].[database].[schema].[table]` – Lukasz Szozda Sep 18 '15 at 18:50

1 Answers1

1

lad2015 answered the first part. The second part can be infinitely more dangerous as it involves calling outside the Sql Server process.

In the bad old days one would use the xp_cmdshell. These days it may be more worthwhile to create an Unsafe CLR stored procedure that'll call the python script.

But it is very dangerous and I cannot stress just how much you shouldn't do it unless you really have no other choices.

I'd prefer to see a polling python script that runs 100% external to Sql that connects to a Status table populated by the trigger and performs work accordingly.

Rachel Ambler
  • 1,440
  • 12
  • 23