Solved: Implementing DBCC calls from ADO

[stanl]

I want to implement something as simple as

[vba]
USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
[/vba]
in ADO for SQL Server. What I would like is a stored proc, I could call via an ADO Connection.Execute("EXEC [mystoredproc] 'dbname'")

so then as a stored proc

[vba]
USE @dbname
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
[/vba]
Of course, USE cannot be used in stored procs, and DBCC commands cannot be called directly were I to execute as a Command Object.

I realize that for SQL Server 2005 the suggested method is ALTER INDEX... REBUILD, but when trying that from ADO I run into security errors.

This all may have to do with the ONLINE= option in TSQL... dunno

Just looking for direction. Stan

[Mavyak]

If the security issues are, in fact, from the ONLINE= option, you can add a WITH clause:

ALTER INDEX REBUILD WITH (ONLINE = ON)

[stanl]

If the security issues are, in fact, from the ONLINE= option, you can add a WITH clause:

ALTER INDEX REBUILD WITH (ONLINE = ON)

I know. I have tried

ALTER INDEX ALL ON [table] WITH (ONLINE=ON)

which works great from SQL Server Management Studio, but when issued from a Command Object it fails. The Server is busy until tonight but I will post a snapshot of the error message.

Stan

[stanl]

To summarize: (using either DBCC or ALTER INDEX)

1. I create a connection object (oConn) to an SQL Server DB w/admin user and pwd (have tested with both client and server cursor)

2. I then create a Command Object: (cTable is the table to be reindexed)

[vba]
oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection=oConn
oCmd.CommandType = adCmdUnknown
oCmd.CommandText = "ALTER INDEX ALL ON " & cTable & " REBUILD WITH (ONLINE=ON)"
oCmd.Execute
[/vba]


and I receive an error "Either the Table Does not Exist or you do not have Permission"

My purpose with the post is not that I cannot reindex, because I can work around the error, but more WHY there are issues when I use ADO.

Stan

[Mavyak]

I've seen that error come from case sensitivity before. If your collation includes the string "_CS_" then your table name will need to be case sensitive. Just brainstorming...

[stanl]

I've seen that error come from case sensitivity before. If your collation includes the string "_CS_" then your table name will need to be case sensitive. Just brainstorming...

good suggestion. I was actually using

SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' then selecting from a pre-determined list matched by ucase conversion.

What seems to be working (for ALTER INDEX) is I changed from

Persist Security Info=False;User ID=[uid];Password=[pwd]

to

Integrated Security=SSPI

in the connection string and it appears to be working. I'll mark this solved.. until the next ADO mystery pops up Stan

[Mavyak]

I'll mark this solved.. until the next ADO mystery pops up Stan

Ha! ADO is awesome but it sure can be quirky, too. Glad you're up and running again.