Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    9

    Unanswered: SMO : Changing a linkedserver datasource

    Hi all,

    I'm trying to change the datasource of a SQL Server LinkedServer using SMO

    I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
    However, the change is lost as soon as the object is destroyed.
    Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
    Alter failed for LinkedServer '<servername>'.


    any ideas how I should be using this class to do what I want?

    I can post my script (PowerShell) if it would help.

    Thanks, Robin.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    please post your code.

  3. #3
    Join Date
    Feb 2007
    Posts
    9
    Code:
    clear-host
    trap {
        ""
        "----------------------"
        "AN ERROR OCCURRED! :-("
        return 255
        }
    [reflection.assembly]::LoadwithPartialName("Microsoft.SQLServer.SMO") | out-Null
    
    
    # Set variables
    if ($args[0] -ne $null) {
        $servers = $args[0] }
    else {$servers = "instancetoconnectto"}
    
    if ($args[1] -ne $null) {
        $linkedserver = $args[1] }
    else {$linkedserver = "LSNAME"}
    
    if ($args[2] -ne $null) {
        $newtarget = $args[2] }
    else {$newtarget = "newserverinstance"}
    
    
    # Display current linked server definition
    "Linked server definition before change:"
    $servers | %{
        # Create SQL Server object
        "Server : " + $_
        $sql = New-Object 'Microsoft.sqlserver.management.smo.server' $_
        $sql.linkedservers|?{$_.Name -eq $linkedserver}|select-object name,datasource
    }
    
    # For each server that was passed on the command line, connect to it and 
    # change the linked server's definition
    $servers | %{
        # Create SQL Server object
        $sql = New-Object 'Microsoft.sqlserver.management.smo.server' $_
        $sql.linkedservers|?{$_.Name -eq $linkedserver}|%{
            $_.DataSource = $newtarget
            $_.Alter()
        }
    }
    
    # Display linked server definition after changes
    ""
    ""
    "Linked server definition after change:"
    $servers | %{
        # Create SQL Server object
        $sql = New-Object 'Microsoft.sqlserver.management.smo.server' $_
        $_
        $sql.linkedservers|?{$_.Name -eq $linkedserver}|select-object name,datasource
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •