sql server msde: Hello All,

I'm using Access as a front end to MSDE 2000. What is the best way to
shrink the database using code?

Presently I'm using a stored procedure called from VB, however error
3251 is generated and the database is still shrunk. It works, but I
thought maybe there may be a problem due to the error message in VB. The
VB error message, stored procedure, and VB procedure are in the box
quotes below:

,----- [ VB error message ]
| Run-time error '3251':
|
| Object or provider is not capable of performing requested operation.
`-----

,----- [ MSDE 2000 stored procedure ]
| ALTER PROCEDURE dbo.spShrinkDB
| AS
| DBCC SHRINKDATABASE (ScrCdSetupSQL, 50)
`-----

,----- [ VB Procedure ]
| Sub ShrinkDBafterDropTbls()
| Dim cmd As ADODB.Command
| Dim lngRecs As Long
| On Error GoTo Error_ShrinkDBafterDropTbls
|
| Set cmd = New ADODB.Command
|
| Set cmd.ActiveConnection = CurrentProject.Connection
| cmd.CommandText = "spShrinkDB"
| cmd.CommandType = adCmdStoredProc
| cmd.Execute RecordsAffected:=lngRecs, _
| Options:=adExecuteNoRecords
|
| Error_ShrinkDBafterDropTbls:
| If Err.Number = 3251 Then
| MsgBox "The database has been shrunk.", vbOKOnly, "Shrunk Database"
| Else
| Resume Next
| End If
|
| Set cmd = Nothing
| End Sub
`-----

I'm learning Microsoft SQL server, hence the question.

TIA!

--
Regards,

hi Greg,
[quoted text, click to view]

did not use Access but VB6, and the following code works as expected,
without errors..

-- SQL Code
USE Pubs;
GO
CREATE PROC dbo.usp_shrink
AS BEGIN
DBCC SHRINKDATABASE (Pubs, 50);
END;
GO
EXEC dbo.usp_shrink;
GO
DROP PROCEDURE dbo.usp_shrink;
'------------------------------------
Option Explicit

Private Sub Form_Load()

Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
With oCon
.ConnectionString = "Provider=sqloledb;Data Source=(Local);Initial
Catalog=pubs;Integrated Security=SSPI;"
' default is adUseServer = 2
.CursorLocation = adUseClient
.Open
End With

Dim oCmd As ADODB.Command
Set oCmd = New ADODB.Command
With oCmd
.ActiveConnection = oCon
.CommandText = "dbo.usp_shrink"
.CommandTimeout = 90
.CommandType = adCmdStoredProc
End With

'lRows will alway be -1 as no data will be returned, both with
' connection.cursorlocation = adUseServer and adUseClient
Dim lRows As Long

On Local Error Resume Next
oCmd.Execute Recordsaffected:=lRows, Options:=adExecuteNoRecords
Debug.Print Err.Number; Err.Description; Err.Source
On Local Error GoTo 0

Set oCmd = Nothing
Set oCon = Nothing

End Sub

what kind of connection string (aka provider) do you use to interact with
SQL Server?
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

On Thu, 27 Apr 2006 17:42:16 +0200, "Andrea Montanari"
[quoted text, click to view]

Since you are using VB6 and I'm using Access VB, there maybe
differences.


[quoted text, click to view]

If I use Access project (i.e. MyDBSQL.adp file) with 'Create Text Stored
Procedure' on the query tab I MUST start the text with 'CREATE
PROCEDURE', so I think the most basic difference is how Access ADP files
connect to MSDE 2000 ( & probably SQL Server) which probably changes the
code somewhat. This works in a text stored procedure from MS Access:

,----- [ Access stored procedure ]
| ALTER PROC dbo.sp_Shrink
| AS BEGIN
| DBCC SHRINKDATABASE (ScrCdSetupSQL, 50);
| END
| EXEC dbo.sp_Shrink
| DROP PROCEDURE dbo.sp_Shrink
`-----

[quoted text, click to view]

Ok. Is local error on the client? I would think it is. If yes, then you
are touching a subject that I've thought about. How can Access receive
error messages from MSDE 2000 (&/or SQL Server)?

From what I've read it is much easier to use Access with SQL server
(i.e. MSDE being scaled down version). I couldn't really say because I'm
learning. Just rolling up the sleeves and learning by doing.


[quoted text, click to view]

In Access ADP (i.e. Project file) I was using:

Set cmd.ActiveConnection = CurrentProject.Connection

You have helped. As stated previously you have touched a subject that
I've previously thought. Anyhow after going through your code I've made
some modifications to my code which now runs only printing the error to
the 'Immediate Window'. In doing so I've learned that apparently there
ARE differences in how the connection is made. It looks like you are
trying to set specifics to the connection string that I could NOT get to
work in Access VB. Specifically:

.ConnectionString = "Provider=sqloledb;Data
Source=(Local);Initial "Catalog=pubs;Integrated Security=SSPI;"


I tried:

.ConnectionString = "Provider=SQLOLEDB;Data
Source=GWS-P4-2-4\GW;Initial "Catalog=ScrCdSetupSQL;Integrated
Security=SSPI;"

This generates an error. Please note I use a named instance. The
following modification works:

.ConnectionString = "Provider=SQLOLEDB;Data
Source=GWS-P4-2-4\GW;Database=ScrCdSetupSQL;User Id=sa;Password=MyPswd;"

I just don't like having to put the password in code. With the above it
looks like SQL is passing an error to Access because the immediate
window prints:

-2147217900 Maximum stored procedure, function, trigger, or view nesting
level exceeded (limit 32).Microsoft OLE DB Provider for SQL Server

It looks like you are trying to use integrated security with NT which I
can't get to work. I'll have to investigate further the differences
between the 2 methods use to connect to SQL server (i.e. MSDE 2000 in my
case) because it appears the one provide a means for SQL Server to pass
errors to MS Access's immediate window.

Thanks again!

--
Regards,