How to create, edit and remove stored procedures

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

I'll try to get some sample code posted this weekend if no one else does it first!

Andy

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

Sorry it took so long, had a few things to catch up on! See if this helps:

Dim oServer As SQLDMO.SQLServer

Dim oProc As SQLDMO.StoredProcedure

Dim oProcs As SQLDMO.StoredProcedures

Set oServer = New SQLDMO.SQLServer

With oServer

.LoginSecure = True

.Connect "servernamehere"

End With

Set oProcs = oServer.Databases("Pubs").StoredProcedures

'create the new proc here

Set oProc = New SQLDMO.StoredProcedure

With oProc

.Text = "create proc usp_test as select count(*) from dbo.sysobjects"

End With

'add it

oProcs.Add oProc

'clean up

Set oProc = Nothing

Set oProcs = Nothing

oServer.DisConnect

Set oServer = Nothing

Andy