ASP SQL Server slow connection

I just migrated the database and web pages from one machine to another
machine. The speed of the original web pages are faster than the current
one. I think there is no problem on the web pages, but maybe the setting
problem in IIS or SQL Server
I wonder if anything to do on IIS or SQL Server settings'This thread is active in other groups still.
Ray at work
"John Davis" <jrefactor@.hotmail.com> wrote in message
news:e2myQeJfDHA.2292@.TK2MSFTNGP10.phx.gbl...
> I just migrated the database and web pages from one machine to another
> machine. The speed of the original web pages are faster than the current
> one. I think there is no problem on the web pages, but maybe the setting
> problem in IIS or SQL Server
> I wonder if anything to do on IIS or SQL Server settings'
>|||Hello,
Try this KB article :
FIX: Connection to SQL Server Database Using IP Address Is Unusually
Slow
http://support.microsoft.com/?id=300420
Regards,
Paul Lynch
MCSE

ASP returning null sized field from MS SQL database

We currently have an SQL db running on a web server.
One of these fields is a large(ish) amount of text data up to 400
characters and has been cast variously as varchar, nchar and text
type to overcome a problem. The problem appears to be in retrieving
the data via ASP. I understand that ASP can handle string data of this
size so I am okay there.
When the records are retrieved from the db, the data string length =0.
I know the data is there because I have written a Delphi data manager
which interrogates the db and shows all records and their contents.
So if ASP can handle strings this size and the data is there, why do I
get a data length of zero bytes returned when I interrogate the record
set?
Whichever way I cast this field I get the same result.

I know the code is sound as it works locally through a MS SQL server
on my PS.
Anyone have this problem or know what's causing it? I have logged a
support call with my hosting company, but they haven't replied as yet
and I am stuck on an urgent project.

Any suggestions?

Cheers
GrantGrant Davis (grant@.gbdavis.com) writes:
> We currently have an SQL db running on a web server.
> One of these fields is a large(ish) amount of text data up to 400
> characters and has been cast variously as varchar, nchar and text
> type to overcome a problem. The problem appears to be in retrieving
> the data via ASP. I understand that ASP can handle string data of this
> size so I am okay there.
> When the records are retrieved from the db, the data string length =0.
> I know the data is there because I have written a Delphi data manager
> which interrogates the db and shows all records and their contents.
> So if ASP can handle strings this size and the data is there, why do I
> get a data length of zero bytes returned when I interrogate the record
> set?
> Whichever way I cast this field I get the same result.
> I know the code is sound as it works locally through a MS SQL server
> on my PS.
> Anyone have this problem or know what's causing it? I have logged a
> support call with my hosting company, but they haven't replied as yet
> and I am stuck on an urgent project.
> Any suggestions?

Yes one: Post the relevant pieces of your code. Today it is a beautiful
day here in Stockholm, the sun is shining from blue skies. Nevertheless,
I am completely in the dark about what you are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ASP error while accessing the website

Hi
I get different errors when I access the same web page....This page opens a
ADO connection....
First time --
__________________________________________________ ___
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/common/common.asp, line 65
_______________________________________________--
Another time...I got an Invalid connection error..
Can someone tell me what the problem really is...The website was working
perfectly fine until few days agao..There were no changes to the ASP code...
Thanks & Regards
Imtiaz
sql client changed from tcp\ip to named pipes ?
nic card setting set from 100mb Full to "Auto"
?
Greg Jackson
PDX, Oregon
|||Timeout occurs if you don't take network congestion (i.e. lag) into account.
Double check your connection string to see if you have given enough time for
sql connection (default is 15 sec) and sql command execution (default is 30
sec).
-oj
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
> Hi
> I get different errors when I access the same web page....This page opens
> a
> ADO connection....
> First time --
> __________________________________________________ ___
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> /common/common.asp, line 65
> _______________________________________________--
> Another time...I got an Invalid connection error..
> Can someone tell me what the problem really is...The website was working
> perfectly fine until few days agao..There were no changes to the ASP
> code...
>
> Thanks & Regards
> Imtiaz
>
|||Problem has been solved...Web Server wasn't able to ping to SQL Server
properly...
Thanks for your replies...
"oj" wrote:

> Timeout occurs if you don't take network congestion (i.e. lag) into account.
> Double check your connection string to see if you have given enough time for
> sql connection (default is 15 sec) and sql command execution (default is 30
> sec).
> --
> -oj
>
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
>
>

ASP error while accessing the website

Hi
I get different errors when I access the same web page....This page opens a
ADO connection....
First time --
________________________________________
_____________
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/common/common.asp, line 65
________________________________________
_______--
Another time...I got an Invalid connection error..
Can someone tell me what the problem really is...The website was working
perfectly fine until few days agao..There were no changes to the ASP code...
Thanks & Regards
Imtiazsql client changed from tcp\ip to named pipes ?
nic card setting set from 100mb Full to "Auto"
?
Greg Jackson
PDX, Oregon|||Timeout occurs if you don't take network congestion (i.e. lag) into account.
Double check your connection string to see if you have given enough time for
sql connection (default is 15 sec) and sql command execution (default is 30
sec).
-oj
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
> Hi
> I get different errors when I access the same web page....This page opens
> a
> ADO connection....
> First time --
> ________________________________________
_____________
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> /common/common.asp, line 65
> ________________________________________
_______--
> Another time...I got an Invalid connection error..
> Can someone tell me what the problem really is...The website was working
> perfectly fine until few days agao..There were no changes to the ASP
> code...
>
> Thanks & Regards
> Imtiaz
>|||Problem has been solved...Web Server wasn't able to ping to SQL Server
properly...
Thanks for your replies...
"oj" wrote:

> Timeout occurs if you don't take network congestion (i.e. lag) into accoun
t.
> Double check your connection string to see if you have given enough time f
or
> sql connection (default is 15 sec) and sql command execution (default is 3
0
> sec).
> --
> -oj
>
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:F757C5B8-0EC9-4A09-888E-6A7282DFC922@.microsoft.com...
>
>

ASP- Db Connection problem

Hi there,
I have a web application developed in ASP and SQL-server. One of my clients wanted the SQL DB to reisde in a separate machine than the IIS box. Also he doesn't want the SQL - DB box to be protected from public access. So only the IIS box is assiged a public IP, and the SQL box is in the same network(LAN) as of the IIS box. Now using TCP/IP can I connect to the SQL server from my ASP? or should I have to use Named pipes?

Here is the Connection string that I used:
constr="Provider=SQLOLEDB;Network Library=DBMSSOCN;SERVER=xxx;Initial Catalog=mydb;User Id=user;Password=xxxxxx;"

When I use the above, I don't know what is happening.. but my IE animated picture at the top right corner is moving(i.e its talking to the server) without any response. Can anyone of you give me a clue why this is happening?
Also I am NOT receiving any error, like 'Connection timed out' , or Script Timed out. That makes me nervous.Try creating a dsn and test the connection and/or write a vb app that uses the ado data control and use the automated connection string creator and test the connection. If that works, copy the connection string from your ado data control to your asp page.|||I like to use TCP/IP to connect, even though you hear that Name Pipe is faster. The simplest thing to do to see if you can use TCP/IP from your IIS machine is to physically logon to your IIS machine and go to DOS and enter the PING command to your SQL Server machine. Make sure you PING by hostname and not by IP address. If you get a response, perfect use TCP/IP to connect. If you don't get a response and your SQL Server machine has a static IP address, then PING by IP Address. If that is fine then I would enter my SQL Server machine into an LMHost file.

ASP Data Grid Needed

I need to display data from a sql server 2000 db on an asp web page and I need a vertical split. Does anyone have any solutions? I really can't afford to spend much/any money so freeware/shareware or coding suggestions is what I am interested in.
Thanks!What do you mean by vertical split?

Did you try GotDotNet.com?

Sorry im windows not much with asp|||Would TrueDBGrid do it for you?sql

ASP Code to Monitor Scheduled SQL Jobs via the web

Hi Guys,

I'm Hoping some one could help me out, I'm in need of creating a web interface to monitor scheduled SQL Jobs, both on sql 2000 & 2005.

I'm new to asp, Can anyone point me in the right direction.Really appreciate it.

Regards

This should get you started for SQL2005.

<%@. Page Language="VB" EnableTheming="False" %><%@. ImportNamespace="System.Data" %><%@. ImportNamespace="System.Data.SqlClient" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)If Not Page.IsPostBackThen JobHistory_GridView.DataSource = FJobHistory_GridView() JobHistory_GridView.DataBind()End If End Sub Function FJobHistory_GridView()As System.Data.IDataReaderDim SelectConnAs New SqlConnection("SQL_Server_ConnectionString_With_User_That_Can_Access_MSDB_Database")Dim SelectCmdAs New SqlCommand() SelectCmd.CommandText ="msdb.dbo.sp_help_jobactivity" SelectCmd.CommandType = CommandType.StoredProcedure SelectCmd.Connection = SelectConn SelectConn.Open()Dim dataReaderAs IDataReader = SelectCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)Return dataReaderEnd Function </script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>SQL Job History</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="JobHistory_GridView" runat="server" AutoGenerateColumns="True"> </asp:GridView> </div> </form></body></html>

|||

Thanks a mil Brad,

This will def come in handy, I'm new at this company, i've noticed that they already have a web interface set up to monitor sql jobs, at present it is setup to monitor jobs on sql 2000, I need to now get this working with sql2005, I've configured everything neccessary on the config & aspx pages. I get a connection to sql 2005, I know this because as part of the monitoring tool you are able to view proccesser info as well, I can view this information of the the sql 2005 server however it does not display the jobs. (Just Blank)

Any idea why this might be? I could post the code if that would help.

Thanks again for your assistance.

BR

|||

