Customising Legends for chart

Hi to all,

A query about legends in a chart, is it possible to customise legends in chart? I have used formula fields as the value of a pie chart and apparently the legend shows "@." infront of the fieldname... I'd like to have my own legend name for reading friendliness.

Thanks.

-HenryAnd for your information I am using Crystal Report included with VS.net 2003.

Cheers.

-Henry|||i'm having the same problem..

anyone der to help?|||Hi Cynthia, it has been many years since I have post my question... But I think I don't have a solution. I believe a full version of Crystal Report allow renaming field name.|||ok. where can i get the full version of Crystal report? is it consider an additional component to integrate into visual studio?

CustomColumn

Hi All, How to define a column in sql2k with format of its value as
10,000,000
TIA"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>
Formatting is a front-end issue. You should probably create the column as a
BIGINT, or some type of Decimal.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi
create table #t
(
col1 int,
col2 as convert(varchar,cast(col1 as money),1)
)
insert into #t values (10000)
select * from #t
If it does not help you ,please lookup SUBSTRING system function in the BOL
"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>|||You don't. Formatting is controlled by your client application, not by
SQL Server.
If it's a numeric column then use one of the numeric datatypes.
David Portas
SQL Server MVP
--|||how about if you are exporting data to excel and want to populate in excel
in that format...avoiding writing format in excel...
TIA
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128957032.919975.66170@.f14g2000cwb.googlegroups.com...
> You don't. Formatting is controlled by your client application, not by
> SQL Server.
> If it's a numeric column then use one of the numeric datatypes.
> --
> David Portas
> SQL Server MVP
> --
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
> how about if you are exporting data to excel and want to populate in excel
> in that format...avoiding writing format in excel...
Still a front-end issue.
Storing is not the same as displaying or migrating.
If you want to migrate it as something else, then you have multiple options.
You could create a view and convert it to just about any format that you
wish.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I am looking the convert format to the desired Column
type..(1,00,000)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
excel
>
> Still a front-end issue.
> Storing is not the same as displaying or migrating.
> If you want to migrate it as something else, then you have multiple
options.
> You could create a view and convert it to just about any format that you
> wish.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||how to convert a view to desired format?
Thanks a lot
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23sqAnObzFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks, I am looking the convert format to the desired Column
> type..(1,00,000)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> excel
> options.
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23iPvPQbzFHA.3660@.TK2MSFTNGP15.phx.gbl...
> how to convert a view to desired format?
> Thanks a lot
Take a look at the CONVERT function in SQL Server.
-- Create a table variable to hold our data.
DECLARE @.Foo TABLE (
x bigint
)
-- Load a value
INSERT @.Foo VALUES (10000000)
-- Convert BigInt to money datatype, then convert the money to a character
string and specify the "1" flag
-- so that commas are added in the appropriate place.
SELECT CONVERT(varchar(20), CONVERT(money, x), 1) FROM @.Foo
Rick Sawtell
MCT, MCSD, MCDBA

Custom sorting

I have a parameter in my report called SortType and depending on the value of the parameter I want to sort by a particular column. I have tried doing this by using a custom function as follows:

Function SortTypeValue() As String
If Report.Parameters!SortType.Value = "1" Then
Return "=Fields!StockDesc.Value"
else Return "=Fields!StockCode.Value"
End If
End Function

Then in the sorting tab I have entered in the expression field: =Code.SortTypeValue().

However this is not doing anything. The data is just displayed in the order it is taken from the database. Can this be done? If so can anybody please tell me what I am doing wrong?

Thanks in advance.
Dynamic use of parameters for grouping can be achieved using the following syntax:

=Fields(Parameters!ParamName.Value).Value

Maybe this will also work for sorting

--

Your code is going to return the same value regardless of sort type anyway.
=Fields!StockDesc.Value
You don't need it if the above method works.

--

You can set interactive sorting so the user can sort on any column they wish:

right click on the column header
interactive sort
tick tickbox
set sort expression to the value held in that particular column (i.e. if column is sales amt then =fields!salesamt.value)

done

if you need to do this for a whole raft of columns, it is quicker to do it in the (F4) properties window:
expand the 'user sort' propery and set the 'sort expression' drop down as per the column name
NB keep sort expression selected otherwise when you click on the next column you wish to add a sort to, the properties collapse|||You say my code will return the same value regardless of sort type anyway. Why? Each condition returns a different string.

If I was to use =Fields(Parameters!ParamName.Value).Value I would still need a way to determine which column the user wants to sort on so I know which ParamName to insert.|||To paraphrase the jerky boys "my eyes is going crazy"
but you still don't need the code anyway.

With regards to using my technique for sorting.....

It works.

I just created a very simple report.
I added one parameter called 'sort by'

