TechNetTop Questions - July 24, 2000

In this issue, we have a couple of SQL Server questions, and a couple of Windows NT related questions. The first SQL question "SQL Server Will NOT Shut Down" is not one of those neatly wrapped up issues where a suggestion or Knowledge Base article wraps things all up in a nice little "Resolved Bundle". Not every problem can be resolved that way. Sometimes just providing some suggestions to try that might narrow the problem helps a lot.

On This Page

SQL Server Will NOT Shut Down
What the Heck is going On With SQL Server 7.0?
Removing Multiple Shares
Proper way to Remove BDC

SQL Server Will NOT Shut Down

Q: I have a PC with Windows NT Workstation 4 SP 6a. After installing SQL 6.5, it no longer shuts down or restarts. The only way to shut it down is to hit the reset button or power button. It appears to start the process normally, then we just get the hourglass and nothing happens. You can leave it sit for days and nothing would happen. I have tried reinstalling the service pack for Windows NT and that did not work. As soon as you uninstall SQL, everything works fine. I have another machine that is almost identical hardware and software wise and it works fine. Any help is greatly appreciated. This is a machine that must have SQL 6.5 running on it, but not being able to shut it down or restart it is a major problem.

Thanks in advance
Joe User

A: Hey Joe,

Here's a suggestion:

Read, study, and follow information contained in Knowledge Base article "192710 - INF: Basic Guidelines for Installing SQL Server Version 6.5 or 7.0".

If the suggestion above is absolutely no help, then...

We'll assume that you are running the Standard Edition on Windows NT 4.0 Workstation.

You say that you have another machine that is *almost* identical... it's the same except for what?

What does latest SQL Server errorlog display at the very last few lines? This errorlog should be in the \MSSQL\LOG folder.

Any clues logged in the Application or System log of NT Event Viewer?

What network protocols are configured?

How much memory is installed on the problem machine?

What services are running on the "problem" machine that are *not* running on the "almost identical", non-problematic machine?

What type of partition (FAT, NTFS) is SQL Server installed on?

Is the installation CD being used to install SQL Server on the 'problematic' machine the same one used to install SQL Server on the machine that does *not* experience shutdown problems or is it a different CD?

What *SQL Server* Service Pack version is applied? Details for determining this are in Knowledge Base article "202051 - INF: How to Determine SQL Server 6.x Service Pack Installed".

Then, using the Knowledge Base search for any related issues that may have been fixed in a subsequent SQL Server Service Pack. If you are running Service Pack 5a, then that's the latest and the last one that was produced for 6.5. SQL Server 7.0 came out a couple of years ago and SQL Server 2000 will be out later this year. No more Service Packs for 6.5.

What the Heck is going On With SQL Server 7.0?

Q: I have one database that I am upgrading from MS SQL 6.5 to MS SQL 7.0 on two separate systems.

The character sets and sort orders are the same. I have verified this via sp_helpsort.

I executed DBCC checks before the upgrade. There were no errors. There were no errors returned during the upgrade. I am using the Upgrade Wizard.

However, when I run a query on both systems without the ORDER BY clause, it will return the query with the same number of records and the same data but not in the same order.

Why is this?

Michael Henderson

A: Michael,

Do you want to know what I think (that was really a rhetorical question)? I think that it really doesn't matter - nor should one be concerned about the result row order of an 'un-ordered by' query - IF the data being returned is exactly the same (only ordered a little differently). Since one of the fundamental properties of a relational database is that the ordering of rows is immaterial - in the end, it just doesn't matter.

