Linked Tables - ODBC SQL Server Driver Timeout Expired

Maybe a problem with the discovery service or for finding the right port to
use if you are using a dynamically allocated port. Could also be a problem
with the DSN or the AD server on your network.

Try to force to use either the Named Pipe or the TCP/IP protocol by adding
the name of the library in the connection string (Network=DBMSSOCN; in the
case of TCP/IP, see
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer ),
by using the prefix np: or tcp:, by adding the port number at the end, by
adding the name of the instance if it's a named instance or even better, by
creating and using an alias (in the Sql-Server Network Client Utility or
something like that) as the address of the server. By creating an alias,
you're pretty sure to use exactly the protocol that you want and not some
old cr*p like the Multi-protocol. By using a TCP/IP address (192.168.100.2
for example) for TCP/IP; you're also short-circuting any problem with name
or domain resolution.

Does this problem occurs only for the first table to open?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"REC" wrote in message

> Environment: Access 2003 front-end, SQL Server 2005 back-end with a named
> instance.
>
> I need to use the SQL Server driver instead of the Native Client driver
> when
> linking the table since the former is not available in all our pc's. On
> the
> first attempt to open one of these linked tables, I get the SQL Server
> Driver
> Timeout Expired error. But if I immediately try to open the table a
> second
> time, the error goes away and I can connect to the table. The same error
> occurs even for tables with only 1 record. (Note: the Native Client
> driver
> works, but it is difficult to distribute the driver to the client pc's
> since
> our pc's are in a locked down evironment.)
>
> Is there some setting that I need to change? Is there a way to trap this
> error, perhaps in a table linking code? Am I beating a dead horse? I
> have
> spent days researching/testing and nothing has fixed this error. Missed
> my
> deliverable date Thanks in advance for any assistance.
 >> Stay informed about: Linked Tables - ODBC SQL Server Driver Timeout Expired 

Thank you, Sylvain for your suggestions. I will try them out when I get to
work today.

To answer your question, yes it happens only to the first table I try to
open. Once I open this first table using the trick I described, then all the
other tables work fine.

"Sylvain Lafontaine" wrote:

> Maybe a problem with the discovery service or for finding the right port to
> use if you are using a dynamically allocated port. Could also be a problem
> with the DSN or the AD server on your network.
>
> Try to force to use either the Named Pipe or the TCP/IP protocol by adding
> the name of the library in the connection string (Network=DBMSSOCN; in the
> case of TCP/IP, see
> http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer ),
> by using the prefix np: or tcp:, by adding the port number at the end, by
> adding the name of the instance if it's a named instance or even better, by
> creating and using an alias (in the Sql-Server Network Client Utility or
> something like that) as the address of the server. By creating an alias,
> you're pretty sure to use exactly the protocol that you want and not some
> old cr*p like the Multi-protocol. By using a TCP/IP address (192.168.100.2
> for example) for TCP/IP; you're also short-circuting any problem with name
> or domain resolution.
>
> Does this problem occurs only for the first table to open?
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "REC" wrote in message
>
> > Environment: Access 2003 front-end, SQL Server 2005 back-end with a named
> > instance.
> >
> > I need to use the SQL Server driver instead of the Native Client driver
> > when
> > linking the table since the former is not available in all our pc's. On
> > the
> > first attempt to open one of these linked tables, I get the SQL Server
> > Driver
> > Timeout Expired error. But if I immediately try to open the table a
> > second
> > time, the error goes away and I can connect to the table. The same error
> > occurs even for tables with only 1 record. (Note: the Native Client
> > driver
> > works, but it is difficult to distribute the driver to the client pc's
> > since
> > our pc's are in a locked down evironment.)
> >
> > Is there some setting that I need to change? Is there a way to trap this
> > error, perhaps in a table linking code? Am I beating a dead horse? I
> > have
> > spent days researching/testing and nothing has fixed this error. Missed
> > my
> > deliverable date Thanks in advance for any assistance.
>
>
>
 >> Stay informed about: Linked Tables - ODBC SQL Server Driver Timeout Expired 

Oops I guess my last post didn't go through...

First and Foremost, THANK YOU Sylvain for your help! You just made my day.
If you were running for politics, I would vote for you. Now I can do some
real development work with this out of the way.

The fix (please let me know if any of the settings are unnecessary):

1) Go to SQL Server Configuration Manager
2) Click Protocols for MyInstance.
3) TCP/IP Properties:

Protocol tab:
Enabled=Yes
Keep Alive=30000
Listen All=No
No Delay=No

IP Addresses tab:
IP1:
Active=Yes
Enabled=Yes
IP Address=xxx.xxx.xxx.xxx
TCP Dynamic Ports=4454 (automatically assigned)
TCP Port=blank
IP2:
Active=Yes
Enabled=No
IP Address=127.0.0.1
TCP Dynamic Ports=0
TCP Port=blank
IPAll:
TCP Dynamic Ports=1072 (automatically assigned)
TCP Port=blank

4) Restart SQL


"REC" wrote:

> Thank you, Sylvain for your suggestions. I will try them out when I get to
> work today.
>
> To answer your question, yes it happens only to the first table I try to
> open. Once I open this first table using the trick I described, then all the
> other tables work fine.
>
> "Sylvain Lafontaine" wrote:
>
> > Maybe a problem with the discovery service or for finding the right port to
> > use if you are using a dynamically allocated port. Could also be a problem
> > with the DSN or the AD server on your network.
> >
> > Try to force to use either the Named Pipe or the TCP/IP protocol by adding
> > the name of the library in the connection string (Network=DBMSSOCN; in the
> > case of TCP/IP, see
> > http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer ),
> > by using the prefix np: or tcp:, by adding the port number at the end, by
> > adding the name of the instance if it's a named instance or even better, by
> > creating and using an alias (in the Sql-Server Network Client Utility or
> > something like that) as the address of the server. By creating an alias,
> > you're pretty sure to use exactly the protocol that you want and not some
> > old cr*p like the Multi-protocol. By using a TCP/IP address (192.168.100.2
> > for example) for TCP/IP; you're also short-circuting any problem with name
> > or domain resolution.
> >
> > Does this problem occurs only for the first table to open?
> >
> > --
> > Sylvain Lafontaine, ing.
> > MVP - Technologies Virtual-PC
> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >
> >
> > "REC" wrote in message
> >
> > > Environment: Access 2003 front-end, SQL Server 2005 back-end with a named
> > > instance.
> > >
> > > I need to use the SQL Server driver instead of the Native Client driver
> > > when
> > > linking the table since the former is not available in all our pc's. On
> > > the
> > > first attempt to open one of these linked tables, I get the SQL Server
> > > Driver
> > > Timeout Expired error. But if I immediately try to open the table a
> > > second
> > > time, the error goes away and I can connect to the table. The same error
> > > occurs even for tables with only 1 record. (Note: the Native Client
> > > driver
> > > works, but it is difficult to distribute the driver to the client pc's
> > > since
> > > our pc's are in a locked down evironment.)
> > >
> > > Is there some setting that I need to change? Is there a way to trap this
> > > error, perhaps in a table linking code? Am I beating a dead horse? I
> > > have
> > > spent days researching/testing and nothing has fixed this error. Missed
> > > my
> > > deliverable date Thanks in advance for any assistance.
> >
> >
> >
 >> Stay informed about: Linked Tables - ODBC SQL Server Driver Timeout Expired