SQL Server Magazine

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 tbltestdata
WHERE test_column = 3)

However, this query fails:

SELECT * FROM tblemployee
WHERE 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)

End of Article

Prev. page     1 [2]     next page -->