Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected]
I'm trying to import Microsoft Access tables into SQL Server 2000 by using the Data Transformation Services (DTS) Wizard. However, after I successfully create the tables, the indexes that were on the tables in Access no longer exist, and I have to rebuild each table manually in SQL Server. Have I omitted something in the process?
You're not missing anything. DTS won't move all of your schema (including indexes) unless the source is SQL Server. DTS will move the base table structure, but it won't move other important schema components, such as indexes. You can move the indexes manually or use the Access Upsizing Wizard. If you choose to use the Access Upsizing Wizard, you need to upgrade to the newest online version of the wizard at the Microsoft article "OFF2000: Administrative Update Available for Access 2000 and SQL Server 2000 Readiness" (http://support.microsoft.com/support/kb/articles/q287/4/84.asp). Be careful: The Access 2000 Upsizing Wizard version that ships in the box doesn't work with SQL Server 2000. Even the new-and-improved Access 2000 Upsizing Wizard that you download does a poor job of upsizing SQL Server schema. For example, the wizard updates all the text fields to nvarchar and upsizes all validation rules as triggers rather than SQL Server constraints. A reasonable compromise might be to upsize the Access database .mdb file to a new SQL Server database simply to generate the indexes. Then, you can use Enterprise Manager to script out the Access-generated indexes and apply them to the SQL Server database.
Do you know of a way to use T-SQL to encrypt a column within a table?
T-SQL has no documented or supported way to encrypt a column directly. You can write an extended stored procedure that accesses the Microsoft Cryptography APIs, but this undertaking is beyond the skill set of many T-SQL developers, including me. Depending on your specific needs, you might be able to take advantage of pwdencrypt() and pwdcompare(), two undocumented T-SQL functions that SQL Server uses to manage and check encrypted passwords. You can find details about using these functions in "SQL Server's Undocumented Password Encryption Functions," http://www.sqlmag.com, InstantDoc ID 9809.
I know that column-level statistics, such as '_WA_Sys_...', are listed together with table indexes in the sysindexes table. However, I notice that some of my SQL Server 7.0 tables also include another kind of statistic, such as 'hind_41_...'. What are the 'hind' statistics? Are they different from '_WA_Sys_...'?
Statistics that start with 'hind_' are hypothetical indexes. These indexes aren't real indexes in that you can't use them to speed data access. Instead, the Index Tuning Wizard creates these column-level statistics as it looks for different indexes that might be helpful. Programmatically, you can easily determine which indexes are hypothetical by using the 'IsHypothetical' property of the INDEXPROPERTY() function. See SQL Server Books Online (BOL) for details about using this function.
I work on a payroll-management database and suggested to the development team that I use a datetime column as a table's primary key. Team members told me not to do this, but when I pressed for a reason, no one could provide a good answer. Why shouldn't I use a datetime column as a primary key?
I agree with your development team. In general, you should avoid using datetime as a primary key. First, datetime is an 8-byte data type, and narrow keys tend to be more efficient and faster than wider keys. If your table is going to be very large, a smaller integer-based data type, such as the 4-byte int or the 2-byte smallint, might be a better fit. Second, and much more important, datetime is accurate only to one three-hundredth of a second, or 3.33 milliseconds (ms). Table 1, which I copied from SQL Server Books Online (BOL), shows how SQL Server rounds different datetime values. By definition, primary key columns must be unique, and you can't ensure that you'll have unique values in a datetime column. Your business rules might say that entering multiple records within 3.33ms of one another is impossible, but I think that making that assumption is dangerous. Business rules and technical assumptions can always change.
Can I address multiple instances of SQL Server 2000 with DB-Library? I use DB-Library when I write most of our batch code, and now I want to take advantage of SQL Server 2000's multiple-instance feature. Converting to ODBC or OLE DB would take 6 to 8 months of coding and testing. Can you provide any information or a workaround?
DB-Library doesn't understand the concept of a SQL Server 2000 named instance, so you might conclude that DB-Library applications can't talk to a named instance. However, as a simple workaround, you can use the Client Network Utility to create a client-side alias. Specific instructions vary somewhat according to the Net-Library that you use. When you create the client-side alias, you can specify the port number for the SQL Server named instance. Then, you can choose a specific TCP/IP port for the named instance the first time you start the service. You can use the Server Network Utility to easily check the TCP/IP port your server is using. Start the Server Network Utility from the SQL Server program group on your Start menu, then click to the Properties tab for the TCP/IP entry. If you use the Named Pipes Net-Library, you need to specify an alternate named pipe. By default, SQL Server named instances use a named pipe similar to '\\computername\pipe\MSSQL$instancename\sql\query'.
Although I've given you the workaround, I strongly encourage you to convert your application to ODBC or OLE DB over time. Few people use DB-Library against SQL Server 2000 named instances, and I wouldn't be surprised if unknown bugs existed. Microsoft labels DB-Library as "stable," meaning that Microsoft won't fix bugs in it, even if you find one. Additionally, if you connect from DB-Library, many SQL Server 2000 features will be unavailable to you.
I need to programmatically drop SQL Server logins associated with Windows authentication. I have a login named PACIFIC\VNE1B that uses Windows NT security authentication, and whether I supply the sp_droplogin procedure with PACIFIC\VNE1B or VNE1B, I get an error message that says the login doesn't exist. What's wrong?
I love the simple questions! You should spend some time in SQL Server Books Online (BOL) reading about the differences between Windows-authenticated and SQL Server authenticated logins. The simple answer is that sp_droplogin is intended for managing SQL Server authenticated logins only. Sp_revokelogin is the correct command to use when you want to drop logins that use Windows authentication.
Under the covers, different sets of T-SQL commands control Windows-authenticated logins and SQL Server authenticated logins. But don't worry—it's easy to get confused if you primarily work through Enterprise Manager's GUI tools, which mask many low-level details.
I want to use the odbcping utility to test network connectivity to a SQL Server 2000 named instance. However, odbcping lets me specify a named instance from one of my machines but not from another. Why am I getting this disparity?
The odbcping utility is a simple command-line utility that lets you test connectivity between a SQL Server client and server. The utility uses ODBC to connect to SQL Server and run a test query. Odbcping installs automatically with SQL Server 7.0, but SQL Server 2000 doesn't load this tool by default. However, you can find odbcping in the \x86\binn directory of the SQL Server 2000 installation media. You can install the executable simply by copying the program to the 'binn' directory of your SQL Server instance.
Machines that have SQL Server 2000 installed but never had SQL Server 7.0 installed won't let you run odbcping until you copy odbcping from the SQL Server 2000 CD-ROM. However, machines running SQL Server 2000 and that previously had SQL Server 7.0 server or tools installed might still have the SQL Server 7.0 version of odbcping in the path. Because SQL Server 7.0 doesn't support named instances, the SQL Server 7.0 version of odbcping doesn't support referencing a named instance. Therefore, some SQL Server 2000 clients can run odbcping against a named instance and others can't.