PRB: Differences Between DMO 7.0 and DMO 6.5 With ExecuteWithResults

• Microsoft SQL Server version 7.0

SYMPTOMS

When using the SQL Server 7.0 Distributed Management Objects (DMO) there are some fundamental differences in how results may be returned with ExecuteWithResults when compared to the 6.5 version of SQLDMO.

CAUSE

SQL Server 7.0 is based on the ODBC API so there are some differences in how informational messages are returned to the client compared to SQL Server 6.5. In SQL 6.5 using some of the DBCC commands, the output was returned as a resultset but with the advent of 7.0 all DBCC commands are now returned as messages. Calling the ExecuteWithResults method on SQL Server 7.0 returns a empty resultset as there are not any records in a resultset, only messages. To get access to the messages, call the ExecuteWithResultsAndMessages method.

To better understand the issue use Query Analyzer 7.0 and use the following steps:

1. Start two instances of Query Analyzer, connect one to SQL Server 7.0 and the other to SQL Server 6.5



2. Execute DBCC SHOWCONTIG in the pubs database against the authors table.

   
   USE pubs
   GO
   DECLARE @id	INT
   SELECT @id = object_id('authors')
   DBCC SHOWCONTIG(@id)
   GO 




3. Once the results have been observed on both instances of Query Analyzer, change the default view from Results in Text to Results in Grid by selecting the option under the Tools menu.



4. Run step 2 again on both instances. On SQL 7.0 results will not be displayed on the Results tab but the information can be observed on the Messages tab of Query Analyzer. On SQL 6.5 the information is being returned as a resultset so the information still shows in the grid

WORKAROUND

In most cases the only workaround is to use the ExecuteWithResultsAndMessages method, but some DBCC commands allow the additional qualifier WITH TABLERESULTS to have the information generated as a resultset. Consult the SQL Server 7.0 documentation to see which commands support the WITH TABLERESULTS qualifier.

Additional query words: SEM enterprise manager vb basic visual

Keywords : kbSQLServ700 kbDSupport
Version : winnt:7.0
Platform : winnt
Issue type : kbprb