It's slow to use ServerXMLHTTP to submit a query to SQLXML virtual directory

I'm using ServerXMLHTTP object in an ASP page (web server) to submit a query to our database server (SQL Server 2000) via SQLXML virtual directory. The ServerXMLHTTP object will return me more than 5000 rows in XML format which is about 15M in size.

The problem is, it takes 1 minute for the ServerXMLHTTP object to get the response from SQLXML web service. That makes our web application not workable because it's really slow.

However, if I use XMLHTTP object instead of ServerXMLHTTP object, it only takes seconds to finish the same query. I know these 2 objects are implemented in different ways. XMLHTTP is designed for client applications and relies on URLMon, which is built upon Microsoft Win32 Internet (WinInet). ServerXMLHTTP is designed for server applications and relies on a new HTTP client stack, WinHTTP. ServerXMLHTTP offers reliability and security and is server-safe. So I'd better use ServerXMLHTTP in my web application if I know how to solve the speed issue.

Can somebody help me out? Thank you very much in advance. This problem happens recently. The program had been working for 3 years.

The url opened by ServerXMLHTTP object is like http://myserver/myvd?sql=select * from staff where gender='M' and staff_id<5000 for xml auto&root=Root

Here are some things to look at.

ServerXMLHTTP can have proxy issues. Check if the time is spent making the connection. [more]

It can take 15+ seconds to negotiate certificates. Are certificates involved? If so, are you reusing the ServerXMLHTTP object? If so does the delay occurs only on the first use of the ServerXMLHTTP object?

If it's possible in your scenario (not enough info for me to tell), you can use ServerXMLHTTP in asynchronous mode to increase concurrency in your app (work while you wait).

HTH!

Tim

sql

Issue while using Transfer Sql Server Objects tasks

Hi
I am trying to use Transfer SQL Server Object task to transfer various
DB objects from a Source DB to a newly created Destination DB. The
Destination DB is blank before this attempted transfer.
Now our source Db contains various SPs and views which internally
reference other SPs/Views of the same DB (source DB). For example we
have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
transfer objects task, if we try to transfer all SPs only, we get an
error saying that SP2 does not exist while creating SP1. It seems SSIS
compiles objects while transfering them. We tried to set the
DelayValidation property of the entire package as well as the task to
True, but this didnt help either.
It would be great if someone could suggest some mechanism to prevent
compilation of objects, or any other method using which we can avoid
the problem that we are facing.
Thanks
Regards,
Piyush
I would redirect any failure to a logging table or other, or even ignore
errors.
On 1st pass sp1 will fail, but sp2 should still be created, correct? Then
perform a second pass which will cause sp2 to fail because already exists,
however sp1 will compile and create fine since dependency now exists...
You will get a quicker response if you post to dts group for ssis questions.
good luck.
ChrisB MCDBA
MSSQLConsulting.com
"whirlwnd" wrote:

> Hi
> I am trying to use Transfer SQL Server Object task to transfer various
> DB objects from a Source DB to a newly created Destination DB. The
> Destination DB is blank before this attempted transfer.
> Now our source Db contains various SPs and views which internally
> reference other SPs/Views of the same DB (source DB). For example we
> have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
> transfer objects task, if we try to transfer all SPs only, we get an
> error saying that SP2 does not exist while creating SP1. It seems SSIS
> compiles objects while transfering them. We tried to set the
> DelayValidation property of the entire package as well as the task to
> True, but this didnt help either.
> It would be great if someone could suggest some mechanism to prevent
> compilation of objects, or any other method using which we can avoid
> the problem that we are facing.
> Thanks
> Regards,
> Piyush
>