How to update Database?

Welcome Forums General PowerShell Q&A How to update Database?

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

 
Participant
1 month ago.

  • Author
    Posts
  • #123924

    Participant
    Points: 90
    Rank: Member

    Based on https://docs.microsoft.com/en-us/bi-reference/tom/add-a-data-source-to-tabular-model-analysis-services-amo-tom

    i am trying to update the Database connectionstring changes:

    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].model.datasources[0].Update(UpdateOptions.ExpandFull)

    But i am getting errors:

    Unexpected token 'UpdateOptions.ExpandFull' in expression or statement.

    if i do regular Update():

    $svr.Databases[1].model.datasources[0].Update()

    i get this:

    Method invocation failed because [Microsoft.AnalysisServices.Tabular.ProviderDataSource] does not contain a method named 'Update'

    and if i try SaveChanges():

    $svr.Databases[1].Model.SaveChanges()

    I get this error:

    The following exception occurred while retrieving member "SaveChanges": "Encountered an invalid type for a default value."

  • #123942

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    What is UpdateOptions it should be a variable of some type.

    See : https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.updateoptions?view=sqlserver-2016

    you can specify any one of the update options.

  • #123959

    Participant
    Points: 90
    Rank: Member

    What is UpdateOptions it should be a variable of some type.

    See : https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.updateoptions?view=sqlserver-2016

    you can specify any one of the update options.

    I did, I specified expandfull

  • #123962

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    I meant, just "Expandfull" (not UpdateOptions.ExpandFull) , PowerShell will try to convert it to the desired type.

  • #123965

    Participant
    Points: 90
    Rank: Member

    tried that, i get

    Unexpected token 'ExpandFull' in expression or statement.

  • #123969

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    Make sure

    $svr.databases has output
    $svr.Databases[1].model has output
    $svr.Databases[1].model.datasources[0] has output

    $svr.Databases[1].model.datasources[0].Update should show the overloaded definitions for this method if available.

    If none of the above has output, then the connection is not established.

  • #123972

    Participant
    Points: 90
    Rank: Member

    $svr.Databases[1].model

    all those have output as i expect except this:

    $svr.Databases[1].model

    i get this error:

    format-default : The following exception occurred while retrieving members: "Encountered an invalid type for a default
    value."
    + CategoryInfo : NotSpecified: (:) [format-default], ExtendedTypeSystemException
    + FullyQualifiedErrorId : CatchFromBaseGetMembers,Microsoft.PowerShell.Commands.FormatDefaultCommand


    this $svr.Databases[1].model.datasources[0]

    prints out this:

    ConnectionString : User Id=...;Data Source=....;Persist Security Info=True
    ImpersonationMode : ImpersonateServiceAccount
    Account :
    Password :
    Isolation : ReadCommitted
    Timeout : 0
    Provider : Teradata.Client.Provider
    ObjectType : DataSource
    Parent : Microsoft.AnalysisServices.Tabular.Model
    Annotations : {ConnectionEditUISource}
    ExtendedProperties : {}
    Name : TD
    Description :
    Type : Provider
    MaxConnections : 10
    ModifiedTime : 10/16/2018 6:21:06 PM
    Model : Microsoft.AnalysisServices.Tabular.Model
    IsRemoved : False


    as for this:

    $svr.Databases[1].model.datasources[0].Update

    it doesnt print out an error, in fact it prints out nothing (expected i guess) but i refreshed the targeted DB and the newConnectionSTring did not take effect at all....

  • #123974

    Participant
    Points: 90
    Rank: Member

    I figured it out! finally!

    $svr.Databases[1].Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)

You must be logged in to reply to this topic.