dbcc commands (has anyone run them)?

[hungryhobbit]

When I run a command like:
  dbcc checkconstraints('someTable')
through pymssql it completes successfully.  However, when I try to view the results, my cursor tells me that there aren't any.  This happens even when I know there should be results (because I ran the same command through the Query Analyzer).

Does anyone here know how I can use pymssql to get the results back from such a dbcc command?

[A]

I just tried it on pymssql 1.0.2. First I took a DBCC command that is guaranteed to return a result set:
DBCC TRACESTATUS(1111)
and it worked properly:

Type "help", "copyright", "credits" or "license" for more information.
>>> import pymssql
>>> con=pymssql.connect(host='.',user='sa',password='P@ssw0rd')
>>> cur=con.cursor()
>>> cur.execute("DBCC TRACESTATUS(1111)")
>>> for i in cur: print i
...
(1111, 0, 0, 0)
>>>

Problem with DBCC CHECKCONSTRAINTS is that it returns result set if and only if constraints are violated. If they are not violated, nothing is returned. In several databases that I was able to test now, it returned nothing (in SQL Server Management Studio as well as in Python). I did the following:
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

Hope this helps.

[hungryhobbit]

Thanks for the response Andzej, but unfortunately using "WITH ALL_CONSTRAINTS" doesn't help either; even if I create an invalid row first I still get:
>>> cursor.execute("DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS")
>>> cursor.rowcount
0

Plus, even if it did work that solution would still be problematic, because "WITH ALL_CONSTRAINTS" checks across the entire DB, not just one table (which takes MUCH longer in a large DB).

If it would be any help, you can reproduce the problem by doing the following:
1) ALTER TABLE *your table* NOCHECK CONSTRAINT *name of some constraint on that table* (to disable a constraint temporarily)
2) UPDATE a record in your table to violate that constraint (ie. if you used a foreign key constraint, update the foreign key column to a value that isn't in the parent table)
3) ALTER TABLE *your table* CHECK CONSTRAINT *name of some constraint on that table* (to re-enable the constraint)
4) DBCC CHECKCONSTRAINTS(*your table*')

If you do the above in the Query Analyzer you will see a rowset for each record/violated constraint combination.  If you do it with pymssql, you will see ... nothing :-(

I realize that the problem comes from the "DBCC CHECKCONSTRAINTS" command only returning a rowset some of the time, but isn't there any way to make pymssql display that rowset if it is returned?

[A]

Please see the following transcript and check if your results are the same.

C:\>python
ActivePython 2.6.1.1 (ActiveState Software Inc.) based on
Python 2.6.1 (r261:67515, Dec  5 2008, 13:58:38) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import _mssql
>>> c=_mssql.connect(server='.',user='sa',password='P@ssw0rd',database='tempdb')
>>> c.execute_non_query("create table testparent(id int primary key, name text)")
>>> c.execute_non_query("create table testchild(id int primary key, idparent int references testparent(id), name text)")
>>> c.execute_non_query("insert into testparent values(1, 'firstparent')")
>>> c.execute_non_query("insert into testchild values(1,1,'firstchild')")
>>> c.execute_non_query("insert into testchild values(2,1111,'badchild')")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
_mssql.MssqlDatabaseException: SQL Server message 547, severity 16, state 0, line 1:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__testchild__idpar__03317E3D". The conflict occurred in database "tempdb", table "dbo.testparent", column 'id'.

>>> c.execute_non_query("alter table testchild nocheck constraint FK__testchild__idpar__03317E3D")  ###### your constraint name will be different
>>> c.execute_non_query("insert into testchild values(2,1111,'badc')")   ###### INSERT succeeded now
>>> c.execute_non_query("alter table testchild check constraint FK__testchild__idpar__03317E3D")
>>> c.execute_query("dbcc checkconstraints('testchild')")
>>> for r in c: print r
...
{0: '[dbo].[testchild] ', 1: '[FK__testchild__idpar__03317E3D] ', 2: "[idparent] = '1111' ", 'Constraint': '[FK__testchild__idpar__03317E3D] ', 'Table': '[dbo].
[testchild] ', 'Where': "[idparent] = '1111' "}
>>>

as you can see it DID return the row. Please give me your feedback.

Regards.

[hungryhobbit]

I'm fairly certain that when I tried (essentially) what you just tried, I got back no results.  However, I just realized that I might be using a somewhat outdated (maybe 6 month old?) version of pymssql, so when the database I'm testing against comes back up (it's offline right now) I'll retest and let you know what I see.

[hungryhobbit]

Finally was able to test things with the latest driver and ... IT WORKS!  Thanks so much for all of your help, and for writing this great driver/library.