09-21-2007
|
#7 (permalink)
|
|
|
RE: Implementing SMO using Powershell (Part 2)
Have a look at the microsoft.sqlserver.management.smo.foreignkey class http://msdn2.microsoft.com/en-us/lib...oreignkey.aspx
--
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:
> 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:
> > >
> > > > 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,
> > > > > > |
My System Specs | |