sqlps transaction issue

Question

I wrote a script that uses sqlps to deploy a SSAS tabular cube. After the deployment, I need to perform a couple of actions on the cube but if I try to access it, I get a message saying that the cube doesn't exist. But it does, in fact, if I split the actions into two scripts (deploy -> exit sql ps -> new sqlps session), it works (for reasons that don't matter now, I cant do that).

It seems that the sqlps session doesn't see the cube it just deployed. I'm wondering if there is a refresh command I can run or if I can run sqlps in a "read uncommited" state.

Answer 1

Do you use the PowerShell Transactions supporting cmdlets? Looks like the transaction is being commited only after your script runs out.

Try to split the deploying logic into two transactions: creating the cube and the other actions you need to perform. For each of this parts you should use it's own transaction, like this:

Start-PSTransaction
// logic
Complete-PSTransaction

If you need to access the transactions programmatically, you should use the TransactionScope analog in the PowerShell, the Cmdlet's CurrentPsTransaction property, like this:

using(CurrentPsTransaction)
{
   ... // Perform transactional work here
}

... // Perform non-transacted work here

using(CurrentPsTransaction)
{
   ... // Perform more transactional work here
}

Update:
May be you can turn on the transaction using the declaration?

Developing for transactions – Cmdlet and Provider declarations Cmdlets declare their support for transactions in a similar way that they declare their support for ShouldProcess:

[Cmdlet(“Get”, “Process”, SupportsTransactions=True)]

Providers declare their support for transactions through the ProviderCapabilities flag:

[CmdletProvider(“Registry”, ProviderCapabilities.Transactions)]

More about the isolation levels in Powershell:

TransactionScope with IsolationLevel set to Serializable is locking all SQL SELECTs