Can I call a web service from SQL?

I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.

Why do you need a webservice? Maybe it's better to archieve this using UPDATE Triggers? Although the performance of firing triggers is not so good in some case, but it should be easier and faster than calling webservice. So let's say you want to trace modification made in database1.dbo.Table1, and you want to copy the modified rows into database2.dbo.Table2, and the 2 tables have almost the same structure, except the Table2 has 1 more column used to record UPDATETIME. You can create an UPDATE/INSERT trigger on Table1 like this:

CREATE TRIGGER trg_TraceMod ON tempdb.dbo.tbl_Const1 FOR UPDATE
AS
IF(object_id('tempdb.dbo.tbl_Trace') IS NULL)
BEGIN
SELECT deleted.*,GETDATE() AS UpdTime INTO tempdb.dbo.tbl_Trace FROM deleted

END
ELSE
INSERT INTO tempdb.dbo.tbl_Trace
SELECT deleted.*,GETDATE() FROM deleted
go

To learn more about triggers, you can refer to:

Enforcing Business Rules with Triggers

|||Thank you very much for the reply. The reason I was think about a webservice is because the two databases are in two different physical locations and cannot access each other without a web service because they are not on the same network. Basically one database is in our office and the other one in downtown in a data center. The one in the data center needs to update the one in our office everytime it changes. Could I use triggers to do that?|||

Then you can tryLinked Servers. Connections between internet SQL Servers may be more complex than in the same network, there may be trouble in locating host SQL box, passing credentials, firewalls, and so on. You may take a look at this post if you fail to establish connections between the 2 SQL servers:

http://forums.asp.net/thread/1289341.aspx

And after the Linked Servers have been created (you can do this in Enterprise Manager->Security->Linked Servers), you can query the tables on the linked server using four part object name:

server.database.owner_name.object_name

Or you can useOPENQUERY.

|||

My question is somewhat related. I need to call a web service upon completion of a sql job. Is this possible? I'm using a product called Captaris Workflow. What I need to do is call a web service that creates a new workflow process and emails the person responsible for the first workflow task. The workflow part may sound foreign, nevertheless, I want to call a web service when a sql job completes. Any help would be much appreciated.

Thanks,

Jason

can anyone show me how to combine these two SQL queries into one

Hello-

i have a fairly big SQL query that is used to display data into a datagrid. Each query grabs data from two seperate databases. Is there anyway to combine these queries into one so all the data appears in 1 datagrid and not 2.

here is the 1st query:

SQL = "SELECT sum(case when month(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers_lots.quantity else 0 end) as Jan ,sum(case when month(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers_lots.quantity else 0 end) as Feb ,sum(case when month(pb_report_shippers.shipper_date_time) = 3 then pb_report_shippers_lots.quantity else 0 end) as Mar ,sum(case when month(pb_report_shippers.shipper_date_time) = 4 then pb_report_shippers_lots.quantity else 0 end) as Apr ,sum(case when month(pb_report_shippers.shipper_date_time) = 5 then pb_report_shippers_lots.quantity else 0 end) as May ,sum(case when month(pb_report_shippers.shipper_date_time) = 6 then pb_report_shippers_lots.quantity else 0 end) as Jun ,sum(case when month(pb_report_shippers.shipper_date_time) = 7 then pb_report_shippers_lots.quantity else 0 end) as Jul ,sum(case when month(pb_report_shippers.shipper_date_time) = 8 then pb_report_shippers_lots.quantity else 0 end) as Aug ,sum(case when month(pb_report_shippers.shipper_date_time) = 9 then pb_report_shippers_lots.quantity else 0 end) as Sept ,sum(case when month(pb_report_shippers.shipper_date_time) = 10 then pb_report_shippers_lots.quantity else 0 end) as Oct ,sum(case when month(pb_report_shippers.shipper_date_time) = 11 then pb_report_shippers_lots.quantity else 0 end) as Nov ,sum(case when month(pb_report_shippers.shipper_date_time) = 12 then pb_report_shippers_lots.quantity else 0 end) as Dec FROM pb_customers INNER JOIN pb_jobs ON pb_customers.customer_id = pb_jobs.customer_id INNER JOIN pb_recipes_sub_recipes ON pb_jobs.recipe_id = pb_recipes_sub_recipes.recipe_id INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id INNER JOIN pb_report_shippers_lots ON pb_jobs_lots.intrack_lot_id = pb_report_shippers_lots.intrack_lot_id INNER JOIN pb_report_shippers ON pb_report_shippers_lots.job_id = pb_report_shippers.job_id AND pb_report_shippers_lots.shipper_id = pb_report_shippers.shipper_id WHERE pb_customers.customer_deleted <> 1 AND pb_jobs.job_deleted <> 1 AND pb_jobs_lots.lot_deleted <> 1 AND pb_report_shippers.shipper_date_time between cast('01/01/2003 00:01AM' as datetime) and cast('12/31/2003 23:59PM' as datetime)"

