SQL server getting data from a webservice

[nswan]

Hi,

I have to get data from a webservice into an SQLserver database. I was just wondering if SQL server can directly access the webservice and read the data, or do i have to build a client in .net that accesses the webservice and then imports the data to SQL server.

Also if i use a client, is a windows service a good thing to use as it needs to do this import at regular intervals during the day?

Cheers
Nick

[hellswraith]

Do you have access to both machines? Can you dictate what is going to be used?

Last, and most important, how much data is being moved?

I have found, for simple data transfers (and small amounts, under 200 records), it works fine and is easy to program against.

When you start pushing more data it gets to be a little slow. This is because the dataset is transfered as XML. So, first, you are doubling your data transfer. Next, you are encoding it to xml, then reconstructing the object from xml.

Like I said though, for small datasets, it works great. It all depends on what you are doing.

Another example:
I have a (highly unoptimized system, lets just get that out of the way now) web service I call for report data. First I call the main function. It returns a dataset of the main report data. Now, for each of those records in the datatable returned, I call another function of the webservice to return detail records. When I have 30 or more records in the main dataset returned, I have a total of 31 calls to make to the web service. This is where I see a huge delay. It takes so long for the round trips. To optimize this, I am going to move the calculations over to the web service side, and only pass the data back when it is completely compiled together.

Lesson learned: don't make needless calls back and forth to the web services. Try to move more processing to the web service side if you find yourself making multiple calls in one of your functions of your application.

Those are just my random thoughts on the issue. Hope they help you out. Another thing to look at if you don't want web services is Remoting.

Good luck.

[nswan]

The webservice is remote, and not developed by me. I just have to access it. I'm gonna be requesting changes to data for example every hour or so the data passed back shouldn't be too large.

One other thing, they are passing the results of the webservice call back as a zipped up file. Is this possible with a webservice????