I just set the values of this to the names of the db columns returned. Spelling exactly as per db cols!

In the table, sorting tab, i just added this

=Fields(Parameters!sortby.Value).Value

go to the preview, Pick your column name, run

simple|||

One step further. . .

Create the parameter as described by "adolf garlic" and also create a parameter named "SortDirection" and give the parameter values "Ascending" and "Descending"

In the table/sort "Expression" field(s) enter:

=Iif(Parameters!SortDirection.Value = "Ascending", Fields(Parameters!SortBy.Value).Value, "")

=Iif(Parameters!SortDirection.Value = "Descending", Fields(Parameters!SortBy.Value).Value, "")

and set the proper direction for both expressions.|||Thanks adolf garlic. Works like a treat. Exactly what I was after.
sql

Custom query string question

Hi there,

I have a custom query string that I placed as a varchar, for instance

@.UnknownColumnName varchar(100)
@.CostumQueryString varchar(1000)
@.Value int

SET @.UnknownColumnName = (SELECT ColName FROM Utility WHERE ID =1)

SET @.CustomQueryString = 'SELECT ' + @.UnknownColumnName + ' From Table2 WHERE .....'

I have to execute this custom querystring now:
SET @.Value = (EXEC sp_executesql @.CustomQueryString)

I get a syntax error....I'm not sure how i can execute this querystirng and set the result to a variable..any help much appreciated

Regards
Mike

you can try this one

SET @.CustomQueryString = 'SELECT ' + @.UnknownColumnName + ' INTO #Values From Table2 WHERE .....'

EXEC sp_executesql @.CustomQueryString

SELECT *
FROM #Values

DROP TABLE #Values|||Hi, thanks for the reply.

I actually found the solution in books online (ooops should have searched harder there) and the best way of doing this is to make use of OUTPUT parameters:

DECLARE @.IntVariable int;
DECLARE @.SQLString nvarchar(500);
DECLARE @.ParmDefinition nvarchar(500);
DECLARE @.max_title varchar(30);

SET @.IntVariable = 197;
SET @.SQLString = N'SELECT @.max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @.level';
SET @.ParmDefinition = N'@.level tinyint, @.max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable, @.max_titleOUT=@.max_title OUTPUT;

SELECT @.max_title;

Custom Property Question

I have a custom transform, with a custom property of value 1. My question is, can I change that value during runtime with the value from a variable?

Thanks

Yes, you would use a property expression.

When you define the property in the transform itself, you need to enable it for expressions through. Set the IDTSCustomProperty90 ExpressionType property to DTSCustomPropertyExpressionType.CPET_NOTIFY.

|||

I did that in ProvideComponentProperties,

IDTSCustomProperty90 exp = outputColumn.CustomPropertyCollection.New();

exp.Name = "Expression";

exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

When I execute the package, I can go into expressions and put in a value but it does not change when I run the package. Is there something I am missing?

Thanks

|||

That should work. Minor point but I thinkl calling the property Expresion could be a bit confusing, but at the end of the day that properties value should be the evaluated expresison result when you read-it at runtime.

Could you send me the code ? darren.green at sqldts, com

|||

Please check your email.

|||Mail checked, reply sent. Assuming I answer your question, can you post a summary here for the benefit of others who may be following or subsequently find this thread and have a similar problem.|||

Hi,

I have exactly the same problem, and I would like to share your solution if possible

Thanks a lot in advance

LVT

Custom Property Question

I have a custom transform, with a custom property of value 1. My question is, can I change that value during runtime with the value from a variable?

Thanks

Yes, you would use a property expression.

When you define the property in the transform itself, you need to enable it for expressions through. Set the IDTSCustomProperty90 ExpressionType property to DTSCustomPropertyExpressionType.CPET_NOTIFY.

|||

I did that in ProvideComponentProperties,

IDTSCustomProperty90 exp = outputColumn.CustomPropertyCollection.New();

exp.Name = "Expression";

exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

When I execute the package, I can go into expressions and put in a value but it does not change when I run the package. Is there something I am missing?

Thanks

|||

That should work. Minor point but I thinkl calling the property Expresion could be a bit confusing, but at the end of the day that properties value should be the evaluated expresison result when you read-it at runtime.

Could you send me the code ? darren.green at sqldts, com

|||

Please check your email.

|||Mail checked, reply sent. Assuming I answer your question, can you post a summary here for the benefit of others who may be following or subsequently find this thread and have a similar problem.|||

Hi,

I have exactly the same problem, and I would like to share your solution if possible

Thanks a lot in advance

LVT

Custom error msg breaks my error handler

Hi,

I added this line of code to my error handler script, in red.

User:criptError is a package-level string variable