Here is the 2nd query:

SQL = "SELECT ISNULL(sum(case when month(nonconformance.nc_date) = 1 then nonconformance.nc_wafer_qty else 0 end),0) as Jan , ISNULL(sum(case when month(nonconformance.nc_date) = 2 then nonconformance.nc_wafer_qty else 0 end),0) as Feb ,ISNULL(sum(case when month(nonconformance.nc_date) = 3 then nonconformance.nc_wafer_qty else 0 end),0) as Mar ,ISNULL(sum(case when month(nonconformance.nc_date) = 4 then nonconformance.nc_wafer_qty else 0 end),0) as Apr , ISNULL(sum(case when month(nonconformance.nc_date) = 5 then nonconformance.nc_wafer_qty else 0 end),0) as May ,ISNULL(sum(case when month(nonconformance.nc_date) = 6 then nonconformance.nc_wafer_qty else 0 end),0) as Jun ,ISNULL(sum(case when month(nonconformance.nc_date) = 7 then nonconformance.nc_wafer_qty else 0 end),0) as Jul ,ISNULL(sum(case when month(nonconformance.nc_date) = 8 then nonconformance.nc_wafer_qty else 0 end),0) as Aug ,ISNULL(sum(case when month(nonconformance.nc_date) = 9 then nonconformance.nc_wafer_qty else 0 end),0) as Sept ,ISNULL(sum(case when month(nonconformance.nc_date) = 10 then nonconformance.nc_wafer_qty else 0 end),0) as Oct ,ISNULL(sum(case when month(nonconformance.nc_date) = 11 then nonconformance.nc_wafer_qty else 0 end),0) as Nov ,ISNULL(sum(case when month(nonconformance.nc_date) = 12 then nonconformance.nc_wafer_qty else 0 end),0) as Dec FROM nonconformance INNER JOIN nc_department on nonconformance.department_id = nc_department.department_id INNER JOIN nc_major_category ON nonconformance.major_category_id = nc_major_category.major_category_id AND nonconformance.status_id <> '5' WHERE nc_department.scrap_category = '1' AND nonconformance.nc_date between cast('01/01/2004 00:01AM' as datetime) and cast('12/31/2004 23:59PM' as datetime)"

I know there has to be someway to combine these into 1. The issue I have is they are in different databases.

ANY HELP would be appreciated.Hi, Andrew,

You may use union query to merge the two sql statements. Make sure they return the same number of columns. If not, add some dummy columns to make them equal(dummy column could be 0 or empty string '')

the issue of diffeent database can be resolved using sytax like
db_name..table_name.
Kind like fully qualified name:)

Take you first query as example: replace 'pb_customers' with 'DatabaseName..pb_customers'.

The query is too big. I am sorry I can't prototype it in my computer. You have to try youself. Let me know if got error message.

Good Luck

-David J.|||thanks figured it out.