December 10, 2009 at 12:31 am
I would like to alter a view which is constituted by tables' data in two different remote servers in a button-click event. As this is the first time I use SMO, there are some coding difficulties.
exception of "Alter failed for View 'dbo.Test4_View'." appeared with 2 more InnerExceptions:
- InnerException1: Use the "new" keyword to create an object instance.
- InnerException2: Check to determine if the object is null before calling the method.
My code in vb file
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Dim conn As New ServerConnection
conn.ConnectionString = "Server=132.100.100.28;Database=StaffSystem;User Id=sa;Password=1983323"
Dim srv As Server
srv = New Server(conn)
Dim db As Database
db = srv.Databases("StaffSystem")
Dim myview As View
myview = New View(db, "Test4_View", "dbo")
myview.TextHeader = "ALTER VIEW dbo.Test4_View AS" & _
myview.TextBody = "SELECT * FROM dbo.Staff"
myview.alter()
'I try to test with a simiplied query in TextBody but the view still cannot be altered
Any Ideas?
kenny Li
December 10, 2009 at 1:59 am
Since the View that you want to ALTER already exists, you cannot create is as a new object, but must instead retrieve it from the database's Views collection:
myview = db.Views("Test4_View", "dbo")
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply