Consideration on SQL Server 2005 64-bit

Hi guys, I am having a new server which carrying 64-bit processor. So SQL server 2005 64-bit is needed and this is my first time to do so. Can I know is there any consideration on having a 64 bit SQL Server 2005 ?

For example,

i ) mirroring between 64 bit principal server with 32 bit mirror server ?

ii) replication between 64 bit server with 32 bit mirror server ?

Hope able to get any advices here. THank you.

Best Regards,

Hans

The On-Disk structure of databases on 32 Bit / 64 Bit is same. The database mirroring between 32 Bit - 64 Bit or 64 Bit - 32 Bit, Replication 32-64,64-32 Bit will not have any problems if configured correctly.|||

Vishal,

Can you provide some additional information regarding "if configured correctly."?

Thanks

|||The differences between 32 and 64 bit are all internal to the SQL server engine. There is no difference between setting up replication, or anything else, on 32 bit vs 64 bit.

sqlsql

Consideration on SQL Server 2005 64-bit

Hi guys, I am having a new server which carrying 64-bit processor. So SQL server 2005 64-bit is needed and this is my first time to do so. Can I know is there any consideration on having a 64 bit SQL Server 2005 ?

For example,

i ) mirroring between 64 bit principal server with 32 bit mirror server ?

ii) replication between 64 bit server with 32 bit mirror server ?

Hope able to get any advices here. THank you.

Best Regards,

Hans

The On-Disk structure of databases on 32 Bit / 64 Bit is same. The database mirroring between 32 Bit - 64 Bit or 64 Bit - 32 Bit, Replication 32-64,64-32 Bit will not have any problems if configured correctly.|||

Vishal,

Can you provide some additional information regarding "if configured correctly."?

Thanks

|||The differences between 32 and 64 bit are all internal to the SQL server engine. There is no difference between setting up replication, or anything else, on 32 bit vs 64 bit.

Consecutive Hours Query

This will challenge you. I have a table that shows time in/ time out
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.
Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:

> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>
sqlsql

Consecutive Hours Query

This will challenge you. I have a table that shows time in/ time out
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:
> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>

Connectivity with sql server

The problem what iam facing,is while updating records from
a network system to sql server,the time taken is very high.
I would like to know how to make faster updation of record
to my sql database.
And what role does ODBC play here.and is there any log
file or temporary files created at server end,if i would
like to know the path.and does it got to do anything with
the record updation speed.
I would be a great help for me...!!What type of update are you doing? Is this a one-time thing or a
continuously needed update? What is your table structure, what does your
update statement look like? Do you have indexes on the table? What recovery
mode are you running the database in? How many users are connected to the
server when you run this update? Are there any locking/blocking issues? Lots
of questions, answer them and I'm sure someone can help.
Ray Higdon MCSE, MCDBA, CCNA
--
"ssd" <ssd_myworld@.yahoo.com.sg> wrote in message
news:0e8e01c3deff$e7afc9f0$a601280a@.phx.gbl...

quote:

---

> The problem what iam facing,is while updating records from
> a network system to sql server,the time taken is very high.
> I would like to know how to make faster updation of record
> to my sql database.
> And what role does ODBC play here.and is there any log
> file or temporary files created at server end,if i would
> like to know the path.and does it got to do anything with
> the record updation speed.
> I would be a great help for me...!!

---

|||I got one sql server connected to 5 workstations,A small
vb application which updates 10-15 fileds,submission of
each record is taking 35-40secs,we will be updating 50
records at a time span of 30-45 minutes.
Here the time taken to update each record is pain taking.
How can i over come this problem inorder to submit my
records faster.
And ODBC got to do anything at workstation end,i mean we
got to configure ODBC at workstation.sqlsql

ConnectionString nightmare

I'm having a hard time making a connection work in my ASP.Net page and would be most appreciative of any help. Here two different code snipets and the errors they generate:

ATTEMPT 1:

Dim cnWebDataUtility As SQLConnection
cnWebDataUtility = new SqlConnection("server="AVILA-4400; database=WebDataUtility; uid=AVILA-4400\ASPNET; pwd=;")
cnWebDataUtility.Open()

ERROR: System.Data.SqlClient.SqlException: Login failed for user 'AVILA-4400\ASPNET'. Reason: Not associated with a trusted SQL Server connection.

ATTEMPT 2:

Dim cnWebDataUtility As SQLConnection
cnWebDataUtility = new SqlConnection("server="AVILA-4400; Database=WebDataUtility; Integrated Security=SSPI")
cnWebDataUtility.Open()

ERROR: System.Data.SqlClient.SqlException: Login failed for user 'AVILA-4400\ASPNET'.

BACKGROUND INFO:

I recently installed SQL Server Developer Edition. During that installation it did not allow me to select anything but "Windows Login" (I am not sure of the exact wording) for authentication, I believe it was. I would expect this to mean, in terms of the connectionstring, "Integrated Security=SSPI." I have tried making this connection in Visual Studio .NET and it works, but when I copy the contents of the ConnectString property in the Properties window in VS to my ConnectionString in the code above, I get errors that it does not recognize a "provider" property, and so on.

How frustrating. If anyone can help me understand this mess, I'd be very grateful.

Thank you,

Paul.I've never seen a connection string with double-quotes around the 'server=' portion of the connection string (probably just a typo?). Nor have I ever seen the server name prepended to the 'uid'. Try:

server=AVILA-4400; database=WebDataUtility; uid=ASPNET; pwd=yourPassword;

I assume that "ASPNET" is the Database Login and you've omitted the password on purpose?

This should work.sqlsql

connections to SQL Server

Hi All,
I need to put a script together that would tell me how many connections to SQL Server there are at any given time. Which table would store this info?
Thanks.Define what you mean by connections, and which version of SQL Server you are targeting. One simplistic answer for SQL 2000 would be:SELECT Count(DISTINCT spid)
FROM master.dbo.sysprocesses
WHERE 50 < spid-PatP|||The information from the sp_who/sp_who2 stored procedures might also be useful...

Connectioni leak with MDAC 2.82

Hi, I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousends). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).

