You can use the CHARINDEX function in a where clause much as you would use the LIKE keyword in a query. For more discussion see:
http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where
When you run into errors running queries after adding a linked server which runs sql server 2000 on a machine running sql server 2008 try the openquery function instead :
Conditionally placed where statement (in stored procedure)
Returns the current database system timestamp as a datetime value, derived from the operating system of the computer on which the instance of SQL Server is running
This stored procedure will give you extensive information about the fields in a table