SQL 2000 Issues

[19972003]

Hi, sql 2000 server uses port 1433; say if the client tries to connect the SQL server instance, should I use port 1433 to connect to the instance of the SQL server or use dynamice port? also,should I use user dsn or system dsn or file dsn? does this thread apply to my situation? what would be the best way to connect to the sql database from the client end (front end is ms-access)?

Many thanks,

thread706-1086592

 

[mrdenny]

If you are using the default instance then typically SQL will use port 1433. Named instances will typically use dynamic port numbers. It's easier to have the client machine use dynamic ports to connect to the server. This way if the port number changes you don't have to update the connection string.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

can you give me some example please? what do you mean the default instance and name instances? when I add the dsn, I just add name and then server alias and tcp/ip; server name.

 

[mrdenny]

SQL Server allows you to install more than one instance on each server. The default instance is referenced by just using the server name. Named instances are called by the server name and then the name of the instance. If you were connecting to the server called machine1 and the instance inst001 you would connect to machine1\inst0001.

Each instance of SQL Server listens on it's own TCP port.

If you are calling the SQL Server by the servers name only then you are connecting to the default instance.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

Configure the server alias on the client computer.

SQL Server 2000
a. Start the Client Network Utility.
b. On the General tab, verify that TCP/IP appears in the list under Enabled protocols by order.
c. Click the Alias tab, and then click Add.
d. Under Network libraries, select TCP/IP.
e. In the Server name text box, type the IP address of the server that is running SQL Server 2005.

Note The IP address that you type here is the one that is configured to use the TCP/IP port number.
f. Click to clear the Dynamically determine port check box, and then type the port number of the instance of SQL Server 2005 in the Port number text box. (do I use port 1433?)
g. Type a name in the Server alias text box, and then click OK. (do I really have to use the name or I can use Ip address)

 

[19972003]

If I am going to connect to one of the databases onthe sql server, (this database is being access by over 20 people from the terminal server) do I use user dsn or system dsn? if I created user dsn and system dsn, the file dsn, am I going to create conflicts for the systems?

 

[mrdenny]

You can use what ever type of DSN you would prefer. You can create all three with the same name without a problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[19972003]

do you have any suggestions of how to make the access 97 to talk to sql 2000 database more efficiently? when I try to run the query from the terminal server I got "connection to server 10.3.2.8 failed", sometime it runs about at 20 minutes and it failed; if I run it on the sql server itself, it is fine. thoughts? I was just thinking that my dsn was not setup correctly.

 

[drtree]

With Access, I usually just try to do any processing on SQL Server. So if there are any queries that you see yourself running a lot (and the data all lives on SQL Server), I would try to create them as Views on SQL Server, so the processing is done there. I've done that with a lot of Crystal Report reports that I've built and it's helped speed them up.

As far as that error goes - how's the connection from your Terminal Server to your DB server? If it works fine on your client machine, but not the Terminal Server, there may be some settings missing on the Terminal Server.

HTH,

Doc Tree

 

[19972003]

how do you create them as views on sql server?