DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 918480 - Last Review: May 17, 2006 - Revision: 1.1

Bug #: 403696 (SQLBUDT)

On This Page

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(@".\MySQLServer");
Database db = srv.Databases["MyDB"];
View vw = db.Views["MyView"];

//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("go");
}

Microsoft Visual Basic .NET

Dim srv As Server = New Server(".\MySQLServer")
Dim db As Database = srv.Databases("MyDB")
Dim vw As View = db.Views("MyView")

'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("go")
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(@".\MySQLServer");
Database db = srv.Databases["MyDB"];
View vw = db.Views["MyView"];

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("go");
}

Visual Basic .NET

Dim srv As Server = New Server(".\MySQLServer")
Dim db As Database = srv.Databases("MyDB")
Dim vw As View = db.Views("MyView")

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("go")
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(@".\MySQLServer");
Database db = srv.Databases["MyDB"];

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("go");
}

Visual Basic .NET

Dim srv As Server = New Server(".\MySQLServer")
Dim db As Database = srv.Databases("MyDB")

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("go")
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 "Applies to" section.

REFERENCES

For more information about the ScriptingOptions class, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx (http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx)

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
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB918480
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support