Try this for the gridview instead

 <asp:GridView ID="JobHistory_GridView" runat="server" AutoGenerateColumns="False" DataKeyNames="session_id,job_id"> <Columns> <asp:BoundField DataField="session_id" HeaderText="session_id"ReadOnly="True" /> <asp:BoundField DataField="job_id" HeaderText="job_id"ReadOnly="True" /> <asp:BoundField DataField="job_name" HeaderText="job_name" /> <asp:BoundField DataField="run_requested_date" HeaderText="run_requested_date" /> <asp:BoundField DataField="run_requested_source" HeaderText="run_requested_source" /> <asp:BoundField DataField="queued_date" HeaderText="queued_date" /> <asp:BoundField DataField="start_execution_date" HeaderText="start_execution_date" /> <asp:BoundField DataField="last_executed_step_id" HeaderText="last_executed_step_id" /> <asp:BoundField DataField="last_executed_step_date" HeaderText="last_executed_step_date" /> <asp:BoundField DataField="stop_execution_date" HeaderText="stop_execution_date" /> <asp:BoundField DataField="next_scheduled_run_date" HeaderText="next_scheduled_run_date" /> <asp:BoundField DataField="job_history_id" HeaderText="job_history_id" /> <asp:BoundField DataField="message" HeaderText="message" /> <asp:BoundField DataField="run_status" HeaderText="run_status" /> <asp:BoundField DataField="operator_id_emailed" HeaderText="operator_id_emailed" /> <asp:BoundField DataField="operator_id_netsent" HeaderText="operator_id_netsent" /> <asp:BoundField DataField="operator_id_paged" HeaderText="operator_id_paged" /> </Columns> </asp:GridView>

If that doesnt work, post your code.

ASP cannot run stored proc until the web user has run the proc in Query Analyzer

I have an ASP that has been working fine for several months, but it
suddenly broke. I wonder if windows update has installed some security
patch that is causing it.

The problem is that I am calling a stored procedure via an ASP
(classic, not .NET) , but nothing happens. The procedure doesn't work,
and I don't get any error messages.

I've tried dropping and re-creating the user and permissions, to no
avail. If it was a permissions problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.

I finally got it to work again, but this is not a viable solution for
our production environment:

1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.

After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.central_scrutinizer,

Does the ASP page hang, timeout or just return with no results? If it hangs
or times out, this may be happening:

1. The command is executed from the ASP page.
2. SQL Server needs to allocate more disk space (data or log) and performs
an autogrow of a large amount of disk space. This could take an extended
period of time.
3. The user cancels the ASP page or the page times out.
4. SQL Server cancels the transaction and also cancels the need for
additional disk space.
5. If steps 1-4 are repeated, you get the same result.
6. A user runs the stored procedure in QA allowing it to finish.
7. SQL Server allocates the addional space.
8. The ASP page now runs fine because the space has been allocated (until
the next time the database needs to allocate more space).

-- Bill

1. We would run the stored
"central_scrutinizer" <cbellur@.hotmail.comwrote in message
news:1172185833.914945.318670@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

>I have an ASP that has been working fine for several months, but it
suddenly broke. I wonder if windows update has installed some security
patch that is causing it.
>
The problem is that I am calling a stored procedure via an ASP
(classic, not .NET) , but nothing happens. The procedure doesn't work,
and I don't get any error messages.
>
I've tried dropping and re-creating the user and permissions, to no
avail. If it was a permissions problem, there would be an error
message. I trace the calls in Profiler, and it has no complaints. The
database is getting the stored proc call.
>
I finally got it to work again, but this is not a viable solution for
our production environment:
>
1. response.write the SQL call to the stored procedure from the ASP
and copy the text to the clipboard.
2. log in to QueryAnalyzer using the same user as used by the ASP.
3. paste and run the SQL call to the stored proc in query analyzer.
>
After I have done this, it not only works in Query Analyzer, but then
the ASP works too. It continues to work, even after I reboot the
machine. This is truly bizzare and has us stumped. My hunch is that
windows update installed something that has created this issue, but I
have not been able to track it down.
>

ASP Authentication with SQL Server 2005 - not express, permanent change

Hi all,

I've read up on the fact that if you use SQL server 2005 instead of SQL express you need to change the connection string in web.config.

Is there anyway to make this default connection type, or do I have to do it for every website I create. i.e. is there a global file that this connection can be entered into. Then whenever I start a new website I don't have to put the string in.

Just me being lazy really. But it's nice to know if it's possible.

Many thanks

DrB2k2

Hi,

I did it also on my dev machine. Just change the setting of the connectionstring for the LocalSqlServer in the machine.config. Make sure to make a backup, just in case, of this file first. You can find the machine.config file in this folder:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG

Grz, Kris.

|||

Lovely I'll give it a go,thanks

DrB2k2

sql

ASP and MSDE Connection Problems

