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 an IDENTITY column be updated?

(SQL Server 2000, SP3)
Hello all!
I have a simple table:
create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
insert into Test (Name) values ('Test')
And I'd like to potentially update the IDENTITY column Id:
update Test set Id = 100 where Id = 1
However, I get the following error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Id'.
Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same error.
Is there any way to update a column with an IDENTITY property?
Thanks!
John PetersonThanks Sue! Ah, I see the blurb in BOL that says an IDENTITY can't be updated. Bummer.
:-(
It seems to me that in older versions of SQL Server, one could update a column with the
IDENTITY property. But, no longer (or my memory isn't what it once was ;-).
Thanks again!
John Peterson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:dc1njvsscenku5aoklp93tl6rjptcibdcf@.4ax.com...
> Identity columns can't be updated - I think it's documented
> under the UPDATE topic in BOL T-SQL reference. One possible
> option would be to set identity_insert on, use the existing
> values for a new record and insert the new record with the
> identity value you need to use and then delete the old
> record.
> -Sue
> On Wed, 13 Aug 2003 20:10:46 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3)
> >
> >Hello all!
> >
> >I have a simple table:
> >
> >create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
> >insert into Test (Name) values ('Test')
> >
> >And I'd like to potentially update the IDENTITY column Id:
> >
> >update Test set Id = 100 where Id = 1
> >
> >However, I get the following error:
> >
> >Server: Msg 8102, Level 16, State 1, Line 1
> >Cannot update identity column 'Id'.
> >
> >Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same
error.
> >
> >Is there any way to update a column with an IDENTITY property?
> >
> >Thanks!
> >
> >John Peterson
> >
>

Can "Date Modified" col be automatically updated w/o trigger for each table?

Hello,

I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated?

Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables.

Thanks!

Using triggers is the only way I can think out for this issue, as you want to record every modification to each table:)