SQLDMO, Can't trap error when executing SQL View script

From: Danny Mansour (anonymous_at_discussions.microsoft.com)
Date: 04/26/04 

Date: Mon, 26 Apr 2004 09:47:11 -0700

Hi There,

I have VB code that uses SQLDMO to transfer views from
DB1 to DB2 by reading the views script from DB1 and
execute the script on DB2. This works fine, but the
problem arises when one of the views in DB1 reference a
table that Does Not exist in DB2.
I put error handling so I can trap for this scenario, but
when the problematic script gets executed, an error
message pops up rather than going to the error handling
code. Even the 'On Error Resume Next' does not do it. I
always get the pop up message. If you have any ideas,
please let me know.

Thanks,
 
Below is a portion of the code:

On Error GoTo ErrorHandler

sSql = "SELECT name FROM sysobjects WHERE (type = 'V') "
Set oQryResult = PubServerObject.Databases
(DB1).ExecuteWithResults(sSql)

iNumberOfRows = oQryResult.Rows
While iNumberOfRows <> 0
  sViewName = oQryResult.GetColumnString(iNumberOfRows, 1)
  Set pView = PubServerObject.Databases
(DB1, "dbo").Views.item(sViewName, "dbo")
 'this is used of the view does not exist
  sScriptFull = pView.Script(SQLDMOScript_Drops +
SQLDMOScript_PrimaryObject + SQLDMOScript_Triggers)
  sScript = pView.text
  Set subView = SubServerObject.Databases
(DB2, "dbo").Views.item(sViewName, "dbo")
  subView.Alter (sScript)

NextView:
  sScript = vbNullString
  iNumberOfRows = iNumberOfRows - 1
 Wend
End If

CleanExit:
  Exit Function

ErrorHandler:
  If Err.Number = -2147199728 Then 'object does not
exist
  SubServerObject.Databases(DB2, "dbo").ExecuteImmediate
sScriptFull, SQLDMOExec_ContinueOnError
  Resume NextView
  End If

Relevant Pages