Microsoft KB Archive/918480

= The part of a script that is defined on an indexed view is not generated when you script an SMO object that is related to the indexed view in SQL Server 2005 =

Article ID: 918480

Article Last Modified on 5/17/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-



Bug #: 403696 (SQLBUDT)



SYMPTOMS
When you script an SQL Server Management Object (SMO) object that is related to an indexed view in Microsoft SQL Server 2005, the part of the script of the clustered index that is defined on the indexed view is not generated. Therefore, you cannot run the generated script to create objects that require a clustered index to be specified on the view first.



WORKAROUND
To work around this problem, use one of the following methods, depending on how you script the SMO object that is related to an indexed view.

Call the Script method of the SMO object

 * 1) Define a ScriptingOptions object.
 * 2) Set the value of the Indexes property of the ScriptingOptions object to True.
 * 3) Instead of calling the Script method of the SMO object, call the Script(ScriptingOptions) method of the SMO object. To do this, pass the ScriptingOptions object to the method Script.

For example, use code that resembles the following code example.

Microsoft Visual C#
Server srv = new Server(@&quot;.\MySQLServer&quot;); Database db = srv.Databases[&quot;MyDB&quot;]; View vw = db.Views[&quot;MyView&quot;];

//Define the ScriptingOptions object. ScriptingOptions sco = new ScriptingOptions;

//Set the value of the Indexes property. sco.Indexes = true;

//Specify the values of the other members of the sco object.

System.Collections.Specialized.StringCollection script = null;

//Pass the ScriptingOptions object. script = vw.Script(sco);

foreach (string str in script) {   Console.WriteLine(str); Console.WriteLine(&quot;go&quot;); }

Microsoft Visual Basic .NET
Dim srv As Server = New Server(&quot;.\MySQLServer&quot;) Dim db As Database = srv.Databases(&quot;MyDB&quot;) Dim vw As View = db.Views(&quot;MyView&quot;)

'Define the ScriptingOptions object. Dim sco As ScriptingOptions = New ScriptingOptions

'Set the value of the Indexes property. sco.Indexes = True

'Specify the values of the other members of the sco object.

'Pass the ScriptingOptions object. Dim script As System.Collections.Specialized.StringCollection = vw.Script(sco)

Dim str As String For Each str In script Console.WriteLine(str) Console.WriteLine(&quot;go&quot;) Next str

Use the Scripter object

 * 1) Set the value of the Options.Indexes member of the Scripter object to True.
 * 2) To generate the script, pass the SMO object reference to the Script method of the Scripter object.

For example, use code that resembles the following code example.

Visual C#
Server srv = new Server(@&quot;.\MySQLServer&quot;); Database db = srv.Databases[&quot;MyDB&quot;]; View vw = db.Views[&quot;MyView&quot;];

Scripter scr = new Scripter; scr.Server = srv;

//Set the value of the Options.Indexes member. scr.Options.Indexes = true;

//Specify the value of the other members of the scr.Options property.

SqlSmoObject[] objs = new SqlSmoObject[1]; objs[0] = vw;

System.Collections.Specialized.StringCollection script = null;

//Pass the SMO object reference to the Script method. script = scr.Script(objs);

foreach (string str in script) {   Console.WriteLine(str); Console.WriteLine(&quot;go&quot;); }

Visual Basic .NET
Dim srv As Server = New Server(&quot;.\MySQLServer&quot;) Dim db As Database = srv.Databases(&quot;MyDB&quot;) Dim vw As View = db.Views(&quot;MyView&quot;)

Dim scr As Scripter = New Scripter scr.Server = srv

'Set the value of the Options.Indexes member. scr.Options.Indexes = True

'Specify the value of the other members of the scr.Options object.

Dim objs As SqlSmoObject = New SqlSmoObject {vw}

'Pass the ScriptingOptions object. Dim script As System.Collections.Specialized.StringCollection = scr.Script(objs)

Dim str As String For Each str In script Console.WriteLine(str) Console.WriteLine(&quot;go&quot;) Next str Note The Options property of a Scripter object returns a ScriptingOptions object.

Use the Transfer object

 * 1) Set the value of the Options.Indexes member of the Transfer object to true.
 * 2) To generate the script, use the ScriptTransfer method of the Transfer object.

For example, use code that resembles the following code example.

Visual C#
Server srv = new Server(@&quot;.\MySQLServer&quot;); Database db = srv.Databases[&quot;MyDB&quot;];

Transfer trans = new Transfer(db);

//Set the value of the Options.Indexes member. trans.Options.Indexes = true;

//Specify the value of the other members of the trans.Options property.

System.Collections.Specialized.StringCollection script = null;

//Use the ScriptTransfer method of the Transfer object to generate the script. script = trans.ScriptTransfer;

foreach (string str in script) {   Console.WriteLine(str); Console.WriteLine(&quot;go&quot;); }

Visual Basic .NET
Dim srv As Server = New Server(&quot;.\MySQLServer&quot;) Dim db As Database = srv.Databases(&quot;MyDB&quot;)

Dim trans As Transfer = New Transfer(db)

'Set the value of the Options.Indexes member. trans.Options.Indexes = True

'Specify the value of the other members of the trans.Options property.

'Use the ScriptTransfer method of the Transfer object to generate the script. Dim script As System.Collections.Specialized.StringCollection = trans.ScriptTransfer

Dim str As String For Each str In script Console.WriteLine(str) Console.WriteLine(&quot;go&quot;) Next str Note The Options property of a Transfer object returns a ScriptingOptions object.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

