Derived Columns in one to many relationships

I'm trying to write a query that concatenates multiple records into one
derived column. Let's say I have an author (Joe Writer) who has written
three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
books are in the tblBooks and they are joined by the AuthorID field
(number). If I use a simple select query to give me the author name and the
title, I will get three records, one for each book written.

What I want is to have all three books combined into one derived column. So
if I do the select statement, I will get one column with the author name,
and the second column will put together all three names of the book
separated by a column. So it will look like:

Author Title

Joe Writer Book 1, Book 2, Book 3,

Rather than having it appear as 3 records:

Joe Writer Book 1
Joe Writer Book 2
Joe Writer Book 3

Could someone help me with the SQL involved in this?

Thanks for the help.

Cheers,

MikeOne approach is shown in http://www.mvps.org/access/modules/mdl0008.htm at
"The Access Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in message
news:cfu2e7$18gm$1@.lettuce.bcit.ca...
> I'm trying to write a query that concatenates multiple records into one
> derived column. Let's say I have an author (Joe Writer) who has written
> three books (Book 1, Book2 and Book 3). The author is in tblAuthors, his
> books are in the tblBooks and they are joined by the AuthorID field
> (number). If I use a simple select query to give me the author name and
the
> title, I will get three records, one for each book written.
> What I want is to have all three books combined into one derived column.
So
> if I do the select statement, I will get one column with the author name,
> and the second column will put together all three names of the book
> separated by a column. So it will look like:
> Author Title
> Joe Writer Book 1, Book 2, Book 3,
> Rather than having it appear as 3 records:
> Joe Writer Book 1
> Joe Writer Book 2
> Joe Writer Book 3
> Could someone help me with the SQL involved in this?
> Thanks for the help.
> Cheers,
> Mike|||Mike,

read this article...

http://www.mvps.org/access/modules/mdl0004.htm

It has code that does this.|||Try this out
DECLARE @.BookNames varchar(1000)
SET @.BookNames = ''
SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
where author = 'Joe Writer'
SELECT 'Joe Writer',@.BookNames|||JK (jaikrishnan_nair@.hotmail.com) writes:
> Try this out
> DECLARE @.BookNames varchar(1000)
> SET @.BookNames = ''
> SELECT @.BookNames = @.BookNames +Book + ', ' FROM Books
> where author = 'Joe Writer'
> SELECT 'Joe Writer',@.BookNames

This may work. Or not work. The result of this sort of operation is
undefined in SQL Server. This is one of the few situations where
iterating over the data is a better option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

sql

Derived column not processing any records

Hi,

I have built a package in which i use a derived column to create a new set of columns and then pass the same to another target transformation.

The issue now what I am facing is, the re are certain number of records coming from source(16 rows) and gets processed before the Derived Column transformation, but after that, no records gets processed after the derived column transformation.

The package status shows as Success, but there is no records being written in the target table.

Any idea what could be the issue here?

Thanks,

Manish

Have you used data viewers on the data flow to see why records are not "passing" through? The records HAVE to go through the derived column -- it can't stop them.

Are you positive the records are not coming out of the derived column transformation?|||

Are no records being written to your destination, or you're just missing the derived values?

If the latter, I'd also check your column mappings in your destination (or any transformations downstream of your Derived Column Trx and upstream of your destination) to make sure your new columns are mapped. This is particularly important if you added the Derived Column Trx later in the development of your data flow. The mappings won't automatically appear.

|||

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

|||

ManishSingh wrote:

Hi Phil,

Thanks for the response.

Yes, I have put data viewers before and after the derived column transformation. The data viewer before the transformation shows the 13 records being passed, but the data viewer after the transformation doesnt show up any record, it is blank. I have tried many option in the mapping, but no luck.

I am now re-creating the mapping to check if the issue persists.

THough, let me know if you can think of any other scenario which could help in resolving this.

Thanks,

Manish

What mapping? There are no mappings in the derived column transformation.|||By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow|||

ManishSingh wrote:

By mapping I was referring to the package. I tried various options to delete transformation and add another etc in the package to check for the data flow

Make a copy of your package for this test.

Leave everything as is, but remove EVERYTHING AFTER the derived column transformation and add a row count transformation. Hook it up to the derived column transformation. This will be your new destination. You'll need to add a variable for the row counter, of course.

Run the package. What happens? Does the derived column pass all rows through now?|||

Phil,

Thanks for your response. I was out of office, so couldnt reply back.

I tried adding the row count as the destination after the derived column transformation, and it was running successfully. Then again, I removed the row count and added the transformation again, and it started working. I am not sure, what could be the issue, but now its running fine.

Thanks for all your help. Really appreciate it!!!!!!!!

Deployment Issue

We have a requirement to display some 80,000 records in the report. The Stored procedure in the back end is displaying results in less than 2 mins..but the system hangs when we run the report.

Any work around for this strange behaviour?

When you run a report, not only do you have to collect the data, you also have to render the report, 80K records is 800 pages if you have 100 records per page.

What happens if you run the report as a scheduled report, and set the run time to the middle of the night when database and report server activity is low?

