Many-to-Many data

If you have ever ran across the same problem you will now what I am talking about.

The designer gave us a report to develop that displays many to many data. The traditional approach of hierarchal data reporting just will not work. Any ideas on hwo to display this data?

Here is a quick example:

Think of Musician to Albums. A musician can have many albums, and an album can have many musicians.

The report will list grouped by musician thier albums and the total sales, but when you sum up the sales it will be overstated because an album can have multiple artists. Also, if three artists contributed to the same album, it should list the three artists, then the one album, not artist/album X3.

So... what is the best approach to displayign this type of data on a report? We are stumped.

Thanks,
Eric

I was able to solve it. The trick was two have two seperate groups for Albums, then show/hide depending on conditions.

Many Queues and one Activation Procedure for that queues.

Hi, All!

Could you please help me in my problem?

I develop Service Broker applcation, SB has 20 queues, also I have one Activation Procedure which carries out following actions:

CREATE PROCEDURE proc_ms_Activation

AS

BEGIN

WAITFOR (RECEIVE TOP(1) message_type_name, message_body, conversation_handle, message_id FROM Queue1)

-- process message, save statisctisc and etc

END

Problem in that: when I execute command WAITFOR (RECEIVE TOP (1) message_type_name, message_body, conversation_handle, message_id FROM Queue1) I should specify a name concrete Queue, for example "... FROM Queue1 ". I would like to use one procedure for processing all Queue.

How I can design my application that one Activation Procedure processed messages from all Queues?

Thank a lot for help.

You can figure out what queue you were activated for by looking in sys.dm_broker_activated_tasks and filter for the current session (spid = @.@.SPID). Then use database_id and queue_id to lookup the queue name is sys.service_queues and execute the WAITFOR (RECEIVE... ) as dynamic SQL.

BTW, If you run into EXECUTE AS context restrictions (like inability to see the records in servel level views) see this article: http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx Simples fix is to mark the database trustworthy.

HTH,
~ Remus

|||

Hi Svitlana!

What's the reason that you have one activated stored procedure for the whole 20 queues? Because when you get different message types on each of the 20 queues, then it makes (for me) no sense to use just one activated stored procedure that handles all message types. Why you don't use one activated stored procedure for each queue?

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Thanks for the shown interest, excuse that long did not answer.

Remus, thanks a lot for help! you post is very useful.

Klaus, allow me to explain what's the reason that I have one activated stored procedure for the 20 queues.

In my application only one custom message type. Stored Procedures identical and also differ only Queue name. It would be convenient to have one stored procedure for all queues.

managing live and develop database

Hello,

I haven written a Windows program in Visual Studio 2005. This program stores data in and retrieves data from a SQL Express database. The customer is starting to use the program and filling the database with data. However the program is still under construction (new functionality will be added) and so is the database (new tables, views etc. will be added).

Of course I could send the customer the new database, but than all data already entered will be lost.....

I'm thinking about using the SQL Management Studio Express and VS2005 to connect remotely to the database (a VPN connection is availabe) but worry about performance issues.... Or should I use some kind of replication / synchronization and how, because this is new to me.

What is the best way to solve this 'problem' ?

hi,

Silencer#1 wrote:

Hello,

I haven written a Windows program in Visual Studio 2005. This program stores data in and retrieves data from a SQL Express database. The customer is starting to use the program and filling the database with data. However the program is still under construction (new functionality will be added) and so is the database (new tables, views etc. will be added).

Of course I could send the customer the new database, but than all data already entered will be lost.....

I'm thinking about using the SQL Management Studio Express and VS2005 to connect remotely to the database (a VPN connection is availabe) but worry about performance issues.... Or should I use some kind of replication / synchronization and how, because this is new to me.

What is the best way to solve this 'problem' ?

the best article I've read about database versioning and related issue is available at http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/ ... it support both "original" installation as successive metadata updates..

regards

|||

The short answer is that you should use scripts to update the schema of your deployed database. The article Andrea points to is a good one that discusses ways to deploy and update databases. A new tool to help you manage you database across versions and create scripts for upgrades is Visual Studio Team Systems for Database Professionals.

Mike

|||

After doing some research on the web I found out that InstallShield 12 from Macrovision is able to script SQL 2005 databases.

I have downloaed the trial version and created a sample installer and it worked perfectly !!. You can let the end-user specifiy the server to logon, how to log on (windows authentication or SQL login) and which catalog (database) to use.

|||

After a day of testing I found out that InstallShield works fine but is rather expensive !!
So I googled some more and ran into the products of Red Gate. They have several SQL products and one of them is called SQL Packager.
You can use this software to package your database or upgrade an existing database. You can package the database as a .NET executable or as an C# project.

managing live and develop database

Hello,

I haven written a Windows program in Visual Studio 2005. This program stores data in and retrieves data from a SQL Express database. The customer is starting to use the program and filling the database with data. However the program is still under construction (new functionality will be added) and so is the database (new tables, views etc. will be added).

Of course I could send the customer the new database, but than all data already entered will be lost.....

I'm thinking about using the SQL Management Studio Express and VS2005 to connect remotely to the database (a VPN connection is availabe) but worry about performance issues.... Or should I use some kind of replication / synchronization and how, because this is new to me.

What is the best way to solve this 'problem' ?

hi,

Silencer#1 wrote:

Hello,

I haven written a Windows program in Visual Studio 2005. This program stores data in and retrieves data from a SQL Express database. The customer is starting to use the program and filling the database with data. However the program is still under construction (new functionality will be added) and so is the database (new tables, views etc. will be added).

Of course I could send the customer the new database, but than all data already entered will be lost.....

I'm thinking about using the SQL Management Studio Express and VS2005 to connect remotely to the database (a VPN connection is availabe) but worry about performance issues.... Or should I use some kind of replication / synchronization and how, because this is new to me.

What is the best way to solve this 'problem' ?

the best article I've read about database versioning and related issue is available at http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/ ... it support both "original" installation as successive metadata updates..

regards

|||

The short answer is that you should use scripts to update the schema of your deployed database. The article Andrea points to is a good one that discusses ways to deploy and update databases. A new tool to help you manage you database across versions and create scripts for upgrades is Visual Studio Team Systems for Database Professionals.

Mike

|||

After doing some research on the web I found out that InstallShield 12 from Macrovision is able to script SQL 2005 databases.

I have downloaed the trial version and created a sample installer and it worked perfectly !!. You can let the end-user specifiy the server to logon, how to log on (windows authentication or SQL login) and which catalog (database) to use.

|||

After a day of testing I found out that InstallShield works fine but is rather expensive !!
So I googled some more and ran into the products of Red Gate. They have several SQL products and one of them is called SQL Packager.
You can use this software to package your database or upgrade an existing database. You can package the database as a .NET executable or as an C# project.