A while back, we upgraded to SQL 7.0 from SQL 6.5. A short while later, the powers that be decreed we would be moving back to SQL 6.5. We rewrote our stored procedures to avoid using SQL 7.0 syntax.

So we now have had both versions installed on our development server and workstations, but the actual database is currently a 6.5 database. However, we have continued to use the SQL Server 7.0 Query Analyser as a development tool as it is a friendlier environment which still works with a 6.5 database.

Some strange behaviour has resulted with SQL Server. A couple of our stored procedures behave differently depending on where they are compiled. If compiled in the SQL Server 7.0 Query Analyzer (which works even with a 6.5 database), they do return the expected records.

If you keep the 7.0 Query Analyser open & also load the 6.5 ISQL window, & recompile the stored procedures in 6.5 ISQL, they no longer return records. However, if you shut down both applications & then reopen 6.5 ISQL ONLY & recompile, the stored procedures do once again work.

In short, it seems to be that if you compile in 6.5 with the 7.0 Query Analyser running (& loaded first), they do not work. However, the uncompiled SQL works, er, usually, in either window whatever is running.

After many hours spent trying to work out which part of the stored procedures was the culprit, I think I've narrowed it down to a particular method of using LEFT JOIN clause which both procedures contain, although on different tables. This method involves checking for nulls on the right side of the LEFT JOIN to replace the need for an extremely inefficient NOT IN clause, eg

..FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID
WHERE table2.ID IS NULL

- which is equivalent to, but much more efficient than:

..FROM table1
WHERE TABLE1.ID not in (select ID from table2)

The problem is inconsistent - occasionally it also occurs on uncompiled code in the SQL 6.5 ISQL window. Sometimes I can't get the problem to repeat in a new stored procedure, sometimes I can - I think it might depend what is in memory at the time. On certain servers, it has occasionally occurred when compiling with 7.0 OSQL on the command line, which is particularly alarming as we stopped using the 6.5 ISQL command line to get rid of the problem when compiling for release. Also, in many cases where we're using LEFT JOINs for this purpose, we haven't had a problem - its just these two stored procedures.

Has anyone else come across a similar phenonomen, and if so, any clue how to get rid of it?