Hi,
I use a Remote Sql Server Express instance, and I have a strange behavior..
The first connection is really slow and I don't know how to fix that.
I read some posts about this topic but I didn't find the right solution.
Is there a way to "keep alive" the connection between my IIS server and the SQL one ?
I check the auto-close property and it sets to false.
Any help ?
Stan
I have a SQL Server 2005 Std. Ed. 64-bit installation. There is one instance supporting a single production database. I have a CLR udf. This udf uses the XMLDocument object to retrieve XML from a URL. When the CLR udf is executed, there seems to be an initial slow response time. Subsequent response times are very fast. If the CLR udf is not called for a few minutes and then called, the slowdown appears again.
Is there something happening behind the scenes with compilation or something like that which could cause this slowdown?
Any guidance is appreciated.
Thanks in advance.
I've just moved a website/database application from windows server 2000 and sql server 2000 to windows server 2003 sp2 and SQL 2005 Express SP2. Database intensive pages now take about 40 seconds where before they took 2-3 seconds.
Things I've tried that haven't helped...
- I changed the ADODB connection string
from: sCnString = "dsn=mydsn;Database=mydatabase;uid=myuid;pwd=mypassword"
to: sCnString = "driver={SQL Native Client};server=myserverSQLEXPRESS;Database=mydatabase;uid=myuid;pwd=mypassword"
- Checked that autoclose is false.
What else can I look at?
Hi,
In a .net application there is a link that brings up a SSRS report.
I have noticed that if it is the first time this report is requested i.e. Application has just been opened and the report button is clicked, then it takes a while to get this report to appear on the screen. But if this report is requested again (i.e. for the second time or more) then it only takes a few moments for the report to appear on the screen.
So it seems that only the first time the report is requested it takes a longer time to get this report. Is there a way to reduce this initial load of the report?
Thanks
Each day, the first user who launches our RS reports always gets a long wait time. Subsequent report launches are normal.
Does anyone know what is going on? If yes, what is the remedy?
Thanks
After installing SQL Server Express I can now connect using my Windows authentication but am unable to create a database because all BUILTINUsers only have public permissions. I don't have the sa password and there are no other users or user groups set up which I could use to upgrade my permissions. So I can't do much of anything with SQL Server.
For background, I'm setting this up on a personal laptop running Windows Vista. I'm one of only two users on the machine and I have an Administrator user account.
Any help is appreciated.
Hi there, I am in the process or deploying our existing .NET 1.1 application together with its SQL Server 2000 databases to 2 new hardware platforms (one for databases and one for application). I have transferred the databases and installed the application (using our installers). I have tested the database logins and checked the database user rights - everything is fine - or so it appears to be. However, when I try to open the application it receive the following exception
[Exception: Data Source=172.16.7.1;Persist Security Info=False;UID=myAccount; password=myPassword;Connect Timeout=60;;Initial Catalog=http://xxx.xx.x.x/directory/myPage.aspxMYDATABASE;Pooling = 'false';Application Name=WEBAPP; System.Data.SqlClient.SqlException: Cannot open database requested in login 'http://xxx.xx.x.x/directory/myPage.aspxMYDATABASE'. Login fails.
Login failed for user 'myAccount'.
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection, SqlConnectionString connectionOptions)
at System.Data.SqlClient.SqlConnection.Open()
at FrameWork.DataServices.DBConnect.GetInternalConnection(Int32& icnt)]
FrameWork.DataServices.DBConnect.GetInternalConnection(Int32& icnt) +655
FrameWork.DataServices.DBConnect.GetInternalConnection() +16
FrameWork.Components.DBComponent.get_m_SqlConnection() +59
FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd, Int32 iRetry, Boolean bIgnorecase) +53
FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd, Boolean bIgnorecase) +337
FrameWork.Components.DBComponent.ExecuteReader(SqlCommand cmd) +7
It appears as if my ADO connection string is faulty. If a manually connect using a connection string in the format I expected it to be, everything works. Here are my manual connection string:
Data Source=xxx.xx.x.x;Persist Security Info=False;UID=myAccount; password=myPassword;Connect Timeout=60;;Initial Catalog=MYDATABASE;Pooling = 'false' What is inserting the URL that I used to open the page ahead of my Initial Catalog parameter?Any assistance will be more than welcome.CJ
We are having some unusal problems with an upgrade to SQL Server 2005. I've come across two other people who have had this type of issue but I haven't found a resolution. We are running SQL Server 2000 in production and we have installed SQL Server 2005 on new hardware. We have been working for about a week to verify that all of out existing applications will work properly with SQL 2005.
We have several old VB applications that open a connection to the database and reuse it for days on end. We can test these apps against the production 2000 server and they run fine, when they connect to the 2005 server the connections work fine for 5 - 15 minutes then we get an error trying to execute a command and are told that the connection doesn't exist. (One interesting note, there is only one line of code in the whole program that connects to the database but we have seen 3+ connections from the app when we check in Management Studio for SQL 2005.)
Just to get though the tests we put in some error handling to reconnect if the connection is lost. When we do this the application ends up re-connecting only once, the new connection never has a failure even after hours of testing and we see a 300% performance improvement.
We can reliably cause this problem by stopping the sql server, starting it, waiting a few minutes, then running our app. After one run of the app the problem seems to go away for a while and we can't re-create it nearly as consistantly. Only occasionally will the initial connection fail and every time it does it will be after 5-10 minutes of activity.
Both machines are connected to the same switch and we have ruled out networking issues. We are only seeing this problem with VB6 apps, the VS2005 apps run great. We are looking for the cause of this connection behavior and a fix for it because we don't have the time to fix code in dozens of VB apps because the connections are not stable. Granted the practice of holding a connection open isn't good to begin with but with legacy code you have to work with what you're dealt.
Any thoughts as to why ADO connections from VB6 to SQL 2005 would not be stable?
Hi!
I have a SQLServer 2005 running om a 64bit cluster. It is used to run a SharePoint 2007 portal. My problem is that the access to the database from the other servers in the farm is very slow.
I made a test program in C# that creates a standard .Net sqlConnection and runs a simple query 100 times. When run on the database server it takes less than a second, but when I run it from the web frontend server it takes 30 seconds. It never fails, it's just slow. The network connection is fast when copying files etc.
I see nothing out of the ordinary in the event log.
Do any of you have an idea what might be the problem or know how I could try to find the problem?
Thanks!
I have a linked tables from access 2003 to sql server 2005 connected using odbc.
the connection is very slow.
when i use the same linked tables to sql server 2000 the connection is fine.
what seems to be the problem???
We have installed SQL 7.0 Client to connect to the SQL server 7.0. After installing the client (which updated all ODBC drivers) the database connections, even ODBC calls using MS access drivers become extremely slow. Any hints where to look?
View 3 Replies View RelatedHi,
I have got an application running which connects 2 databases and performs an action that copies data from one database to the other database.
When using SQL Server 2000 MSDE, it takes about 5 seconds.
When using SQL Server 2005 Express Edition it takes about 30 seconds.
For testing 2005, I use 'upgraded' databases, so same indexes and data. I tried reindexing the databases, but always get the same result.
My setup string for installing SQL Server 2005 Express is :
/qb ADDLOCAL=ALL INSTANCENAME=DBNAME SECURITYMODE=SQL SAPWD=QWERTY SQLACCOUNT="NT AUTHORITYSYSTEM"
The code (ADO) is something like :
SELECT FROM original database
-- do something with data
INSERT INTO other database
Until EOF
Any idea how to solve this ???
P.S. When perfoming a simple Query on the databases (with 1.000.000 rows result) with SQL Server Management Studio Express, I see 37 seconds for SQL Server 2000 and 33 Second for SQL Server 2005. That is more the result I expect.
-------
XPPro SP2 3Ghz 1Gb
Hello,
i want to install the SQL 2005 express Server on a HP-Netserver LH4 with 1 Xeon 500 CPU. I know, that i must have a 600MHz CPU, but i cant change the CPU. Is there one way to start the SQL server?
Thanks
Michael
I have had a problem with Enterprise Manager connecting to SQL Server. At first this problem was experienced with one particular network user... whichever PC he logged onto, Enterprise Manager took ages to connect to SQL Server and every operation was painfully slow. Creating a new Windows NT logon fixed the problem.
I now have this same problem but only on my PC. It doesn't matter which Windows NT logon I use, using Enterprise Manager is painfully slow. I've tried creating a new Windows NT profile, checking the hard drive for errors, defragmenting the disk, reinstalling Enterprise Manager etc but nothing works.
What is strange is that connections from VB applications on my PC are fast. It is only Enterprise Manager that is slow.
I am using the latest service pack for SQL Server.
I thought it could be a problem with the Enterprise Manager registry values but don't want to start messing with them!
Has anyone else experienced this problem? Any advice would be great as I can't find any help from microsoft other than installing the latest service pack for SQL Server.
Thanks
Hello,I have a Win2K3 Server with SS2005 developers edition. I am working on aWindows XP Pro workstation which has SQL Server 2000 installed as well asthe SQL Native Client. I'm using an MS Access ADP to connect to the serverand for some reason it's extremely slow, even to the point of throwing timeout errors and "can't generate SSPI context" messages. I've hit the MSwebsite and found info on the SSPI error, but none of the items thatgenerate the error apply to my situation. I've tried using the surface areamanager to change the connection to name pipes, name pipes and tcpip etc,but no luck.Is there anything I should be looking at or any known issues that wouldaffect this kind of performance?Thanks!Rick
View 2 Replies View RelatedHi,I am seeing very slow connections from some client enterprise managersto server. Even at server level it takes 1 minute or less to open adatabase. I have adjusted some of the memory for the SQL server, butthere are only about 60 Dbs on the server, totalling about 2 GBs.CPU is very low at all times, 1 GB of ram, p3 1.4Ghz. SQL 2k.I know I should refrag the disk, but is there anything else I can doto speed up SQL operation via enterprise manager--ODBC connections is fairly fast, as well as query analyzer.Adam
View 2 Replies View RelatedHi,
I've recently installed SQL Server 2005 Express on my PC (winXP).
I'm finding that SQL Server Management Studio Express runs very slow. Every simple task takes 10-60seconds (open table, creating a table, saving anything etc). I can't find an answer to this anywhere. I've read neumerous articles that people wern't online therefore it was running slow because a certificate was trying to verify in the background... but I'm online and I've disabled the certificate so it can't be that.
This is a fresh install so I can't understand why it runs so slowly! When I used to use enterprise manager with SQL Server 2000 it worked at lighting speed.
What's going on?
CHeers
Matthew
I have an application written in VB6 that creates a ADO connection using the (native SQL2005 clien)t from the client to SQLServer 2005 on Server 2003 configured as a stand alone server. The application works great on XP and has for a number of years.
Now I am attempting to deploy in Vista and using the same code the connection speed CRAWLS. it's in the magnitude of atleast 10 times slower. It eventualy works but the selects and doing a readnext against the resulting record set is at a snails pace.
What am I missing. It's has to be some sort of configuration problem somewhere.
I have an application built with Access 2003 (MDB). It is running under Windows XP without any problems. If I run it under Vista, it works technically well but I get the data very slow from the server.
Server:
Windows Server 2003 R2, SP2
SQL Server 2005, version 9.00.3054.00
Firewall: off
Client:
Access 2003, SP3
Connection strings:
ODBC;DRIVER={SQL Native Client};UID=SD_Admin;DATABASE=SDX;SERVER=MARS;PWD=xxx;
or
ODBC;DRIVER={SQL Native Client};DATABASE=SDX;Trusted_Connection=Yes;SERVER=MARS;
Windows Firewall on client: off
Onecare Firewall on client: off
How can i Fix this issue?
SQL 2012 SSIS package.I have a package connection that has the initialcatalog set in the connection string/properties page. This package connection also has an expression defined to set the initial catalog at runtime according to a passed in parameter. It works fine.
I am trying to create a second package in this same manner, but the connection does not seem to want to keep both the hardcoded initialcatalog and the expression to set it dynamically.
I can hardcode the initcatalog just fine, when when I add the expression to set it dynamically later, it clears out the initialcatalog I added.
What am I missing, why was I able to do this in the other package? I compared as much as I can think of between the two packages, all seems similar.
We find that if we deploy the OLAP database with a different name on the test server, then regardless of how we change the connection string provided to the SSIS package that processes the cube, then the package fails to connect to the database. To clarify:
In development the OLAP database is called MyOlapDB and the source database is called MySqlDB. Both are on the same machine. When the the application is built and released for test, the test team install the databases on a replica of the production environment (i.e. web app on one machine, OLAP DB on another and SQL database on yet another). They also, quite rightly, implement the new test databases so they incorporate the build version number. So, MyOlapDB123 and MySqlDB123 are both from build 123.
This is when the problems start. Regardless of how the connection string is specified in the job that processes the cube, the SSIS integration package fails with the error:
[Analysis Services Execute DDL Task] Error: Errors in the metadata manager. Either the database with the ID of 'MyOlapDB' does not exist in the server with the ID of 'OurTestServer', or the user does not have permissions to access the object.
We have tried config files and job properties, but neither work. Also, simply attempting to run the package using the DTEXECUI does not work either.
Looking inside the XML of the package, we clearly see the ConnectionManager object which has the original connection string, which is
Data Source=localhost;Initial Catalog=MyOlapDB;Provider=MSOLAP.3;Integrated Security=SSPI;Impersonation Level=Impersonate;
However, editing the initial catalog here still does not solve the problem. Searching the XML for the string MyOlapDB reveals the OLAP database name in two other places - both within the object data of the two Analysis Services Execute DDL tasks.
Anyone know how to solve this problem without having to hack the XML of the package?
I am running simple queries against test SQL Express installation and they take a very long time to return data. I have two SQL Express instances installed on colleagues' machines to which I connect for my testing and both exhibit the same problem. The setup is Windows XP SP 2 with 2 GB RAM and 3.6 GHz CPU. I am querying a table with around 7000 records and my query is simply SELECT TOP 1000 * FROM MyTable. It takes over 10 seconds to return the recordset!
I have done the research and found posts, which talk about AUTO_CLOSE option, indexes, query execution plan, etc. I have done everything those posts recommend, but performance is still terrible. All the instances have SQL 2005 SP2 applied.
I also found that the query runs fast locally on each SQL Express instance, the problem seem to happen when I am trying to pull the data over the network.
I am really not sure what else to look for.
Thank you,
Michael
Hi
We have SQL Express running very slowly on Small Business Server. Does
anyone know if there is a conflict between the two? Or any ideas on how
to solve our problem?
(Autoclose is set to
false)
Thanks
Creating a web application. Running a simple query "SELECT username FROM vwCustomer"
vwCustomer is a view built on top of an Access DB which is set up as a linked server. Within SQL Server that view responds immediately. But when I try to access it from an ASP page it takes over 20 seconds to respond.
Used the following to create the linked server:
EXEC sp_addlinkedserver 'DBName', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:databasesdatabase.mdb'
GO
EXEC sp_addlinkedsrvlogin 'DBName', FALSE, NULL, 'Admin', ''
GO
The view is simply(this works fine in SQL Server):
SELECT * FROM DBName.db.dbo.customer
The ASP page:
SELECT * FROM vwCustomer
Can anyone give me a hint as to where the bottleneck is? Or how I can figure that out using SQL Profiler or something?
Please help!
All --
Please help.
I have some questions about connection strings.
BACKGROUND...
Note that I am using the "SQL Native Client OLE DB Provider", SQL Sever 2005 Express, ASP.NET, C#.NET, and VS.NET 2005.
Note that I do not want to use the "Attach a database file" type of connection string.
Note that I am using the site http://www.ConnectionStrings.com as a reference.
Note that this "Standard security" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Uid=MyDatabaseUser;Pwd=MyDatabasePassword;" />...does not work and causes the following run-time error...A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
Note that this "Trusted connection" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Trusted_Connection=yes;" />...works but is less-than ideal because it uses a Trusted Connection rather than a Username/Password combination.
QUESTIONS...
(A) What exactly does the error in Item 4 mean?
(B) Do 4 and 5 imply that one must use a Trusted_Connection for all SQL Server 2005 Express connection strings when the database does not reside in the web application's App_Data folder or use a "Attach a database file" type of connection string?
(C) Are there any alternatives?
(D) What do you suggest?
Please advise.
Thank you.
-- Mark Kamoski
Hi all,
I have SQL Server Express and VB 2005 Express installed in a Microsoft Windows XP Pro PC that is a terminal PC in our office Network. My Network Administrator has granted my terminal PC for Administrator Use. I tried to do the ADO.NET 2.0-VB 2005 programming in my terminal PC and I could not get a remote connection in the Window Form Application via Window Authorization. I do not know how to make this kind of remote connection in my Network. Please help and advise.
Thanks in advance,
Scott Chang
I have a 2GHZ cpu with 1GB of RAM. I occassionally see very slow (long) queries against a local SQL Server 2005 Express (SP2) database. The issue occurs against different SQL Queries, but all queries are rather basic select statements Perfmon shows that the SQL Server counter for the "MEMORY GRANT QUEUE WAIT Avg MS" gets extremely high (25000+ ms). Perfmon also also shows that PAGING is not occuring, and the system is not under unsual stress. The problem is not reproducible with MSDE.
Has anyone seen this issue, or have any recommendations for a next course of action?
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
here is my web.config file:<?xml version="1.0"?><!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
WindowsMicrosoft.NetFrameworkv2.xConfig
--><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings><add key="EmailFrom" value="webmaster@reaganpower.com"/>
<add key="EmailSubject" value="File Ready for Download!"/><add key="SmtpServer" value=""/>
<add key="MailUser" value=""/><add key="MailPassword" value=""/>
<add key="MailPort" value="25"/><add key="EmailFormatSelected" value="Text"/>
<add key="PageTitle" value="Send It Now!"/><add key="ShareLocalFolderPath" value="H:MIS DepartmentIntranetSendItNowFileStorage"/>
<add key="httpDownloadPath" value="http://misfs/SendItNow/ContentFiles/"/>
<!--
<add key="CurrentTheme" value="CleanBlue" />-->
<add key="CurrentTheme" value="CleanOrange"/></appSettings>
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=(local)SqlExpress;AttachDbFilename=|DataDirectory|FileShareDB.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>
</connectionStrings><system.web><!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
Visual Basic options:
Set strict="true" to disallow all data type conversions
where data loss can occur.
Set explicit="true" to force declaration of all variables.-->
<identity impersonate="false"/>
<roleManager enabled="true"/><compilation debug="true" strict="false" explicit="true">
<assemblies><add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies></compilation>
<pages><namespaces>
<clear/><add namespace="System"/>
<add namespace="System.Collections"/><add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/><add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/><add namespace="System.Web"/>
<add namespace="System.Web.Caching"/><add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/><add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/><add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/><add namespace="System.Web.UI.HtmlControls"/>
</namespaces></pages>
<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user. -->
<authentication mode="Forms"/>
<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>-->
</system.web>
</configuration>
Hello,
We have Sql Server installed on the Windows 2000 machine.
There are 2 databases that the employees access on it.
When the machine is just started, there are no problems but after some time the connections get really really slow and then eventually become impossible to connect. We did not have this problem before until we had a computer crash and then Sql server was installed on a new machine. The problems started here. We had 64mb of ram and we put in another 64mb thinking this would solve it but it did not.
On the task manager the cpu percentage stays constant at 100% and the virtual memory increases and increases very slowly until it has reached it's maximum (which is 600mb).
Any information would be greatly appreciated.
Thanks very much,
Kostas
I am running an IIS, windows NT and Sql 7.0
My asp pages load very slow whenever the database is accessed. All the database scripts are in stored procedures. The pages load very fast when they are run on personal web server. I will really appreciate any suggestion to help fix the problem
Thanks
Godwin
IMS
Here is the configuration we have at our site:
64 bit virtual server - Clustered 64-bit SQL Server 2005 Standard SP2 listening on port 1433.
32 bit virtual server - 32-bit application that connects to above SQL Server 2005 via standard OLEDB driver installed with windows (String utilized: Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=applicationdb;Data Source=ServerInstanceName).
We are experiencing a slow reponse of 45 sec to more than a minute on a simple select statement on a table having 5000 rows.
The same select statement on the Server locally in Studio returns results back in 2 seconds or less. But from Stuido on any other desktop (remote) it takes 45 secs or more. Same slow response is seen by the application which is using OLEDB.
I've looked into some of the Server parameters but didn't find any clues.
Any help is greatly appreciated.
I just found out the response time of open a connection or execute a SQL command over VPN is very slow. It takes around 150ms for each round trip. If the same program run on LAN, it takes less than 1ms. I understand that VPN may have encryption and thus have a bit delay. However, if the delay happens whethever I make a SQL call, it will be unacceptable. Is there anything that I have missed out? If the delay occurs once only, it will be still great. (I think this is the point of connection pooling. Right?) However, it's really bad if the delay occurs each time I call SQL. Please help!
View 5 Replies View RelatedI have a desktop and a laptop that connect to a SQL Server machine on our network. From the desktop when I run a particular query it takes 20 sec. to return 71000 rows. From the laptop it takes 5 seconds to return the same results.
Both machines are running Windows XP Pro Sp2. I checked with others that I work with, and their results mirror my laptop (about 5 sec.). I checked the Client Network settings, ODBC drivers, etc. All are the same between the two machines.
I have a VFP app that accesses the same server, and again similar results when running from the desktop vs. the laptop.
What could explain the difference in time (20 sec. vs. 5 sec.)?