Use the SQL Server Configuration Manager to create aliases; under the SQL
Native Client Configuration because an alias is a client thing. When you
specify the port number, you don't have to specify the instance name because
each port can be associated only with a single instance. When you there,
make sure that the Named Pipe Protocol and/or the TCP/IP protocol are
enabled on the client side (Client Protocols) if you want to test them.
You must also check on the *server side* that both protocols are enabled for
Local and Remote Connections by using the SQL-Server Configuration Manager -
Surface Area Configuration for Services and Connections on the server.
For the port number, look under the SQL Server 2005 Network Configuration of
the SQL-Server Configuration Manager. Expand the nodes at the left
(*Important*!!!, expand the nodes at the left, do not use the list of node
in the right window) and chose the Protocols For SQL 2005. From there,
chose the TCP/IP protocols on the Right (must be enabled) | Properties
(right click of the mouse) | IP Addresses Tab | Chose the right IP Address
or use the IP ALL item at the bottom. Notice that SQL-Server can listen to
many tcp/ip address, each one with a possible different port number.
From the BOL:
** Static vs. Dynamic Ports***
The default instance of SQL Server listens for incoming connections on port
1433. The port can be changed for security reasons or because of a client
application requirement. By default, named instances (including SQL Server
2005 Express Edition) are configured to listen on dynamic ports. To
configure a static port, leave the TCP Dynamic Ports box blank and provide
an available port number in the TCP Port box.
***Dynamic Ports***
At startup, when an instance of SQL Server is configured to listen on
dynamic ports, it checks with the operating system for an available port,
and opens an endpoint for that port. Incoming connections must specify that
port number to connect. Since the port number can change each time SQL
Server starts, SQL Server provides the SQL Server Browser Service, to
monitor the ports, and direct incoming connections to the current port for
that instance. Using dynamic ports complicates connecting SQL Server through
a firewall because the port number may change when SQL Server is restarted,
requiring changes to the firewall settings. To avoid connection problems
through a firewall, configure SQL Server to use a static port.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
barnowl said:
Thanks for your respose Sylvain
Absolutely ADP is local - tests if current drive is a network and won't
run
if it is .
I think we are talking about a single server machine though I will ask the
question.
If I use URL and port how do I find the port to use?
Does the instance still go on the end eg 10.10.2.1,1535\SQL2005 ?
Afraid I don't know about Aliases and how to create them. Could you
explain
please?
Thanks
Sylvain Lafontaine said:
Did you make sure that all of your users have each one their own copy of
the
ADP database file? Second, is this server on a cluster with (more or
less)
frequent switching between nodes? For how long did it take before the
same
errors came back again?
The size of the ADE file and the fact that this is a named instance
should
not be a problem. However, in the case of a named instance, the
SQL-Browser
Service is necessary for the SQL OLEDB Provider to discover the port to
use
to communicate over TCP/IP; so maybe by specifying the TCP/IP address of
the
server followed by the port number (10.10.2.1,1535 instead of the
TheServerName) for example would be better. You can even try to create
and
use an Alias for the server instance. This will take the SQL-Browser
Service out of the equation.
A second possibility would be to use the Named Pipes protocol instead of
TCP/IP or vice-versa. Add the prefix tcp: or np: before the name of the
server and in the case of tcp:, don't forget to try with or without the
port
used by the instance. Instead of using these prefixes, you can define
and
use an Alias, of course. With an Alias, you have a much better control
over
the protocol and the port used for the communication.
For the rest, I'm not a network expert myself.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)
barnowl said:
Thanks for your response, Sylvain. Unfortunately problems are still
occuring.
I totally rebuilt the ADP and made ADE on site.
I tried giving the end users the ADP rather than the ADE. Initially it
went
quiet, but now the same errors are coming in.
[DTNETLIB] [ConnectionWrite(send()) General Network Error
is still occuring. Sometimes it appears that Global variables are being
lost.
Sometimes cant find forms that patently are there.
They say that no other application is having problems. There is a Net
based
app from another 3rd party on the same instance of SQL that seems to
be
fine. It is a named instance - would that make any difference? No
error
is
consistent.
Could it be adp is too big - it compiles down to about 10 MB - should I
be
thinking of splitting it up into libraries. Or are there some sql
server
settings I should be looking at? In theory the network hardware is
newer
and
better and they would expect less problems not more. I am more a
developer
than a network expert, and getting a bit desperate now.
:
Importing all objects into a blank ADP is the best thing. Also, some
people
don't recommend to use the /decompile switch because it has not been
fully
tested by MS but are suggesting to use instead the old trick of adding
and
removing a dummy reference in the References dialog window of the VBA
IDE.
Personally, I use the /decompile switch but I'm also using it with the
/repair /compact switches at the same time.
In your case, I suspect that upgrading the SQL-Server from 2000 to
2005
is
not the only thing that has changed. Maybe the new instance don't
have
enough memory or is on an overloaded system or on a (virtual) cluster
with
frequent switching between nodes?
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and
SQL-Server
(French)
Thanks Sylvain
I did most of this - built the ade from adp on target machine but
did
the
decompile on my own system before delivery. Will try the decompile
on
site.
Would there be any difference between
a) use Access Decompile switch option and
b) import all objects into a blank ADP?
:
ADE are like MDE and as such, will often exhibits strange behavior
when
the
target machines are not strictly identical in term of Windows
versions,
Service Pack applied, etc.
If you have not recreated the ADE after the upgrade to SQL-Server,
you
should do it. The best course of action would be to fully
decompile
the
ADP
file, copy it on the target machine and create the ADE from there.
Using
the ADP instead would be even better.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and
SQL-Server
(French)
My customer has moved his SQL to 2005 from 2000.
The new db is a named instance - previously it was not.
I have upgraded my back end database and reconnected the client
which
is
Access ADE. Now frequent errors occur (3 - 4 per day per user) -
Connection
Failure (Sometimes [DBNETLIB][ConnectionWrite(send())] but also
failure
to
fill list / combo boxes, and failure to open forms and reports.
The
problem
is always temporarily resolved by restarting the ade. Happens
with
Access
2003 and 2007. I identified a message form running on a timer as
a
possible
culprit and gave them a means to switch it off. Although this
has
perhaps
reduced it, it has not cured it. Any suggestions would be
appreciated.
