Odtnhj

I am trying to get the connection string of a database datasource with the following script:

 $Analysis_Server = New-Object Microsoft.AnalysisServices.Server 
 $Analysis_Server.connect("$server") 

 $database = $Analysis_Server.Databases[7]
 $c = $database.DataSources[0].ConnectionString
 $c

nothing is outputting.

I have tried debugging like so:

$Analysis_Server.Databases

this prints out all databases on the server

if i index it $Analysis_Server.Databases, it prints whatever database is indexed (so in my case, 7, prints database8)

clearly the Database property is working.

-------------UPDATE:--------------

here is how the Databases look in the server

here is what each of the following lines does:

 $Analysis_Server.Databases

this prints out

One thing one can notice is for some reason they are printed out not in order of how they are on the analysis server as in the picture...I dont know why that is the case

this is what this command prints out:

Analysis_Server.Databases[0]

Now since index 0 worked, we should be able to index [1], [2], etc...

so the following

Analysis_Server.Databases[1] 
Analysis_Server.Databases[2]

prints:

Now unto the connection string:

$Analysis_Server.Databases[0].DataSources[0].ConnectionString

that prints out:

Connection Timeout=60;User Id=someID;Data Source=10.10.10.10;Persist Security Info=True

its appears to be the connection string for Database8

cool, so then we should be able to do this as well:

$Analysis_Server.Databases[1].DataSources[0].ConnectionString

However, nothing prints! the only index that seems to print is with Databases[0]

This is what $Analysis_Server.Databases[0].DataSources[0] prints:

we should be able to do the same for all databases

$Analysis_Server.Databases[1].DataSources[0]

$Analysis_Server.Databases[2].DataSources[0]
etc..

but nothing gets printed!

I just played with things a little more, to make sure I understood the issue that you are facing. On the same box, still with the newest available SqlServer PowerShell module loaded - I get these results.

PS C:UsersAdministrator> $Analysis_Server = New-Object Microsoft.AnalysisServices.Server
PS C:UsersAdministrator> $Analysis_Server.connect("AX2012R2A")
PS C:UsersAdministrator> $Analysis_Server.Databases

Name State Read-Write Mode
---- ----- ---------------
Demand Forecast ps Processed ReadWrite
Demand Forecast Accuracy ps Processed ReadWrite
Demand Forecast Accuracy initial Processed ReadWrite
Dynamics AX ps Processed ReadWrite
Demand Forecast initial Processed ReadWrite
Dynamics AX initial Processed ReadWrite

Now I traverse every database and their datasources, to display the connection string

PS C:UsersAdministrator> $Analysis_Server.Databases | ForEach-Object $_.datasources

Name Isolation Max Connections Connection String
---- --------- --------------- -----------------
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_4
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_2
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_1
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_6
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_3
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_5

And the one-liner that just gives you the connection string and nothing else

PS C:UsersAdministrator> $Analysis_Server.Databases | ForEach-Object $_.datasources 
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_4
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_2
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_1
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_6
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_3
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_5

Could you take the time and try to install the latest SqlServer PowerShell module and see if that makes any difference for you and the problem that you are facing?

Not saying that I have an answer for you, but I think you can use another approach to get what you need, while waiting for Microsoft support.

I'm working on a AX 2012 demo system, that is running SQL Server 2014. It has several olap databases inside the same instance. I've edited all datasources to point to a different database, to show that we get the correct details out.

I installed the latest SQL Server PowerShell module while testing this.

Install-Module SqlServer -Force -Confirm:$false
Import-Module SqlServer

Now you should have a new PowerShell provider, that enables you to traverse the SQL Server as was it a file system

Get-PSProvider

Name Capabilities Drives
---- ------------ ------
Registry ShouldProcess, Transactions HKLM, HKCU
Alias ShouldProcess Alias
Environment ShouldProcess Env
FileSystem Filter, ShouldProcess, Credentials C, E, A, D...
Function ShouldProcess Function
Variable ShouldProcess Variable
SqlServer Credentials SQLSERVER
Certificate ShouldProcess Cert
WSMan Credentials WSMan

With that in our hand, you should be able to do the following, that should produce the correct results

