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):
- Unzip the DTSDemo
.exe file from the SQL Server 7.0 CD-ROM's \DevToolsSamples\DTS folder.
- In the Designer subfolder, run the VB project called ScriptPkg.vbp.
- From the File menu in VB, create an executable file and name it ScriptPkg.exe.
- Use SQL Server 7.0 Enterprise Manager to design a DTS package, and save the package on your local SQL Server system.
- Run ScriptPkg.exe, and enter the package name. ScriptPkg writes a script file (e.g., packagename.txt) to the \temp folder.
- Copy the code from the ScriptPkg script file into your VB application. Add Microsoft ActiveX Data Objects (ADO) 2.1 or later and the Microsoft DTS Package Designer object libraries to your application, or experiment with the three VB DTS samples in the \DevTools\Samples\DTS folder.
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.
Previous
Register
