Questions, Answers, and Tips About SQL Server - 12 Aug 1999

I installed SQL Server 6.5 and SQL Server 7.0 on different machines, and I'm using SQL Server 7.0's new Data Transformation Services (DTS) Import Wizard to convert a SQL Server 6.5 database to a SQL Server 7.0 database. However, on the screen in which I need to specify the source server and the database, I get the following DTS error message:

DTS Wizard ErrorError Source: Microsoft OLE DB Provider for SQL ServerError Description: The catalog stored procedures installed on server FS7 are version 6.50.193; version 07.00.0330 or later is required to support IDBSchemaRowset. Please contact your system administrator.Context: Error calling GetRowset to get Schema info.Your provider does not support all the interfaces/methods required by DTS.

Am I using the correct tool to convert SQL Server 6.5 data to SQL Server 7.0 data?

You're asking two questions: "Why doesn't DTS work when I move data from a SQL Server 6.5 machine to a SQL Server 7.0 machine?" and "Am I using the right method to upgrade my SQL Server 6.5 database to SQL Server 7.0?"

To answer your first question, DTS is failing because your SQL Server 6.5 machine uses an older version of Microsoft's ODBC catalog stored procedures. Running the mssql7install directories' instcat.sql script against your SQL Server 6.5 machine upgrades its catalog procedures so that DTS can work with them. To use this solution, run the following Interactive SQL (ISQL) command from a command prompt:

C:> ISQL -Usa -Psa_password -Sserver_name -ilocationInstcat.sql

Running this script might generate some puzzling error messages. However, if instcat.sql runs to completion, it generates a final message that announces the script completed successfully. In our experience, you can safely ignore the error messages, and everything works fine. However, back up the SQL Server 6.5 machine's master database before you run the script, just in case.

As for your second question, we don't have enough information about the DTS Import Wizard to know whether you're using the right method to upgrade your databases. However, we suspect the upgrade wizard is a much better tool for you to use. Microsoft invested a lot of effort to ensure that the upgrade wizard is as close to perfect as possible and that it carefully explains all your options as you upgrade. You might not know that this wizard is an option because the upgrade wizard icon doesn't appear in a SQL Server system's Start menu unless SQL Server 6.5 is on the system when you install SQL Server 7.0. However, you can start the upgrade wizard on your SQL Server 6.5 system by running the mssql7upgrade directories' upgrade.exe program.

One of my SQL Server 7.0 systems uses the SQL Server 6.5 version of bulk copy program (bcp) rather than the SQL Server 7.0 version of bcp. My other SQL Server 7.0 systems use bcp 7.00.623. Does a server setting direct which version of bcp a SQL Server system uses?

Bcp isn't part of the server; it's just a C program that calls database API functions. Bcp runs from the Windows NT or Windows 9x command line; thus, a SQL Server setting doesn't control which bcp version you're running.

We guess that your problem is a result of having multiple executables with the same name on the same machine. Your machine probably has an old installation of SQL Server 6.5 client tools, and the path statement references the old version of bcp first. Type

path

from an NT command prompt to view the order in which your path references directories; then, make sure an old version of bcp isn't hanging around. You can also use the Find option from Windows Explorer's Tools menu on any system to check for multiple copies of bcp.

SQL Server 7.0 includes an IDENTITY function, which is different from the IDENTITY property. What do you use the new function for?

SQL Server 7.0 includes a new Transact SQL (T-SQL) IDENTITY function. This handy function is similar to the IDENTITY property, which you use to tell SQL Server that a column should be an autoincrement field. However, you use the IDENTITY function in only a SELECT INTO statement, using the following syntax:

IDENTITY(data_type[, seed, increment]) AS column_name

You can use this function for various tasks, such as creating ordinal rankings within a result set. In the following simple example, we add an IDENTITY column called RankCol to the temporary table we're creating:

SELECT  stor_id,  title_id,  qty,  IDENTITY(smallint) AS RankColINTO  RankingFROM  pubs..salesORDER BY  RankColGOSELECT TOP 10 * FROM RankingGO

Figure 1 shows the output of this command. We can then use the RankCol column in a WHERE clause, or we can retrieve it as part of our result set if we need a simple way to determine the ordinal position of a row in a result set.

I changed ANSI settings in sp_configure and sp_dboption (e.g., I set ANSI nulls=OFF), but Query Analyzer ignores these changes, and I have to manually reset them when I log in. Any suggestions?

Both sp_dboption and sp_configure let you change certain ANSI SQL-92 behaviors from a global perspective, so the settings affect all users on a particular server or database (i.e., sp_configure affects settings at the server level, and sp_dboption affects settings at the database level). However, when an application connects to SQL Server, an individual connection can override any ANSI behavior settings you've changed using sp_dboption and sp_configure. Query Analyzer is simply a C program that connects to SQL Server using the same database APIs you can access.

To solve your problem, you can run the appropriate SET commands from Query Analyzer every time you connect, or you can change the default connection settings for all new Query Analyzer connections. To change the settings, open Query Analyzer, and select Configure from the File menu. The New Connections tab, which Screen 1 shows, lets you set certain default behaviors for all new Query Analyzer connections, including four ANSI behavior settings.

Can you provide an up-to-date list of SQL Server version numbers?

If you inherit a SQL Server system or you're consulting at a client site and you want a quick way to discover what SQL Server version and which service packs you're dealing with, run this simple SQL command:

SELECT @@version

To run this command, you can use Interactive SQL (ISQL), ISQL/w, SQL Server Enterprise Manager, or any other tool that lets you submit SQL queries to the server. Figure 2 shows the list of SQL Server version numbers for Intel servers.