Questions, Answers, and Tips - 16 Jun 2000Questions, Answers, and Tips - 16 Jun 2000

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected].

I use the Control Panel ODBC Data Source Name (DSN) applet to see which ODBC drivers are installed on a system. Is a similar utility available that shows the OLE DB providers installed on a system? I usually use Data Transformation Services (DTS) or SQL Server Enterprise Manager to check the installed OLE DB providers, but a better way must exist.

A better way does exist to find which OLE DB providers are installed on a system, but the solution isn't immediately obvious. After you know the trick, however, the solution is simple and powerful. In general, OLE DB providers don't create DSNs in the Registry as ODBC does. Instead, SQL Server stores OLE DB connections in a special type of file called a Universal Data Link (UDL). UDL (.udl) files are available after you install Visual Basic (VB) 6.0, Microsoft Data Access software development kit (SDK) 2.0, or Microsoft Data Access Components (MDAC) 2.x runtime components.

You use UDLs to create and store new connection strings for an OLE DB provider, but you can also use UDLs to browse all installed OLE DB providers on your machine. Depending on your configuration, you can create a new, empty UDL by simply right-clicking your Windows desktop, pointing to New, then selecting MS Data Link. By default, you won't see the MS Data Link option on a Windows 2000 or Windows NT desktop, but you still have access to the UDL editor. Just create a new file with a .udl extension. Open the file, and Windows will launch the UDL editor. If this method doesn't work, you likely don't have the right data access software installed on your machine, and UDLs are simply unavailable.

UDLs are also handy for building OLE DB provider connection strings to embed in an application. Create a .udl file setup, then open the file in Notepad. You'll see a simple text string that you can paste into your application as the OLE DB provider connection string. You can also download the MDAC MS Component Checker, which provides information about a machine's MDAC installation, including installed OLE DB providers. You can find this utility at http://www.microsoft.com/data/download.htm.

I have SQL Server 6.5 Service Pack 5a (SP5a) installed on my system. Every day in a single transaction, I transfer a large amount of data—about 60,000 rows—into a table containing about 7 million data rows. The process worked until yesterday, when I received the message run out of locks. Following are the configuration values of locks:

LE threshold maximum;1000LE threshold minimum;1000LE threshold percent;0locks;100000

The problem occurred when I inserted the data. The exclusive page locks didn't escalate to an exclusive table lock as I expected, and the page locks eventually exceeded 100,000, the maximum number of locks I configured for the locks parameter. Then, the server didn't respond until I restarted it. I added the TABLOCKX optimizer hint to the query, but I received the same message. How can I escalate the page locks to table locks during inserts?

Your problem calls for a two-part answer. First, based on the problem you described, I assume you're using a transaction to insert one row at a time into the huge table instead of using one INSERT INTO SELECT FROM command that inserts many rows at once. SQL Server 6.5 local escalation rules govern the number of pages that must be locked within a single Data Manipulation Language (DML) command before escalation occurs. According to those rules, page locks escalate to a table lock when a single DML statement locks more than 1000 rows, given your minimum lock-escalation threshold value of 1000. But single-row inserts affect only one row at a time, even if you're inserting 60,000 rows in a single transaction. Thus, your INSERT has to affect more than 1000 rows in a single command—not a single transaction—to exceed the lock-escalation threshold.

Second, you should seriously consider using bulk copy program (bcp) to load large amounts of data on a regular basis. You can find tips for optimizing the SQL Server 6.5 version of bcp in "Making the Most of BCP—Seven Tips for Speeding Large Data Loads with Bulk Copy Program" at http://www.win2000mag.com/articles/index .cfm?articleid=257.

One of my tables has a datetime field with a default value of GETDATE(). A bit field in the same table has a default of 0. When I use ADO and OLE DB in an Active Server Pages (ASP) page to add a new record with rs.AddNew, the system automatically adds the default value for the bit field if a user doesn't enter a value, but the datetime field ends up as NULL. However, when I use Microsoft Access 97 through ODBC to add a record to the same table, the system allocates both default values if the user enters nothing for those fields. As a pragmatic solution, I went back and explicitly added the datetime value in the ASP page. Why won't the ADO and OLE DB solution allocate the default datetime value for the new record?

When simple things don't work, you can usually find a simple reason. Because the Access and ODBC solution sets the default GETDATE() value correctly, I assume you've set up the default properly in SQL Server. In that case, my first guess is that your code, either explicitly or implicity, is setting the datetime field to NULL. An explicit application request to set the column to NULL would override the server-side default.

To find the problem, try two things. First, set the column to a DEFAULT of GETDATE(), but also set the column so that it doesn't allow NULLs. SQL Server will raise an error if your application or middleware is somehow setting the column to NULL. Second, use SQL Profiler to trace the exact SQL statements that the ASP page passes to the server.

On a related note, Microsoft suggests that when you apply a DEFAULT constraint to a column, you use the appropriate niladic function instead of a system function, such as GETDATE. Niladic is a fancy name for a function that doesn't accept parameters and doesn't use parentheses in its syntax. The niladic function that replaces GETDATE() is CURRENT_TIMESTAMP.

SQL Server 2000 is great, but many of us are supporting SQL Server 7.0 and 6.x systems in addition to our new SQL Server 2000 installations. What is Microsoft's official policy about supporting these older but still mission-critical systems?

You asked an excellent question. Many customers are still running SQL Server 6.5, and organizations will be running SQL Server 7.0 for a long time. To get Microsoft's official answer, I asked the people in charge of Microsoft's SQL Server support to answer your question. Here's their answer:

"Microsoft continues to drive new product innovation by extending and reengineering the SQL Server database product. We believe these new product capabilities are properly suited for the needs of the ever-changing database community. We also realize, however, that we need to maintain legacy systems indefinitely while customers roll out new technology. In the past 6 years, Microsoft has released four major versions of SQL Server. Here's what you should expect from the official Product Support Services group if you call in on these versions (these support plans hold true for all worldwide releases):

SQL Server 4.21a. We have officially begun the obsolescence of support for this product. Customers with Premier contracts should soon receive a letter explaining that phone support will end around the end of 2000. We are not considering any additional maintenance work, either hotfix (patch) work or service packs, at this time.

SQL Server 6.0. We will continue to take service requests on SQL Server 6.0. We will watch the call volumes for this product, and as these volumes become negligible, we will begin the obsolescence process. We have no current timetable for ending support for this product. But we aren't considering any maintenance work, either hotfix work or service packs, at this time.

SQL Server 6.5. We will continue to take service requests on SQL Server 6.5. We have no plans to discontinue support for SQL Server 6.5, which remains a reportable percentage of our call volumes. Official hotfix support for SQL Server 6.5 will end when SQL Server 2000 ships, although for Premier customers with mission-critical destabilizing issues, we will extend hotfix support for 6.5 to the end of 2000. We are not releasing any more service packs for SQL Server 6.5, although we are planning to publicly post for customer download a recent SQL Server 6.5 maintenance build, which contains many documented post-Server Pack 5 (SP5) fixes.

SQL Server 7.0. We continue to take service requests on SQL Server 7.0 and will continue providing traditional maintenance work, both in the form of hotfixes and service packs. When we are in maintenance mode on a product, we generally consider hotfixes for the current product and one prior version (N-1). We strive to release service packs every six months."