Hello
I have a simple MSDE database set up on this local machine. I have a small
asp.net web page that tries to query the database and returns a list from
one of the columns. Nothing fancy, just trying to set it all up and get it
all working.
I get an error on the page when I run it.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'BBAISLEY\ASPNET'.
I assume this means that the user account isn't set up right, but I am not
telling it to use this account. I have the following connection string:
Dim connectionString As String = "server='BBAISLEY\MSDEBBAISLEY';
trusted_connection=true; database='music_cds'; user id=SQL;
password=xxxxxxxxxx;"
Can someone help me get this working? I would like to make a simple account
for all of my apps to use, don't really care what its called for now.
When I log in, I can only use the windows authencation method, so maybe my
MSDE instance isn't set for SQL passwords? How can I check this?
Thanks
Brett
You are using ASP.NET?
This is a common error as the applications default to using the ASPNET
account.
You have two choices:
1. Give access to the ASPNET account. (I dont think you want to do this
though).
2. Modify the AUTHENTICATION heading in the web.config file, & also the
virtual directory/site/directory property to only allow windows
authenticated users into the folder:
Under the AUTHENTICATION heading add:
<identity impersonate="true" />
Modify IIS to only allow windows authenticated users through...
Cheers,
James Goodman
"Brett Baisley" <baisley@.hotmail.com.REMOVETHIS> wrote in message
news:e$TLLgdLEHA.3292@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have a simple MSDE database set up on this local machine. I have a small
> asp.net web page that tries to query the database and returns a list from
> one of the columns. Nothing fancy, just trying to set it all up and get it
> all working.
> I get an error on the page when I run it.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user 'BBAISLEY\ASPNET'.
> I assume this means that the user account isn't set up right, but I am not
> telling it to use this account. I have the following connection string:
> Dim connectionString As String = "server='BBAISLEY\MSDEBBAISLEY';
> trusted_connection=true; database='music_cds'; user id=SQL;
> password=xxxxxxxxxx;"
> Can someone help me get this working? I would like to make a simple
account
> for all of my apps to use, don't really care what its called for now.
> When I log in, I can only use the windows authencation method, so maybe my
> MSDE instance isn't set for SQL passwords? How can I check this?
> Thanks
> Brett
>
|||Whoops, I misread!
It looks like you do want to use the first suggestion I made.
By default, msde is only setup for Windows Authentication, rather than sql
authentication.
See this link for information on how to modify this:
http://support.microsoft.com/default...en-us%3B319930
http://support.microsoft.com/default...n-us%3BQ322336
Cheers,
James Goodman
"Brett Baisley" <baisley@.hotmail.com.REMOVETHIS> wrote in message
news:e$TLLgdLEHA.3292@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have a simple MSDE database set up on this local machine. I have a small
> asp.net web page that tries to query the database and returns a list from
> one of the columns. Nothing fancy, just trying to set it all up and get it
> all working.
> I get an error on the page when I run it.
> Exception Details: System.Data.SqlClient.SqlException: Login failed for
> user 'BBAISLEY\ASPNET'.
> I assume this means that the user account isn't set up right, but I am not
> telling it to use this account. I have the following connection string:
> Dim connectionString As String = "server='BBAISLEY\MSDEBBAISLEY';
> trusted_connection=true; database='music_cds'; user id=SQL;
> password=xxxxxxxxxx;"
> Can someone help me get this working? I would like to make a simple
account
> for all of my apps to use, don't really care what its called for now.
> When I log in, I can only use the windows authencation method, so maybe my
> MSDE instance isn't set for SQL passwords? How can I check this?
> Thanks
> Brett
>
|||Ahh, that fixed it up. Thanks James
"James" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:e6GypgfLEHA.1644@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Whoops, I misread!
> It looks like you do want to use the first suggestion I made.
> By default, msde is only setup for Windows Authentication, rather than sql
> authentication.
> See this link for information on how to modify this:
> http://support.microsoft.com/default...en-us%3B319930
> http://support.microsoft.com/default...n-us%3BQ322336
>
> --
> Cheers,
> James Goodman
> "Brett Baisley" <baisley@.hotmail.com.REMOVETHIS> wrote in message
> news:e$TLLgdLEHA.3292@.TK2MSFTNGP11.phx.gbl...
small[vbcol=seagreen]
from[vbcol=seagreen]
it[vbcol=seagreen]
not[vbcol=seagreen]
> account
my
>

ASP ADO SQL Time Out

I am programing an asp web page using ado/sql. My query is timing
out. I was doing just fine when I wasnt doing an inner join. I test
an instance of the query (Using the inner join) in sql query and it
ran fine, but when I did the query in the web page it times out.
Any Suggestions
thanks
Kirk"Kirk" <Kirk.Mccann@.NAV-INTERNATIONAL.com> wrote in message
news:eeff0422.0401151918.48768a14@.posting.google.c om...
> I am programing an asp web page using ado/sql. My query is timing
> out. I was doing just fine when I wasnt doing an inner join. I test
> an instance of the query (Using the inner join) in sql query and it
> ran fine, but when I did the query in the web page it times out.
> Any Suggestions
> thanks
> Kirk

You don't give much information, but if your query works fine in QA, then I
would use Profiler to verify that the ASP page is sending the same query to
the server. Profiler should also show you how long the query takes to
execute on the server, so it may help to narrow the problem down to the ASP
side or the database side.

Simon

ASP + SQL loads really slow over intranet

Hi guys,

I've created a web application using ASP together with SQL Server as
our db source, running through IIS 6 on a Winows Server 2003 platform.

