SQL Server Q & A - 18 Nov 1999

I want to create local cubes with OLAP Server (the server side of OLAP Services). Can you recommend a tool that can create a local cube from a cube on a server?

Excel 2000's Create Cube File wizard can help you create local cube files (a subset of the server-side cubes). To find this wizard, you need to create a PivotTable based on the server-side cube by selecting PivotTable and the PivotChart Report option in the Data pulldown menu. After you create the PivotTable, use the PivotTable floating toolbar to drag data elements onto your PivotTable report. Then pull down the PivotTable menu from the PivotTable floating toolbar and select Client-Server Settings. From the resulting window, you can select Create local data file and follow the instructions. The Create Cube File wizard lets you choose which dimensions, levels, and measures you want to include in the cube file.

How do I create the equivalent of Oracle synonyms in SQL Server 7.0? I want to give a user access to another user's tables without having to qualify access with the table owner.

The closest Microsoft comes to Oracle synonyms is providing the capability to create a view of the other user's table. If the table

CREATE TABLE user1.table1 (c1 int not null)

exists, and myuser can create

CREATE VIEW myuser.view1 AS SELECT * FROM user1.table1

then myuser can reference the view without providing owner qualification.

You can also create the view with the owner DBO when you're in the db_ddladmin or db_owner roles because you'll always find objects owned by the user DBO when you reference the view without an object owner qualification.

Can clients transmit and receive data from SQL Server Desktop to the main server? Do I need a Client Access License (CAL) for every client that needs to access SQL Server?

The clients need to run the SQL Server Desktop version on their machines and synchronize with the main SQL Server database. Microsoft built the functionality to replicate data to and from a SQL Server standard or Enterprise Edition installation into SQL Server Desktop and SQL Server. Microsoft designed the SQL Server Desktop as a disconnected database that can run on an earlier version OS, such as Windows 9x. Also, Microsoft designed SQL Server Desktop for users who need the power of SQL Server while they're away from a main office. SQL Server Desktop is ideal for users who need to access a database while they're on the road and synchronize the data with the database when they return to the office. SQL Server Desktop can perform merge replication (bidirectional or synchronization) with a SQL Server standard or Enterprise Edition server.

Microsoft doesn't sell SQL Server Desktop as a separate product; the CD-ROM that comes with SQL Server 7.0 includes it. To use SQL Server Desktop, you need to buy SQL Server and run the server in per-seat mode. Each machine that runs SQL Server Desktop must have a SQL Server Client Access License (CAL), which lets you install SQL Server Desktop on a machine and lets that machine access SQL Server. SQL Server Desktop is hard-coded into the SQL Server product, so if you try to replicate between SQL Server Desktop and SQL Server in per-server mode, the replication won't allow it.

How do I create tables in SQL Server 7.0 with the DBO as the owner? The sa's role isn't a good option because the role is global to all databases.

The only way to create DBO-owned tables is to use the Query Analyzer and specify the object owner. Use the statement

CREATE TABLE dbo.mytable (c1 int not null)

A member of the db_owner or db_ddladmin roles can create any object with DBO as the owner. You can also alias a login to the DBO user, but Microsoft strongly recommends against aliasing because the company plans to eliminate aliasing in a future release. Enter- prise Manager will fix the inability to specify an object. Microsoft recommends that you create production tables with the owner as DBO. When tables have the same owner, it's easier to maintain objects in a production environment, and by default, all name-resolution mechanisms will find objects owned by DBO.

Our SQL Server 7.0 database is on an Intel system with a multiprocessor and Windows NT 4.0, Service Pack 4 (SP4). The database contains Unicode data that was originally double-byte character set (DBCS), Asian, single-byte character set (SBCS), Western languages, etc. We know the data converts correctly because our overseas branch offices can see the data on Web pages that display the data in the branch office's native language, such as Japanese, Chinese, and Korean. However, if a branch office queries a non-numeric column, such as nvarchar, the query returns incorrect data. Also, if you run the same query from the Query Analyzer, no data returns. It appears that the Query Analyzer converts valid Unicode data into garbled string data, so the query fails. However, if you use a nested SELECT statement to extract a nvarchar value without putting the nvarchar data directly into the Query Analyzer window, the query succeeds. The following query works:

SELECT * FROM tblemployee WHERE first_name   IN (SELECT first_name FROM tbltestdataWHERE test_column = 3)

However, this query fails:

SELECT * FROM tblemployeeWHERE first_name LIKE 'valid Unicode data'

Why won't this query work when I use the Query Analyzer?

It sounds as if the data uses the query tools you installed to convert to the client-code page on the client side. Go to the Control Panel, Regional Settings (or International Settings, depending on your version of Windows); the Query Analyzer filters your data based on the client's settings.

Make sure you specify the character N before your string comparisons, so that the string is represented in Unicode. Otherwise, SQL Server converts the string to the server's character set. For example,

SELECT * FROM Test WHERE col1 = N'hello' 

How can I pass a table name to a stored procedure?

You need to dynamically execute a SELECT * FROM statement on any table you want to pass to a stored procedure, as Listing 1 shows.

In Oracle, the following command gives a table an exclusive table lock:

LOCK TABLE table IN EXCLUSIVE MODE

An exclusive table lock is the most restrictive table-lock mode, letting the transaction that holds the lock have exclusive write access to the table. Does SQL Server 7.0 have an exclusive table lock feature?

SQL Server 7.0 holds locks within a transaction, so to lock a table in exclusive mode, you can run

BEGIN TRANSACTION SELECT top 1 'a' FROM TABLE WITH (tablockx)

This query places an exclusive lock on the table until the transaction ends. The top 1 'a' returns only one row and doesn't get data from the table. Look for locking hints in SQL Server Books Online (BOL). Also, Kalen Delaney's Inside SQL Server columns "SQL Server 7.0 Locking" (July 1999), "Indexing and Locking" (August 1999), and "Controlling Locking" (November 1999) describe locking in depth.