Skip navigation

Questions, Answers, and Tips About SQL Server - 01 Sep 1998

SQL Server database administrators can now turn to several new resources. Check out the Web site ( for Windows NT Magazine's forthcoming sister publication SQL Server Magazine. Also, in June Microsoft posted updated Intel and Alpha versions of the SQL Server 6.5 Programmer's Toolkit (approximately 3.5MB­ The official site for SQL Server 7.0 beta products is Finally, check out the Microsoft Knowledge Base articles "INF: How to Manually Install SQL Server Books Online" ( and "INF: Missing System Errors in Books Online for SQL Server 6.5" (

What is the SQL Server Internet Connector, and do I need one to put my SQL Server on the Web?

The SQL Server Internet Connector is a software license, which Microsoft describes as follows: "The SQL Server Internet Connector is an add-on product, required for any customer connecting a Microsoft Internet Information Server or any equivalent third-party Web server to Microsoft SQL Server. It is purchased independent of SQL Server and SQL Server Client Access Licenses (CALs), and allows an unlimited number of Internet connections to a single SQL Server."

Here's our translation. First, calling SQL Server Internet Connector a product is a stretch. It's really just a piece of paper, because no software is involved. The SQL Server Internet Connector is a license that Microsoft requires for connecting a client to SQL Server through any Web server, regardless of who made it. The SQL Server Internet Connector license comes in several versions, and the restrictions depend upon whether the Web client is an Internet user or an intranet user.

SQL Server Internet Connector 6.5 lets an unlimited number of public Internet users connect to SQL Server through a Web server and costs a flat $2995. However, Microsoft defines an Internet user as "any person currently connected to the Internet, other than a person: (i) employed by you (as an employee, independent contractor, or in any other capacity) or (ii) otherwise providing goods or services to you (for example, one of your suppliers) or on your behalf (for example, one of your distributors or resellers, or a consulting firm hired by you)."

The original SQL Server Internet Connector 6.0 let an unlimited number of users connect to SQL Server through a Web server--even if those users were on an internal intranet--but Microsoft quickly realized the loophole this license created and closed the door. You might be eligible for a break if you purchased version 6.x before July 1, 1997. You don't have to upgrade your license to SQL Server Internet Connector 6.5, so you can still let an unlimited number of Web users connect to the Internet Connector 6.0 or 6.5­but only if you purchased the license before July 1, 1997.

The newest versions of SQL Server Internet Connector require all intranet and extranet users to purchase an individual client license. The Internet Connector is obviously a great deal for public Internet users because an unlimited number can connect for $2995, but intranet users still need the same number of CALs that are required without going through a Web server. Both the CALs and SQL Server "Server Software" licenses contain the following multiplexing clause:

"No 'Multiplexing' or 'Pooling'­Use of software or hardware that reduces the number of users directly accessing or utilizing the Server Software (sometimes called 'multiplexing' or 'pooling' software or hardware) does not reduce the number of CALs required­the required number of CALs would equal the number of distinct inputs to the multiplexing or pooling software or hardware 'front end.'" This clause means you must buy individual licenses even if you're using a technique such as Open Database Connectivity (ODBC) connection pooling, but Microsoft doesn't mention the SQL Server Internet Connector.

By the way, don't confuse the SQL Server Internet Connector with the SQL Server Internet Database Connector (IDC). The IDC is an Internet Server API (ISAPI) application that provides ODBC connectivity to browsers via a Web server. The IDC was Microsoft's first Web-database connectivity solution and is very simplistic compared with current options, such as Active Server Pages (ASP).

How can I quickly see the approximate number of rows in each table of a database? I need this information for management reports, and SELECT COUNT(*) for all the tables takes a long time to run.

Database administrators frequently ask this question, especially when they're trying to get a quick handle on someone else's database­to get a rough idea of how data is distributed among the tables and which tables are the biggest. The biggest tables tend to be the most heavily updated and the most heavily queried. Running SELECT COUNT(*) commands for all the tables takes a while to run and might affect the performance of the customer's server, so you can cheat by using some information from the system tables.

Every database contains a special system table called sysindexes, which contains information about indexes and tables in the database. You get a feel for the tables' size by looking at the rows and dpages columns in the sysindexes table, as Table 1 shows. Rows tells you how many rows are in the table, and dpages shows how many 2KB data pages the table is using. These values are approximations; you wouldn't use these numbers if you needed an exact count.

The id column tells you which table the sysindexes entry refers to, and the indid tells you what type of entry it is. An indid = 0 means the table has no clustered index and the entry refers to the base table. An indid = 1 means the entry refers to a clustered index. Indids of 0 and 1 are mutually exclusive--either a table has a clustered index or it doesn't­ so you can get a list of all the user tables in a database and the number of rows and 2KB data pages in use by running the query in Listing 1.

I want to use bulk copy program (bcp) and the money datatype to export data from a column. How can I eliminate the commas embedded in the amount?

Bcp always uses the default money format (which includes commas if you're using the us_english language character set) to export data, as you see in Listing 2. You can't eliminate the embedded commas directly, but you can get around the problem by creating a view and exporting data out of the view. Listing 3, is a script that creates a view on MoneyInfo that uses the CONVERT function to delete the commas.

How can I tell which service pack is running on SQL Server?

Just type SELECT @@version using Interactive SQL (ISQL), ISQL/w, SQL Enterprise Manager (EM), or any other tool that lets you submit SQL queries to the server. The result will include a line such as

Microsoft SQL Server 6.50 - 6.50.240 (Intel X86)

FIGURE 1: SQL Server Service Pack Query
6.50.201 = Original SQL Server 6.5 release
6.50.213 = SQL Server 6.5 with SP1
6.50.240 = SQL Server 6.5 with SP2
6.50.258 = SQL Server 6.5 with SP3
6.50.281 = SQL Server 6.5 with SP4
7.00.717 = SQL Server 7.0 Beta 3
Figure 1 shows the possible results and their interpretations. These codes are for Intel systems running SQL Server 6.5. Alpha systems have different service packs, and SQL Server 6.0 for Intel, Alpha, and MIPS also had three service packs for each platform.

After what appears to be a normal shutdown of SQL Server 6.5 Service Pack 4 (SP4), I see in the error log on startup that the server still recovers transactions on my database. When the shutdown occurs, no transactions are open and no users are connected. Why is SQL Server rolling transactions forward? Where did they come from?

According to SQL Server Books Online (BOL), SQL Server issues an implicit CHECKPOINT command whenever you stop SQL Server normally with the SHUTDOWN command or from the service control manager. Following a normal shutdown (a quiesce, which includes a CHECKPOINT), SQL Server usually doesn't authenticate any more logons but lets transactions in process finish and COMMIT. The portion of the transaction (modifications, commit record) that follows the system CHECKPOINT on shutdown will reside on the transaction log until the next time you start the server. The server always goes through this recovery process on restart, no matter how it was shut down previously. It will always do a backward repair, forward repair, roll back, and roll forward, as long as the transaction log is intact. In other words, what you're observing is normal.

Briefly, the likely cause of SQL Server's always rolling forward one transaction when SQL Server starts is a database that has the trunc.log on chkpt option enabled. This option causes the database's transaction log to be truncated each time the checkpoint checking process issues a CHECKPOINT. And because SHUTDOWN performs a CHECKPOINT before stopping SQL Server, it truncates the log at that time. A CHECKPOINT keeps track of all uncommitted transactions, so the CHECKPOINT record in the log shows the dump process from the log truncation as an open transaction when the CHECKPOINT was issued.

When you restart SQL Server, it scans the transaction log of each database, rolls back any uncommitted transactions, and rolls forward transactions that completed but didn't write to disk during the last CHECKPOINT. When SQL Server encounters the CHECKPOINT record that shows the dump process as open, SQL Server rolls forward that transaction to show that it had completed. That roll-forward action is the source of the message. Microsoft Knowledge Base article Q81340 ( explains what happens.

You can create devices on an NTFS partition very quickly, but creating the same device on the same disk using a FAT partition takes forever. Is something wrong with my system?

The difference in speed on the partitions is no cause for worry, but you can't do anything about it. The difference in speed is a result of the differences between NTFS and FAT. When SQL Server creates a device, it zeros out all the space on the device file. Unlike FAT, NTFS doesn't need to physically rewrite the entire file to ensure that it's clean, so creating a device on an NTFS partition usually takes seconds, regardless of how big the device is. However, creating a device on a FAT partition could take from minutes to hours, depending on the size of the partition and the speed of your disks. We recommend that you use the NTFS file system, not only because of its speed, but because NTFS is the file system of choice for BackOffice servers.

What causes a SQL Server 6.5 Service Pack 4 (SP4) system to show configuration options such as symmetric multiprocessing (SMP) concurrency and remote authentication (RA) options in the Server Configuration tab?

Some options within sp_configure are considered advanced, and you can view them only if you've enabled the show advanced options setting by running

EXEC sp_configure "show advanced options", 1


This setting lets you see all the configuration options--whether you're viewing them from the sp_configure Transact SQL (T-SQL) interface or the SQL Enterprise Manager (EM) GUI interface (which you bring up by right-clicking Server in SQL EM). Be careful about who has access to these settings. They're the SQL Server equivalent of the Registry.

Can I install SQL Server on a Windows NT Workstation system?

NT Workstation 4.0 and NT Server 4.0 are legally distinct, and so are SQL Workstation 6.5 (which costs only $499) and SQL Server 6.5. You can physically install SQL Server on an NT Workstation system, but this installation isn't legal. When you mix entities, you're constrained by NT Workstation's limitations--a maximum of two processors, 10 concurrent users, and one dial-in connection. You can't use an NT Workstation system as a Primary Domain Controller (PDC) or Backup Domain Controller (BDC). Windows knows whether a system is running NT Server or NT Workstation from Registry settings.

To install SQL Server on a standalone NT Workstation system, select the Microsoft Loopback Adapter in place of an NIC. In SQL Server 7.0, however, you won't be able to install the server edition on an NT Workstation system.

Our development house has an ISDN connection between our site and the client. The problem is, whenever we start SQL Enterprise Manager (EM), it kicks in the router and keeps the connection open. How can I prevent this? Surely we don't need to register and unregister the servers every time! I'm concerned about very large phone bills.

SQL EM wants to check the status of the SQL Server services so it can show the stoplights properly. In your Registry, under HKEY_CURRENT_USER/Software/Microsoft/MSSQLServer/SQLEW/PollingInterval, you probably have a key called EnableServerPolling set to a 1. Our sources at Microsoft say they think that if you change it to 0, you'll turn off that check.

Recently I got an Error 2620 (Severity Level 21) and had to reload my database. What happened?

In the spring, Microsoft posted Knowledge Base article Q165166, "FIX: Load Tran May Cause 806, 605, 2620, or 6902 with IRL On" (, describing an unfortunate situation that can occur if SQL Server has Insert Row Locking on and stops while it is loading a transaction log. The database is left in an unknown state, and you'll probably get an Error 2620: The offset of the row number at offset %d does not match the entry in the offset table of the following page when you restart SQL Server.

Because the process of loading the transaction log (which copies database pages and recovers the database) was interrupted, SQL Server can't guarantee the state of the current database because the reload wasn't completed. The solution: Reload from a known database state. Microsoft fixed the problem in SQL Server 6.5 Service Pack 3.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.