Re: Moving User Defined Functions between Databases
From: Stefan Berglund (keepit_at_in.thegroups)
Date: 09/11/04
- Next message: Matt: "should we use a quote for int type?"
- Previous message: Scott: "Re: Subquery Problem and Count"
- In reply to: Aubrey: "Moving User Defined Functions between Databases"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 11 Sep 2004 16:42:23 -0700
On Wed, 8 Sep 2004 18:47:13 -0700, Aubrey <Aubrey@discussions.microsoft.com> wrote:
in <AA5CB124-5AC2-485A-BF94-E11530EA0D4B@microsoft.com>
>On 8/19/2004 amber asked 'moving stored procedures between different
>computers'. Stefan Berglund gave a great answer.
>
>Any suggestions on syntax and/or process to move UDF's?
>
>Using Access 2002 Developer Edition, I tried to modify the SP code to move
>UDF's (User Defined Functions) but code won't compile (or Run). Object
>Browser shows SQLDMO objects including UDF, but they don't seem to be present
>in the database container.
Hi Aubrey-
You can include all your functions, triggers, and stored
procedures in one fell swoop with the SQLDMO.StoredProcedures
object, but all dependencies must be in the correct order.
In other words it's possible to create a single text string with
every stored procedure, function, and trigger in your entire
database.
Here's some sample code that does just that. The source is a
decompressed text file which is stored as a compressed resource,
but it contains all stored procedures, user defined functions and
triggers in the database.
Dim LocalDB As SQLDMO.Database: Set LocalDB = oServer.Databases("ShowTime")
Dim LocalSP As SQLDMO.StoredProcedure: Set LocalSP = New StoredProcedure
ReconstituteData AppPath(GetTempDir), xProcedures
LocalSP.Text = LoadFileAsString(AppPath(GetTempDir) & CStr(xProcedures) & ".xxD")
LocalDB.StoredProcedures.Add LocalSP
In order to create a text file consisting of all your triggers,
user defined functions and stored procedures, do the
following (in VB6 again):
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2
Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "yourserver\andinstancename"
Dim oDB As SQLDMO.Database2
Set oDB = oServer.Databases("yourdatabasename")
Dim strText() As String
ReDim strText(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
For Each oSP In oDB.StoredProcedures
If (Not InStr(1, oSP.Name, "dt_") = 1) Then
Dim lngN As Long
strText(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
End If
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim strTriggers As String: strTriggers = strTriggers & Trim$(oTrigger.Text) & "GO" & vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim strFunctions As String: strFunctions = strFunctions & Trim$(oUDF.Text) & "GO" & vbCrLf & vbCrLf
Next
Set oUDF = Nothing
Set oDB = Nothing
oServer.DisConnect
Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFileNumber
Print #intFileNumber, Trim$(Join(strText, vbCrLf)) & vbCrLf & Trim$(strTriggers) & Trim$(strFunctions)
Close #intFileNumber
End Sub
--- Stefan Berglund
- Next message: Matt: "should we use a quote for int type?"
- Previous message: Scott: "Re: Subquery Problem and Count"
- In reply to: Aubrey: "Moving User Defined Functions between Databases"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