If the question is an academic question (that is, Michael, you're just one of those curious-type IT Pros), then one reason the result order would be different between the two instances (6.5 and 7.0) could be attributed to different indexing schemes between SQL Server 6.5 and 7.0 (i.e. a clustered index on one and none/different clustered index on the other).

But that's probably not what's going on. The phenomenon that you're seeing is most likely related to the fairly significant enhancements made to SQL Server 7.0 index design. It is pretty darn near impossible to say for sure without knowing a lot more about the data, indexing scheme, and the query you're executing.

The common reason is that SQL Server 6.5 used a different method to generate the results. While many operations in 6.5 used a sort behind the scenes to generate the results (it could only do nested loops), 7.0 has many more options available and often the sort isn't necessary, thus you'll see the results in a different order.

There is another issue that isn't applicable to the scenario that you describe in your question. If you install SQL Server 7.0 on a second machine and then use the Data Transformation Services (DTS) to copy tables from SQL Server 6.5 to SQL Server 7.0, DTS does not copy identity attributes, indexes, primary keys, or other constraints. This is by design, and is documented in SQL Server 7.0 Books Online. Michael, in your question, I did read that you used the Upgrade Wizard, which does transfer all objects (like indexes), but I thought I'd mention this issue with DTS in case anyone else got any bright ideas.

So... the bottom line is: "If you want the results sorted in a certain order, then use an ORDER BY clause.

Hope this clears things up.

Removing Multiple Shares

Q: I have about 300 shared directories I want to remove the shares from. Is there a way to do this all at one time or do I have to remove each share individually. Thanks for any help.

Dawn B.

A: Good Morning, Dawn!

(I had to say that – I couldn't resist.) Yes there's an easy way to do this. You will need to get your hands on either the Windows NT 4.0 Server Resource Kit or the Windows NT 4.0 Workstation Resource Kit.

Each of these resource kits contains a "tool" that makes this job much, much easier - although it's not a tool unto itself, rather it's an extension to Explorer. The following is a blurb from the resource kit that describes this extension, which is called "SHAREUI". It's really easy to install and use, and makes managing a lot of shares pretty much painless.

Description:

SHAREUI

This stand-alone extension of Explorer makes it easier to manage network shares.

ShareUI is a special shell folder that allows you to view, add, remove, and configure the properties of network shares for any local or remote computer that you have permission to administer. Network shares are objects that represent shared directories on a computer.

With ShareUI, you can configure all the directory sharing for a computer from a single window. Without ShareUI, you must manually find all shared directories on a computer by using Explorer, and configure them individually. In addition, without ShareUI the only way to administer shares on remote computers is to use Windows NT Server Manager, which is not integrated with the new shell.

Only users that have permission to view and configure shares on a remote computer will see the Shared Directories folder on that remote computer. On the local computer, the Shared Directories folder will still exist, but no configuration operations will be available—the user will only be allowed to view the network shares.

Now, off you go! Banish those shares you no longer need!

Proper way to Remove BDC

Next we have a straightforward question and an accompanying straightforward answer. This question may have popped into other IT Pro's minds, so including it here in this edition of Top Questions might help others that have wondered. The question, posted by IT Pro "Kevin":

Q: Is there a proper way to remove a BDC from a domain? Or do you just shut down the server and remove it from server manager?

TIA,

Kevin

A: Amiri Jones replied:

That pretty much sums it up....

Don't forget to remove the BDC from the PDC's replication list if you are using directory replication.

Regards,

Andrew Dadmun
Senior Network Engineer - e-Builder, Inc

Thanks Amiri and Andrew! Yup, that's pretty much it – do the following and then remove the computer account from the domain.

Some details that might help:

To remove the BDC from the replication list, on the PDC, click Start, then Settings, Control Panel, and Server. You should see the following window:

Click Replication and then click Remove under Export Directories to remove a computer name or domain name from the To List. Click Remove under Import Directories to remove a computer name or domain name from the From List.

Now, just to be complete, details on removing the computer account:

At the primary domain controller (PDC), remove the computer account for the BDC from the domain:

1. In the Server Manager list, click the BDC.

2. On the Computer menu, click Remove From Domain.

3. To confirm the removal, click Yes, and then click OK.

At the PDC, synchronize the entire domain:

1. In the Server Manager list, click the PDC.

2. On the Computer menu, click Synchronize Entire Domain, and then click Yes.

That's it! Pretty easy, huh?