Comparison on Text column

In T-SQL, "Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types."

Using "Like" on the text column instead.

check is table exists in mysql 4.x

show tables like 'tbl name'

but you can not use this in t-sql openquery. in order to check the status from T-SQL openquery, you can use this statement instead.

show table status like 'tbl name'

sample code in T-SQL:
declare @rowCnt int
select @rowCnt = count(*) from OPENQUERY(PPC_DB_MYSQL,'show table status like "tbl name"')
if (@rowCnt=1)
BEGIN
END

you can also use exists function to see if there's record returned.

Classify of Indian Gamings

until today, I find a clear explaination on the classification of Indian games.
it's from http://500nations.com/Indian_Casinos.asp.

Class I Gaming
Defined as "traditional tribal gaming and social gaming" with minimal prizes.
There is no regulation outside of the tribal government.

Class II Gaming
Defined as gambling played exclusively against other players and not the house.
Examples are bingo, poker, and other “non-banked” card games.
These games are permitted on Indian land as long as they are legal elsewhere in the state.
.
Class III Gaming
Defined as gambling played against the casino.
Includes slot machines, blackjack, craps, roulette, and "all forms of gaming that are not class I gaming or class II gaming."
Requires a compact with the state.

Put SQL Server to single user mode

In order to have exclusive access to the database, the following command can be issued, it will kill all connections to the specified database except for the connection that we currently have to the database.

alter database xxx
set single_user
with rollback immediate

the following command bring it back to multiple user access.
alter database xxx
set multi_user

cross database ownership in sqlserver 2000

this works on sqlserver 2000 since sp3.

(ATER DATABASE db SET DB_CHAINING ON is not working)
-- when I listed the options,the option is displayed as "DB CHAINING", no underscore is included.
--it also works with 'on' and 'off'
sp_dboption 'xxxdb','DB CHAINING',true
sp_dboption 'xxxdb','DB_CHAINING',true

after enabling the cross database ownership, as long as the creator of the stored procedure in one database has corresponding ownership in another database, the user in the calling database does not have to has db access to the second database as long as it's granted the privilege to execute the stored procedure.

Note: by default, in sqlserver 2000 sp3, the stored procedure is executed as it's creator's privileges.

Change default setting of TCP ports allowed in Windows

This happens again so that it maybe a good time to record it here.

If you try to set up TCP connections from ports that are greater than 5000, the local computer responds with the following WSAENOBUFS (10055) error message:

An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.

The default maximum number of ephemeral TCP ports is 5000,
To increase the maximum number of ephemeral ports, follow these steps:

Start Registry Editor.
Locate the following subkey in the registry, and then click Parameters:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
On the Edit menu, click New, and then add the following registry entry:
Value Name: MaxUserPort
Value Type: DWORD
Value data: 65534
Valid Range: 5000-65534 (decimal)
Default: 0x1388 (5000 decimal)

Description: This parameter controls the maximum port number that is used when a program requests any available user port from the system. Typically , ephemeral (short-lived) ports are allocated between the values of 1024 and 5000 inclusive.

Exit Registry Editor, and then restart the computer.

Note: An additional TCPTimedWaitDelay registry parameter determines how long a closed port waits until the closed port can be reused.

Mysql transaction log restoration

almost all the documents you can find on the Net are telling you to use mysqlbinlog to read the binary logs and pipe the output to mysql client, but this method simply will fail on windows system if the database contains binary data such as blob and binvarchar, due to escaping and piping problem in mysqlbinlog utility. the mysql utility simply can not read the file or output stream created by mysqlbinlog.

here's a workaround though, we can tell mysql to use --execute "source xx" option to parse the log files from mysqlbinlog.

E.g.
C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
C:\> mysql --user=root --execute "source /tmp/bin.sql"

ref 1: http://bugs.mysql.com/bug.php?id=33048
ref 2: http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/windows-vs-unix.html