#Connect to the SSAS part of the sql server 
PS C:UsersAdministrator> cd SQLSERVER:SQLAS

#List all local instances
PS SQLSERVER:SQLAS> dir

Host Name
---------
AX2012R2A
HTTP_DS

#Connect to the server (local) - you should be able to type in a valid network name
PS SQLSERVER:SQLAS> cd AX2012R2A

#List all instances on that server
PS SQLSERVER:SQLASAX2012R2A> dir

Instance Name
-------------
DEFAULT
POWERPIVOT
TABULAR

#Connect to the default instance on the server you are connected to
PS SQLSERVER:SQLASAX2012R2A> cd default

#List all available collections / areas
PS SQLSERVER:SQLASAX2012R2Adefault> dir

#Connect to the databases area
PS SQLSERVER:SQLASAX2012R2Adefault> cd databases

#List all databases that are available
PS SQLSERVER:SQLASAX2012R2Adefaultdatabases> dir

Name State Read-Write Mode
---- ----- ---------------
Demand Forecast Accuracy initial Processed ReadWrite
Demand Forecast Accuracy ps Processed ReadWrite
Demand Forecast initial Processed ReadWrite
Demand Forecast ps Processed ReadWrite
Dynamics AX initial Processed ReadWrite
Dynamics AX ps Processed ReadWrite

#Getting the same result with Get-ChildItem
PS SQLSERVER:SQLASAX2012R2Adefaultdatabases> Get-ChildItem

Name State Read-Write Mode
---- ----- ---------------
Demand Forecast Accuracy initial Processed ReadWrite
Demand Forecast Accuracy ps Processed ReadWrite
Demand Forecast initial Processed ReadWrite
Demand Forecast ps Processed ReadWrite
Dynamics AX initial Processed ReadWrite
Dynamics AX ps Processed ReadWrite

#Traverse all databases and show their connection string details
PS SQLSERVER:SQLASAX2012R2Adefaultdatabases> Get-ChildItem | ForEach-Object $_.Datasources

Name Isolation Max Connections Connection String
---- --------- --------------- -----------------
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_1
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_2
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=MicrosoftDynamicsAX
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_4
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_5
Dynamics Database ReadCommitted 10 Provider=SQLNCLI11.1;Data
 Source=AX2012R2A;Integrated
 Security=SSPI;Initial
 Catalog=DatabaseName_6

PS SQLSERVER:sqlasax2012r2Adefaultdatabases> Get-ChildItem | ForEach-Object $_.Datasources 
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_1
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_2
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_3
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_4
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_5
Provider=SQLNCLI11.1;Data Source=AX2012R2A;Integrated Security=SSPI;Initial Catalog=DatabaseName_6

I would say that this should get you going, because you can now trust that you have the correct connection string details. I relation to your other question - this should give you all the details for the alter script that you want to produce, so you supply the entire alter statement as the SQL Server expects from you.

found this in a post here: http://www.mrtsql.com/2011/03/powershell-updating-analysis-services.html

See if this function in that post helps you out.

function UpdateDataSources()

 # Lets get our server name
 $SSASServerName="$env:ComputerName" + $SSASInstanceName
 $MyConnection = New-Object("Microsoft.AnalysisServices.Server")
 $MyConnection.Connect($SSASServerName)
 # lets return the number of data sources
 [int]$DataSourcecount=$MyConnection.Databases[$DatabaseName].DataSources.count
 for ($count=0; $count -ne $DataSourcecount;++$count)
 
 $MyCS=$MyConnection.Databases[$DatabaseName].DataSources[$count].ConnectionString
 $NewCS=setNewValue -MyCS $MyCS -Pattern "Data Source=" -ReplaceWith $DataSourceServer

 if ($PW.Length -ne 0)
 
 $NewCS=setNewValue -MyCS $NewCS -Pattern "Password=" -ReplaceWith $PW
 
 if ($UserName.length -ne 0)
 
 $NewCS=setNewValue -MyCS $NewCS -Pattern "User ID=" -ReplaceWith $UserName
 
 $MyConnection.Databases[$DatabaseName].DataSources[$count].ConnectionString=$NewCS
 # write the change back to SSAS
 $MyConnection.Databases[$DatabaseName].DataSources[$count].update()

 write-output $NewCS