Dependent Records

I want to delete a parent record.

Is there a simple call I could make that would tell me if there are dependent child records, without me having to hard code in all the child tables and manually calling each child table one at a time.

A .net framework call that that returned a Yes/No to a "Are there any dependent records?" sort of query, or better still if it told me the table the dependent record is in.

I would then know if I could delete the record.

I have referential integrity turned on and am using a Access 2000 .mdb, but will soon be upsizeing to SQL Server Express (the MSDE thingy).

I realise I could use the sledge hammer technique and let the Referential Integrity stop me, but a more polite approach would be better, and I am not sure if you can have Referential Integrity in SQL Express like you do in Access.

Warm regards

NeilReferential integrity in SQL Server Express is pretty much the same as Access, although you'll have more methods available for creating and maintaining the constraints (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_9183.asp).

If you want to delete all child records associated with a parent record, you can use cascading deletes (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp). This is not a good idea as a rule though, since most times you'll want the application to throw an exception if a parent record is deleted before the child records. In a typical application, you'd delete the child records first, then delete the parent record(s).

If this is a UI, I always like it when developers catch the RI exception if a user attempts to delete a parent, then displays a message such as "there are other records associated with the row you want to delete, all associated data will be deleted, are you sure this is OK?".

Hope this helps,
Josh Lindenmuth

Dependant assemblies in CLR

This is related to one of my previous posts.

I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.

I am using the Novell ldap library.

I want to do this with SSL so my code referneces the Mono security library as well.

However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.

Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)

Any ideas?

Thanks,

BIG

Hi BIG,

Are you loading the Novell library into SQL? One restriction with CLR integration is that outside of a static list of "approved" assemblies that we access in the GAC, all assemblies must be loaded into SQL.

Cheers,

-Isaac

|||Yes, they are loaded assemblies.
New info:

System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile)
at Novell.Directory.Ldap.Connection.connect(String host, Int32 port, Int32 semaphoreId)|

what the heck?|||

Hi BIG,

As you surmised, Novell is trying to load the Mono security assembly as part of the connect method. The problem is that dynamically loading assemblies under SQL CLR is explicitly disallowed as the error message states.

Unfortunately, Novell is explicitly calling Assembly.LoadFrom("Mono.Security.dll") which will always try to load the assembly from disk so there is no easy way to work around this problem. If instead the call had been Assembly.Load with the Mono.Security DLL's four-part name then you could solve this by preloading the Mono.Security.Dll into SQL Server yourself.

Steven

|||What if I use the sgen.exe tool and serialize the assembly and load teh serialized assembly?|||

That is the solution for a different problem where Xml Serialization also tries to load a dynamic assembly. Good try though.

Without changing the Novell source code to use Assembly.Load rather than Assembly.LoadFrom, I don't think there is any way around this. Sorry I can't be of any help.

Steven

|||Believe it or not, I got the source code for that .dll and did change it to Load now it does not want to work...gonna contact Novell.

Thanks,

BIG|||

So the Mono.Security assembly now loads correctly, but you're hitting a different error that prevents you from using it? Or are you still running into Loading issues?

|||Loading issue...
Original code: Assembly.LoadFrom("Mono.Security.dll");
My code: Assembly.Load("Mono.Security.dll");

Actually:

// Load Mono.Security.dll
Assembly a;
try
{
a = Assembly.Load("Mono.Security.dll");
}
catch(System.IO.FileNotFoundException)
{
throw new LdapException(ExceptionMessages.SSL_PROVIDER_MISSING,LdapException.SSL_PROVIDER_NOT_FOUND, null);
}

Error message is from the Exception thrown when the assembly is not found.

I am gonna research other ways to load an assembly, but I am sure I am screwed here, will try to post this in the relavant Novell forum as well.

What I may end up doing is trying to integrate these two assemblies into one (which I think Novell should have done in the first place :( )
OR
Making this into a standard exec and have SQL Server run the job calling that exec every night, so not in CLR, and lose portability with db.

Unless anyone has a better idea?

Thanks,

BIG S|||

Do you have the Mono.Security.Dll loaded in SQL Server yet? In order for the Assembly.Load to suceed, the assembly already needs to exist in your appdomain. So you need to do CREATE ASSEMBLY [Mono.Security] FROM 'path\Mono.Security.dll' with permission_set = unsafe first.

Also, you need to pass the full, 4-part assembly name to the Mono.Security assembly as documented: http://msdn2.microsoft.com/en-us/library/ky3942xh.aspx. You can get the 4-part name from sys.assemblies.

If this still doesn't work, you can verify that the Mono.Security.dll is reported as being loaded in your appdomain from the sys.dm_clr_loaded_assemblies dmv. If it's not there, try doing a dummy CREATE FUNCTION foo() returns int as external name [Mono.Security].bar.foo. (Sorry I can't verify if this is necessary right now - I just moved to Vista at home and don't have SQL Server installed yet).

Steven

|||ok working on it...

BTW: Even if I can't get it going, Steven u rock!

BIG S|||You are bloody brilliant...I got it working!

If you are ever in Toronto, beer is on me!

BIG S