Confused about different State values - PowerShellCommunity.org - Windows PowerShell Discussion Forums - Using PowerShell

Confused about different State values

Last Post 03 Aug 2009 07:35 AM by geedeearr. 3 Replies.

Sort:

	Prev Next
You are not authorized to post a reply.

Author

Messages

geedeearr

New Member
Posts:7
Avatar

29 Jul 2009 12:09 PM
Hi All, (I'm using PowerGUI to write these scripts) When I use the following to query Linked Servers, param ( [string]$ComputerName = "MyServerName" ) [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') \| out-null $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ComputerName $ls = new-object Microsoft.SqlServer.Management.Smo.LinkedServer $lss = $s.LinkedServers foreach ($ls in $lss) { Write-Host "------------" Write-Host "Name: "$ls.Name \| Out-Null Write-Host "DataSource: "$ls.DataSource \| Out-Null Write-Host "DateLastModified: "$ls.DateLastModified \| Out-Null Write-Host "Catalog: "$ls.Catalog \| Out-Null Write-Host "State: "$ls.State \| Out-Null Write-Host "DataAccess: "$ls.DataAccess \| Out-Null Write-Host "<><><><><><>" } $ls.State = Existing. However when I use this Insert statement into SQL Server $cmd.CommandText = "INSERT INTO DBServerLinkedServers(DBServerPKID, ` NameAlias, ` DataSource, ` DateLastModified, ` LinkedServerCatalog, ` LinkedServerState, ` LinkedServerDataAccess) VALUES (@DBServerPKID, ` @Name, ` @DataSource, ` @DateLastModified, ` @Catalog, ` @State, ` @DataAccess);" $cmd.Connection = $conn $cmd.Parameters.AddWithValue("@DBServerPKID", $DBServerPKID) \| Out-Null $cmd.Parameters.AddWithValue("@Name", $ls.Name) \| Out-Null $cmd.Parameters.AddWithValue("@DataSource", $ls.DataSource) \| Out-Null $cmd.Parameters.AddWithValue("@DateLastModified", $ls.DateLastModified) \| Out-Null $cmd.Parameters.AddWithValue("@Catalog", $ls.Catalog) \| Out-Null $cmd.Parameters.AddWithValue("@State", $ls.State) \| Out-Null $cmd.Parameters.AddWithValue("@DataAccess", $ls.DataAccess) \| Out-Null $cmd.ExecuteNonQuery() \| Out-Null $ls.State = 2. I know that the enumeration for existing is 2, but why the difference in the return value? Is there something going on "under the hood" that I am obviously unaware? Thank you gdr

Chad Miller User is Offline

Basic Member
Posts:160
Avatar

29 Jul 2009 01:35 PM
The underlying type of an emumeration is an int, just as the underlying type for a boolean is bit. If you want string you could call the ToString method on an emun. In any case, why do you care about the state anyways? My understanding of state in SMO is that used internally and state will always be existing unless you haven't called the create method on certain SMO objects.

geedeearr

New Member
Posts:7
Avatar

03 Aug 2009 07:35 AM
---see next post----

geedeearr

New Member
Posts:7
Avatar

03 Aug 2009 07:35 AM
OK Thank you. Since State is not a property to be concerned about (although it was not me who wanted to know about it) how about LoginMode? Or a couple of other parameters I know have this same result....that have these same characteristics. When called as in the first example of my post, param ( [string]$ComputerName = "MyComputer" ) [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') \| out-null $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ComputerName Write-Host "------------" Write-Host "LoginMode: " $s.LoginMode Write-Host "------------" it returns either "Integrated" or "Mixed". When inserting into a SQL Server database, [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') \| out-null # For data acquisition - Create the SMO connection object $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ComputerName # For data insert - Create SqlConnection object, define connection string, and open connection $conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = "Server=MyServer; Database=MyDatabase; Integrated Security=true" $conn.Open() # For data insert - Create SqlCommand object, define command text, and set the connection $cmd = New-Object System.Data.SqlClient.SqlCommand $cmd.CommandText = "INSERT INTO DBServer(DBServerLoginMode) VALUES (@LoginMode)" $cmd.Connection = $conn $cmd.Parameters.AddWithValue("@LoginMode", $s.Settings.LoginMode) \| Out-Null # Execute INSERT statement $cmd.ExecuteNonQuery() \| Out-Null # Close the connections $conn.Close() it inserts 1 or 2. Of course I am inserting more parameters that just LoginMode) You will also notice that when returning "Integrated" or "Mixed" I have NOT used ToString. I know about the enumeration and my question stands: "Why does this return different values as I can see no difference in what I've written?" Thank you. gdr

You are not authorized to post a reply.

Forums > Using PowerShell > SQL Server

Active Forums 4.3