This application retrieves a list of customer codes from our db, so
records returned could be as many as 2000+ for any single transaction.

The application runs fine for users from the same state. However, our
interstate colleagues have notice that it takes more than 3-4mins for
the page to load, while it only takes me < 2secs to load.

Our intranet server is located in the same state as I, so anyone from
within this state has no problems loading the page. All other states
are finding it unbearable.

I've done some debugging, and it appears to be a server factor.
I saved the page with the longest list to a local drive and opened it
locally in IE and it loads quickly.

Does anyone have any suggestions as to how to speed this application up
for our interstate users?
Any ideas would appreciated.

Thanks,
ShawnI would start by trying to identify the bottleneck - from what you've
said, it's not clear if this is an MSSQL issue. If I understand you,
both you and the other users are hitting the same IIS server, and
issuing the same queries, but they get the results slowly and you
don't. That could suggest that the delay is in serving the pages to the
remote users, not in retrieving the data from the database.

You can use the MSSQL Profiler to check the duration and execution plan
of your queries, to see if there's any difference depending on the user
requesting the data. If there is a difference, then hopefully the
execution plan will give you a clue as to where the problem is.

You might also want to post in an ASP/IIS group, if you haven't already
done so - many performance problems aren't due to only one issue, so
getting some ideas about how to investigate the other components of the
system would probably be useful.

Simon|||Hi Simon,

I did some debugging, and it took SQL Server the same about of time to
do complete the query for both local and interstate, and it's the
serving of the page that is definitely taking much longer on our
interstate servers.

Usually, most users would do a query that should return about 1000+
records, and than these records gets displayed into a table on the asp
page. The page (file size) itself is roughly around 110+kB, so it's not
exactly a BIG file to be tranferred. But then why the slow down?

Any advices/ideas on how to by-pass this problem?

Regards,
Shawn

Simon Hayes wrote:
> I would start by trying to identify the bottleneck - from what you've
> said, it's not clear if this is an MSSQL issue. If I understand you,
> both you and the other users are hitting the same IIS server, and
> issuing the same queries, but they get the results slowly and you
> don't. That could suggest that the delay is in serving the pages to
the
> remote users, not in retrieving the data from the database.
> You can use the MSSQL Profiler to check the duration and execution
plan
> of your queries, to see if there's any difference depending on the
user
> requesting the data. If there is a difference, then hopefully the
> execution plan will give you a clue as to where the problem is.
> You might also want to post in an ASP/IIS group, if you haven't
already
> done so - many performance problems aren't due to only one issue, so
> getting some ideas about how to investigate the other components of
the
> system would probably be useful.
> Simon|||"Shawn H" <ShawnStyler@.gmail.com> wrote in message
news:1112975388.441101.9010@.z14g2000cwz.googlegrou ps.com...
> Hi Simon,
> I did some debugging, and it took SQL Server the same about of time to
> do complete the query for both local and interstate, and it's the
> serving of the page that is definitely taking much longer on our
> interstate servers.
> Usually, most users would do a query that should return about 1000+
> records, and than these records gets displayed into a table on the asp
> page. The page (file size) itself is roughly around 110+kB, so it's not
> exactly a BIG file to be tranferred. But then why the slow down?
> Any advices/ideas on how to by-pass this problem?
> Regards,
> Shawn

<snip
No idea, unfortunately. Since the problem seems to be serving the pages,
you'll probably get better assistance in an IIS/ASP group.

Simon

ASP + SQL Loads Really Slow Over Intranet

Hi guys,
I've created a web application using ASP together with SQL Server as
our db source, running through IIS 6 on a Winows Server 2003 platform.
This application retrieves a list of customer codes from our db, so
records returned could be as many as 2000+ for any single transaction.
The application runs fine for users from the same state. However, our
interstate colleagues have notice that it takes more than 3-4mins for
the page to load, while it only takes me < 2secs to load.
Our intranet server is located in the same state as I, so anyone from
within this state has no problems loading the page. All other states
are finding it unbearable.
I've done some debugging, and it appears to be a server factor.
I saved the page with the longest list to a local drive and opened it
locally in IE and it loads quickly.
Does anyone have any suggestions as to how to speed this application up
for our interstate users?
Any ideas would appreciated.
Thanks,
ShawnHi
Returning 2000+ customer codes and putting it into a dropdown will make any
application unbearable. How do you find what you are looking for with a drop
down like that?
How are you retrieving the codes, a select * from ...or a select CustomerID
from ...?
Make sure you are not doing any data binding.
If the application is fast in the local state, it means that SQL Server does
all it's work in under 2 seconds, no matter what state, and then pushing the
resultant web page over the wire is your performance bottleneck. How big is
the resultant page? Anything more than 100Kb is generally considered bad.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Shawn H" <ShawnStyler@.gmail.com> wrote in message
news:1112934431.434915.93490@.o13g2000cwo.googlegroups.com...
> Hi guys,
>
> I've created a web application using ASP together with SQL Server as
> our db source, running through IIS 6 on a Winows Server 2003 platform.
>
> This application retrieves a list of customer codes from our db, so
> records returned could be as many as 2000+ for any single transaction.
>
> The application runs fine for users from the same state. However, our
> interstate colleagues have notice that it takes more than 3-4mins for
> the page to load, while it only takes me < 2secs to load.
>
> Our intranet server is located in the same state as I, so anyone from
> within this state has no problems loading the page. All other states
> are finding it unbearable.
>
> I've done some debugging, and it appears to be a server factor.
> I saved the page with the longest list to a local drive and opened it
> locally in IE and it loads quickly.
>
> Does anyone have any suggestions as to how to speed this application up
> for our interstate users?
> Any ideas would appreciated.
>
> Thanks,
> Shawn
>|||Hi Mike,
I'm not sure where you got the idea that I'm putting all the records
into a drop-down list, coz all I'm doing is just puting it into a
table.
I forgot to mention that to retrieve the records, I call a stored
procedure that does this:
"select fldCustCode, fldCustName (etc..)
from tbCust
where fldCustCode = @.custcode"
I think you might be right about the bottleneck being it has to send
the page back over the network.
I did a quick check, and the file size is just a little over 110+kB.
Is there anyway I can get around this?
Somehow I can reduce/compress the file size while it gets sent over the
network? (if thats even possible)
Regards,
Shawn
Mike Epprecht (SQL MVP) wrote:
> Hi
> Returning 2000+ customer codes and putting it into a dropdown will
make any
> application unbearable. How do you find what you are looking for with
a drop
> down like that?
> How are you retrieving the codes, a select * from ...or a select
CustomerID
> from ...?
> Make sure you are not doing any data binding.
> If the application is fast in the local state, it means that SQL
Server does
> all it's work in under 2 seconds, no matter what state, and then
pushing the
> resultant web page over the wire is your performance bottleneck. How
big is
> the resultant page? Anything more than 100Kb is generally considered
bad.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Shawn H" <ShawnStyler@.gmail.com> wrote in message
> news:1112934431.434915.93490@.o13g2000cwo.googlegroups.com...
as
platform.
transaction.
our
for
from
states
it
application up|||Shawn wrote on 7 Apr 2005 23:56:13 -0700:

> Hi Mike,
> I'm not sure where you got the idea that I'm putting all the records
> into a drop-down list, coz all I'm doing is just puting it into a
> table.
> I forgot to mention that to retrieve the records, I call a stored
> procedure that does this:
> "select fldCustCode, fldCustName (etc..)
> from tbCust
> where fldCustCode = @.custcode"
> I think you might be right about the bottleneck being it has to send
> the page back over the network.
> I did a quick check, and the file size is just a little over 110+kB.
Large tables are really noticeable when the bottleneck is the connection -
until the end table tag is received IE won't show anything. If you remove
the table tags for testing and just show plain text, you'll probably find
those users start seeing data almost immediately. Still, 110kB isn't much -
are you sure it's not 1.1MB? I've got a table displayed in my browser right
now with only a menu system, 6 columns (and mostly numbers in each of
those), and for around 100 rows that's 160kB. This page shows up on my home
PC in around 4-5 secs over a 512kbps ADSL line. I guess if your users have
dialup then it might take a few mins, but anything faster shouldn't.

> Is there anyway I can get around this?
> Somehow I can reduce/compress the file size while it gets sent over the
> network? (if thats even possible)
If you don't need all the data to be seen at once is to use paging - show
say 100 per page, with clickable page numbers to get other records.
If the data doesn't need to look nice you could look at pre tags and fixed
width spacing of lines.
If both ends support gzip compression then it's possible I guess.
Dan|||Hi Daniel,
The file is definitely 110+kB and not 1.1Mb, I wouldn't miss such a big
difference.
The asp page itself is pretty simple, it doesn't contain any menu(s) or
any images, it only contains two buttons and a table consisting of 8
columns and over 1000+ rows (each with some short text and small
numbers).
I still gotta find out how fast our interstate connections are, but
it's definitely not dial-up.
I'll keep trying ... but keep the suggestions coming!
Regards,
Shawn
Daniel Crichton wrote:
> Shawn wrote on 7 Apr 2005 23:56:13 -0700:
>
records
send
110+kB.
> Large tables are really noticeable when the bottleneck is the
connection -
> until the end table tag is received IE won't show anything. If you
remove
> the table tags for testing and just show plain text, you'll probably
find
> those users start seeing data almost immediately. Still, 110kB isn't
much -
> are you sure it's not 1.1MB? I've got a table displayed in my browser
right
> now with only a menu system, 6 columns (and mostly numbers in each of

> those), and for around 100 rows that's 160kB. This page shows up on
my home
> PC in around 4-5 secs over a 512kbps ADSL line. I guess if your users
have
> dialup then it might take a few mins, but anything faster shouldn't.
>
the
>
> If you don't need all the data to be seen at once is to use paging -
show
> say 100 per page, with clickable page numbers to get other records.
> If the data doesn't need to look nice you could look at pre tags and
fixed
> width spacing of lines.
> If both ends support gzip compression then it's possible I guess.
> Dan|||Shawn wrote on 8 Apr 2005 09:00:24 -0700:

> The file is definitely 110+kB and not 1.1Mb, I wouldn't miss such a big
> difference.
> The asp page itself is pretty simple, it doesn't contain any menu(s) or
> any images, it only contains two buttons and a table consisting of 8
> columns and over 1000+ rows (each with some short text and small
> numbers).
OK, I was just wondering as my HTML is quite large (I guess due to using
links and having a number of columns with around 50 characters each per
row). A basic table with 1000 rows, 8 columns, and 1 character per column is
around 90kB.

> I still gotta find out how fast our interstate connections are, but
> it's definitely not dial-up.
> I'll keep trying ... but keep the suggestions coming!
As your earlier posts state that the page comes up immediately for yourself
for the same data, then the bottleneck has to be something that's between
the other states and your server that your PC doesn't go through. Could be a
router or firewall somewhere in the path that's overloaded and is passing
data really slowly. Have you tried a traceroute from some of these locations
to your server to see what the response times are like from each router in
the path?
Dan|||some other things you might want to take a look at a well...
How often is the clients cache cleared?
Stylesheets...how big are the stylesheets being used?
Scripts .... are they set to "DEFER" correctly?
Message posted via http://www.webservertalk.com

ASP .NET Web Site Administration Tool Problem

Hi,

I have a problem when I use ASP .NET Web Application Administration tool. When I click the Security tab, I receive following message

"There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem:Unable to connect to SQL Server database."

In the same page, there is a button to go to Provider section. I clicked it, and I went to the Provider page. There, there were two links, which were: "Select a single provider for all site management data" and
"Select a different provider for each feature (advanced)". I clicked the earlier, and I was redirected to a page to select provider. There, there was a link, I clicked it and I receive following message:

"

Provider ManagementCould not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

"

After reading this message, I opened Command Prompt and went to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. There, I ran the aspnet_regsql command, and then ASP .NET SQL Server Setup Wizard was displayed. In the first page of this wizard I chose "Configure SQL Server for application services" and then I clicked "Next" button. In the second page I left all fields as they were and then I clicke"Next". In the third page I only clicked "Next" and then some process ran. Finally, in the last page I received following message:

"

Setup failed.

Exception:
Unable to connect to SQL Server database.

------------
Details of failure
------------

System.Web.HttpException: Unable to connect to SQL Server database. --> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
-- End of inner exception stack trace --
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install)
at System.Web.Management.SqlServices.Install(String database, SqlFeatures features, String connectionString)
at System.Web.Management.ConfirmPanel.Execute()
"

What should I do? I tried to use this administration tool on computer in my house and everything was just fine. Why do I have this problem in my office?

Hi,

It seems that you are not providing correct information about your SQL Server database on second page of that wizard. It should be properly pointing to the database which would be used by your application. When you have properly pointed it to that, it will then create some default tables and objects for you to use and enable this feature in your application.

|||

Yes, you are right! I made a mistake in step 2 of ASP .NET SQL Server Setup Wizard. Recently, I successfully went through all the steps. However, I still can't open the Security tab in ASP .NET Web Site Administration Tool. When I open the Provider section, I still receive the message below:

"

Provider ManagementCould not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

"

What should I do? I need to create a login functionality for my site in near future. Please help me...

|||

Hi haris101,

There, there were two links, which were: "Select a single provider for all site management data" and
"Select a different provider for each feature (advanced)". I clicked the earlier, and I was redirected to a page to select provider.

Open your machine.config and search for "localsqlserver" connection string. The default is configured to use sql server express database. So, if you are not using sql express, you need to modify this "localsqlserver" connection string or specify a new connection string for the default provider.

I suggestion you read this walk-through first:http://msdn2.microsoft.com/en-us/library/879kf95c(VS.80).aspx

Hope my suggestion helps

|||

hey haris

im having the same problem -
i can get the web site to run queries
but i cant get the website administration to connect
did you ever find out the solution??
would love to know your experience

thanks
toy

|||

I'm also having a similar problem, I tried to connect the aspnet_regsql to my sql 2005 server on the network but it keeps telling me it can't connect. and i'm not sure why that is. I already have information for my web app stored in this server so i know that it is running just fine. if anyone can help that would be great, im running the vs 2005 pro with the 2.0 framework.

Thank,

Shane S.
FDOT

|||

Bo Chen – MSFT:

Hi haris101,

There, there were two links, which were: "Select a single provider for all site management data" and
"Select a different provider for each feature (advanced)". I clicked the earlier, and I was redirected to a page to select provider.

Open your machine.config and search for "localsqlserver" connection string. The default is configured to use sql server express database. So, if you are not using sql express, you need to modify this "localsqlserver" connection string or specify a new connection string for the default provider.

I suggestion you read this walk-through first:http://msdn2.microsoft.com/en-us/library/879kf95c(VS.80).aspx

Hope my suggestion helps

Thanks Bo! You are right!