Is there a knwon bug like this? Is there a way to trace the ADO provider?are you sure you are explicitly closing your connections?|||What is your code that opens and closes connection to the server?|||

Hi, sorry for the delay, the forum alert seems not to work for me either :-)

Yes. I am closing all connections, the program leaves only about 5-10% of the connections behind, not all of them. Also, I run the same code using ODBC provider and that works absolutely fine. I my code there is a chance to leave connection behind in case of error, but than I would see error messages inmy log, which I don't. Here is the code:

_ConnectionPtr
AsyncOpen(struct s_profiles *profile)
{
time_t start;
string sConnStr;
HRESULT hr;
SYSTEMTIME st1,st2;
_ConnectionPtr pConn = NULL;
char msg[1024];
try {
start = time(NULL);
GetSystemTime(&st1); //time before connect
TESTHR(pConn.CreateInstance(__uuidof(Connection)));
pConn->ConnectionTimeout = profile->conn->timeout; //set ADO Connection Timeout (default 30sec)
pConn->CommandTimeout = profile->timeout; //set ADO Command Timeout (default 30sec)

if (pConn->State == adStateOpen) {
nimLog(1,"Profile: %s, connection: %s already opem", profile->name, profile->connection);
return pConn; //Already open
}

if (strcmp(profile->cursor,"server") == 0)
pConn->CursorLocation = adUseServer; //use server cursor
else
pConn->CursorLocation = adUseClient; //use client cursor (default)

sConnStr = CreateConnString(profile->conn);

hr = pConn->Open(_bstr_t(sConnStr.c_str()),bsNull,bsNull,adAsyncConnect);
if (FAILED(hr))
{
nimLog(0,"Profile %s, AsyncOpen - failed for connection '%s'",profile->name, profile->connection);
return pConn;
}

while (pConn->State == adStateConnecting)
{
//nimLog(0,"Profile: %s - Wait for Connection to open", profile->name);
if(time(NULL) > (start + profile->conn->timeout + gSetup->COM_tout_delay))
{
nimLog(0,"Profile %s, AsyncOpen - connection timeout",profile->name;
pConn->Cancel();
pConn = NULL;
return pConn;
}
Sleep(5);
}
nimLog(2,"Profile %s, AsyncOpen - connected to database",profile->name);

if (FAILED(hr))
{
nimLog(0,"Open - failed for connection '%s'",profile->connection);
return 0;
}
}
catch(_com_error &e ) {
nimLog(0,"Profile: %s, Error in AsyncOpen", profile->name);
LogComError(e, profile->name, 0, "Open");
return 0;
}
catch(...){
nimLog(0,"Profile: %s, Unknown error in Open", profile->name);
sprintf(msg,"Profile %s, unknown error in Open", profile->name);
return 0;
}
//return (pConn->State == adStateOpen ? 1 : 0);
return pConn;
}

//close

try
{
hr = pConn->Close();
if (FAILED(hr))
{
nimLog(0,"Close connection failed (%s:%d)",__FILE__,__LINE__);
EXIT_THREAD(0);
}
pConn = NULL;
}

Thanks for help!

|||Is Cancel method same as Close for the ADO Connection in C++? I do not know C++, but if this is the case, then code looks good. What you could do is to run SQL Profiler to trace activity between the server and your application. It could provide additional information as well|||

Close and Cancel are not exactly the same, Cancel is used to close an asynchronous request, Close to end after you're done. I am using asynchronous Open to be able to bail out if timeout reach, than I need Cancel.

To use the Profiler shows, unfortunately only half of the story. I can see what the ADO provider send towards SQL, but not what my application sends to ADO. At the end I can see every once in a whole there is a connection not closed, but no idea why - did the application not sent the close? Not to be seen in profiler. And ODBC like trace does not exeists, at least to my knowledge.

I am believing now there is something wrong with the ADO provider on that one machine, as it doens't happen on others and also it works there with ODBC. The question remains what is wrong, I cannot see anything obvious. I only observed, that the response time with ADO was considerable slower than with ODBC, althoug I would expect it the other way around.

Any other ideas out there?

|||It is possible that Cancel is not enough. Can you try to use Close to see if it helps or you cannot?|||Unfortunately, it is a production machine where it happens, and I am limited in my activities there. But on my test machine Cancel works, quite the oposite, Close doesn't work there. But to this part of code I get only in "timeout" situation, where I get a message - I didn't get any timeout at all yet on the production machine. I would say it must be something else.|||

I am wondering if your ADO connection is actually getting pooled.

Add "Pooling=false;" to your ado connection string. If the problem goes away, its not an issue, remove the keyword/value pair and carry on. If the problem persists, I am wondering if you need an analog to your _ConnectionPtr->CreateInstance that releases the instance?

|||Make sure that the connection is closed--even if the code throws an exception.|||

Hi Dbrich,

Same problem what you faced long back ago.

I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousands). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790),SP4 for SQL Server.

