SQL Server Solutions

SQL Server 7.0 lets you load data by using Data Transformation Services (DTS), bulk copy program (bcp), or the new BULK INSERT statement. What's the fastest way to load a large amount of data?

SQL Server 7.0 offers several high-speed mechanisms for loading data. Bcp is a high-speed file-import utility that SQL Server has supported since the early days of the database-management system. Bcp lets you quickly load large files and is often a good choice, but it's not user- friendly.

In SQL Server 7.0, Microsoft extended SQL Server's data-import capabilities with DTS and the new T-SQL command BULK INSERT. DTS offers a tremendous amount of data-handling flexibility, but BULK INSERT can be twice as fast as either bcp or DTS when used in comparable circumstances.

Why is BULK INSERT so much faster? BULK INSERT is a T-SQL command, so it runs in-process with the SQL Server engine. Thus, SQL Server doesn't need to pass the data along the usual client API network-abstraction layer, called a Network Library (NetLib). Bypassing the NetLib layer saves a huge amount of time.

In addition, SQL Server 7.0 supports a custom task add-on that lets you write a BULK INSERT task directly from a DTS package. (Microsoft also integrated this feature into SQL Server 2000.) If you're looking for the best combination of speed and programmatic workflow control, BULK INSERT from DTS might be the answer.

I've used SQL Profiler to save trace data to a table in SQL Server. I'd like to query this data by event class, but I don't see a way to determine what the integer values in the EventClass column mean. How can I translate the integer values to the event class names I see in the SQL Profiler GUI?

If you're using SQL Server 7.0, you can execute master..xp_trace_geteventnames. The result sets of this stored procedure will contain the integer values for EventClass and their corresponding EventNames.

I'm not aware of a corresponding procedure in SQL Server 2000, but you can grab the values from the SQL Server Books Online (BOL) entry for sp_trace_setevent. Let me know if you run across a good way to get this information directly from a SQL Server 2000 instance, and I'll be sure to pass it along to everyone else.

What is the Data Transformation Services (DTS) ScriptPkg utility, and where can I find it?

The standard SQL Server 7.0 DTS Package Designer is basically a GUI that writes to the underlying DTS COM. Unfortunately, the tool isn't as flexible as most developers would like it to be. The biggest complaint users have about DTS is that it doesn't provide a good script editor. If you're having problems with your DTS routines, you'll quickly discover that the DTS errors that SQL Server writes to the error-handling file are so obscure that they're almost useless. To help solve your problems, you can use the ScriptPkg utility to turn a DTS package into a Visual Basic (VB) program. ScriptPkg is a free VB-based tool that you can use to generate a VBScript file for a DTS package. This utility ships on the SQL Server 7.0 CD-ROM, but it's so well hidden that most users don't know about it. The following steps outline where to find the utility and how to install it (you need VB 6.0 to proceed):

ScriptPkg writes an entire DTS package as one procedure, and VB procedures can't be larger than 64KB. Thus, if the ScriptPkg script file is larger than 64KB, you must manually break up the code into smaller chunks.

ScriptPkg is an invaluable troubleshooting aid, and it's a great way to learn DTS programming at the COM: You can use ScriptPkg to view and manipulate undocumented DTS. If you're not a VB programmer, now is the time to learn. Knowing VBScript is an integral part of future SQL Server administration. In SQL Server 2000, Microsoft bundles ScriptPkg directly in the DTS Package Designer.

I installed a new instance of SQL Server 2000, and all my SQL Server 7.0 tools have disappeared. What happened to them?

Although you can run multiple instances of the server engine on one machine, you can't keep multiple instances of the client and administrator tools on the same machine. When you install multiple instances of SQL Server, you'll use the new SQL Server 2000 versions of SQL Server tools (e.g., Query Analyzer, SQL Enterprise Manager, Microsoft Data Access Components­—MDAC) to connect to the SQL Server 7.0 instance. Don't install a new instance if you must keep the old tools.

