I am trying to create a new view with SQL-DMO. When I actually try to add the view to the views collection, I get an error.
The error is:
The sequence is #7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNING options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query....
I am not sure how I am suppose to set these options in SQL-DMO. The error message refers to options of the connection. I don't see a connection object with options to set. How do I set these options in SQL-DMO? The sequence leading to this was:
loSqlServer = CREATEOBJECT('SQLDMO.SqlServer')
loSqlServer.LoginSecure = .T.
loSqlServer.Connect('WAC_SQL_02')
loDataBases = loSqlServer.DataBases
loDataBase = loDataBases.Item['USP']
loDataBaseViews = loDataBase.Views
loDataBaseView = loDataBaseViews.Item['ExistingViewName']
lcText = loDataBaseView.Text
lcText = STRTRAN(lcText,'ExistingViewName','NewViewName')
loNewView = CREATEOBJECT("SQLDMO.View")
loNewView.Name = "NewViewName"
loNewView.Text = lcText
loDataBaseViews.Add(loNewView)