actually when i run the application on my machine i didn't find any connection leak but on production machine i find connection leak.

Is there a know bug like this? Is there a way to trace the ADO provider?

In my SQL Profiler I got this error number 16945,16955,208

Connectioni leak with MDAC 2.82

Hi, I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousends). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).

Is there a knwon bug like this? Is there a way to trace the ADO provider?are you sure you are explicitly closing your connections?|||What is your code that opens and closes connection to the server?|||

Hi, sorry for the delay, the forum alert seems not to work for me either :-)

Yes. I am closing all connections, the program leaves only about 5-10% of the connections behind, not all of them. Also, I run the same code using ODBC provider and that works absolutely fine. I my code there is a chance to leave connection behind in case of error, but than I would see error messages inmy log, which I don't. Here is the code:

_ConnectionPtr
AsyncOpen(struct s_profiles *profile)
{
time_t start;
string sConnStr;
HRESULT hr;
SYSTEMTIME st1,st2;
_ConnectionPtr pConn = NULL;
char msg[1024];
try {
start = time(NULL);
GetSystemTime(&st1); //time before connect
TESTHR(pConn.CreateInstance(__uuidof(Connection)));
pConn->ConnectionTimeout = profile->conn->timeout; //set ADO Connection Timeout (default 30sec)
pConn->CommandTimeout = profile->timeout; //set ADO Command Timeout (default 30sec)

if (pConn->State == adStateOpen) {
nimLog(1,"Profile: %s, connection: %s already opem", profile->name, profile->connection);
return pConn; //Already open
}

if (strcmp(profile->cursor,"server") == 0)
pConn->CursorLocation = adUseServer; //use server cursor
else
pConn->CursorLocation = adUseClient; //use client cursor (default)

sConnStr = CreateConnString(profile->conn);

hr = pConn->Open(_bstr_t(sConnStr.c_str()),bsNull,bsNull,adAsyncConnect);
if (FAILED(hr))
{
nimLog(0,"Profile %s, AsyncOpen - failed for connection '%s'",profile->name, profile->connection);
return pConn;
}

while (pConn->State == adStateConnecting)
{
//nimLog(0,"Profile: %s - Wait for Connection to open", profile->name);
if(time(NULL) > (start + profile->conn->timeout + gSetup->COM_tout_delay))
{
nimLog(0,"Profile %s, AsyncOpen - connection timeout",profile->name;
pConn->Cancel();
pConn = NULL;
return pConn;
}
Sleep(5);
}
nimLog(2,"Profile %s, AsyncOpen - connected to database",profile->name);

if (FAILED(hr))
{
nimLog(0,"Open - failed for connection '%s'",profile->connection);
return 0;
}
}
catch(_com_error &e ) {
nimLog(0,"Profile: %s, Error in AsyncOpen", profile->name);
LogComError(e, profile->name, 0, "Open");
return 0;
}
catch(...){
nimLog(0,"Profile: %s, Unknown error in Open", profile->name);
sprintf(msg,"Profile %s, unknown error in Open", profile->name);
return 0;
}
//return (pConn->State == adStateOpen ? 1 : 0);
return pConn;
}

//close

try
{
hr = pConn->Close();
if (FAILED(hr))
{
nimLog(0,"Close connection failed (%s:%d)",__FILE__,__LINE__);
EXIT_THREAD(0);
}
pConn = NULL;
}

Thanks for help!

|||Is Cancel method same as Close for the ADO Connection in C++? I do not know C++, but if this is the case, then code looks good. What you could do is to run SQL Profiler to trace activity between the server and your application. It could provide additional information as well|||

Close and Cancel are not exactly the same, Cancel is used to close an asynchronous request, Close to end after you're done. I am using asynchronous Open to be able to bail out if timeout reach, than I need Cancel.

To use the Profiler shows, unfortunately only half of the story. I can see what the ADO provider send towards SQL, but not what my application sends to ADO. At the end I can see every once in a whole there is a connection not closed, but no idea why - did the application not sent the close? Not to be seen in profiler. And ODBC like trace does not exeists, at least to my knowledge.

I am believing now there is something wrong with the ADO provider on that one machine, as it doens't happen on others and also it works there with ODBC. The question remains what is wrong, I cannot see anything obvious. I only observed, that the response time with ADO was considerable slower than with ODBC, althoug I would expect it the other way around.

Any other ideas out there?

|||It is possible that Cancel is not enough. Can you try to use Close to see if it helps or you cannot?|||Unfortunately, it is a production machine where it happens, and I am limited in my activities there. But on my test machine Cancel works, quite the oposite, Close doesn't work there. But to this part of code I get only in "timeout" situation, where I get a message - I didn't get any timeout at all yet on the production machine. I would say it must be something else.|||

I am wondering if your ADO connection is actually getting pooled.

Add "Pooling=false;" to your ado connection string. If the problem goes away, its not an issue, remove the keyword/value pair and carry on. If the problem persists, I am wondering if you need an analog to your _ConnectionPtr->CreateInstance that releases the instance?

|||Make sure that the connection is closed--even if the code throws an exception.|||

Hi Dbrich,

Same problem what you faced long back ago.

I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousands). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790),SP4 for SQL Server.