User:criptError has a value assigned to it when another script encounters an error condition (which I define)

I added "User:criptError" as a read-only variable to the error handler script task.

Public Sub Main()

Dim messages As Collections.ArrayList

Try

messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)

Catch ex As Exception

messages = New Collections.ArrayList()

End Try

messages.Add(Dts.Variables("SourceName").Value.ToString)

messages.Add(Dts.Variables("ErrorDescription").Value.ToString())

messages.Add(Dts.Variables("scriptError").Value.ToString)

Dts.Variables("errorMessages").Value = messages

Dts.TaskResult = Dts.Results.Success

End Sub

Now, when I run the package, and an it encounters an error, it task just hangs, that is, it stays yellow instead of turning red... if I remove the line in red above, it works ok again.

Why would this line cause a problem?

Thanks

Make sure you are unlocking the scriptError variable in the first task. It might help if you post the script from that task.|||

Hi,

What do you mean by "unlocking the scriptError variable"?

Thanks

|||Dts.Variables.Unlock()|||

I will try that. Why is unlocking necessary? I've never run into this before.

Thanks

|||If the first task is raising an error, the error handling task can be fired before the variable is unlocked in the first task. Make sure that you unlock it in the error handler in the first script.|||

This is the part of my code that seems to be causing the problem. I am trying to set the "scriptError" variable to the error message below. As you can see, I am setting the Dts.TaskResult to "failure" BEFORE assigning the value to my variable. The "failure" causes execution to go to my Error Handler script task. That's where it hangs.

Perhaps I should put the the "failure" AFTER the variable assignment.

I don't understand all this "unlocking" stuff quite yet. What do you mean by "unlock it in the error handler"? Perhaps that doesn't apply here, I don't know.

Thanks

Else

Dts.TaskResult = Dts.Results.Failure

Dts.Variables("scriptError").Value = "The extracts in " & CStr(Dts.Variables("Folder").Value) & " are not current! Data NOT loaded."

End If

|||Adding

Dts.Variables.Unlock()

right after the variable is written to (but not before) works!

Custom error msg breaks my error handler

Hi,

I added this line of code to my error handler script, in red.

User:criptError is a package-level string variable

User:criptError has a value assigned to it when another script encounters an error condition (which I define)

I added "User:criptError" as a read-only variable to the error handler script task.

Public Sub Main()

Dim messages As Collections.ArrayList

Try

messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)

Catch ex As Exception

messages = New Collections.ArrayList()

End Try

messages.Add(Dts.Variables("SourceName").Value.ToString)

messages.Add(Dts.Variables("ErrorDescription").Value.ToString())

messages.Add(Dts.Variables("scriptError").Value.ToString)

Dts.Variables("errorMessages").Value = messages

Dts.TaskResult = Dts.Results.Success

End Sub

Now, when I run the package, and an it encounters an error, it task just hangs, that is, it stays yellow instead of turning red... if I remove the line in red above, it works ok again.

Why would this line cause a problem?

Thanks

Make sure you are unlocking the scriptError variable in the first task. It might help if you post the script from that task.|||

Hi,

What do you mean by "unlocking the scriptError variable"?

Thanks

|||Dts.Variables.Unlock()|||

I will try that. Why is unlocking necessary? I've never run into this before.

Thanks

|||If the first task is raising an error, the error handling task can be fired before the variable is unlocked in the first task. Make sure that you unlock it in the error handler in the first script.|||

This is the part of my code that seems to be causing the problem. I am trying to set the "scriptError" variable to the error message below. As you can see, I am setting the Dts.TaskResult to "failure" BEFORE assigning the value to my variable. The "failure" causes execution to go to my Error Handler script task. That's where it hangs.

Perhaps I should put the the "failure" AFTER the variable assignment.

I don't understand all this "unlocking" stuff quite yet. What do you mean by "unlock it in the error handler"? Perhaps that doesn't apply here, I don't know.

Thanks

Else

Dts.TaskResult = Dts.Results.Failure

Dts.Variables("scriptError").Value = "The extracts in " & CStr(Dts.Variables("Folder").Value) & " are not current! Data NOT loaded."

End If

|||Adding

Dts.Variables.Unlock()

right after the variable is written to (but not before) works!

Custom error msg breaks my error handler

Hi,

I added this line of code to my error handler script, in red.

User:criptError is a package-level string variable

User:criptError has a value assigned to it when another script encounters an error condition (which I define)

I added "User:criptError" as a read-only variable to the error handler script task.

Public Sub Main()

Dim messages As Collections.ArrayList

Try

messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)

Catch ex As Exception

messages = New Collections.ArrayList()

End Try

messages.Add(Dts.Variables("SourceName").Value.ToString)

