MSSQL Server connectivity

Hi,

I have two Windows machines in the Local Area Network. One of the machines has MSSQL Enterprise Server 8.0 installed whereas the other has MSSQL Server 2005 installed. When I try to establish the connectivity between these two servers, I can not do so. When I try to look up Network servers, the two machines can't see each other. How can I make them talk to each other? Is there some type of driver or some such thing to be installed which can help the matter?

Please let me know.

Need a bit more information to help you:

Are the instances default or named?
Do you have any firewall software running on either server?
What is the error msg you're getting?
Can you use SQLCMD to connect to the remote server?

|||

Thanks for the response.

Answers to your questions:

1. The MSSQL Server instances are the default ones.

2. I don't think I have a firewall running on the servers.

3. When I go to SQL Server Management Studio and try to register the server by looking on the network, I can not find my other machine with Enterprise Manager 8.0. So I don't get any error, but I can not see the machine either.

4. Login failed for user 'xyz'.
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

This is the error that I see on the command prompt.

Here's what I suggest: You may want to go through my answers above, but I think I don't know much about this new SQL version? Can you quickly list a set of steps I can follow to connect from SQL Server 2005 to another machine having SQL Enterprise Manager 8.0 running on it? Thanks in advance.

|||

Enterprise Manager has nothing to do with remote connectivity. This is what I understand you're doing:

Machine 1: Running SQL Server 2000, default instance

Machine 2: Running SQL Server 2005, default instance

From Machine 2 running SQL Server Management Studio you want to connect to Machine 1.

First, make sure that Machine 1 is setup to accept remote connections. This is documented in Books Online for SQL Server 2000.

You'll want to make sure you're providing the correct credentials: Windows or SQL Server depending on how the instance is configured.

|||

Hi Dan,

Thanks for the response. However, we somehow managed to do the connectivity between the 2 servers. Apparently, the SQL user which we were using to establish the connectivity did not work initially. We dropped and recreated the user and then it went through fine. Don't know why it did not connect initially.

I have another problem though. And I would really appreciate if someone could help with this real quick.

So, there's this MSSQL 2005 installed on a new machine, which has 40 G on C Drive and 250+ G on a D Drive. But all of my C Drive has been taken up by "tempdb.mdf". It has taken up nearly 26 G of space on this drive. Why should that be the case? I know that this is a System database that gets created when one installs MSSQL Server, but I have never seen it this big. Is there a problem with the installation that I can fix? Please let me know.

Thanks.

|||This is certainly suspicious. I'm not expert on tempdb so I recommend you post this issue to http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1. Someone there will certainly be able to help you.