Getting Stored Procedure text with vb.net 
Author Message
 Getting Stored Procedure text with vb.net

I need to copy stored procedures from one server to another using a vb.net
application. What I would first like to do is script out the existing stored
procedure so I can back it up somewhere, then copy a stored proc between two
servers. Is that possible using .net?

Thanks,
Shaun



Sun, 08 May 2005 09:20:42 GMT  
 Getting Stored Procedure text with vb.net
Of course David Sceppa might disagree with old RT on this one but you could
use the SQLDMO to acquire all the SPs of a specific database from SQL
Server. And they don't have to be local, either.

You're going to need a reference SQLDMO. It will be locatedin the COM
references.

Dim MyServer as SQLServer
Set MyServer = New SQLServer
MyServer.LoginSecure = True
MyServer.Connect "" '(Between the quotes add the name of your remote server)
', UserName, Password not required here if LoginSecure is set to true
Set MyDatabase = MyServer.Databases("Northwind", MyServer)

Set SProcedures = MyDatabase.StoredProcedures
For Each SProcedure In SProcedures
console.Out.WriteLine(SProcedure.Name)
Next

HTH


Quote:
> I need to copy stored procedures from one server to another using a vb.net
> application. What I would first like to do is script out the existing
stored
> procedure so I can back it up somewhere, then copy a stored proc between
two
> servers. Is that possible using .net?

> Thanks,
> Shaun



Sun, 08 May 2005 11:34:50 GMT  
 Getting Stored Procedure text with vb.net
I was trying to do this with SQLDMO before, but I couldn't log on. Now that
I have LoginSecure on it is working. It doesn't look like SQLDMO supports
passing parameters to MyServer.Databases, at least it doesn't come up in
IntelliSense. Is that the correct function?

When you script out stored procedures from SQL 2000 you get the option the
script out object level permissions. Is that possible using SQLDMO?

Thanks for your help,
Shaun



Quote:
> Of course David Sceppa might disagree with old RT on this one but you
could
> use the SQLDMO to acquire all the SPs of a specific database from SQL
> Server. And they don't have to be local, either.

> You're going to need a reference SQLDMO. It will be locatedin the COM
> references.

> Dim MyServer as SQLServer
> Set MyServer = New SQLServer
> MyServer.LoginSecure = True
> MyServer.Connect "" '(Between the quotes add the name of your remote
server)
> ', UserName, Password not required here if LoginSecure is set to true
> Set MyDatabase = MyServer.Databases("Northwind", MyServer)

> Set SProcedures = MyDatabase.StoredProcedures
> For Each SProcedure In SProcedures
> console.Out.WriteLine(SProcedure.Name)
> Next

> HTH



> > I need to copy stored procedures from one server to another using a
vb.net
> > application. What I would first like to do is script out the existing
> stored
> > procedure so I can back it up somewhere, then copy a stored proc between
> two
> > servers. Is that possible using .net?

> > Thanks,
> > Shaun



Sun, 08 May 2005 12:05:31 GMT  
 Getting Stored Procedure text with vb.net
I found the problem with that .Databases call. It should be:

Set MyDatabase = MyServer.Databases.Item("Northwind", MyServer)


Quote:
> I was trying to do this with SQLDMO before, but I couldn't log on. Now
that
> I have LoginSecure on it is working. It doesn't look like SQLDMO supports
> passing parameters to MyServer.Databases, at least it doesn't come up in
> IntelliSense. Is that the correct function?

> When you script out stored procedures from SQL 2000 you get the option the
> script out object level permissions. Is that possible using SQLDMO?

> Thanks for your help,
> Shaun



> > Of course David Sceppa might disagree with old RT on this one but you
> could
> > use the SQLDMO to acquire all the SPs of a specific database from SQL
> > Server. And they don't have to be local, either.

> > You're going to need a reference SQLDMO. It will be locatedin the COM
> > references.

> > Dim MyServer as SQLServer
> > Set MyServer = New SQLServer
> > MyServer.LoginSecure = True
> > MyServer.Connect "" '(Between the quotes add the name of your remote
> server)
> > ', UserName, Password not required here if LoginSecure is set to true
> > Set MyDatabase = MyServer.Databases("Northwind", MyServer)

> > Set SProcedures = MyDatabase.StoredProcedures
> > For Each SProcedure In SProcedures
> > console.Out.WriteLine(SProcedure.Name)
> > Next

> > HTH



> > > I need to copy stored procedures from one server to another using a
> vb.net
> > > application. What I would first like to do is script out the existing
> > stored
> > > procedure so I can back it up somewhere, then copy a stored proc
between
> > two
> > > servers. Is that possible using .net?

> > > Thanks,
> > > Shaun



Sun, 08 May 2005 12:09:59 GMT  
 Getting Stored Procedure text with vb.net
So, from this, I take it you're happy?


Quote:
> I found the problem with that .Databases call. It should be:

> Set MyDatabase = MyServer.Databases.Item("Northwind", MyServer)



> > I was trying to do this with SQLDMO before, but I couldn't log on. Now
> that
> > I have LoginSecure on it is working. It doesn't look like SQLDMO
supports
> > passing parameters to MyServer.Databases, at least it doesn't come up in
> > IntelliSense. Is that the correct function?

> > When you script out stored procedures from SQL 2000 you get the option
the
> > script out object level permissions. Is that possible using SQLDMO?

> > Thanks for your help,
> > Shaun



> > > Of course David Sceppa might disagree with old RT on this one but you
> > could
> > > use the SQLDMO to acquire all the SPs of a specific database from SQL
> > > Server. And they don't have to be local, either.

> > > You're going to need a reference SQLDMO. It will be locatedin the COM
> > > references.

> > > Dim MyServer as SQLServer
> > > Set MyServer = New SQLServer
> > > MyServer.LoginSecure = True
> > > MyServer.Connect "" '(Between the quotes add the name of your remote
> > server)
> > > ', UserName, Password not required here if LoginSecure is set to true
> > > Set MyDatabase = MyServer.Databases("Northwind", MyServer)

> > > Set SProcedures = MyDatabase.StoredProcedures
> > > For Each SProcedure In SProcedures
> > > console.Out.WriteLine(SProcedure.Name)
> > > Next

> > > HTH



> > > > I need to copy stored procedures from one server to another using a
> > vb.net
> > > > application. What I would first like to do is script out the
existing
> > > stored
> > > > procedure so I can back it up somewhere, then copy a stored proc
> between
> > > two
> > > > servers. Is that possible using .net?

> > > > Thanks,
> > > > Shaun



Sun, 08 May 2005 13:00:24 GMT  
 Getting Stored Procedure text with vb.net
Hey Shawn:

Here's the other way:

Imports System.Data.OleDb

Public Class Form1

Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

End If

MyBase.Dispose(disposing)

End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

<System.Diagnostics.De{*filter*}StepThrough()> Private Sub InitializeComponent()

Me.DataGrid1 = New System.Windows.Forms.DataGrid()

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()

Me.SuspendLayout()

'

'DataGrid1

'

Me.DataGrid1.DataMember = ""

Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText

Me.DataGrid1.Location = New System.Drawing.Point(0, 8)

Me.DataGrid1.Name = "DataGrid1"

Me.DataGrid1.Size = New System.Drawing.Size(656, 320)

Me.DataGrid1.TabIndex = 0

'

'Form1

'

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(656, 518)

Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1})

Me.Name = "Form1"

Me.Text = "Form1"

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()

Me.ResumeLayout(False)

End Sub

#End Region

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim cn As OleDbConnection = New OleDbConnection("Provider=sqloledb;Data
Source=;Initial Catalog=Northwind;Integrated Security=SSPI")

cn.Open()

Dim tb As System.Data.DataTable

tb = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, Nothing)

DataGrid1.DataSource = tb

End Sub

End Class

HTH


Quote:
> I found the problem with that .Databases call. It should be:

> Set MyDatabase = MyServer.Databases.Item("Northwind", MyServer)



> > I was trying to do this with SQLDMO before, but I couldn't log on. Now
> that
> > I have LoginSecure on it is working. It doesn't look like SQLDMO
supports
> > passing parameters to MyServer.Databases, at least it doesn't come up in
> > IntelliSense. Is that the correct function?

> > When you script out stored procedures from SQL 2000 you get the option
the
> > script out object level permissions. Is that possible using SQLDMO?

