header
header Register : : Login header
header
divider
menuleft
menuright
submenu
left

We have a new sponsor!  Introducting Pragma Systems.  See the home page for details.
Unanswered Active Topics Forums Search Members
Forums > Using PowerShell > SQL Server
Confused about different State values
Last Post 03 Aug 2009 03:35 PM by geedeearr. 3 Replies.
Printer Friendly
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
geedeearrUser is Offline
New Member
New Member
Posts:7
Avatar

--
29 Jul 2009 08: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
cmille19User is Offline
New Member
New Member
Posts:51
Avatar

--
29 Jul 2009 09: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.
geedeearrUser is Offline
New Member
New Member
Posts:7
Avatar

--
03 Aug 2009 03:35 PM  

---see next post----
geedeearrUser is Offline
New Member
New Member
Posts:7
Avatar

--
03 Aug 2009 03:35 PM  
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.1
right
   
footer Sponsored by Quest Software • SAPIEN Technologies • Compellent • Microsoft Windows Server 2008 footer
footer