very basic question re DBCC shrinkdatabase 1

[cyberbiker]

In the app I am working on converting to SQL, I will need to provide a way to shrink the database from a menu. The threads and FAQ's have been pretty helpful, but I seem to be missing something.

Am I correct in assuming that to use DBCC I will need to call a stored procedure?

Is it possible run a DBCC command directly from vb code?

I think I can see the advantage to running a stored procedure on the server, but I am wondering if I am correct or heading off in the wrong direction again.

Advice would be appreciated Terry (cyberbiker)

 

[mhweiss]

Why wouldn't you want to run a stored procedure? More secure and easy to implement...I would wholeheartedly recommend the stored procedure route...
hth,
Michael

 

[srinivasaraghavan]

Hi

U can do anything from Vb
try this
sp_executesql N'DBCC CHECKDB'


Srinivasa Raghavan

 

[tlbroadbent]

You can do anything from VB that the SQL user has permission to do. DBCC SHRINKDATABASE permissions default to sys admins and db owners. No other user can be granted permission.

What is the purpose of adding a maintenance utility to a VB program? Maintenance processes should be controlled by DBAs. Allowing users to run processes such as SHRINKDATABASE could lead to performance problems if the processes are run at the wrong time.

If you need to develop a maintenance tool, have you considered using SQLDMO? SQLDMO can be used in a VB program and provides many objects, methods and properties. For example, you could use the SHRINK method of the database object. (Note: The user must still be a sys admin or the db owner to shrink a DB.)

SQL BOL contains a SQLDMO reference. Many samples were provided with SQL Server. BOL explains where to find the samples. Here are some additional links. Some have downloadable code.

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/sql-dmo.asp

http://www.vbcodemaster.com/sqldmo.asp

http://msdn.microsoft.com/msdnmag/issues/01/05/sqldmo/sqldmo.asp

faq183-1637

http://www.swynk.com/friends/warren/introdmo.asp

http://www.swynk.com/friends/warren/moredmo.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cyberbiker]

Thanks to all for the help and advice.
To Terry Broadbent. The answer to why is:
The program will only use SQL tables and may or may not be on a network. Our program is part of a machine control package. Most customers will not have a dba. If I can get pemission to do so I would like to consider at least automating the process but (and I quote) "This is the way I want it"
My programming skills are improving here I must admit, but my people skills need honed I guess.
To mhweiss: No real reason not to use the stored procedure other than not knowing which way to turn. Your information has given me something to think about Thanks.
to Srinivasa Raghavan: Your post shows me that I can do this from vb if I choose to. Thanks very much.
Now I just need to analyze this and determine which way fits what I need. Thanks a lot
Terry (cyberbiker)