actually when i run the application on my machine i didn't find any connection leak but on production machine i find connection leak.

Is there a know bug like this? Is there a way to trace the ADO provider?

In my SQL Profiler I got this error number 16945,16955,208

ConnectionCheckForData Error on Delete

Hi,
I'm trying to delete 1.5 million records from at table containing 121
million records using a delete with a simple subquery. After some time
processing the following error message is returned in Query Analyzer:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
8.00.818).
I did some checking on the net and saw where an install of SP3a fixed a
related error message however the KB states that because most of the issues
are related to setup that SP3a need not be installed if SP3 is currently
installed.
Has anyone encountered this problem and if so how did you resolve it?
Thanks
JerryFollow up:
Ran the delete statement directly from QA on the SQL Server to remove the
network connectivity as a possible issue. Received the following error:
Server: Msg 8646, Level 21, State 1, Line 1
The index entry for row ID was not found in index ID 2, of table 1403152044,
in database 'XXX'.
Connection Broken
Referenced to KB Article 822747 - possible corrupt nonclustered index.
Dropped the NC index and the deleted now succeeded. Now rebuilding the NC
index again.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eC3SJlWXFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to delete 1.5 million records from at table containing 121
> million records using a delete with a simple subquery. After some time
> processing the following error message is returned in Query Analyzer:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
> 8.00.818).
> I did some checking on the net and saw where an install of SP3a fixed a
> related error message however the KB states that because most of the
> issues are related to setup that SP3a need not be installed if SP3 is
> currently installed.
> Has anyone encountered this problem and if so how did you resolve it?
> Thanks
> Jerry
>

Connection Time is too long

I'm using ado to connect to a sql server 2000, and suddendly the line that
does the connection (conx.open) is taking a long time. All the apps that
connecto to the server take a long time also. Additionally, when you use the
sql manager, when you retrieve the rows from a table it takes a long time. It
is not a network problem, because we have pretty good speed in our network.
It's something to do with the sql server, but I have not figure out what is
it. I cheked the current activity in the server, and doesn't have a lot of
processes running and half of them are sleeping.
any ideas how to solve this issue?
Thanks
If you connect on the actual SQL Server is there an issue? If not, then you
are looking at an application server or network issue.
"Adal" <Adal@.discussions.microsoft.com> wrote in message
news:5287E88B-270C-4A92-8FF6-2F1E0A252A64@.microsoft.com...
> I'm using ado to connect to a sql server 2000, and suddendly the line that
> does the connection (conx.open) is taking a long time. All the apps that
> connecto to the server take a long time also. Additionally, when you use
the
> sql manager, when you retrieve the rows from a table it takes a long time.
It
> is not a network problem, because we have pretty good speed in our
network.
> It's something to do with the sql server, but I have not figure out what
is
> it. I cheked the current activity in the server, and doesn't have a lot of
> processes running and half of them are sleeping.
> any ideas how to solve this issue?
> Thanks
|||Hi, I went to the SQL server, and worked directly on it, with the enterprise
manager, and it seem to me that the time to retrieve the information from
tables was still a little high. I still have to wait long time to get a
connection to sql from ado. But, as I said before, this is not all the time,
is every minute or so. I don't think it has anything to do with ado or
connection poolin, because when I work with enterprise manager and retrieve
information from tables, it sometimes takes longer to retrieve the
information, no matter how many rows does it have.
thanks

Connection Time is too long

I'm using ado to connect to a sql server 2000, and suddendly the line that
does the connection (conx.open) is taking a long time. All the apps that
connecto to the server take a long time also. Additionally, when you use the
sql manager, when you retrieve the rows from a table it takes a long time. I
t
is not a network problem, because we have pretty good speed in our network.
It's something to do with the sql server, but I have not figure out what is
it. I cheked the current activity in the server, and doesn't have a lot of
processes running and half of them are sleeping.
any ideas how to solve this issue?
ThanksIf you connect on the actual SQL Server is there an issue? If not, then you
are looking at an application server or network issue.
"Adal" <Adal@.discussions.microsoft.com> wrote in message
news:5287E88B-270C-4A92-8FF6-2F1E0A252A64@.microsoft.com...
> I'm using ado to connect to a sql server 2000, and suddendly the line that
> does the connection (conx.open) is taking a long time. All the apps that
> connecto to the server take a long time also. Additionally, when you use
the
> sql manager, when you retrieve the rows from a table it takes a long time.
It
> is not a network problem, because we have pretty good speed in our
network.
> It's something to do with the sql server, but I have not figure out what
is
> it. I cheked the current activity in the server, and doesn't have a lot of
> processes running and half of them are sleeping.
> any ideas how to solve this issue?
> Thanks|||Hi, I went to the SQL server, and worked directly on it, with the enterprise
manager, and it seem to me that the time to retrieve the information from
tables was still a little high. I still have to wait long time to get a
connection to sql from ado. But, as I said before, this is not all the time,
is every minute or so. I don't think it has anything to do with ado or
connection poolin, because when I work with enterprise manager and retrieve
information from tables, it sometimes takes longer to retrieve the
information, no matter how many rows does it have.
thanks

Connection takes a long time to open

