I am trying to connect to a default instance of SQL Server 2000 Enterprise on
a remote network server using ADO.NET objects through ASP.NET Web Application.
The SQL SERVER is on a Windows XP machine (no firewall enabled) with TCP/IP
and Named Pipes enabled
For the test purposes, I created a new Web Form, created a new SqlConnection
in the Server Explorer to the remote database. I test the connection is VS
IDE and everything is fine. I create a SqlDataAdapter on the web form,
generate a dataset, test the dataset and the results are valid. I bind the
SqlDataAdapter to a datagrid and run the webform. I now get the following
error.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:lin e 39
Hmph! I take the same connection string from the web app, create a win app.
Place a datagrid on the form, do the binding and load the app. Hey Presto! it
works.
I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
create a connection, but its at though SQL Server is rejecting the
connection, although I see no failures in SQL Server.
Any thoughts would be much appreciated.
Andy
Sounds like firewall to me.
As a quick test to see if firewall is blocking port, try this from command
prompt on client machine ->
telnet mysqlmachine 1433
If you can't telnet to 1433, then you have firewall issue.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:40AC00C0-0402-452B-AAC8-F25D97E94ACD@.microsoft.com...
>I am trying to connect to a default instance of SQL Server 2000 Enterprise
>on
> a remote network server using ADO.NET objects through ASP.NET Web
> Application.
> The SQL SERVER is on a Windows XP machine (no firewall enabled) with
> TCP/IP
> and Named Pipes enabled
> For the test purposes, I created a new Web Form, created a new
> SqlConnection
> in the Server Explorer to the remote database. I test the connection is VS
> IDE and everything is fine. I create a SqlDataAdapter on the web form,
> generate a dataset, test the dataset and the results are valid. I bind the
> SqlDataAdapter to a datagrid and run the webform. I now get the following
> error.
> System.Data.SqlClient.SqlException: SQL Server does not exist or access
> denied.
> at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
> isInTransaction)
> at
> System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString
> options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
> c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:lin e 39
> Hmph! I take the same connection string from the web app, create a win
> app.
> Place a datagrid on the form, do the binding and load the app. Hey Presto!
> it
> works.
> I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
> create a connection, but its at though SQL Server is rejecting the
> connection, although I see no failures in SQL Server.
> Any thoughts would be much appreciated.
> Andy
Showing posts with label ado. Show all posts
Showing posts with label ado. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Connectivity Issues - Bizzare
I am trying to connect to a default instance of SQL Server 2000 Enterprise o
n
a remote network server using ADO.NET objects through ASP.NET Web Applicatio
n.
The SQL SERVER is on a Windows XP machine (no firewall enabled) with TCP/IP
and Named Pipes enabled
For the test purposes, I created a new Web Form, created a new SqlConnection
in the Server Explorer to the remote database. I test the connection is VS
IDE and everything is fine. I create a SqlDataAdapter on the web form,
generate a dataset, test the dataset and the results are valid. I bind the
SqlDataAdapter to a datagrid and run the webform. I now get the following
error.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:line 39
Hmph! I take the same connection string from the web app, create a win app.
Place a datagrid on the form, do the binding and load the app. Hey Presto! i
t
works.
I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
create a connection, but its at though SQL Server is rejecting the
connection, although I see no failures in SQL Server.
Any thoughts would be much appreciated.
AndySounds like firewall to me.
As a quick test to see if firewall is blocking port, try this from command
prompt on client machine ->
telnet mysqlmachine 1433
If you can't telnet to 1433, then you have firewall issue.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:40AC00C0-0402-452B-AAC8-F25D97E94ACD@.microsoft.com...
>I am trying to connect to a default instance of SQL Server 2000 Enterprise
>on
> a remote network server using ADO.NET objects through ASP.NET Web
> Application.
> The SQL SERVER is on a Windows XP machine (no firewall enabled) with
> TCP/IP
> and Named Pipes enabled
> For the test purposes, I created a new Web Form, created a new
> SqlConnection
> in the Server Explorer to the remote database. I test the connection is VS
> IDE and everything is fine. I create a SqlDataAdapter on the web form,
> generate a dataset, test the dataset and the results are valid. I bind the
> SqlDataAdapter to a datagrid and run the webform. I now get the following
> error.
> System.Data.SqlClient.SqlException: SQL Server does not exist or access
> denied.
> at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> isInTransaction)
> at
> System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString
> options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
> c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:line 39
> Hmph! I take the same connection string from the web app, create a win
> app.
> Place a datagrid on the form, do the binding and load the app. Hey Presto!
> it
> works.
> I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
> create a connection, but its at though SQL Server is rejecting the
> connection, although I see no failures in SQL Server.
> Any thoughts would be much appreciated.
> Andy
n
a remote network server using ADO.NET objects through ASP.NET Web Applicatio
n.
The SQL SERVER is on a Windows XP machine (no firewall enabled) with TCP/IP
and Named Pipes enabled
For the test purposes, I created a new Web Form, created a new SqlConnection
in the Server Explorer to the remote database. I test the connection is VS
IDE and everything is fine. I create a SqlDataAdapter on the web form,
generate a dataset, test the dataset and the results are valid. I bind the
SqlDataAdapter to a datagrid and run the webform. I now get the following
error.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:line 39
Hmph! I take the same connection string from the web app, create a win app.
Place a datagrid on the form, do the binding and load the app. Hey Presto! i
t
works.
I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
create a connection, but its at though SQL Server is rejecting the
connection, although I see no failures in SQL Server.
Any thoughts would be much appreciated.
AndySounds like firewall to me.
As a quick test to see if firewall is blocking port, try this from command
prompt on client machine ->
telnet mysqlmachine 1433
If you can't telnet to 1433, then you have firewall issue.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:40AC00C0-0402-452B-AAC8-F25D97E94ACD@.microsoft.com...
>I am trying to connect to a default instance of SQL Server 2000 Enterprise
>on
> a remote network server using ADO.NET objects through ASP.NET Web
> Application.
> The SQL SERVER is on a Windows XP machine (no firewall enabled) with
> TCP/IP
> and Named Pipes enabled
> For the test purposes, I created a new Web Form, created a new
> SqlConnection
> in the Server Explorer to the remote database. I test the connection is VS
> IDE and everything is fine. I create a SqlDataAdapter on the web form,
> generate a dataset, test the dataset and the results are valid. I bind the
> SqlDataAdapter to a datagrid and run the webform. I now get the following
> error.
> System.Data.SqlClient.SqlException: SQL Server does not exist or access
> denied.
> at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> isInTransaction)
> at
> System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString
> options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> at DBConnTest.WebForm1.Page_Load(Object sender, EventArgs e) in
> c:\inetpub\wwwroot\dbconntest\webform1.aspx.cs:line 39
> Hmph! I take the same connection string from the web app, create a win
> app.
> Place a datagrid on the form, do the binding and load the app. Hey Presto!
> it
> works.
> I used TCPView (systeminternals.com) and I can see aspnet_wp trying to
> create a connection, but its at though SQL Server is rejecting the
> connection, although I see no failures in SQL Server.
> Any thoughts would be much appreciated.
> Andy
Sunday, March 11, 2012
Connection to SQL2k5 from OLEDB
I have program which work with SQL2k. The program was written on C++ and it
use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
hardcoded in the program. May I connect to SQL2k5 without change it in
program and recompile it, with use of old OLEDB provider? The program big,
and it will be necessary to change it in several places...Hi
AFAIK you should be ok with the application as is, you will not be able to
use the features of the SQL Native Client.
John
"andsm" wrote:
> I have program which work with SQL2k. The program was written on C++ and i
t
> use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
> hardcoded in the program. May I connect to SQL2k5 without change it in
> program and recompile it, with use of old OLEDB provider? The program big,
> and it will be necessary to change it in several places...|||You are right, as far as you don=B4t need the new features you can get
pre-SQL 2k5 functionality with the old providers. Only SNAC with its
new providers or .NET 2.0 will take you to the heaven of SQL2k5 ;-)
(=2ENET 2.0 even a bit higher)
HTH, Jens Suessmeyer.
use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
hardcoded in the program. May I connect to SQL2k5 without change it in
program and recompile it, with use of old OLEDB provider? The program big,
and it will be necessary to change it in several places...Hi
AFAIK you should be ok with the application as is, you will not be able to
use the features of the SQL Native Client.
John
"andsm" wrote:
> I have program which work with SQL2k. The program was written on C++ and i
t
> use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
> hardcoded in the program. May I connect to SQL2k5 without change it in
> program and recompile it, with use of old OLEDB provider? The program big,
> and it will be necessary to change it in several places...|||You are right, as far as you don=B4t need the new features you can get
pre-SQL 2k5 functionality with the old providers. Only SNAC with its
new providers or .NET 2.0 will take you to the heaven of SQL2k5 ;-)
(=2ENET 2.0 even a bit higher)
HTH, Jens Suessmeyer.
Connection to SQL2k5 from OLEDB
I have program which work with SQL2k. The program was written on C++ and it
use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
hardcoded in the program. May I connect to SQL2k5 without change it in
program and recompile it, with use of old OLEDB provider? The program big,
and it will be necessary to change it in several places...Hi
AFAIK you should be ok with the application as is, you will not be able to
use the features of the SQL Native Client.
John
"andsm" wrote:
> I have program which work with SQL2k. The program was written on C++ and it
> use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
> hardcoded in the program. May I connect to SQL2k5 without change it in
> program and recompile it, with use of old OLEDB provider? The program big,
> and it will be necessary to change it in several places...|||You are right, as far as you don=B4t need the new features you can get
pre-SQL 2k5 functionality with the old providers. Only SNAC with its
new providers or .NET 2.0 will take you to the heaven of SQL2k5 ;-)
(=2ENET 2.0 even a bit higher)
HTH, Jens Suessmeyer.
use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
hardcoded in the program. May I connect to SQL2k5 without change it in
program and recompile it, with use of old OLEDB provider? The program big,
and it will be necessary to change it in several places...Hi
AFAIK you should be ok with the application as is, you will not be able to
use the features of the SQL Native Client.
John
"andsm" wrote:
> I have program which work with SQL2k. The program was written on C++ and it
> use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
> hardcoded in the program. May I connect to SQL2k5 without change it in
> program and recompile it, with use of old OLEDB provider? The program big,
> and it will be necessary to change it in several places...|||You are right, as far as you don=B4t need the new features you can get
pre-SQL 2k5 functionality with the old providers. Only SNAC with its
new providers or .NET 2.0 will take you to the heaven of SQL2k5 ;-)
(=2ENET 2.0 even a bit higher)
HTH, Jens Suessmeyer.
Connection to SQL2k5 from OLEDB
I have program which work with SQL2k. The program was written on C++ and it
use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
hardcoded in the program. May I connect to SQL2k5 without change it in
program and recompile it, with use of old OLEDB provider? The program big,
and it will be necessary to change it in several places...
Hi
AFAIK you should be ok with the application as is, you will not be able to
use the features of the SQL Native Client.
John
"andsm" wrote:
> I have program which work with SQL2k. The program was written on C++ and it
> use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
> hardcoded in the program. May I connect to SQL2k5 without change it in
> program and recompile it, with use of old OLEDB provider? The program big,
> and it will be necessary to change it in several places...
|||You are right, as far as you don=B4t need the new features you can get
pre-SQL 2k5 functionality with the old providers. Only SNAC with its
new providers or .NET 2.0 will take you to the heaven of SQL2k5 ;-)
(=2ENET 2.0 even a bit higher)
HTH, Jens Suessmeyer.
use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
hardcoded in the program. May I connect to SQL2k5 without change it in
program and recompile it, with use of old OLEDB provider? The program big,
and it will be necessary to change it in several places...
Hi
AFAIK you should be ok with the application as is, you will not be able to
use the features of the SQL Native Client.
John
"andsm" wrote:
> I have program which work with SQL2k. The program was written on C++ and it
> use OLEDB (not ADO) to connect to server. Name of provider (OLEDB) is
> hardcoded in the program. May I connect to SQL2k5 without change it in
> program and recompile it, with use of old OLEDB provider? The program big,
> and it will be necessary to change it in several places...
|||You are right, as far as you don=B4t need the new features you can get
pre-SQL 2k5 functionality with the old providers. Only SNAC with its
new providers or .NET 2.0 will take you to the heaven of SQL2k5 ;-)
(=2ENET 2.0 even a bit higher)
HTH, Jens Suessmeyer.
Connection to SQL Server 2005 lost
We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
(Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
years, but after upgrading to SQL Server 2005, the database connection is
sometimes lost during larger operation involving open/close of several
recordsets. The general error message says:
“[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist or access
denied". "Simpler" parts of the program involving just a recordset or two
always work fine. Any idea of what is going on here?
Have you tried using the SQL Native Client (SNAC) instead to see if that
makes a difference?
http://msdn2.microsoft.com/en-us/data/aa937733.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
> We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
> (Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
> years, but after upgrading to SQL Server 2005, the database connection is
> sometimes lost during larger operation involving open/close of several
> recordsets. The general error message says:
> “[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist or
> access
> denied". "Simpler" parts of the program involving just a recordset or two
> always work fine. Any idea of what is going on here?
|||Thank you for the suggestion.
We have not tried the native client, because the FAQ says:
Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
server?
A. Applications deployed before SQL Server 2005 was released can and should
continue to use MDAC.
Most likely it is a server configuration problem. Our program is used by
many clients, accessing the datebase from different computers/configurations,
and all of them experience this error message when conneting to a given SQL
Server 2005 Server. Some of our clients from other firms also have their own
SQL Server 2005 running, and they have successfully managed to connect to
their database using MDAC and never experience this error message.
"Andrew J. Kelly" wrote:
> Have you tried using the SQL Native Client (SNAC) instead to see if that
> makes a difference?
> http://msdn2.microsoft.com/en-us/data/aa937733.aspx
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
> message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
>
|||Sorry I don't have another answer for you. I have not heard of or seen this
particular issue before.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
|||Ah, I would STILL upgrade to SNAC. I agree that if the application is
working and deployed it should not be disturbed, but yours is not working
and SNAC solves any number of issues like this.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
|||We upgraded to SNAC, but still experience the very same error. The text of
the error message reported is a little bit different, thought. It now says
"Named Pipes Provider: No process is on the other end of the pipe." We did
get rid of the error message when we changed the recordsets cursor from
server-side to client-side (CursorLocation = adUseClient).
"William Vaughn" wrote:
> Ah, I would STILL upgrade to SNAC. I agree that if the application is
> working and deployed it should not be disturbed, but yours is not working
> and SNAC solves any number of issues like this.
> --
> __________________________________________________ ________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
> __________________________________________________ __________________________________________
> "Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
> message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...
>
|||You are putting less work on the SQL Server by doing that and is generally a
good idea anyway. But you should also think about changing to TCP instead of
using named pipes.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:530FDB75-0B6B-46E3-A9CA-FFD3E2B3F75F@.microsoft.com...[vbcol=seagreen]
> We upgraded to SNAC, but still experience the very same error. The text of
> the error message reported is a little bit different, thought. It now says
> "Named Pipes Provider: No process is on the other end of the pipe." We did
> get rid of the error message when we changed the recordsets cursor from
> server-side to client-side (CursorLocation = adUseClient).
>
> "William Vaughn" wrote:
|||It sounds like the server is rejecting the Open. Make sure that the server
configuration is not limiting the total number of connections in any way.
These might have been configured to comply with license or other artifical
constraints.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:530FDB75-0B6B-46E3-A9CA-FFD3E2B3F75F@.microsoft.com...[vbcol=seagreen]
> We upgraded to SNAC, but still experience the very same error. The text of
> the error message reported is a little bit different, thought. It now says
> "Named Pipes Provider: No process is on the other end of the pipe." We did
> get rid of the error message when we changed the recordsets cursor from
> server-side to client-side (CursorLocation = adUseClient).
>
> "William Vaughn" wrote:
(Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
years, but after upgrading to SQL Server 2005, the database connection is
sometimes lost during larger operation involving open/close of several
recordsets. The general error message says:
“[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist or access
denied". "Simpler" parts of the program involving just a recordset or two
always work fine. Any idea of what is going on here?
Have you tried using the SQL Native Client (SNAC) instead to see if that
makes a difference?
http://msdn2.microsoft.com/en-us/data/aa937733.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
> We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
> (Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
> years, but after upgrading to SQL Server 2005, the database connection is
> sometimes lost during larger operation involving open/close of several
> recordsets. The general error message says:
> “[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist or
> access
> denied". "Simpler" parts of the program involving just a recordset or two
> always work fine. Any idea of what is going on here?
|||Thank you for the suggestion.
We have not tried the native client, because the FAQ says:
Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
server?
A. Applications deployed before SQL Server 2005 was released can and should
continue to use MDAC.
Most likely it is a server configuration problem. Our program is used by
many clients, accessing the datebase from different computers/configurations,
and all of them experience this error message when conneting to a given SQL
Server 2005 Server. Some of our clients from other firms also have their own
SQL Server 2005 running, and they have successfully managed to connect to
their database using MDAC and never experience this error message.
"Andrew J. Kelly" wrote:
> Have you tried using the SQL Native Client (SNAC) instead to see if that
> makes a difference?
> http://msdn2.microsoft.com/en-us/data/aa937733.aspx
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
> message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
>
|||Sorry I don't have another answer for you. I have not heard of or seen this
particular issue before.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
|||Ah, I would STILL upgrade to SNAC. I agree that if the application is
working and deployed it should not be disturbed, but yours is not working
and SNAC solves any number of issues like this.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
|||We upgraded to SNAC, but still experience the very same error. The text of
the error message reported is a little bit different, thought. It now says
"Named Pipes Provider: No process is on the other end of the pipe." We did
get rid of the error message when we changed the recordsets cursor from
server-side to client-side (CursorLocation = adUseClient).
"William Vaughn" wrote:
> Ah, I would STILL upgrade to SNAC. I agree that if the application is
> working and deployed it should not be disturbed, but yours is not working
> and SNAC solves any number of issues like this.
> --
> __________________________________________________ ________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
> __________________________________________________ __________________________________________
> "Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
> message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...
>
|||You are putting less work on the SQL Server by doing that and is generally a
good idea anyway. But you should also think about changing to TCP instead of
using named pipes.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:530FDB75-0B6B-46E3-A9CA-FFD3E2B3F75F@.microsoft.com...[vbcol=seagreen]
> We upgraded to SNAC, but still experience the very same error. The text of
> the error message reported is a little bit different, thought. It now says
> "Named Pipes Provider: No process is on the other end of the pipe." We did
> get rid of the error message when we changed the recordsets cursor from
> server-side to client-side (CursorLocation = adUseClient).
>
> "William Vaughn" wrote:
|||It sounds like the server is rejecting the Open. Make sure that the server
configuration is not limiting the total number of connections in any way.
These might have been configured to comply with license or other artifical
constraints.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:530FDB75-0B6B-46E3-A9CA-FFD3E2B3F75F@.microsoft.com...[vbcol=seagreen]
> We upgraded to SNAC, but still experience the very same error. The text of
> the error message reported is a little bit different, thought. It now says
> "Named Pipes Provider: No process is on the other end of the pipe." We did
> get rid of the error message when we changed the recordsets cursor from
> server-side to client-side (CursorLocation = adUseClient).
>
> "William Vaughn" wrote:
Connection to SQL Server 2005 lost
We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
(Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
years, but after upgrading to SQL Server 2005, the database connection is
sometimes lost during larger operation involving open/close of several
recordsets. The general error message says:
“[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist
or access
denied". "Simpler" parts of the program involving just a recordset or two
always work fine. Any idea of what is going on here?Have you tried using the SQL Native Client (SNAC) instead to see if that
makes a difference?
http://msdn2.microsoft.com/en-us/data/aa937733.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
> We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
> (Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
> years, but after upgrading to SQL Server 2005, the database connection is
> sometimes lost during larger operation involving open/close of several
> recordsets. The general error message says:
> “[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsis
t or
> access
> denied". "Simpler" parts of the program involving just a recordset or two
> always work fine. Any idea of what is going on here?|||Thank you for the suggestion.
We have not tried the native client, because the FAQ says:
Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
server?
A. Applications deployed before SQL Server 2005 was released can and should
continue to use MDAC.
Most likely it is a server configuration problem. Our program is used by
many clients, accessing the datebase from different computers/configurations
,
and all of them experience this error message when conneting to a given SQL
Server 2005 Server. Some of our clients from other firms also have their own
SQL Server 2005 running, and they have successfully managed to connect to
their database using MDAC and never experience this error message.
"Andrew J. Kelly" wrote:
> Have you tried using the SQL Native Client (SNAC) instead to see if that
> makes a difference?
> http://msdn2.microsoft.com/en-us/data/aa937733.aspx
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
> message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
>|||Sorry I don't have another answer for you. I have not heard of or seen this
particular issue before.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
>|||Ah, I would STILL upgrade to SNAC. I agree that if the application is
working and deployed it should not be disturbed, but yours is not working
and SNAC solves any number of issues like this.
________________________________________
__________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
________________________________________
____________________________________
________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
>
(Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
years, but after upgrading to SQL Server 2005, the database connection is
sometimes lost during larger operation involving open/close of several
recordsets. The general error message says:
“[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsist
or access
denied". "Simpler" parts of the program involving just a recordset or two
always work fine. Any idea of what is going on here?Have you tried using the SQL Native Client (SNAC) instead to see if that
makes a difference?
http://msdn2.microsoft.com/en-us/data/aa937733.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
> We have developed a VB6 client program accsessing SQL Sever 7.0 via ADO
> (Provider=sqloledb) and MDAC 2.8. This program has worked flawlessly for
> years, but after upgrading to SQL Server 2005, the database connection is
> sometimes lost during larger operation involving open/close of several
> recordsets. The general error message says:
> “[DBNETLIB][ConnectionOpen,Connect()).]SQL Server does not exsis
t or
> access
> denied". "Simpler" parts of the program involving just a recordset or two
> always work fine. Any idea of what is going on here?|||Thank you for the suggestion.
We have not tried the native client, because the FAQ says:
Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
server?
A. Applications deployed before SQL Server 2005 was released can and should
continue to use MDAC.
Most likely it is a server configuration problem. Our program is used by
many clients, accessing the datebase from different computers/configurations
,
and all of them experience this error message when conneting to a given SQL
Server 2005 Server. Some of our clients from other firms also have their own
SQL Server 2005 running, and they have successfully managed to connect to
their database using MDAC and never experience this error message.
"Andrew J. Kelly" wrote:
> Have you tried using the SQL Native Client (SNAC) instead to see if that
> makes a difference?
> http://msdn2.microsoft.com/en-us/data/aa937733.aspx
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Glenn Handeland" <Glenn Handeland@.discussions.microsoft.com> wrote in
> message news:9A2E8820-DEFC-4209-B019-8A0996CF7FDF@.microsoft.com...
>|||Sorry I don't have another answer for you. I have not heard of or seen this
particular issue before.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
>|||Ah, I would STILL upgrade to SNAC. I agree that if the application is
working and deployed it should not be disturbed, but yours is not working
and SNAC solves any number of issues like this.
________________________________________
__________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
________________________________________
____________________________________
________________
"Glenn Handeland" <GlennHandeland@.discussions.microsoft.com> wrote in
message news:C425386F-DA1A-4D79-AEFB-AE29F7AC6606@.microsoft.com...[vbcol=seagreen]
> Thank you for the suggestion.
> We have not tried the native client, because the FAQ says:
> Q. Must I upgrade clients to use SQL Native Client as soon as I upgrade my
> server?
> A. Applications deployed before SQL Server 2005 was released can and
> should
> continue to use MDAC.
> Most likely it is a server configuration problem. Our program is used by
> many clients, accessing the datebase from different
> computers/configurations,
> and all of them experience this error message when conneting to a given
> SQL
> Server 2005 Server. Some of our clients from other firms also have their
> own
> SQL Server 2005 running, and they have successfully managed to connect to
> their database using MDAC and never experience this error message.
>
> "Andrew J. Kelly" wrote:
>
Wednesday, March 7, 2012
Connection timeout problem
Hi
Using ado/ole-db, I create a connection to a database (SQL 2000) specifying
a time-out value of 43200 (which should equate to 12 hours).
I then create a command and use the active connection and also take the
added precaution (which is probably pointless) of setting the command
timeout value to also be 12 hours.
I then execute some SQL that has been dynamically prepared (and could take
many hours to complete) and run this. However, I get an error in the
Application event log showing that the connection timed-out. This doesn't
happen everytime - most times it connects and runs flawlessly... The error
details for the timeout are:
Err.Number = -214746759
Err.Description = Timeout expired
And this occurs in the region of approximately 1 minute after the code was
initialised (the machine is ultra busy at this time).
I may well be wrong here, but I assume that it wasn't the connection that
timed out (i.e. was made and then was dropped) but that there's a specified
time allowed to make the connection and that it's this allowed time that is
too short - remember, the machine is ultra busy at this time). If I am
right in this assumption then I need a way of telling it not to give up
quite so quickly (or to retry several hundred times). Any insight as to
what might be happening and any suggestions concerning how to fix this would
be most welcome.
I include my code below (error handling and app logging removed for
clarity):
' Declarations
Dim oCmd As ADODB.Command
Dim oCn As ADODB.Connection
Dim lTimeOut As Long
'--
' Set the timeout ( = 43200 seconds = 12 hours)
lTimeOut = CLng(mIni.iniKeyValue(mIni.computerName, "timeOut"))
'--
' Create a new connection to the database
Set oCn = New ADODB.Connection
With oCn
.ConnectionString = mIni.iniKeyValue(computerName,
"connectionString")
.CommandTimeout = lTimeOut
.Open
End With
'--
' Instantiate the command object
Set oCmd = New ADODB.Command
With oCmd
'--
Set .ActiveConnection = oCn
.CommandType = adCmdText
.CommandText = sSQL
.CommandTimeout = lTimeOut
'--
' Execute the SQL
.Execute
'--
' Disconnect command
Set .ActiveConnection = Nothing
'--
End With
'--
' Trash the command and connection objects
oCn.Close
Set oCn = Nothing
Set oCmd = Nothing
'--
Many thanks in advance
GriffHi GriffithsJ, try using CommandTimeout = 0.
Vctor Koch From Argentina.
"GriffithsJ" <GriffithsJ_520@.hotmail.com> escribi en el mensaje
news:uzXSsXp2DHA.2060@.TK2MSFTNGP10.phx.gbl...
specifying
error
specified
is
would
That's the equivalent of saying infinite...
Two things.
First is that I'm always a little cautious about using infinite values, I'd
prefer to set values to very large finite values - that way, server
resources should eventually tidy themselves up.
Second is that I had a commandTimeout value of 12 hours and it died within a
minute. I'm not sure who increasing the commandTimeout value from 12 hours
to infinite would affect it being killed off in under a minute (unless
setting it to 0 has any other special effects I'm not aware of.)
Griff|||Hi Griff,
Command.CommandTimeout must be explicitly set as you did.
It is not passed over from the Connection.CommandTimeout
when assigning the Command.ActiveConnection from there.
So the taken precaution is obligatory.
You mean the c o n n e c t i o n times out, not the command, right?
When does it time out - when oCn.Open is executed or where?
If so, increase the ConnectionTimeout of oCn before Open
so that it waits longer to establish the connection:
.ConnectionTimeout = 300 '****************************
However, if you mean that the c o m m a n d times out at oCmd.Execute,
check whether oCmd.CommandTimeout has indeed the intended 43200 at that time
and whether it is really a command timeout or a connection error.
The error number might be wrong. Could be -2147467259 (the notorious 8000400
5H).
Regards,
Frank
Message sent 14 Jan 2004 15:19:22 GMT|||Hi Frank
Many thanks for that answer...I've made all the necessary changes as you
suggested and have uploaded the compiled code.
Will find out tomorrow morning whether this worked...
Thanks
Griff|||> Using ado/ole-db, I create a connection to a database (SQL 2000)
specifying
Comand timeout is seperate from connection timeout.
http://www.devguru.com/Technologies...andtimeout.html
Try the command GUI in a adoanywhere command gui.
http://www.adoanywhere.com/help/command.htm#Command
Mike Collier
http://www.adoanywhere.com
http://www.adoanywhere.com/forum
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:uzXSsXp2DHA.2060@.TK2MSFTNGP10.phx.gbl...
specifying
error
specified
is
would
Using ado/ole-db, I create a connection to a database (SQL 2000) specifying
a time-out value of 43200 (which should equate to 12 hours).
I then create a command and use the active connection and also take the
added precaution (which is probably pointless) of setting the command
timeout value to also be 12 hours.
I then execute some SQL that has been dynamically prepared (and could take
many hours to complete) and run this. However, I get an error in the
Application event log showing that the connection timed-out. This doesn't
happen everytime - most times it connects and runs flawlessly... The error
details for the timeout are:
Err.Number = -214746759
Err.Description = Timeout expired
And this occurs in the region of approximately 1 minute after the code was
initialised (the machine is ultra busy at this time).
I may well be wrong here, but I assume that it wasn't the connection that
timed out (i.e. was made and then was dropped) but that there's a specified
time allowed to make the connection and that it's this allowed time that is
too short - remember, the machine is ultra busy at this time). If I am
right in this assumption then I need a way of telling it not to give up
quite so quickly (or to retry several hundred times). Any insight as to
what might be happening and any suggestions concerning how to fix this would
be most welcome.
I include my code below (error handling and app logging removed for
clarity):
' Declarations
Dim oCmd As ADODB.Command
Dim oCn As ADODB.Connection
Dim lTimeOut As Long
'--
' Set the timeout ( = 43200 seconds = 12 hours)
lTimeOut = CLng(mIni.iniKeyValue(mIni.computerName, "timeOut"))
'--
' Create a new connection to the database
Set oCn = New ADODB.Connection
With oCn
.ConnectionString = mIni.iniKeyValue(computerName,
"connectionString")
.CommandTimeout = lTimeOut
.Open
End With
'--
' Instantiate the command object
Set oCmd = New ADODB.Command
With oCmd
'--
Set .ActiveConnection = oCn
.CommandType = adCmdText
.CommandText = sSQL
.CommandTimeout = lTimeOut
'--
' Execute the SQL
.Execute
'--
' Disconnect command
Set .ActiveConnection = Nothing
'--
End With
'--
' Trash the command and connection objects
oCn.Close
Set oCn = Nothing
Set oCmd = Nothing
'--
Many thanks in advance
GriffHi GriffithsJ, try using CommandTimeout = 0.
Vctor Koch From Argentina.
"GriffithsJ" <GriffithsJ_520@.hotmail.com> escribi en el mensaje
news:uzXSsXp2DHA.2060@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi
> Using ado/ole-db, I create a connection to a database (SQL 2000)
specifying
quote:
> a time-out value of 43200 (which should equate to 12 hours).
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
> I then execute some SQL that has been dynamically prepared (and could take
> many hours to complete) and run this. However, I get an error in the
> Application event log showing that the connection timed-out. This doesn't
> happen everytime - most times it connects and runs flawlessly... The
error
quote:
> details for the timeout are:
> Err.Number = -214746759
> Err.Description = Timeout expired
> And this occurs in the region of approximately 1 minute after the code was
> initialised (the machine is ultra busy at this time).
> I may well be wrong here, but I assume that it wasn't the connection that
> timed out (i.e. was made and then was dropped) but that there's a
specified
quote:
> time allowed to make the connection and that it's this allowed time that
is
quote:
> too short - remember, the machine is ultra busy at this time). If I am
> right in this assumption then I need a way of telling it not to give up
> quite so quickly (or to retry several hundred times). Any insight as to
> what might be happening and any suggestions concerning how to fix this
would
quote:|||> Hi GriffithsJ, try using CommandTimeout = 0.
> be most welcome.
> I include my code below (error handling and app logging removed for
> clarity):
> ' Declarations
> Dim oCmd As ADODB.Command
> Dim oCn As ADODB.Connection
> Dim lTimeOut As Long
> '--
> ' Set the timeout ( = 43200 seconds = 12 hours)
> lTimeOut = CLng(mIni.iniKeyValue(mIni.computerName, "timeOut"))
> '--
> ' Create a new connection to the database
> Set oCn = New ADODB.Connection
> With oCn
> .ConnectionString = mIni.iniKeyValue(computerName,
> "connectionString")
> .CommandTimeout = lTimeOut
> .Open
> End With
> '--
> ' Instantiate the command object
> Set oCmd = New ADODB.Command
> With oCmd
> '--
> Set .ActiveConnection = oCn
> .CommandType = adCmdText
> .CommandText = sSQL
> .CommandTimeout = lTimeOut
> '--
> ' Execute the SQL
> .Execute
> '--
> ' Disconnect command
> Set .ActiveConnection = Nothing
> '--
> End With
> '--
> ' Trash the command and connection objects
> oCn.Close
> Set oCn = Nothing
> Set oCmd = Nothing
> '--
> Many thanks in advance
> Griff
>
quote:
> --
That's the equivalent of saying infinite...
Two things.
First is that I'm always a little cautious about using infinite values, I'd
prefer to set values to very large finite values - that way, server
resources should eventually tidy themselves up.
Second is that I had a commandTimeout value of 12 hours and it died within a
minute. I'm not sure who increasing the commandTimeout value from 12 hours
to infinite would affect it being killed off in under a minute (unless
setting it to 0 has any other special effects I'm not aware of.)
Griff|||Hi Griff,
quote:
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
Command.CommandTimeout must be explicitly set as you did.
It is not passed over from the Connection.CommandTimeout
when assigning the Command.ActiveConnection from there.
So the taken precaution is obligatory.
quote:
> I then execute some SQL that has been dynamically prepared (and could take
> many hours to complete) and run this. However, I get an error in the
> Application event log showing that the connection timed-out. This doesn't
> happen everytime - most times it connects and runs flawlessly... The erro
r
> details for the timeout are:
You mean the c o n n e c t i o n times out, not the command, right?
When does it time out - when oCn.Open is executed or where?
If so, increase the ConnectionTimeout of oCn before Open
so that it waits longer to establish the connection:
quote:
> With oCn
> .ConnectionString = mIni.iniKeyValue(computerName,
> "connectionString")
> .CommandTimeout = lTimeOut
.ConnectionTimeout = 300 '****************************
quote:
> .Open
quote:
> End With
However, if you mean that the c o m m a n d times out at oCmd.Execute,
check whether oCmd.CommandTimeout has indeed the intended 43200 at that time
and whether it is really a command timeout or a connection error.
quote:
> Err.Number = -214746759
The error number might be wrong. Could be -2147467259 (the notorious 8000400
5H).
Regards,
Frank
Message sent 14 Jan 2004 15:19:22 GMT|||Hi Frank
Many thanks for that answer...I've made all the necessary changes as you
suggested and have uploaded the compiled code.
Will find out tomorrow morning whether this worked...
Thanks
Griff|||> Using ado/ole-db, I create a connection to a database (SQL 2000)
specifying
quote:
> a time-out value of 43200 (which should equate to 12 hours).
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
Comand timeout is seperate from connection timeout.
http://www.devguru.com/Technologies...andtimeout.html
Try the command GUI in a adoanywhere command gui.
http://www.adoanywhere.com/help/command.htm#Command
Mike Collier
http://www.adoanywhere.com
http://www.adoanywhere.com/forum
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:uzXSsXp2DHA.2060@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi
> Using ado/ole-db, I create a connection to a database (SQL 2000)
specifying
quote:
> a time-out value of 43200 (which should equate to 12 hours).
> I then create a command and use the active connection and also take the
> added precaution (which is probably pointless) of setting the command
> timeout value to also be 12 hours.
> I then execute some SQL that has been dynamically prepared (and could take
> many hours to complete) and run this. However, I get an error in the
> Application event log showing that the connection timed-out. This doesn't
> happen everytime - most times it connects and runs flawlessly... The
error
quote:
> details for the timeout are:
> Err.Number = -214746759
> Err.Description = Timeout expired
> And this occurs in the region of approximately 1 minute after the code was
> initialised (the machine is ultra busy at this time).
> I may well be wrong here, but I assume that it wasn't the connection that
> timed out (i.e. was made and then was dropped) but that there's a
specified
quote:
> time allowed to make the connection and that it's this allowed time that
is
quote:
> too short - remember, the machine is ultra busy at this time). If I am
> right in this assumption then I need a way of telling it not to give up
> quite so quickly (or to retry several hundred times). Any insight as to
> what might be happening and any suggestions concerning how to fix this
would
quote:
> be most welcome.
> I include my code below (error handling and app logging removed for
> clarity):
> ' Declarations
> Dim oCmd As ADODB.Command
> Dim oCn As ADODB.Connection
> Dim lTimeOut As Long
> '--
> ' Set the timeout ( = 43200 seconds = 12 hours)
> lTimeOut = CLng(mIni.iniKeyValue(mIni.computerName, "timeOut"))
> '--
> ' Create a new connection to the database
> Set oCn = New ADODB.Connection
> With oCn
> .ConnectionString = mIni.iniKeyValue(computerName,
> "connectionString")
> .CommandTimeout = lTimeOut
> .Open
> End With
> '--
> ' Instantiate the command object
> Set oCmd = New ADODB.Command
> With oCmd
> '--
> Set .ActiveConnection = oCn
> .CommandType = adCmdText
> .CommandText = sSQL
> .CommandTimeout = lTimeOut
> '--
> ' Execute the SQL
> .Execute
> '--
> ' Disconnect command
> Set .ActiveConnection = Nothing
> '--
> End With
> '--
> ' Trash the command and connection objects
> oCn.Close
> Set oCn = Nothing
> Set oCmd = Nothing
> '--
> Many thanks in advance
> Griff
>
Saturday, February 25, 2012
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
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
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.
>
>
>
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.
>
>
>
Subscribe to:
Posts (Atom)