I'm getting a "bad token" error message from SQL Server - what causes this?

A. The full errors for this problem are "Bad token from SQLServer" and "Datastream processing out of sync".

They are caused by the NO_BROWSETABLE undocumented option. As it isundocumented the error almost always occurs using ADO as it uses it without theprogrammer knowing about it.

It is caused when NO_BROWSETABLE is set and a SELECT operation includes oneor more columns in an ORDER BY clause that are not in the SELECT list.

The problem is fixed in SP5 for SQL 6.5. It does not occur in SQL 7.0.

A script to show the problem from ISQL/W follows :-

SET NO_BROWSETABLE OFF
go
CREATE PROCEDURE my_sp AS
CREATE TABLE #t(a int)
INSERT #t VALUES(3)
SELECT * FROM #t
go
EXEC my_sp -- Goes OK.
go
SET NO_BROWSETABLE ON
go
EXEC my_sp -- Goes OK too.
go
EXEC my_sp WITH RECOMPILE -- Now, you're dead.
go