Dear All,
An application is written in Delphi 5 using ADO to
connect to SQL Server 2000. It is installed in 30 machines
but in two of them, the program takes about 60 seconds to
startup. It is believed that it is waiting for connection.
Those two machines are running windows 2000 SP2.
Any comment?
Thanks.Always the same 2 machines?
What time frame has this problem been observed over?
"Geo" <anonymous@.discussions.microsoft.com> wrote in message
news:067901c39469$f10d6650$a401280a@.phx.gbl...
> Dear All,
> An application is written in Delphi 5 using ADO to
> connect to SQL Server 2000. It is installed in 30 machines
> but in two of them, the program takes about 60 seconds to
> startup. It is believed that it is waiting for connection.
> Those two machines are running windows 2000 SP2.
> Any comment?
> Thanks.
>
>
>

connection string with authenticated user info

Hi. New to ASP.NET and first time posting.

My web app connects to a SQL database - SQL authentication.

Users login to the web app through the login server control. Once authenticated, it is my understanding that the user name and password are stored on the client as a cookie.

How do you programmatically get this user info and use it for the userid and password parameters of the connection string?

Is there a better way to use the authenticated user info to access a SQL database?

Thanks

Once authenticated (using asp.net login control), you can get the user info programmatically using:

Page

.User.Identity ( for example,Page.User.Identity.Name returns the username )|||

Thanks for the info.

I did some reading on Page.User.Identity, but don't understand how to get the password used for login. I need both the username and password to authenticate in SQL server.

Connection refusals

Hi,
We have a 64bit SQL Server 16GB Memory running on Windows 2003 x64. This is
one of our main business database servers. At this time we have an issue
where the server will stop allowing new connections in, the client will
receive a timeout (instantly) and from .NET the stack is
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()
If the client keeps trying eventually they might get in but other connection
attempts continue to fail. This lasts for about 10 minutes usually then goes
away.
Occassionally when this happens the .NET AppDomain is unloaded and a new one
is created. However this does not happen all the time and appears to be a
side-effect not the cause.
What I really would like some help on is what performance counters might be
worth looking at.
This is an upgrade of a server with 4GB 32bit SQL Server on Win2003 32 bit
which never had these issues. No code has changed, there are 5 CLR Stored
procedures which have not changed.
Any pointers would be much appreciated.
Thanks
James WrenThis sounds suspiciously like a full connection pool. Check out my
whitepaper on managing the connection pool on my site.
http://www.betav.com/sql_server_magazine.htm
I would write an application to monitor the pool or simply setup a
performance counter view on the counters exposed by ADO.NET. I expect that
you've missed a Connection Close in an exception handler.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:50D6E4FA-173A-4751-A2EC-A9F8549C7356@.microsoft.com...
> Hi,
> We have a 64bit SQL Server 16GB Memory running on Windows 2003 x64. This
> is
> one of our main business database servers. At this time we have an issue
> where the server will stop allowing new connections in, the client will
> receive a timeout (instantly) and from .NET the stack is
> 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()
> If the client keeps trying eventually they might get in but other
> connection
> attempts continue to fail. This lasts for about 10 minutes usually then
> goes
> away.
> Occassionally when this happens the .NET AppDomain is unloaded and a new
> one
> is created. However this does not happen all the time and appears to be a
> side-effect not the cause.
> What I really would like some help on is what performance counters might
> be
> worth looking at.
> This is an upgrade of a server with 4GB 32bit SQL Server on Win2003 32 bit
> which never had these issues. No code has changed, there are 5 CLR Stored
> procedures which have not changed.
>
> Any pointers would be much appreciated.
> Thanks
> James Wren|||I agree that sounds like a probable cause, however there are two issues I am
not sure about. However in my experience filling the connection pool affects
the client not the SQL Server.
1. No code has changed at all just the build of SQL Server and the fact it
is SP2 - so this would mean the behaviour of SQL Server w.r.t the code has
changed to cause this issue now.
2. Also there is a large amount of code called from the main website,
internal software and application servers, so I'm not sure it will be very
easy at all to track down a wrongly closed connection.
3. If, say, a rogue web server component did not close connections surely
this would not effect a .NET application connecting to the SQL Server from
another machine.
Thank you for your response.
James Wren
"William (Bill) Vaughn" wrote:

> This sounds suspiciously like a full connection pool. Check out my
> whitepaper on managing the connection pool on my site.
> http://www.betav.com/sql_server_magazine.htm
> I would write an application to monitor the pool or simply setup a
> performance counter view on the counters exposed by ADO.NET. I expect that
> you've missed a Connection Close in an exception handler.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:50D6E4FA-173A-4751-A2EC-A9F8549C7356@.microsoft.com...
>
>|||The fact that the server is calling out to other code could certainly lead
to client-side connection pool issues as the application threads queue up
waiting for the server to respond. I'm assuming this is an ASP application
where this sort of behavior is most common. Remember if the client (the ASP
app) request is not handled, when another app instance is started it can't
reuse the connection but has to create another. If this continues the pool
can fill quite quickly. I would monitor the pooled connections and see if
this is not the case.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@.microsoft.com...[vbcol=seagreen]
>I agree that sounds like a probable cause, however there are two issues I
>am
> not sure about. However in my experience filling the connection pool
> affects
> the client not the SQL Server.
> 1. No code has changed at all just the build of SQL Server and the fact it
> is SP2 - so this would mean the behaviour of SQL Server w.r.t the code has
> changed to cause this issue now.
> 2. Also there is a large amount of code called from the main website,
> internal software and application servers, so I'm not sure it will be very
> easy at all to track down a wrongly closed connection.
> 3. If, say, a rogue web server component did not close connections surely
> this would not effect a .NET application connecting to the SQL Server from
> another machine.
> Thank you for your response.
> James Wren
>
> "William (Bill) Vaughn" wrote:
>|||Thank you for the follow up. As I had indicated the client does start to fai
l
to contact SQL Server but alll clients will stop at the same time indicating
a server side issue not a client side issue. SQL Server prevents new
connecitons being created. SQL Server allows unlimited connections. I can
understand a client filling its connection pool but why do all clients,
anywhere, including SQL Management Studio all fail to connect to SQL Server.
The SQL Server perf counters themselves show only 300 logical connections.
Regards
James
"William (Bill) Vaughn" wrote:

> The fact that the server is calling out to other code could certainly lead
> to client-side connection pool issues as the application threads queue up
> waiting for the server to respond. I'm assuming this is an ASP application
> where this sort of behavior is most common. Remember if the client (the AS
P
> app) request is not handled, when another app instance is started it can't
> reuse the connection but has to create another. If this continues the pool
> can fill quite quickly. I would monitor the pooled connections and see if
> this is not the case.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@.microsoft.com...
>
>|||Ok, so if it's a server issue, we need to look at what's bogging down the
server. Is it hosing Reporting Services or acting as a print server? If the
server bogs down, the ASP clients have to wait and their pool(s) grow
accordingly as new requests come in. Eventually, the server crowbars and
won't accept any more connections due to the fact it has no CPU cycles to
launch another thread--even for SSMS. I would at least open up PerfMon to
monitor what's going on AFA the CPU use and other critical counters such as
logical connections etc. The connection pools are not on the server unless
IIS (which is hosting the ASP applications) is there too. In that case, the
application code itself might be bogging down the host system.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:8E8970C6-84BB-4B05-AB71-A7E4381147EB@.microsoft.com...[vbcol=seagreen]
> Thank you for the follow up. As I had indicated the client does start to
> fail
> to contact SQL Server but alll clients will stop at the same time
> indicating
> a server side issue not a client side issue. SQL Server prevents new
> connecitons being created. SQL Server allows unlimited connections. I can
> understand a client filling its connection pool but why do all clients,
> anywhere, including SQL Management Studio all fail to connect to SQL
> Server.
> The SQL Server perf counters themselves show only 300 logical connections.
> Regards
> James
> "William (Bill) Vaughn" wrote:
>|||Thanks again for the response. The SQL Server is extremely powerful for what
we need, it is brand new and based on analysis of running the old version
(4GB 32 bit). The CPU remains very low all the time even though there are a
very large number of queries primarily from the website, however there are
numerous background services and client applications running on many other
machines.
The Logical connections never goes above 380 and when this problem happens
existing connections in Management studio work but are VERY slow in returnin
g
any results - yet the CPU is very low (typically under 105)
The problem did not happen at all today but happened 5 times in 6 hours
yesterday. Nothing else runs on the SQL Server, we do not use Reporting
Services, Analysis Services or Notification Services in SQL Server from this
machine.
Yes if the server is getting slow then the clients would back up, fill their
pools and then throw the "No connection available from pool" error but it is
not obvious in anyway that the SQL Server is getting bogged down. It has 16G
B
of RAM, the main database is 20GB but this worked fine on a 32bit 4GB machin
e.
The issue stills revolves around if code is constant then what has changed
the behaviour when using 64bit SQL Server with 16GB RAM on Service Pack 2?
Regards
James
"William (Bill) Vaughn" wrote:

> Ok, so if it's a server issue, we need to look at what's bogging down the
> server. Is it hosing Reporting Services or acting as a print server? If th
e
> server bogs down, the ASP clients have to wait and their pool(s) grow
> accordingly as new requests come in. Eventually, the server crowbars and
> won't accept any more connections due to the fact it has no CPU cycles to
> launch another thread--even for SSMS. I would at least open up PerfMon to
> monitor what's going on AFA the CPU use and other critical counters such a
s
> logical connections etc. The connection pools are not on the server unless
> IIS (which is hosting the ASP applications) is there too. In that case, th
e
> application code itself might be bogging down the host system.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:8E8970C6-84BB-4B05-AB71-A7E4381147EB@.microsoft.com...
>
>

Connection refusals

Hi,
We have a 64bit SQL Server 16GB Memory running on Windows 2003 x64. This is
one of our main business database servers. At this time we have an issue
where the server will stop allowing new connections in, the client will
receive a timeout (instantly) and from .NET the stack is
at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
If the client keeps trying eventually they might get in but other connection
attempts continue to fail. This lasts for about 10 minutes usually then goes
away.
Occassionally when this happens the .NET AppDomain is unloaded and a new one
is created. However this does not happen all the time and appears to be a
side-effect not the cause.
What I really would like some help on is what performance counters might be
worth looking at.
This is an upgrade of a server with 4GB 32bit SQL Server on Win2003 32 bit
which never had these issues. No code has changed, there are 5 CLR Stored
procedures which have not changed.
Any pointers would be much appreciated.
Thanks
James Wren
This sounds suspiciously like a full connection pool. Check out my
whitepaper on managing the connection pool on my site.
http://www.betav.com/sql_server_magazine.htm
I would write an application to monitor the pool or simply setup a
performance counter view on the counters exposed by ADO.NET. I expect that
you've missed a Connection Close in an exception handler.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:50D6E4FA-173A-4751-A2EC-A9F8549C7356@.microsoft.com...
> Hi,
> We have a 64bit SQL Server 16GB Memory running on Windows 2003 x64. This
> is
> one of our main business database servers. At this time we have an issue
> where the server will stop allowing new connections in, the client will
> receive a timeout (instantly) and from .NET the stack is
> at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
> at System.Data.SqlClient.SqlConnection.Open()
> If the client keeps trying eventually they might get in but other
> connection
> attempts continue to fail. This lasts for about 10 minutes usually then
> goes
> away.
> Occassionally when this happens the .NET AppDomain is unloaded and a new
> one
> is created. However this does not happen all the time and appears to be a
> side-effect not the cause.
> What I really would like some help on is what performance counters might
> be
> worth looking at.
> This is an upgrade of a server with 4GB 32bit SQL Server on Win2003 32 bit
> which never had these issues. No code has changed, there are 5 CLR Stored
> procedures which have not changed.
>
> Any pointers would be much appreciated.
> Thanks
> James Wren
|||I agree that sounds like a probable cause, however there are two issues I am
not sure about. However in my experience filling the connection pool affects
the client not the SQL Server.
1. No code has changed at all just the build of SQL Server and the fact it
is SP2 - so this would mean the behaviour of SQL Server w.r.t the code has
changed to cause this issue now.
2. Also there is a large amount of code called from the main website,
internal software and application servers, so I'm not sure it will be very
easy at all to track down a wrongly closed connection.
3. If, say, a rogue web server component did not close connections surely
this would not effect a .NET application connecting to the SQL Server from
another machine.
Thank you for your response.
James Wren
"William (Bill) Vaughn" wrote:

> This sounds suspiciously like a full connection pool. Check out my
> whitepaper on managing the connection pool on my site.
> http://www.betav.com/sql_server_magazine.htm
> I would write an application to monitor the pool or simply setup a
> performance counter view on the counters exposed by ADO.NET. I expect that
> you've missed a Connection Close in an exception handler.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:50D6E4FA-173A-4751-A2EC-A9F8549C7356@.microsoft.com...
>
>
|||The fact that the server is calling out to other code could certainly lead
to client-side connection pool issues as the application threads queue up
waiting for the server to respond. I'm assuming this is an ASP application
where this sort of behavior is most common. Remember if the client (the ASP
app) request is not handled, when another app instance is started it can't
reuse the connection but has to create another. If this continues the pool
can fill quite quickly. I would monitor the pooled connections and see if
this is not the case.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@.microsoft.com...[vbcol=seagreen]
>I agree that sounds like a probable cause, however there are two issues I
>am
> not sure about. However in my experience filling the connection pool
> affects
> the client not the SQL Server.
> 1. No code has changed at all just the build of SQL Server and the fact it
> is SP2 - so this would mean the behaviour of SQL Server w.r.t the code has
> changed to cause this issue now.
> 2. Also there is a large amount of code called from the main website,
> internal software and application servers, so I'm not sure it will be very
> easy at all to track down a wrongly closed connection.
> 3. If, say, a rogue web server component did not close connections surely
> this would not effect a .NET application connecting to the SQL Server from
> another machine.
> Thank you for your response.
> James Wren
>
> "William (Bill) Vaughn" wrote:
|||Thank you for the follow up. As I had indicated the client does start to fail
to contact SQL Server but alll clients will stop at the same time indicating
a server side issue not a client side issue. SQL Server prevents new
connecitons being created. SQL Server allows unlimited connections. I can
understand a client filling its connection pool but why do all clients,
anywhere, including SQL Management Studio all fail to connect to SQL Server.
The SQL Server perf counters themselves show only 300 logical connections.
Regards
James
"William (Bill) Vaughn" wrote:

> The fact that the server is calling out to other code could certainly lead
> to client-side connection pool issues as the application threads queue up
> waiting for the server to respond. I'm assuming this is an ASP application
> where this sort of behavior is most common. Remember if the client (the ASP
> app) request is not handled, when another app instance is started it can't
> reuse the connection but has to create another. If this continues the pool
> can fill quite quickly. I would monitor the pooled connections and see if
> this is not the case.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:7E6E797A-9481-4871-AF86-DFCAA043A9E9@.microsoft.com...
>
>
|||Ok, so if it's a server issue, we need to look at what's bogging down the
server. Is it hosing Reporting Services or acting as a print server? If the
server bogs down, the ASP clients have to wait and their pool(s) grow
accordingly as new requests come in. Eventually, the server crowbars and
won't accept any more connections due to the fact it has no CPU cycles to
launch another thread--even for SSMS. I would at least open up PerfMon to
monitor what's going on AFA the CPU use and other critical counters such as
logical connections etc. The connection pools are not on the server unless
IIS (which is hosting the ASP applications) is there too. In that case, the
application code itself might be bogging down the host system.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
news:8E8970C6-84BB-4B05-AB71-A7E4381147EB@.microsoft.com...[vbcol=seagreen]
> Thank you for the follow up. As I had indicated the client does start to
> fail
> to contact SQL Server but alll clients will stop at the same time
> indicating
> a server side issue not a client side issue. SQL Server prevents new
> connecitons being created. SQL Server allows unlimited connections. I can
> understand a client filling its connection pool but why do all clients,
> anywhere, including SQL Management Studio all fail to connect to SQL
> Server.
> The SQL Server perf counters themselves show only 300 logical connections.
> Regards
> James
> "William (Bill) Vaughn" wrote:
|||Thanks again for the response. The SQL Server is extremely powerful for what
we need, it is brand new and based on analysis of running the old version
(4GB 32 bit). The CPU remains very low all the time even though there are a
very large number of queries primarily from the website, however there are
numerous background services and client applications running on many other
machines.
The Logical connections never goes above 380 and when this problem happens
existing connections in Management studio work but are VERY slow in returning
any results - yet the CPU is very low (typically under 105)
The problem did not happen at all today but happened 5 times in 6 hours
yesterday. Nothing else runs on the SQL Server, we do not use Reporting
Services, Analysis Services or Notification Services in SQL Server from this
machine.
Yes if the server is getting slow then the clients would back up, fill their
pools and then throw the "No connection available from pool" error but it is
not obvious in anyway that the SQL Server is getting bogged down. It has 16GB
of RAM, the main database is 20GB but this worked fine on a 32bit 4GB machine.
The issue stills revolves around if code is constant then what has changed
the behaviour when using 64bit SQL Server with 16GB RAM on Service Pack 2?
Regards
James
"William (Bill) Vaughn" wrote:

> Ok, so if it's a server issue, we need to look at what's bogging down the
> server. Is it hosing Reporting Services or acting as a print server? If the
> server bogs down, the ASP clients have to wait and their pool(s) grow
> accordingly as new requests come in. Eventually, the server crowbars and
> won't accept any more connections due to the fact it has no CPU cycles to
> launch another thread--even for SSMS. I would at least open up PerfMon to
> monitor what's going on AFA the CPU use and other critical counters such as
> logical connections etc. The connection pools are not on the server unless
> IIS (which is hosting the ASP applications) is there too. In that case, the
> application code itself might be bogging down the host system.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "James Wren" <JamesWren@.discussions.microsoft.com> wrote in message
> news:8E8970C6-84BB-4B05-AB71-A7E4381147EB@.microsoft.com...
>
>

Connection Prodlem-with instance node

Hi
I have an active/passive sql server 2000 cluster. Most of the time I remote-login into the server and use the enterprise manager to work. But now when I click on either of the node I get the following error:
Encryption not supported on SQL Server ConnectionOpen (PreLoginHandshake())
But I'm able to connect to the server from my desktop.
Any help in this regard?
Thanks,
This means that the "Enable Protocol Encryption" option was set on the
machine you're using as a client.
Use the SQL Client Network Utility to uncheck this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin
Thank you so much it worked.
Razi
"Kevin McDonnell [MSFT]" wrote:

> This means that the "Enable Protocol Encryption" option was set on the
> machine you're using as a client.
> Use the SQL Client Network Utility to uncheck this.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||You're welcome!
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connection Prodlem-with instance node

Hi
I have an active/passive sql server 2000 cluster. Most of the time I remote-
login into the server and use the enterprise manager to work. But now when I
click on either of the node I get the following error:
Encryption not supported on SQL Server ConnectionOpen (PreLoginHandshake())
But I'm able to connect to the server from my desktop.
Any help in this regard?
Thanks,This means that the "Enable Protocol Encryption" option was set on the
machine you're using as a client.
Use the SQL Client Network Utility to uncheck this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin
Thank you so much it worked.
Razi
"Kevin McDonnell [MSFT]" wrote:

> This means that the "Enable Protocol Encryption" option was set on the
> machine you're using as a client.
> Use the SQL Client Network Utility to uncheck this.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||You're welcome!
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connection Problems

Each time a user tries to connect to an application, they
keep getting the following error:
Unable to create ADODB Connection - [DBNETLIB]
[ConnectionOpen (connectionOpen()).]SQL Server does not
exist or access denied.
This has been happening for the last three days and each
time this happens the server has to be rebooted before
users can connect to the database. The application uses
windows authentication. The database is SQL Server 2000
SP3a.Can you use the same accounts to connect to sql server ? Anything you see
in the Application and system event logs? Try connecting locally (on the
same box) and checking.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thanks Vikram,
I have solved the problem.
>--Original Message--
>Can you use the same accounts to connect to sql server ?
Anything you see
>in the Application and system event logs? Try connecting
locally (on the
>same box) and checking.
>Thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>

Connection Problems

Each time a user tries to connect to an application, they
keep getting the following error:
Unable to create ADODB Connection - [DBNETLIB]
[ConnectionOpen (connectionOpen()).]SQL Server does not
exist or access denied.
This has been happening for the last three days and each
time this happens the server has to be rebooted before
users can connect to the database. The application uses
windows authentication. The database is SQL Server 2000
SP3a.
Can you use the same accounts to connect to sql server ? Anything you see
in the Application and system event logs? Try connecting locally (on the
same box) and checking.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Thanks Vikram,
I have solved the problem.
>--Original Message--
>Can you use the same accounts to connect to sql server ?
Anything you see
>in the Application and system event logs? Try connecting
locally (on the
>same box) and checking.
>Thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>