I want to store column definitions associated with a column in a table, and I want the definitions to reveal the business meaning of the column. For example, if I have a column called OrderDate in my Order table, I want to store a definition that specifies whether the column contains the date the customer placed the order or the date the company fulfilled the order. How do I use the Microsoft Repository to configure this setup?

SQL Server 2000 lets you store extended properties with many database object types. Extended properties are user defined and store a value of type SQL_VARIANT. Visual Basic (VB) programmers are familiar with the new variant data type. Like VB's variant data type, SQL_VARIANT lets you store different data types' data values in one column, parameter, or variable. Each instance of a SQL_VARIANT column records two items: the data value and the metadata that describes the value (i.e., the value's base data type, maximum size, scale, precision, and collation). You can use the SQL_VARIANT_PROPERTY function to get the metadata information for any SQL_VARIANT instance.

For example, if you want to store a description of the au_id column in the authors table in the pubs database, right-click the column name in the new Object Browser that the Query Analyzer interface provides, then select Extended Properties. We added a new property called WhatAmI with a value of I am the author id column!!! Alternatively, you can use the sp_addextendedproperty procedure to accomplish the same task.

sp_addextendedproperty   'WhatAmI2', 'This is a new   property value', 'user', dbo,   'table', authors, 'column',   au_id

You can then use a standard SELECT statement with a new function called fn_listextendedproperty to retrieve the information, as Listing 1 shows.

Did Microsoft enhance Declarative Referential Integrity (DRI) support in SQL Server 2000?

Microsoft hinted several years ago that it would support cascading DRI and disappointed many people when that feature didn't make SQL Server 7.0. With SQL Server 2000, however, the wait is over. SQL Server 2000 supports cascading DRI, a feature that Microsoft Access users have enjoyed for years. What's the difference between earlier SQL Server versions' restrictive DRI and cascading DRI? Let's say you have the classic parent-to-child (one-to-many) relationship of customers and orders. Restrictive DRI prevents deletion of a customer with active orders, whereas cascading DRI assumes you know what you're doing and simply deletes the related orders when you delete a customer.

SQL Server 2000's SQL-92 syntax for the CREATE TABLE command, which Listing 2, page 78, shows, supports cascading actions on updates and deletes. You can implement cascading deletes in SQL Server 7.0 and earlier by writing code or suitable update and delete triggers.

Can I generate a list of all queries or server process IDs (SPIDs) executing within a transaction?

The Sysprocesses table in the Master database contains many pieces of valuable information, including a column called open_tran. Sysprocesses has a row for every unique SPID in a SQL Server connection. The open_tran column holds the current value of @@trancount for the SPID you're looking at in the Sysprocesses table. An open_tran value greater than 0 means that the SPID is in a transaction; a value of 1 means that the SPID is in a top-level transaction, a value of 2 means that the SPID is nested one level deep in a transaction, and so on.

To view which connections are inside a transaction, you can issue the following command:

SELECT spid, open_tran FROM master..sysprocesses WHERE open_tran > 0

You can then use the dbcc inputbuffer command to see which command is executing. For example, dbcc inputbuffer(10) shows you the first 255 characters of the last SQL command executed in SPID 10.

I've found the Microsoft SQL Server newsgroups to be a great source of useful information. But sometimes I'm working at a site that doesn't permit Network News Transfer Protocol (NNTP) packets to pass through, so I can't access the news servers. Is a Web version of the newsgroups available?

Yes, you can access the Microsoft newsgroups through the Web at http://msdnnews.microsoft.com. If you've never browsed Microsoft's SQL Server newsgroups, you're missing out on a valuable resource. The newsgroups provide a great place to ask questions. In addition, you can learn a lot simply by reading other people's questions and informative answers.

Not even familiar with the regular NNTP newsgroups? You should be! We don't have space to answer all your questions, and Microsoft's free newsgroups available at http://msnews.microsoft.com are invaluable sources of information.