Hi!

I am writing couple of utility procs for AS. I want to be able to add a data
source and a data source view to an existing database without crating a
project, by simply using a stored procedure in AS. After that I can create
Data Mining models using DMX, therefore I can do everything with SSMS. I
managed to create the procedures. But my procs work only if I am in context
of a different AS database than I am updating. If I try to add a data
source, for example, to a database in which context I am in SSMS, I get an
endless query. I think this is because the whole database is locked. Is
there any possibility to lock on different granularity, or is database
locking fixed in the Update method? Or did I make some other mistake?

Here is the code for creating a data source, if anybody wants to try:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using AMO = Microsoft.AnalysisServices;
using ADOMD = Microsoft.AnalysisServices.AdomdServer;

namespace ServerProcs
{

public class ASProcs
{

public static void CreateDataSource(string relDbName, string
asDbName)
{
AMO.Server currSvr = new AMO.Server();
currSvr.CaptureXml = false;
currSvr.Connect("DataSource=" + ADOMD.Context.CurrentServerID +
";");
// comment out this part if you want to play in current database
if (asDbName == ADOMD.Context.CurrentDatabaseName)
{
throw new ArgumentException(" Can't update current database!
");
}
// comment out this part if you want to play in current database
AMO.Database targetDb = currSvr.Databases.GetByName(asDbName);
AMO.RelationalDataSource newRds = new
AMO.RelationalDataSource(relDbName, relDbName);
if (targetDb.DataSources.Contains(newRds.ID))
{
targetDb.DataSources.Remove(newRds.ID);
}
newRds.ConnectionString =
"Provider=SQLOLEDB" +
";Data Source=localhost" +
";Initial Catalog=" + relDbName +
";Integrated Security=SSPI";
targetDb.DataSources.Add(newRds);
targetDb.Update(AMO.UpdateOptions.ExpandFull);
}

}
}

Thanks,

--
Dejan Sarka

Hi,
After you add a major object (like DataSource in this example, or Dimension, Cube, Partition, MiningStructure, MiningModel), you can only save that object and not necessarily the entire parent object.

In this case you are saving the entire full database with the line:

targetDb.Update(AMO.UpdateOptions.ExpandFull);

but you can just save the newly added data source with this line:

newRds.Update();

I believe this will solve the locking problem.

Adrian,

Thank you for your answer, but it did not solve the problem. Even if I
update the data source only, I still get an endless query if I do it for the
database in which context I am currently.

--
Dejan Sarka

[quoted text, click to view]