Connect same database in the CLR store procedure

Hi everybody:

I have a urgent problem, and can not google any useful answer. Please help me!

In my CLR store procedure, I have used a context connect, and I want to creat another connect to the same database. I have set the database permission to external, creat asymmetric key in my assembly, and creat login for that key. The dbo is the owner.

When I use this connect string as store procedure input, and I opened this new connect in the CLR sp, it throw an exception --"***.MDF is in used, failed to connect"!!

How can I do?

Thank you very much!

Why do you need to connect back to the same database from the CLR stored procedure?

Opening a connection back to the same database is not supported for CLR integration stored procedures. If you are trying to achieve a multiple active result set scenario on the server, your best bet is to use server side cursors. You may wish to check out the ResultSet sample which demonstrates how to use cursors from CLR integration code. This sample would normally be located at systemdrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\ResultSet\ after following the instructions for installing samples in the topic "Installing Samples" in SQL Server 2005 Books Online. For more information about this sample, see http://msdn2.microsoft.com/en-us/library/ms160831(SQL.90).aspx.

Confustion on ROLAP and MOLAP and HOLAP modes

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on ROLAP and MOLAP storage modes. In ROLAP mode, we store all data in source relational databases and though then we can gain near real-time data for the queries. But with MOLAP and Proactive caching, we can solve this problem by setting update notifications to the MOLAP cubes. Also, with HOLAP, we can combine ROLAP and MOLAP (store leaf data in source relational database, while aggregations in MOLAP format), thus we can actually get the near real-time data for the queries as well once there is any changes to the underlying source relational database.

I am just a bit confused about the distinct benefts we can differenciate between these aspects regarding MOLAP with proactive caching and HOLAP. (not convinced about the reason to use ROLAP storage mode).

I hope my question is clear for your help and thanks a lot in advance for your advices and help for that and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello again Helen. MOLAP is the best choice for performance. HOLAP will give you wrong results if you have aggregations in the cube because the leaf records in the fact table will change but the aggregations will not. Avoid HOLAP.

Seems like you have SSAS2005 built on top of your transaction systems(OLTP) and no data warehouse/mart?

If you need real time reports build something with Reporting Services instead of using ROLAP or proactive caching.

I am not sure that MOLAP with proactive caching is a possible scenario. I think it is ROLAP with proactive caching, else you will have a MOLAP cube that only does processing 24 hours a day.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for your kind advices.

I thought the reason to have HOLAP is because they store the leaf data in source relational database, so once they have any changes to the source relational database, then the aggregations in the MOLAP format will be updated automatically. But then now it seemes like there is no reason to have HOLAP as well. Why not just use MOLAP, and keep the source relational database, set the proactive caching for this MOLAP to be near real-time?

Hope my thinking is right. And your further advices are most welcome. I am looking forward to hearing from you shortly.

Thanks again.

With best regards,

Yours sincerely,

|||

I'm a little confused by this thread, but here goes.

HOLAP is a wonderful option for keeping query processing times and cube population times down. Performance tends to degrade when querying at the leaf level because you are using ROLAP. With ROLAP, SSAS translates your MDX query into various SQL queries that it submits against your relational database and then uses the results to generate a cellset to return. The tradeoff is cube population times are lowest with ROLAP. MOLAP is the best for query performance but worst for cube population times.

Both MOLAP and HOLAP can get out of synch with your relational database but both can be configured to support proactive caching. MOLAP and HOLAP handle proactive caching differently. I personally find the HOLAP option more appealing, but read the Books Online topic "Choosing a Standard Storage Setting " for yourself to find what works best in your situation.

Regarding SSRS vs. SSAS, I'm not sure why the comparison. SSRS works against both relational and multidimensional database technologies, e.g. SQL Server Database Enging and SSAS. You can have data sets from both types of technologies in a single report if you need to go that route. The proactive caching and storage options above are transparent to any data consumer, be it SSRS, Proclarity, Excel, etc.

Hope that helps,
Bryan

|||

Hello helen. You already have another answere but I thought I should make some comments.

HOLAP does not include any automatic updates of aggregations so when the fact data changes the leaf transactions and the aggregations will be out of synch. If you have a data warehouse that is updated every day, but only once, then this will be no problem but you can choose MOLAP anyway.

MOLAP with proactive caching means that you will have a new version of the cube being built in the background when ever SSAS2005 detects a change in the data. Another way to do the same is to run an incremental update of the cube and the dimensions in a scheduled job that runs several times during the day.

With SSRS2005 you can write SQL-queries against a relational data store and have a real time report if you query your ERP-system(an approach that I do not recommend).

HTH

Thomas Ivarsson

|||

Hi, Brian,

Thanks a lot for your very advanced and helpful advices.

With best regards,

Yours sincerely,

|||

Hi, Thomas,

Again, your suggestions have been really helpful as well. It gave me more ideas on the OLAP cubes storage modes and help me to have more ideas on choosing very interesting storage modes.

With best regards,

Yours sincerely,

Confused on funcion Raiserror!

My error handle in store procedure is :

Error:
Begin
If (@.errno > 50000)
Begin
Raiserror (@.errno, 16,1, @.errarg1 ) @.errno is 50023 now.
End
RollBack Transaction
End

Where the SqlException is below, I am confused by the Number and the message of the SqlException!

Message: Error 50023, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger

Number :18054

Thank you!

The error number you passed to RAISERROR is a custom error number isn't it? So it can't find the number in the sys.messages table.

Instead you can pass your own error message to RAISERROR

RAISERROR('A big error has occurred.. alert the president', 16,1) WITH NOWAIT