How to alter connection string using powershell?

Welcome Forums General PowerShell Q&A How to alter connection string using powershell?

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #123753

    Participant
    Points: 90
    Rank: Member

    Apparently the databases/cubes in an analysis server contain "script connection as > alter" option

    I want to use a powershell script like this:

        $hashtable = @{}
    Import-Csv "CSV_file" | ForEach-Object {
        $hashtable += @{$($_.Server) = ($_.Cube -split '\s*,\s*') }
    }
    
    Import-Module SqlServer
    
    foreach($server in $hashtable.Keys){ 
    
       $Analysis_Server = New-Object Microsoft.AnalysisServices.Server  
       $Analysis_Server.connect("$server") 
    
       foreach($CUBE in $hashtable[$server]) {
    
          ####### Setting connection property for $Cube #######
    "  
        
        
            $CUBE
        
        
            
                Connection Timeout=60;User Id=someID;Password=pass;Data Source=td.domain.com;Persist Security Info=True;Session Character Set=UTF8
            
        
        
        "
    
        }
    }

    that takes input a csv file

    Server,Cube

    server1.domain.com,Database1

    and loops through the databases/cubes in the server to alter their DataSource ``

    However i am sure that there is something missing in that script, but the thing is if i run that XML in SSMS, it complain about the Name element for DataSource is required. i understand that the full XML is needed, but it wont work from a script perspective that i am trying to accomplish because i cant have the same DataSource for other databases i have in the server. they are all possibly different, so the ID, Name, etc...have to be removed from the XML query in powershell script.

    i receive this error

    The object definition supplied for the ALTER statement is of a different type that the object reference to be altered.

  • #124194

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    Unfortunately, this isn't really a PowerShell question per se. The error you're getting is from the database server. Also, note that you can't just paste XML here in the forums. You'd need to make a Gist of it in order to get raw XML to display.

    I'm not sure I'd personally be able to help, here, because I don't understand entirely what you're asking the database server to do. It might be worth asking in a more SQL Server-specific forum, or on StackOverflow.com, which is a bit broader...?

  • #124197

    Participant
    Points: 90
    Rank: Member

    Unfortunately, this isn't really a PowerShell question per se. The error you're getting is from the database server. Also, note that you can't just paste XML here in the forums. You'd need to make a Gist of it in order to get raw XML to display.

    I'm not sure I'd personally be able to help, here, because I don't understand entirely what you're asking the database server to do. It might be worth asking in a more SQL Server-specific forum, or on StackOverflow.com, which is a bit broader...?

    It's ok, I have figured it out 🙂

    Import-Module SqlServer
    
    $newConnectionString = "Connection Timeout=60;User Id=SOME_NEW_ID;Data Source=10.10.19.10;Persist Security Info=True;Session Character Set=UTF8"
    
    $svr = new-Object Microsoft.AnalysisServices.Tabular.Server
    $svr.Connect("server1.domain.com")
    
    $svr.databases[1].model.datasources[0].ConnectionString = $newConnectionString
    $svr.Databases[1].Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)

You must be logged in to reply to this topic.