Hey Rich,
Sorry to trouble you again but I’m struggling with finding information on
the SMO objects and what parameters can be passed etc... Do you know of an
SMO object I can use to create relationships between entities?
I'm guessing its something like Microsoft.SqlServer.Management.Smo.Index
however I’ve had a play and can't seem to work out what are acceptable
parameters; the lack of intellisense is killing me
So effectively I have created two tables with their own PK and all I want to
do create a relationship between AccessPerson and Test. Here is my code:
# Initialise Pk for AccessPerson.
$index = New-Object Microsoft.SqlServer.Management.Smo.Index($apTable,
"PK_AccessPerson")
$index.IndexKeyType =
[Microsoft.SqlServer.Management.Smo.IndexKeyType]:
riPrimaryKey
$indexedColumn = New-Object
Microsoft.SqlServer.Management.Smo.IndexedColumn($index, "AccessPersonID")
$index.IndexedColumns.Add($indexedColumn)
$apTable.Indexes.Add($index)
# Initialise PK for Test.
$index = New-Object Microsoft.SqlServer.Management.Smo.Index($testTable,
"PK_Test")
$index.IndexKeyType =
[Microsoft.SqlServer.Management.Smo.IndexKeyType]:
riPrimaryKey
$indexedColumn = New-Object
Microsoft.SqlServer.Management.Smo.IndexedColumn($index, "TestID")
$index.IndexedColumns.Add($indexedColumn)
$testTable.Indexes.Add($index)
How to create referential integrity???
Thanks,
"NJC" wrote:
Quote:
> Cool, cheers Rich.
>
> "RichS" wrote:
> Quote:
> > Glad to help. Learning PowerShell and the way to use various .NET features
> > in it is not an easy task.
> >
> > be very interested in what else you are doing with SMO
> > --
> > Richard Siddaway
> > Please note that all scripts are supplied "as is" and with no warranty
> > Blog: http://richardsiddaway.spaces.live.com/
> > PowerShell User Group: http://www.get-psuguk.org.uk
> >
> >
> > "NJC" wrote:
> > Quote:
> > > Ah cool, works perfectly! Many thanks (again) RichS your really helping me
> > > out today. I'm still getting used to the Powershell syntax, so as i say your
> > > help has been much appreciated.
> > >
> > > "RichS" wrote:
> > >
> > > > try
> > > >
> > > > $index.IndexKeyType =
> > > > [Microsoft.SqlServer.Management.Smo.IndexKeyType]:riPrimaryKey
> > > > --
> > > > Richard Siddaway
> > > > Please note that all scripts are supplied "as is" and with no warranty
> > > > Blog: http://richardsiddaway.spaces.live.com/
> > > > PowerShell User Group: http://www.get-psuguk.org.uk
> > > >
> > > >
> > > > "NJC" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I am trying to create a PK for an entity which is being instantiated and
> > > > > added using SMO in Powershell. However, I have found no useful information on
> > > > > this subject matter for Powershell so have decided to evaluate SMO in C# and
> > > > > then convert it to Powershell where possible. I know Powershell uses the .Net
> > > > > library so in theory the SMO object functionality should be the same.
> > > > >
> > > > > The following C# code snippet seems to work fine but I am having difficulty
> > > > > trying to convert it into Powershell. I’ve had a go but I am struggling how
> > > > > exactly to make the newly created index of value “DriPrimaryKey”.
> > > > >
> > > > > Please can someone have a look over my Powershell conversion and advise me
> > > > > where I’m going wrong?
> > > > >
> > > > > C#
> > > > > // Create a PK Index for the table
> > > > > Index index = new Index(newTable, "PK_TestTable");
> > > > > index.IndexKeyType = IndexKeyType.DriPrimaryKey;
> > > > >
> > > > > // The PK index will consist of 1 column, "ID"
> > > > > index.IndexedColumns.Add(new IndexedColumn(index,"ID"));
> > > > >
> > > > > // Add the new index to the table.
> > > > > newTable.Indexes.Add(index);
> > > > >
> > > > > // Physically create the table in the database
> > > > > newTable.Create();
> > > > >
> > > > >
> > > > > Powershell
> > > > > // Create a PK Index for the table
> > > > > $index = New-Object Microsoft.SqlServer.Management.Smo.Index($newTable,
> > > > > "PK_NewTable")
> > > > > index.IndexKeyType = IndexKeyType.DriPrimaryKey;????
> > > > >
> > > > > // The PK index will consist of 1 column, "ID"
> > > > > $indexedColumn = New-Object
> > > > > Microsoft.SqlServer.Management.Smo.IndexedColumn($index, "ID")
> > > > > $index.IndexedColumns.Add($indexedColumn)
> > > > >
> > > > > // Add the new index to the table.
> > > > > $apTable.Indexes.Add($index)
> > > > >
> > > > > Also, I am currently using PowershellAnalyzer as an IDE. Is this best IDE
> > > > > currently available for Powershell? Ideally it would be easier if there was
> > > > > IDE with intellisense but I haven’t as of yet come across one.
> > > > >
> > > > > Thanks,
> > > > >