How to update Database?

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."

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

See : UpdateOptions Enum (Microsoft.AnalysisServices) | Microsoft Docs

you can specify any one of the update options.

[quote quote=123942]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.

[/quote]
I did, I specified expandfull

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

[quote quote=123962][/quote]

tried that, i get

Unexpected token ‘ExpandFull’ in expression or statement.

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.

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: (:slight_smile: [format-default], ExtendedTypeSystemException
+ FullyQualifiedErrorId : CatchFromBaseGetMembers,Microsoft.PowerShell.Commands.FormatDefaultCommand

<hr />

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

<hr />

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…

I figured it out! finally!

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