messages.Add(Dts.Variables("ErrorDescription").Value.ToString())

messages.Add(Dts.Variables("scriptError").Value.ToString)

Dts.Variables("errorMessages").Value = messages

Dts.TaskResult = Dts.Results.Success

End Sub

Now, when I run the package, and an it encounters an error, it task just hangs, that is, it stays yellow instead of turning red... if I remove the line in red above, it works ok again.

Why would this line cause a problem?

Thanks

Make sure you are unlocking the scriptError variable in the first task. It might help if you post the script from that task.|||

Hi,

What do you mean by "unlocking the scriptError variable"?

Thanks

|||Dts.Variables.Unlock()|||

I will try that. Why is unlocking necessary? I've never run into this before.

Thanks

|||If the first task is raising an error, the error handling task can be fired before the variable is unlocked in the first task. Make sure that you unlock it in the error handler in the first script.|||

This is the part of my code that seems to be causing the problem. I am trying to set the "scriptError" variable to the error message below. As you can see, I am setting the Dts.TaskResult to "failure" BEFORE assigning the value to my variable. The "failure" causes execution to go to my Error Handler script task. That's where it hangs.

Perhaps I should put the the "failure" AFTER the variable assignment.

I don't understand all this "unlocking" stuff quite yet. What do you mean by "unlock it in the error handler"? Perhaps that doesn't apply here, I don't know.

Thanks

Else

Dts.TaskResult = Dts.Results.Failure

Dts.Variables("scriptError").Value = "The extracts in " & CStr(Dts.Variables("Folder").Value) & " are not current! Data NOT loaded."

End If

|||Adding

Dts.Variables.Unlock()

right after the variable is written to (but not before) works!

Custom Code problem

I have this very basic custom code in my report:
Public Shared Function Basis(byVal Value as Integer) As Double
Basis = Basis + value
End Function
When I call this code, using =Code.Basis(Fields!BWT.Value), I expect it to
be like a running total. But all I get back is whatever the value of
Fields!BWT.Value is, without it adding up. I know that I could use the
runningvalue function, but I am wondering why this code doesn't work?quite possible the value of the function is not static. So everytime you come
into the function the value is reset. Try setting a temp invisible textbox to
basis' value evertime it updates.
"dachrist" wrote:
> I have this very basic custom code in my report:
> Public Shared Function Basis(byVal Value as Integer) As Double
> Basis = Basis + value
> End Function
> When I call this code, using =Code.Basis(Fields!BWT.Value), I expect it to
> be like a running total. But all I get back is whatever the value of
> Fields!BWT.Value is, without it adding up. I know that I could use the
> runningvalue function, but I am wondering why this code doesn't work?

Custom Code Access Error

I've created a function Period in custom code block. Then I put expression
=Code!Period()
in the textbox. Then I got this message:
The value expression for the textbox â'textbox6â' contains an error: [BC30367]
Class 'ReportExprHostImpl.CustomCodeProxy' cannot be indexed because it has
no default property.
Am I doing something wrong?
--
Thanks in advance,
IDcode.period not code!period
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"exkievan" <exkievan@.discussions.microsoft.com> wrote in message
news:58940691-9E20-49AE-82E8-4F8BE0092E9C@.microsoft.com...
> I've created a function Period in custom code block. Then I put expression
> =Code!Period()
> in the textbox. Then I got this message:
> The value expression for the textbox 'textbox6' contains an error:
[BC30367]
> Class 'ReportExprHostImpl.CustomCodeProxy' cannot be indexed because it
has
> no default property.
> Am I doing something wrong?
> --
> Thanks in advance,
> ID

Custom Code

Hi,
In Report Properties - Code Tab- i have written custom code, i want to get
the report parameter value in custom code, so i have written as follows:
ie assigning to a variable.
strValue=Parameters!UserID.Value
Is this code is correct to get the parameter value in custom code or i need
to make any other changes.
A quick response is appriciated.
Thanks in advance,
AnithaParameters!UserID.Value is the correct syntax.
Are you getting any errors with the above code?
"Anitha Naidu" wrote:
> Hi,
> In Report Properties - Code Tab- i have written custom code, i want to get
> the report parameter value in custom code, so i have written as follows:
> ie assigning to a variable.
> strValue=Parameters!UserID.Value
> Is this code is correct to get the parameter value in custom code or i need
> to make any other changes.
> A quick response is appriciated.
> Thanks in advance,
> Anitha|||Hi Kiran,
Thank you for the reply, i got error as "error in line 1" for
Parameters!UserID.Value, so i have used Report.Parameters!UserID.Value.
Now its working fine.
Regards,
Anitha
"Kiran" wrote:
> Parameters!UserID.Value is the correct syntax.
> Are you getting any errors with the above code?
> "Anitha Naidu" wrote:
> > Hi,
> >
> > In Report Properties - Code Tab- i have written custom code, i want to get
> > the report parameter value in custom code, so i have written as follows:
> > ie assigning to a variable.
> > strValue=Parameters!UserID.Value
> > Is this code is correct to get the parameter value in custom code or i need
> > to make any other changes.
> >
> > A quick response is appriciated.
> >
> > Thanks in advance,
> > Anitha

Custom code

I have a report parameter that gets its value from a custom assembly. I am
not sure when or where to call the assembly. The examples show that I can
override the OnInit() method of the report.
Protected Overrides Sub OnInit()
m_myClass = new MyClass(User!Language, Paramters!Territory)
End Sub
Aside from the OnInit(), what other methods can be overriden from the report
code window?
TIA.You need to call your class from the default value of the report parameter,
right?
If yes then you probably need to have your class initialized before it is
called.
This can be done either thru Code (define public member variable of your
class, instantiate the class in OnInit) or you can specify member variable
name and class name in the classes tab. In this case report server will
automatically create member variable and instantiate your class using
default constructor (constructor with no parameters).
In both cases you can get your class instance using Code.member_variable
syntax.
--
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:uqJNO#KBFHA.2568@.TK2MSFTNGP11.phx.gbl...
> I have a report parameter that gets its value from a custom assembly. I
am
> not sure when or where to call the assembly. The examples show that I can
> override the OnInit() method of the report.
> Protected Overrides Sub OnInit()
> m_myClass = new MyClass(User!Language, Paramters!Territory)
> End Sub
> Aside from the OnInit(), what other methods can be overriden from the
report
> code window?
> TIA.
>

Custom Assembly in RDL - Need Solution - Very Urgent

Hi All,
I have a Custom .net dll, refered in the RDL, the dll has a function which
gets the encrypted CreditCard# and returns the Decrypted value to the
Report.
I have attached the code @. the end.
I have given created the permission set and code group and copied the dll
in the following location
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\
bin\
Now when I run the report I am getting the Credit Card# decrypted shown in
the Preview window of Report Designer.....
But when I deploy the report in my report server I am getting #Error in the
CC# field.
I followed the step in the (http://support.microsoft.com/?
kbid=842419#XSLTH3133121123120121120120) to-grant permissions to a custom
assembly that is referenced in a report in Reporting Services
But there are four steps
1. Assert permissions in your custom assembly.
2. Make the custom assembly available to Report Designer and to Report
Server.
3. Reference the custom assembly in your report.
4. Modify the code access security settings for your custom assembly.
I am not sure with the STEP -1 WHAT ASSET PERMISSION IN CUSTOM ASSEMBLY TO
ADD FOR THE CRPTOGRAPY...because in the link I can see for the textfile and
SQLClinet...
I need your help on this...
Thanks in advance
Balaji.
using System;
using System.Text;
using System.IO;
using System.Collections.Specialized;
using System.Configuration;
using System.Security.Cryptography;
using FCLX509 = System.Security.Cryptography.X509Certificates;
using WSEX509 = Microsoft.Web.Services2.Security.X509;
using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
namespace RDLCustomCode
{
/// <summary>
/// This class is responsible for the data decryption of the
/// user credit card information
/// </summary>
public class DataDecryptionClass
{
public DataDecryptionClass()
{
}
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
string sCreditCard = "";
try
{
FileStream file = new FileStream("C:\\path.txt", FileMode.OpenOrCreate,
FileAccess.Write);
StreamWriter sw = new StreamWriter(file);
WSEX509.X509CertificateStore.StoreLocation location = WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
WSEX509.X509CertificateStore.StoreProvider provider = WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs = store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp = (WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
sw.Write(sCreditCard);
}
}
sw.Close();
file.Close();
if (store != null)
store.Close();
return sCreditCard;
//return cc;
}
catch
{
return "ErrorHandling";
}
}
--
Message posted via http://www.sqlmonster.comDid you get an error ? WHat is the error ? Did you see a .NET stack trace ?
Did you try to put the Assembly in the GAC ? There you check wheter if it is
a permission problem.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"BALAJI KRISHNAN via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb
im Newsbeitrag news:22c746269e61489188230bc4b59e35df@.SQLMonster.com...
> Hi All,
> I have a Custom .net dll, refered in the RDL, the dll has a function which
> gets the encrypted CreditCard# and returns the Decrypted value to the
> Report.
> I have attached the code @. the end.
> I have given created the permission set and code group and copied the dll
> in the following location
> C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\
> bin\
> Now when I run the report I am getting the Credit Card# decrypted shown in
> the Preview window of Report Designer.....
> But when I deploy the report in my report server I am getting #Error in
> the
> CC# field.
> I followed the step in the (http://support.microsoft.com/?
> kbid=842419#XSLTH3133121123120121120120) to-grant permissions to a custom
> assembly that is referenced in a report in Reporting Services
> But there are four steps
> 1. Assert permissions in your custom assembly.
> 2. Make the custom assembly available to Report Designer and to Report
> Server.
> 3. Reference the custom assembly in your report.
> 4. Modify the code access security settings for your custom assembly.
>
> I am not sure with the STEP -1 WHAT ASSET PERMISSION IN CUSTOM ASSEMBLY TO
> ADD FOR THE CRPTOGRAPY...because in the link I can see for the textfile
> and
> SQLClinet...
> I need your help on this...
> Thanks in advance
> Balaji.
>
> using System;
> using System.Text;
> using System.IO;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace RDLCustomCode
> {
> /// <summary>
> /// This class is responsible for the data decryption of the
> /// user credit card information
> /// </summary>
> public class DataDecryptionClass
> {
> public DataDecryptionClass()
> {
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> string sCreditCard = "";
> try
> {
> FileStream file = new FileStream("C:\\path.txt", FileMode.OpenOrCreate,
> FileAccess.Write);
> StreamWriter sw = new StreamWriter(file);
>
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> sw.Write(sCreditCard);
>
> }
> }
> sw.Close();
> file.Close();
> if (store != null)
> store.Close();
> return sCreditCard;
> //return cc;
> }
> catch
> {
> return "ErrorHandling";
> }
>
> }
> --
> Message posted via http://www.sqlmonster.com|||I ve solved similar problem a day before. See this...
Problem is: You dont ve File Permission for your assembly (dll).
Solution:
Step1: Change rrsvpolicy.config with following snippet added-
<PermissionSet
class="NamedPermissionSet"
version="1"
Name="HelloFilePermissionSet"
Description="A special permission set that grants read access to my
hello file.">
<IPermission
class="FileIOPermission"
version="1"
PathDiscovery="C:\inetpub\mailroot"
/>
<IPermission
class="SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
</PermissionSet>
Step 2: Change Dll with the following code added on class-
[FileIOPermissionAttribute(SecurityAction.Assert,
PathDiscovery="c:\\inetpub\\mailroot\\")]
public class MailSystem
{
public MailSystem()
{
}
............//rest of class.
Step 3: Change Dll with the following code added in Assemblyinfo.cs-
[assembly:AllowPartiallyTrustedCallers]
Note: Please add appropriate namespaces.
:o) It works for sure. and if this doesnt work.. mail
me(suneetmohan@.gmail.com). i'll mail ya complete code.

Custom Assembly

We have written a custom assembly which makes a call to database and
executes one stored procedure and based on stored procedure returned value a
method called IsAuthorized() returns true or false. In the development
environment assembly and report behaves properly. But when we deploy it to
production it gives us error. Any ideas what we are doing wrong ? We are
guessing its some kind of permission issues. Any help greatly appreciated.There is a very useful article on permissions with custom assemblies here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_16g2.asp
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>|||two books specifically talk about this. microsoft reporting services in
action and hitchhikers guide to sql server 2000.
they solved my problems.
"new.microsoft.com" <gsinthoju@.mail.educo-int.com> wrote in message
news:O0ka2itaFHA.3620@.TK2MSFTNGP09.phx.gbl...
> We have written a custom assembly which makes a call to database and
> executes one stored procedure and based on stored procedure returned value
> a
> method called IsAuthorized() returns true or false. In the development
> environment assembly and report behaves properly. But when we deploy it to
> production it gives us error. Any ideas what we are doing wrong ? We are
> guessing its some kind of permission issues. Any help greatly appreciated.
>

cursors problem

hi i am getting that message to update field value into table
Msg 16933, Level 16, State 1, Procedure RmDupValWSWCode, Line 39
The cursor does not include the table being modified or the table is not
updatable through the cursor.
I have two databases with same tables, in one database it work on that table
but in another database its not working on the same table with the same
data......
why cursor behave like thatHello!
Could you post your code?
Thanks!|||Since you haven't posted the code. I am guessing :)
are you using the two part name where ever you refer to the table.
I mean refer to all the table names as
dbo.table1
or whichever schema you are using.
Hope this helps.|||Also, make sure the user running the stored procedure has access to the
tables.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
> Since you haven't posted the code. I am guessing :)
> are you using the two part name where ever you refer to the table.
> I mean refer to all the table names as
> dbo.table1
> or whichever schema you are using.
> Hope this helps.|||Hey Jim..
long time no clashes.. was missing you :)
"Jim Underwood" wrote:

> Also, make sure the user running the stored procedure has access to the
> tables.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2919D9C0-492D-4582-89B0-BEDABA2B0E6E@.microsoft.com...
>
>|||Lol. Yeah, you missed an entire day last w. All day long I was looking
for some posts from you. You usually have a few creative and thought
provoking solutions. I think today's is the negative flag * 2 +1 to get
either a positive or negative number. I had to get some caffeine before I
could figure it out.
Actually, I was hoping to see if you had a mathematical formula to solve the
matrix (Matriz) problem posted yesterday. The best I could come up with was
a mapping table. I couldn't seem to find a mathematical approach to the
problem, so I went with simple brute force.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
> Hey Jim..
> long time no clashes.. was missing you :)
> "Jim Underwood" wrote:
>|||Gee...Thanks for the complments..
Well network was down yesterday.. and didn't see the post on matrix.. will
look into it now :)
"Jim Underwood" wrote:

> Lol. Yeah, you missed an entire day last w. All day long I was lookin
g
> for some posts from you. You usually have a few creative and thought
> provoking solutions. I think today's is the negative flag * 2 +1 to get
> either a positive or negative number. I had to get some caffeine before I
> could figure it out.
> Actually, I was hoping to see if you had a mathematical formula to solve t
he
> matrix (Matriz) problem posted yesterday. The best I could come up with w
as
> a mapping table. I couldn't seem to find a mathematical approach to the
> problem, so I went with simple brute force.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:6FEE9DDC-CEBC-46B8-8AE1-17E1CCEE47A1@.microsoft.com...
>
>

Custom aggregation value for all hierarchies'' levels above a specific level name?

I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.

The behavior that I'd want is:

If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.

I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.

Is this definitely possible and does it have minimal impact on query times and performance?

There might be a better design - if so, please comment and set me straight. Thanks!

I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)

But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.

How would one go about overriding the cube behavior?

If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.

|||

Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?

I've included syntax of 'currenthierarchy', but i don't know if that's really possible.

SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])

this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)

END SCOPE

Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?

Custom aggregation value for all hierarchies'' levels above a specific level name?

I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.

The behavior that I'd want is:

If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.

I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.

Is this definitely possible and does it have minimal impact on query times and performance?

There might be a better design - if so, please comment and set me straight. Thanks!

I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)

But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.

How would one go about overriding the cube behavior?

If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.

|||

Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?

I've included syntax of 'currenthierarchy', but i don't know if that's really possible.

SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])

this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)

END SCOPE

Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?

Custom aggregation value for all hierarchies'' levels above a specific level name?

I currently have a customer and product dimension. I'm planning on adding in a fact 'customer count', based on whether or not the customer has a specific product, and I'd also be adding a Product Status dimension. I think I'll have the default member of Product Status be 'Active' because generally, people want to see only this type of number.

The behavior that I'd want is:

If Product Status is used with Product.Product.All, then I want a behavior of "check for at least 1 Active product status for the current customer and if it is found, then the customer's status is "Active", otherwise it would be "Inactive". This is similar to what I'd want for different hierarchies in the product dimension.

I have several product hierarchies that and 'Product' is at different levels of the hierarchies. Any level above 'Product' should have a behavior as described above.

Is this definitely possible and does it have minimal impact on query times and performance?

There might be a better design - if so, please comment and set me straight. Thanks!

I did a test and I see now that the default behavior takes care of this for 'active' (anything above product level is only counted once because I'm using a distinct count type measure)

But now what's needed is a custom behavior when inactive is used with a 'customer count' measure. If looking at inactive counts for a Product.Product.All, an item (customer) should only be counted if all of their products are inactive.

How would one go about overriding the cube behavior?

If there are no other ideas, I may have to do a dirty workaround and make another status - 'Entirely Inactive' and it would mean that all products for a customer are inactive, calculated in the dsv table.

|||

Here is my attempt at trying to override the inactive member, but what needs to change for it to work correctly?

I've included syntax of 'currenthierarchy', but i don't know if that's really possible.

SCOPE(Measures.[Customer Count], [Product Status].[Product Status].[Inactive])

this = IIF( IsEmpty(HEAD(EXISTS(DESCENDANTS([Product].CurrentHierarchy.CurrentMember) * [Customer].[Customer].CurrentMember, [Product Status].[Product Status].[Active]))), [Product Status].[Product Status].[Inactive], [Product Status].[Product Status].[Active[)

END SCOPE

Again in English, what I want to do is override the value of [Product Status].[Product Status] to be active if any of the descendants of the current product have an active status. Some attributes of the product dimension can't determine this - ie color, but having several trees, I'm not sure how to get at the hierarchy dynamically. Also, is "this = " correct?

Cursors aren't recommended I know but...

Is there any other choice?
Here's what I'm working with. I have to concacentate together a series of
description fragments into one value. The table I'm working with has a
primary & foreign key, as well as a field containing the 'line' number, and
another containing the 48 character description. The descriotion could be
from 1 to 4 to 8 or any number of lines, where each line has a primary key
and ties into a foreign key.
Example:
FKey: 55
PKey: 20
Line: 1
Desc: Outside Delivery Services regarding Federal
FKey: 55
PKey: 21
Line: 2
Desc: Express Corporation.
Obviously you'd want to return the whole description as: Outside Delivery
Services regarding Federal Express Corporation.
Now imagine a description could be even 7 or 8 lines, or just 1. Without
using a cursor (which I did and it takes 2-3 minutes to return back), is
their a way to return back into a variable or result set the full
description?
Thanks!Why can't the client loop through the rows and do the concatenation itself?
Loop through, if it's the same FKey as the previous then append the line,
else write out the full row detail.
(Or, why not change the design so that the whole description is contained in
one column.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number,
> and
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>|||Fair questions certainly.
I can't change the database structure as it is a vendor's database we use
for our accounting system, and changing the structure wold violate our
support agreement I'm sure.
The client program in this case is going to be an ASP.Net web application.
I've already got 99% of the sql stored procedure written and returning what
I want, just need to add the description. It was my plan to have the ASP.Net
Datagrid display the results of my stored proc (which I've done before and
have no problems handling).
So does SQL Server and T-SQL have another option for me to accomplish this.
All the logic doesn't *have* to stay in a procedure, but I would like it to
obviously.
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Why can't the client loop through the rows and do the concatenation
itself?
> Loop through, if it's the same FKey as the previous then append the line,
> else write out the full row detail.
> (Or, why not change the design so that the whole description is contained
in
> one column.)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
of
be
key
Delivery
>|||Better if you do that in the client app. Here you will find some solutions.
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"Steve Schroeder" wrote:

> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number, an
d
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>
>|||You can try a kludge like this:
http://www.aspfaq.com/2529
However, you are likely going to notice some significant performance deltas
compared to doing this where it belongs...
In the meantime, you should talk to the vendor about having them hire some
database people.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:O36igoRQFHA.3144@.tk2msftngp13.phx.gbl...
> Fair questions certainly.
> I can't change the database structure as it is a vendor's database we use
> for our accounting system, and changing the structure wold violate our
> support agreement I'm sure.
> The client program in this case is going to be an ASP.Net web application.
> I've already got 99% of the sql stored procedure written and returning
> what
> I want, just need to add the description. It was my plan to have the
> ASP.Net
> Datagrid display the results of my stored proc (which I've done before and
> have no problems handling).
> So does SQL Server and T-SQL have another option for me to accomplish
> this.
> All the logic doesn't *have* to stay in a procedure, but I would like it
> to
> obviously.
> Thanks
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> itself?
> in
> of
> be
> key
> Delivery
>|||Heh...yeah, but they brag about how they have world-wide fortune 500
companies using their database...chumps.
Anyhow...looks like the best thing to do would be to return two datasets,
then populate the datagrid in ASP.Net with the descriptions. Anything else
to too slow or too kludgey. Thanks Billy <roll eyes>
But thank you Aaron & Alejandro
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ulOphtRQFHA.2136@.TK2MSFTNGP14.phx.gbl...
> You can try a kludge like this:
> http://www.aspfaq.com/2529
> However, you are likely going to notice some significant performance
deltas
> compared to doing this where it belongs...
> In the meantime, you should talk to the vendor about having them hire some
> database people.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:O36igoRQFHA.3144@.tk2msftngp13.phx.gbl...
use
application.
and
line,
contained
series
a
number,
could
primary
>|||Actually...I could update the ado dataset using a loop before I bind the
result set to the datagrid couldn't I? Via VB.Net & ADO code. That would
probably be quickest, as you suggested.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Why can't the client loop through the rows and do the concatenation
itself?
> Loop through, if it's the same FKey as the previous then append the line,
> else write out the full row detail.
> (Or, why not change the design so that the whole description is contained
in
> one column.)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
of
be
key
Delivery
>|||Steve,
Another option to do is something like
select isnull(line1.desc,'') + isnull(line2.desc,'') + ... +
isnull(line8.desc,'')
from desc line1
left join desc line2 on line1.fkey=line2.fkey and line1.line=line2.line+1
left join desc line3 on line2.fkey=line3.fkey and line2.line=line3.line+1
..
left join desc line8 on line7.fkey=line8.fkey and line7.line=line8.line+1
where line1.fkey=55
Upside is great index usage. Downside is limited number of lines.
-- Alex Papadimoulis
"Steve Schroeder" wrote:

> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number, an
d
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>
>