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 I automatically see which VIEWS are used for each REPORT?

Is there a way to see which database views (or tables) were used for each report?

(For example, I have a report called "Customer Oriented" and it uses 2 views: Customers, and Products. Can I automaticaly pull out this information?

Thanks,

The dataset information is just stored as text, unless you code your own logic for parsing that information will will have no chance of getting that information that you wanted to have.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens,

Where can I see that text? (the one in which you can see the name of dataset). Although I am more looking for seeing database VIEWS or TABLES used to create a report rather than a DATASET, but that might give me some insight.

Thank you.

Alexan

|||

You can call GetReportDefinition() on the SOAP API for a published report and retrieve the dataset elements from the report. You would then have to parse the contents of the dataset manually to determine the names of the tables or views which are referenced.

Out of curiosity, why do you have this requirement?

|||The dataset definition is presented as a node in the XML data (WHat John ment with Report Definition), just can just retrieve that with e.g. a method like SelectSingleNode.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for your answers. Your answers are still very higher than my knowledge and I don't know how to do them.

John: The reason that I need to do this is because we need to change the Views from time to time (to satisfy newer reports needs) so we want to see what reports that we have already made from a particular view might be affected by these changes. I think this is a very lame way, and it's better to create a new view for each specific report, but my boss says maintaining a lot of views is pain for him. I am not sure what he meant.

|||

He meant that it is better for you to have pain than for him to have it.
(There are lots of times I am all shades of wrong.)

R

|||

In all honesty you shouldn't try to do this from Reporting Services. The data entered in the query box is any valid statement that will return a result set. Considering just SQL server as a data source, valid statements include stored procedure and table valued function calls and trying to parse the SQL to extract table names is crazy.

You should perform an audit and store and maintain that information in some spreadsheet.

That's my 2 cents.

|||

Think you're right. Thank you all.

Can DTS automatically create primary key values upon export?

I have two practice tables I have created and want to export the values of one into the source table. I want to know if I can export into a table and have the destination table automatically give a primary key value to a record? I haven't been able to figure this out even after fiddling with the "Enable identity insert" checkbox under the Column Mappings tab. I have created source tables with and without primary keys and neither works because of the fact that I need to have a value for a primary key in order to INSERT into the destination.

Do I have to copy the source records into a staging table and assign the PK values myself by hand? This can't be the answer.

ddaveyou have to clear the enable identity insert checkbox if you want to allow the identity values to be poped by you
adding a check indicates that you want to programmatically provide the identity values
try clearing it and setting your identity on the column|||Thanks. I did do as you mention but I also had to create the destination table with a Primary Key with an identity field that incremented automatically by 1. I also had to create a source table that had NO primary key field. I then imported the source into the destination and the incoming rows were assigned Primary Key values in sequence.

ddave

you have to clear the enable identity insert checkbox if you want to allow the identity values to be poped by you
adding a check indicates that you want to programmatically provide the identity values
try clearing it and setting your identity on the column

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:)