View Full Version : Solved: Implementing DBCC calls from ADO
stanl
08-08-2008, 08:55 AM
I want to implement something as simple as
USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
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
USE @dbname
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
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:dunno
Just looking for direction. Stan
Mavyak
08-08-2008, 11:47 AM
If the security issues are, in fact, from the ONLINE= option, you can add a WITH clause:
ALTER INDEX REBUILD WITH (ONLINE = ON)
stanl
08-09-2008, 05:03 AM
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
08-10-2008, 06:10 AM
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)
oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection=oConn
oCmd.CommandType = adCmdUnknown
oCmd.CommandText = "ALTER INDEX ALL ON " & cTable & " REBUILD WITH (ONLINE=ON)"
oCmd.Execute
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
08-10-2008, 12:30 PM
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
08-10-2008, 04:55 PM
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:bug: Stan
Mavyak
08-11-2008, 06:57 AM
I'll mark this solved.. until the next ADO mystery pops up:bug: Stan
Ha! ADO is awesome but it sure can be quirky, too. Glad you're up and running again.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.