For others who experience the same problem like mine, all you have to do is modify machine.config file in ...\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG (this is the default location). Scroll down until you find

<connectionStrings> <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /></connectionStrings>

What I did is changing the connection string to "Data Source=IT-HARIS\SQLEXPRESS;Integrated Security=True" since "IT-HARIS\SQLEXPRESS" is my SQL Server name and I use windows authentication to log on to this server. I hope this works for you too.

Best regards,

Haris

ASP .Net 2.0 SQLServer MembershipProvider

Hi,

Currently I am developing a web application which takes a SQL Database file mdf as backend. I use SQL Server express edition as backend. I use SQL Membership provider as well as Role provider. All the data related to membership & the application is combined in a common database and exists as a single mdf file which I have attached through SQL Server Express Edtion.

My problem is I am able to access the application Tables & Stored procedures etc but not getting connected with the Membership methods.

Can any one help me in this aspect. The connection string what I have done for the attaching the database file is as follows

<addname="connectionstring"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirectory|JsskDb.mdf";Trusted_Connection=Yes"providerName="System.Data.SqlClient"/>

<addname="MyProviderConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|JsskDb.mdf;Integrated Security=SSPI;"providerName="System.Data.SqlClient"/>

The first one works while the later doesn't work.

Help please,

Thanks,

Uma Ramiya

Hi Ramiya,

According to the code you posted, I think the way you create your connection string is right, It can be declared in the connectionString node but not for Membership settings, it should be declared in the membership node. Here's the correct way.

<membership defaultProvider="AspNetSqlProvider">
<providers>
<add connectionStringName="connectionstring" /// the value is according to the name in your connectionString node you set
applicationName="/"
description=""
minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0"
requiresUniqueEmail="false"
enablePasswordRetrieval="false"
enablePasswordReset="false"
requiresQuestionAndAnswer="false"
passwordFormat="Hashed"
name="AspNetSqlProvider"
type="System.Web.Security.SqlMembershipProvider"/>
</providers>
</membership>

Hope this helps. Thanks.

asp - vb6 communication

Hi,
I have created vb6 app to connect to sql server 2000 db, and now i have a new requirement: to publish this app on web, but I should use an exising vb6 application that includes all buisness logic and all communication with database: quieries, stored procedures, etc.
To implement this, i create a client :HTML interface and server: asp file that receives an input from client and calls vb6 functions with parameters he received from client. My question is: how can i call from asp application a vb6 application?The best thing would be to convert your vb6 app to a COM object.

ask help for licence problem

I am using asp and crystal report to create a report on web.

However, due to the licence problem, I need to clear the user's login session info each time after they access the report.

The probelm, when user returns to other page, since the session info is clear, user needs to login again.

Can anyone help me to solve this problem? Thanks a lot!It seems problem in Session valriable. Increase the lifetime of session

Arrays and lists in SQL 2005

I am glad to announce that there is now a version of my article "Arrays and
Lists in SQL Server" for SQL 2005 available on my web site.

THe URL for the article is
http://www.sommarskog.se/arrays-in-sql-2005.html. If you are curious about
the performance numbers they are in an appendix at
http://www.sommarskog.se/arrays-in-sql-perftest.html.

The old version of the aritlce remains, as the new article covers SQL 2005
only. The old version is now at
http://www.sommarskog.se/arrays-in-sql-2000.html.

The old URL, http://www.sommarskog.se/arrays-in-sql.html leads to a page
that links to both articles.

And the reason that there are two articles is simply that SQL 2005 adds
so many new features: nvarchar(MAX), the CLR, the xml data type, CTE that
all can be used in the realm of arrays and lists.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxGreat article Erland! The performance analysis is an excellent reading by
itself! Few people would take the time to implement such a detailed test.

Thank you for sharing!

Plamen Ratchev
http://www.SQLStudio.com

Array List in WebService

Hi,

How i can get the FileNames that are stored in Web Server.

Dim strFiles As String = Server.MapPath("~/UploadedFiles/")

Dim dirinfo As New DirectoryInfo(strFiles)

dirinfo.GetFiles("*.doc")

The above Method Iam writing in Web Service. These files will display in a DataGrid in Windows Application.

Is this question for SSIS?

Thanks.

Arithmetic operation resulted in an overflow. (System.Data) - Connection error with SQL Se

We've been using SQL Server 2005 for a while as the db for our web app. Everything has been working fine, until yesterday when we started getting a "Arithmetic operation resulted in an overflow. (System.Data)" error message when trying to connect from SQL Server Management Studio or from our web app. This only happens when trying to connect remotely, although remote connections have worked for us perfectly in the past. The full error message is reproduced below. Thanks ahead of time for any help.

===================================

Cannot connect to serverName

===================================

Arithmetic operation resulted in an overflow. (System.Data)

----------
Program Location:

at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Hi sergeipetrov,

Could you please elaborate your question a little more? I'm afraid we cannot help you just from your description above. "Arithmetic Overflow" is not particularly related to sql server, it could be caused by some other reasons. And by the way, from your code stack it seems the error happends while the data connection. So, maybe some connection code of your application would be helpful. thanks