Questions, Answers, and Tips About SQL Server - 06 Jan 2000

I have two SQL Server 7.0 databases on separate machines. To send data from one machine to the other, I used Data Transformation Services (DTS) to import the data on the receiving machine. To transfer the data, I selected the data I wanted to transfer and dropped all objects before I imported them. After I imported the data, the DTS import report showed that all objects and data transferred successfully. However, a varchar field that contained 500 to 1000 words before import was set to NULL on the receiving machine after import. Some of the records contain quotation marks, so I assumed that SQL Server was choking on the quotes. However, if quotes are the problem, SQL Server should have imported the data that doesn't contain quotes. Am I missing something about importing varchar data?

We've heard about many cases in which DTS has problems handling large character fields from large input data sets. You can usually solve the problem by clearing the Fast Load option on the Transformation Task Properties dialog box Advanced tab. If this solution doesn't fix the problem, you can use the bulk copy program (bcp) to import the data. Although bcp isn't as easy to use as DTS is, using bcp lets you avoid DTS gotchas. For example, the Microsoft article "BUG: DTS Transfer Does Not Report Error When Input File is Missing Text Qualifier in Last Row" (http://support.microsoft.com/support/kb/articles/q241/7/61.asp) confirms that DTS transfer fails to generate an error message when importing the last row of an input file to a SQL Server table when the input file contains text qualifiers and the last column is missing the last text qualifier. To work around this gotcha, you must manually edit the input file.

Alternatively, the target system's NULL configurations might be causing your problem. Contrary to the ANSI standard, by default, SQL Server creates columns that don't accept NULL values unless you use CREATE TABLE or ALTER TABLE commands to explicitly declare the NULL values. Curiously, both ODBC and OLE DB override this default setting. You can tweak various settings to fine-tune how your system handles NULL values. At the database level, you can use sp_dboption to select either ANSI-standard NULL values or backward-compatible SQL Server NULL values. In addition, you can adjust session settings one connection at at time using the Transact SQL (T-SQL) SET ANSI_NULL_DFLT {ON | OFF} command to control what type of NULL values you want. Or use the GETANSINULL() function to determine the default NULL values for the current session.

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

The standard 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 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.

I run a small telecommunications company and have been using Microsoft Visual FoxPro as my development environment and database engine. I want to port a few applications to SQL Server. However, my applications rely on user-defined functions (UDFs), and I can't find where to define UDFs in SQL Server. I've searched newgroups and read SQL Server 7.0 Books Online (BOL) to no avail. Does SQL Server support UDFs?

We've got good news and bad news. The bad new is that SQL Server 7.0 doesn't support UDFs. The good news is that SQL Server 2000, which is currently in beta, will support UDFs. In the meantime, you can use Transact SQL (T-SQL) to simulate UDFs, but this method might be difficult and frustrating.

On a related note, FoxPro users might have encountered these two error messages: The Microsoft FoxPro driver is no longer supported and has been replaced by the Microsoft Visual FoxPro driver and Could not find installable ISAM. For an explanation and a solution, read the Microsoft article "PRB: FoxPro ODBC Driver Replaced by Visual FoxPro ODBC Driver" (http://support.microsoft.com/support/kb/articles/q235/3/57.asp).

Where did the FoodMart data that ships with Microsoft SQL Server OLAP Services (MSOLAP) come from? Why did Microsoft base the FoodMart data on an Access database instead of basing it on SQL Server tables?

Microsoft shipped the FoodMart data in .mdb format because not everyone who wants to use MSOLAP has SQL Server installed. Many users forget that MSOLAP works with SQL Server and other Microsoft databases. MSOLAP even works well against low-end, old-fashioned, legacy databases such as Oracle8i. Shipping FoodMart in .mdb format lets you experiment with MSOLAP without installing SQL Server. In addition, if you're planning to build a front end to a Microsoft-based data warehouse, you can practice using FoodMart data in a SQL Server format. On the SQL Server Web site (http://www.microsoft.com/sql/productinfo/evalexercises.htm), you can download a set of self-extracting, self-paced exercises that will walk you through the processes that Table 1 lists. This Web site also provides sample exercises that help you learn replication and upgrading.