Saturday, February 25, 2012

AmoAdventureWorks sample problems.

Can execute the above sample (on XP Pro SP2) successfully once. Subsequent attempts result in error below(after grabbing all system resources for a few minutes). Tried deleting the database and then stop starting the server before retrying - to no avail.

Kind of sapping my confidence in AMO objects.

Any ideas?

Thanks in advance

Zub

--

Unhandled Exception: System.Data.OleDb.OleDbException: Deferred prepare could no

t be completed.

Query timeout expired

at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

at System.Data.OleDb.OleDbDataReader.BuildSchemaTableRowset(Object handle)

at System.Data.OleDb.OleDbDataReader.GenerateSchemaTable(OleDbDataReader data

Reader, Object handle, CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav

ior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(Comman

dBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataT

able datatable, SchemaType schemaType, IDbCommand command, String srcTable, Comm

andBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType sc

hemaType, IDbCommand command, String srcTable, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType sc

hemaType, String srcTable)

at Microsoft.Samples.SqlServer.Program.AddTable(DataSourceView dsv, OleDbConn

ection connection, String tableName) in AmoAdventure

Works\Program.cs:line 239

at Microsoft.Samples.SqlServer.Program.CreateDataSourceView(Database db) in AmoAdventureWorks\Program.cs:line 211

at Microsoft.Samples.SqlServer.Program.CreateAndProcessDatabase(Server svr) in AmoAdventureWorks\Program.cs:line 72

at Microsoft.Samples.SqlServer.Program.Main() in

AmoAdventureWorks\Program.cs:line 48

The sample application assumes you have installed SQL Server on the same machine you have your Analysis Server.

And you have attached sample AdventureWorksDW SQL database. Search for "connectionString" in your project and you be able to point it to any SQL Server you have AdventureWorksDW database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward I'm not sure what you are suggesting.

I have SQL Server Anaylsis Server and AMOAdventureWorks.exe all running on the same machine.

The point is that it works once. The AS database is created, cubes defined and populated. The problem is that subsequent exceutions fail.

Thanks

Zub

|||

The error message suggests problems with connecting to relational database.

AMOAdventureWorks creates a AS database and populates it with data from SQL server you installed locally. Is it possible that SQL server was unavalible?

I've tried to run AMOAdventureWorks several times. And it worked without a problem.

Try running it in the debugger and see exactly where it fails.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward

Thanks for the response.

OK I'm debugging.

Rebooted machine. Ran the program once - it succeeded.

Second time I get a IndexOutofRangeException on the line

DataTable dataTable = dataTables[0];

in

static void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)

{

OleDbDataAdapter adapter = new OleDbDataAdapter(

"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",

connection);

DataTable[] dataTables = adapter.FillSchema(dsv.Schema,

SchemaType.Mapped, tableName);

DataTable dataTable = dataTables[0];

......

when called from

AddTable(dsv, connection, "DimCustomer");

rgds

Zub

|||

Couple things I notice in the code.

1) It works the first time but not the second time. Does your table name exist in DB when you pass in second time?

2) If you pass same table name on second time, it does not make sense because DimCustomer is already in dsv.Schema after you call it first time. This is redundent.

Try replacing with following lines and see if you can get the table everytime you call.

DataSet dataSet = new DataSet();

DataTable[] dataTables = adapter.FillSchema(dataSet,

SchemaType.Mapped, tableName);

3) Anyway, you don't use any AMO object except in the line of doing FillSchema. If you still have problem as my suggestion in Step 2, then it must be the connection problem. Check if the table exists in the DB and valid permission to access that table as well.

|||

Sorry I just notice one more thing.

You got Query time out error on second time. Is your table a big table or a complicated view when you call second time? It seems that it took a lot of time to execute the statement. You may want to increase the connection time out in your connection or set it to unlimited.

Also, you can try the same SQL Statement "Select * from [dbo].[DimCustomer] where 1=0" as New query in SQL Management studio. See how long it takes to get the result.

No comments:

Post a Comment