2006/12/28

SQL server 2005 publishing views to SQL 2000 subscribers

If you are in the process of migrating your SQL Server 2000 scenario to SQL Server 2005 and you have replication configured, here is a hint that you must have in mind before changing the publisher to be a SQL Server 2005: SQL Server 2005 has changed the syntax for SELECT clauses (when ORDER BY is used) in the following way:
SQL 2000: SELECT TOP 100 PERCENT Field1, Field2 FROM Table1 ORDER BY Field1
SQL 2005: SELECT TOP (100) PERCENT Field1, Field2 FROM Table1 ORDER BY Field1
Please note the parentesys. This newer syntax is not recognised by SQL Server 2000 engine, thus giving you errors when the initial snapshot is tried to be applied. If this is your case and you need to set up SQL Server 2005 as a publisher to SQL Server 2000 subscriber(s), you will need to change those views that need to be published to remove the ORDER BY so that no TOP [n] PERCENT is needed (and also be removed), and modify your application accordingly. Besides, according ORDER BY Clause (Transact-SQL)(in BOL):
When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
In other words, it is useless to do a SELECT TOP 100 PERCENT... ORDER BY FieldX in a view (let's say View1), since all records will be returned, but not granted to be ordered as expected, unless we do a SELECT * FROM View1 ORDER BY FieldX from the view itself. See also: SQL Server 2005 Ordered View and Inline Function Problems from OakLeaf Systems' blog.

2006/12/27

Microsoft SQL Server Search Engine using Google co-op

Google has recently launched a beta service called Google co-op. According their overview:
Using it you can create a highly specialized Custom Search Engine that reflects your knowledge and interests.
Since it seemed to be a good idea, I gave it a try and created a customised search engine for specialised content on Microsoft SQL Server. This search engine allows you to use the power and knowledge of Google and refine the results of your queries, according additional keywords, giving a higher weight to those sites you consider to be more interesting, in order to return more precise results. In summary, all I have done is customise the search engine with websites related to SQL Server (Microsoft, blogs, usenet groups and so on). Feel free to give it a try and suggest me more sites to include to refine the searches further. It seems to work fine.