Vista - Implementing SMO using Powershell (Part 2)

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,

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:

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:

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:

Cool, cheers Rich.

"RichS" wrote:

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:

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:

Hey Rich,

Thanks for info (again ). Thats all I needed to get it sorted!

Cheers again,


"RichS" wrote: