Defining Tables, Exporting Diagrams, and More

Answers from Microsoft

When I use a hierarchical table to represent a cube with hierarchical dimensions in a relational scheme, I get the following results:

Department table:ID  - PKDepNameIdParent - FK

When I define table dimensions, I can't describe the ID and IdParent relationship. As a result, all records from this table calculate as one level of the hierarchy. I tried to define this relation in a relational scheme and use two tables:

Department table:ParentTable table:ID - PKID - PKDepNameIdParentDepartment - FKIdParentTable - FK

But when I tried to define the dimensions, the error Can't create cyclic dimension appeared. I can't use the pure star or snowflake scheme, because I don't know how many levels will be in the hierarchy. What do you suggest?

You need to define two tables. But because of the limitations on the OLAP Server side, instead of using two instances of the same table, you define a view in SQL Server of the original table and join the table with the view.

In the Diagrams feature of SQL Server Enter- prise Manager, can I export a diagram to a document or file that I can distribute to others?

No, but Microsoft is planning this functionality for a future release.

I have been hard-coding tablenames in my stored procedures as follows:

DECLARE @orderno intSELECT @orderno=4822DECLARE @tablename char(30)SELECT @tablename='tblOrderHist'

EXEC ('Select' + @orderno + '=OrderNumber From' + @tablename)

How can I pass in tablenames based on a certain criteria and set a WHERE clause equal to an @variable?

This code is the preferred way to pass in tablenames:

DECLARE @orderno varchar(10)SELECT @orderno='4822'DECLARE @tablename char(30)SELECT @tablename='tblOrderHist'EXEC ('Select OrderNumber From ' + @tablename +  'where OrderNumber = ' + @orderno) 

I created a data file of 600MB and a log file of 300MB. My application, in turn, dynamically created a Temp table, inserted records, performed all the validations, and updated my original table. As a result, the original table increased in size, and my database does not have enough space for it, even though I deleted the Temp table. (My database cannot hold more than 300MB.) How do I overcome this limitation?

You can create the Temp table in the Tempdb database rather than in the currently specified database. If you use the # or ## table syntax, the table will exist in the Tempdb database. If you create the table without the # or ## symbol in the name, the table will be part of the currently specified database.

To implement a replication environment, can I use SQL Server 6.5 publishers and subscribers with a SQL Server 7.0 distribution server?

Yes. SQL Server 7.0 Books Online (BOL) includes information about replicating between different SQL Server versions. Look at Replication, Advanced Replication.

In the April issue, you discussed how SQL Server uses a windowing technique to handle the century portion of a date. Does this approach apply to all database fields defined as type DATE?

Yes. SQL Server interprets the four-digit year cutoff point based on the way you've configured the server for all datetime data types. The windowing of the century portion of a date applies only when you don't specify a four-digit century. This configuration parameter does not change how SQL Server stores the dates after you set the century properly on the Insert or Update.

The maker of an application that runs on SQL Server 6.5 claims to be working on an implementation of the application that will run on SQL Server 7.0. Does it make sense to install SQL Server 7.0 in SQL Server 6.5 emulation mode to become familiar with the SQL Server 7.0 interface and to take advantage of SQL Server 7.0's new features? Is it a good idea to run SQL Server 7.0 in SQL Server 6.5 emulation while in production mode?

Upgrading to SQL Server 7.0 is worth a try, but you need support from your application vendor in case problems arise. If the vendor won't support SQL Server 7.0 in 6.5 emulation mode, you need to weigh that factor heavily in your decision. I recommend that you get a commitment from your vendor before proceeding.

The summary.log file for the upgrade process from SQL Server 6.5 to 7.0 shows missing logins on the master database on SQL Server 6.x. The log says users in sysusers don't have logins on the master database. Is this situation possible?

When you restore a database from another server, the log usually records missing logins. If you want to clean up your login to user mismatch (which you should do before upgrading), look at the system stored procedure sp_change_users_login. This stored procedure remaps logins in the master..syslogins table to ..sysusers. Run this procedure on your SQL Server 6.5 system, and then perform an upgrade again using the wizard.

I have heard of problems in using the wizard to upgrade from SQL Server 6.5 to SQL Server 7.0. As rumor has it, to avoid problems, you need to create script files from SQL Server 6.5 and run them on the SQL Server 7.0 database. You then use Data Transformation Services (DTS) to transfer the data from SQL Server 6.5 to SQL Server 7.0. Should I use the wizard or perform the migration with scripts?

I recommend that you use the wizard instead of scripting and manually transferring the data. The wizard has been thoroughly tested and is reliable. The upgrade whitepaper at http://www.microsoft.com/ sql/70/whpprs/Upgrade.htm has many useful tips for preparing for a migration.

I tried the SQL Server upgrade wizard. The results look OK, but I received the following error messages in the Export and Import via Named Pipe - 006DB_Test file: Already has data. Skipping to next table. Is already locked by another user. Did the upgrade work correctly?

Yes, you can expect this behavior on a multi-CPU box. The box runs multiple export and import processes that don't know about each other. So, when a process hits a table that already has data or is currently locked (because one of the other processes got there first) it issues this message and continues. This message is for diagnostic purposes only. You wouldn't be able to tell what actually happened on a multi-CPU box if it didn't issue these messages. The messages do not cause the task to stop, nor are they displayed at the end of the conversion unless a real error occurs in that step.

I'm analyzing the best way to upgrade from Access 97 to SQL Server 7.0. Is it feasible to upgrade from Access 97 to Access 2000, and then to SQL Server 7.0?

Upgrade directly to SQL Server 7.0 if that's your goal. You can use Access as a front end to SQL Server. For more information, see http://www.microsoft.com/ sql/70/whpprs/migaccwp.htm.