> > Thanks for your help,
> > Shaun



> > > Of course David Sceppa might disagree with old RT on this one but you
> > could
> > > use the SQLDMO to acquire all the SPs of a specific database from SQL
> > > Server. And they don't have to be local, either.

> > > You're going to need a reference SQLDMO. It will be locatedin the COM
> > > references.

> > > Dim MyServer as SQLServer
> > > Set MyServer = New SQLServer
> > > MyServer.LoginSecure = True
> > > MyServer.Connect "" '(Between the quotes add the name of your remote
> > server)
> > > ', UserName, Password not required here if LoginSecure is set to true
> > > Set MyDatabase = MyServer.Databases("Northwind", MyServer)

> > > Set SProcedures = MyDatabase.StoredProcedures
> > > For Each SProcedure In SProcedures
> > > console.Out.WriteLine(SProcedure.Name)
> > > Next

> > > HTH



> > > > I need to copy stored procedures from one server to another using a
> > vb.net
> > > > application. What I would first like to do is script out the
existing
> > > stored
> > > > procedure so I can back it up somewhere, then copy a stored proc
> between
> > > two
> > > > servers. Is that possible using .net?

> > > > Thanks,
> > > > Shaun



Sun, 08 May 2005 13:15:33 GMT  
 Getting Stored Procedure text with vb.net
Yeah, thanks for your help. My main problem was setting LoginSecure.

Thanks,
Shaun



Quote:
> So, from this, I take it you're happy?



> > I found the problem with that .Databases call. It should be:

> > Set MyDatabase = MyServer.Databases.Item("Northwind", MyServer)



> > > I was trying to do this with SQLDMO before, but I couldn't log on. Now
> > that
> > > I have LoginSecure on it is working. It doesn't look like SQLDMO
> supports
> > > passing parameters to MyServer.Databases, at least it doesn't come up
in
> > > IntelliSense. Is that the correct function?

> > > When you script out stored procedures from SQL 2000 you get the option
> the
> > > script out object level permissions. Is that possible using SQLDMO?

> > > Thanks for your help,
> > > Shaun



> > > > Of course David Sceppa might disagree with old RT on this one but
you
> > > could
> > > > use the SQLDMO to acquire all the SPs of a specific database from
SQL
> > > > Server. And they don't have to be local, either.

> > > > You're going to need a reference SQLDMO. It will be locatedin the
COM
> > > > references.

> > > > Dim MyServer as SQLServer
> > > > Set MyServer = New SQLServer
> > > > MyServer.LoginSecure = True
> > > > MyServer.Connect "" '(Between the quotes add the name of your remote
> > > server)
> > > > ', UserName, Password not required here if LoginSecure is set to
true
> > > > Set MyDatabase = MyServer.Databases("Northwind", MyServer)

> > > > Set SProcedures = MyDatabase.StoredProcedures
> > > > For Each SProcedure In SProcedures
> > > > console.Out.WriteLine(SProcedure.Name)
> > > > Next

> > > > HTH



> > > > > I need to copy stored procedures from one server to another using
a
> > > vb.net
> > > > > application. What I would first like to do is script out the
> existing
> > > > stored
> > > > > procedure so I can back it up somewhere, then copy a stored proc
> > between
> > > > two
> > > > > servers. Is that possible using .net?

> > > > > Thanks,
> > > > > Shaun



Sun, 08 May 2005 22:25:13 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. ASP.net VB.Net Stored Procedures in SQL Server

2. VB.Net => Ado.Net = Stored Procedure

3. Getting a recordset from a DB2 Stored Procedure with ADO in VB 6

4. Getting a return value from a SQL Server stored procedure through VB

5. Getting data from a stored procedure using VB

6. Problem getting parameter returned from stored procedure in VB

7. Getting the output from a stored procedure in VB.

8. Getting the output from a stored procedure in VB.

9. Create Stored Procedures in VB.NET

10. VB.net and Stored Procedure

11. VB.NET,SQL SERVER 2000 STORED PROCEDURE PARAMETERS, CRYSTAL REPORTS

12. VB.Net Stored Procedure Generation

 

 
Powered by phpBB® Forum Software