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 with 2003 virtual server and ASP.NET 2.0 Application

I'm using Sql server 2005 Express.

my connection string is as fallows:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ccinfo.mdf;Integrated Security=True;User Instance=True

When testing this website on my local computer everything works fine. But then I uploaded it to my server... dun dun dun....

Its a godaddy virtual server running windows server 2003.

When I try to connect to the database stored in my app_data folder I get this error message:

'Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. '

What does this mean?

There was a similar discussion on a different forum (SQL Server Database Engine) that hopefully contains enough information to help you on this particular scenario. You can find it at: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1.

Please, take a look to this link, and if the information posted there doesn’t help you, please let us know.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server engine

|||

I figured it out.

the problem was I needed to detach my mdf file from my local sql server before I uploaded the database to the host server. thanks anyway

|||

I was wondering if you could provide any more information about what was causing the problem, and how detaching the mdf files fixed it?

I'm having the exact same problem on the exact same server setup described in the original post. I have already read the other threads mentioned above and tried detaching the mdf files, but nothing seems to work.

I was able to find a work around that gets past the error, but that introduces its own strange issues. Basically, if I go into IIS Manager and set the website to impersonate a user with sufficient access rights, it works -- BUT, only if I first login to the server as that user (via remote desktop, since it's a remote virtual dedicated server). I have to do this every few hours (login as the impersonated user, that is) or the app goes back to giving the same error:

"Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed."

I can't figure out what logging in accomplishes -- unless somehow that lets ASP .NET or SQL Express retrieve the user's local app path? I don't need to start or run the app from the user's account -- just login to it.

The other thing I don't understand is if user instances in SQL Server Express Edition cause the system DBs to be copied into the user's local application path (which seems to be related to this problem), where do the default NETWORK SERVICE or ASPNET accounts have the files copied? I know that ASP .NET uses the Default User when no local app path exists, but I can't confirm what SQL Server Express does. I just know that giving "Everyone" write access to the SQL Express directory under Default User does not help.

If anyone can shed any light onto this situation, I'd really appreciate it. I've spent the last few days reading through everything I can find related to this issue, but can't find an explanation. I realize that not using user instances would eliminate the problem, but this should work, and I'd really like to figure out what's going on here.

|||

User instances (RANU) is a feature that was originally designed for interactive users and it requires a local profile. Typically the profile is created when a user interactively logs in a machine.

The error message suggests that the user you are impersonating in your web application has not login to this server; and from your description (things work after you login in the machine), I think this is the case indeed.

I assume you are trying to use the attach database feature in RANU, but probably a different approach may work for your scenario. For example, create the user instance with attach database using a fixed (low privileged) Windows user for your application, and allow other users to connect directly via the instance named pipe (you can find the pipe name in the management view sys.dm_os_child_instances).

The main idea behind RANU is to allow any usre in the machine to run their own (isolated) copy of SQL Server as an application running under their own context. A copy of all default databases (including master) is copied to the application path. These files are not copied from the parent instance, instead, they are copied from the SQL Express “Template Data” folder. The only exception to this rule is resource database, a read-only system database that is shared across all instances.

I hope this information helped to answer your questions, and please, let us know if you have other questions.

-Raul Garcia

SDE/T

SQL Server Engine