customers in 2003, not 2004 (was "Help with (probably) simple query")

I have 2 tables showing customers from 2003 and 2004.
They show customer number, customer name, and date attended.
I need to show what customers came in 2003, but not in 2004 by name. Problem with the customer number is that some times the customers lose their cards and get new numbers.

Any help is appreciated.You will still have difference if also the name changed!
But you could try:
SELECT NAME FROM TAB2003
MINUS
SELECT NAME FROM TAB2004;
;)

Customer Accounts Report

I am on security doubt on how to design a report that must show the accounts
of a customer.
I have an aspx page showing the customer info , then he must push a button a
see its accounts on a report generated by Reporting Services.
Obviously the report can not be accesed via a Customer parameters because
another users could see any customer 's accounts.
How can i solve it.
I am thinking in generating a temp table with a random id an pass it by
parameter, but it is only so as to reduce an intruder access.
ThanksHi Fernando,
If you are using ASP.NET you have more than one option:
1.- Storing in Sessions variables
2.- Using QueryString
3.- Using Server side code (static variables)
4.- Change the report viewer (passsing SOAP params)
The fact is the report params does not to be part of the URL you can easily
hide them using multiples techniques, depends how your app is designed you
may use some of them.
Thanks
Jerry
--
You can use this example to show you how to call the report params.
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
"Fernando Hunth" wrote:
> I am on security doubt on how to design a report that must show the accounts
> of a customer.
> I have an aspx page showing the customer info , then he must push a button a
> see its accounts on a report generated by Reporting Services.
> Obviously the report can not be accesed via a Customer parameters because
> another users could see any customer 's accounts.
> How can i solve it.
> I am thinking in generating a temp table with a random id an pass it by
> parameter, but it is only so as to reduce an intruder access.
> Thanks|||Thats true we have multiples techniques to pass parameters but not to avoid
access to those public parameters.
When a report is deployed , anyone (authenticated) can access to the report
and can access a report via URL and with the posibility of changing any
parameter value via URL.
That 's what i want to avoid. I am trying to investigate doing it with a
DataProcessingExtension
Thanks.
"Jerry" wrote:
> Hi Fernando,
> If you are using ASP.NET you have more than one option:
> 1.- Storing in Sessions variables
> 2.- Using QueryString
> 3.- Using Server side code (static variables)
> 4.- Change the report viewer (passsing SOAP params)
> The fact is the report params does not to be part of the URL you can easily
> hide them using multiples techniques, depends how your app is designed you
> may use some of them.
> Thanks
> Jerry
> --
> You can use this example to show you how to call the report params.
> http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> "Fernando Hunth" wrote:
> > I am on security doubt on how to design a report that must show the accounts
> > of a customer.
> > I have an aspx page showing the customer info , then he must push a button a
> > see its accounts on a report generated by Reporting Services.
> > Obviously the report can not be accesed via a Customer parameters because
> > another users could see any customer 's accounts.
> > How can i solve it.
> >
> > I am thinking in generating a temp table with a random id an pass it by
> > parameter, but it is only so as to reduce an intruder access.
> >
> > Thanks|||Check out this link:
http://www.codeproject.com/useritems/SQLRSViewer.asp#TheCodeBehind
It's an ASP.NET app that lets you recreate the entire look of the report
viewer. It also give you flexibilty in what you feed the report.
I was able to use this program and feed in my own parameters. The report
will display and no parameters are visible in the URL.
Look for the variable 'reportParameterValues2' in the code. It's a array
that stores the parameters for your report.
Hope this helps, or at least gets you started.
"Fernando Hunth" wrote:
> Thats true we have multiples techniques to pass parameters but not to avoid
> access to those public parameters.
> When a report is deployed , anyone (authenticated) can access to the report
> and can access a report via URL and with the posibility of changing any
> parameter value via URL.
> That 's what i want to avoid. I am trying to investigate doing it with a
> DataProcessingExtension
> Thanks.
>
> "Jerry" wrote:
> > Hi Fernando,
> >
> > If you are using ASP.NET you have more than one option:
> >
> > 1.- Storing in Sessions variables
> > 2.- Using QueryString
> > 3.- Using Server side code (static variables)
> > 4.- Change the report viewer (passsing SOAP params)
> >
> > The fact is the report params does not to be part of the URL you can easily
> > hide them using multiples techniques, depends how your app is designed you
> > may use some of them.
> >
> > Thanks
> > Jerry
> > --
> > You can use this example to show you how to call the report params.
> > http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> >
> > "Fernando Hunth" wrote:
> >
> > > I am on security doubt on how to design a report that must show the accounts
> > > of a customer.
> > > I have an aspx page showing the customer info , then he must push a button a
> > > see its accounts on a report generated by Reporting Services.
> > > Obviously the report can not be accesed via a Customer parameters because
> > > another users could see any customer 's accounts.
> > > How can i solve it.
> > >
> > > I am thinking in generating a temp table with a random id an pass it by
> > > parameter, but it is only so as to reduce an intruder access.
> > >
> > > Thanks|||Yes, i previously took a look a this project, but that is another client
solution wher you can pass parameters to a public report.
"Hausbro" wrote:
> Check out this link:
> http://www.codeproject.com/useritems/SQLRSViewer.asp#TheCodeBehind
> It's an ASP.NET app that lets you recreate the entire look of the report
> viewer. It also give you flexibilty in what you feed the report.
> I was able to use this program and feed in my own parameters. The report
> will display and no parameters are visible in the URL.
> Look for the variable 'reportParameterValues2' in the code. It's a array
> that stores the parameters for your report.
> Hope this helps, or at least gets you started.
> "Fernando Hunth" wrote:
> > Thats true we have multiples techniques to pass parameters but not to avoid
> > access to those public parameters.
> > When a report is deployed , anyone (authenticated) can access to the report
> > and can access a report via URL and with the posibility of changing any
> > parameter value via URL.
> > That 's what i want to avoid. I am trying to investigate doing it with a
> > DataProcessingExtension
> >
> > Thanks.
> >
> >
> >
> > "Jerry" wrote:
> >
> > > Hi Fernando,
> > >
> > > If you are using ASP.NET you have more than one option:
> > >
> > > 1.- Storing in Sessions variables
> > > 2.- Using QueryString
> > > 3.- Using Server side code (static variables)
> > > 4.- Change the report viewer (passsing SOAP params)
> > >
> > > The fact is the report params does not to be part of the URL you can easily
> > > hide them using multiples techniques, depends how your app is designed you
> > > may use some of them.
> > >
> > > Thanks
> > > Jerry
> > > --
> > > You can use this example to show you how to call the report params.
> > > http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
> > >
> > > "Fernando Hunth" wrote:
> > >
> > > > I am on security doubt on how to design a report that must show the accounts
> > > > of a customer.
> > > > I have an aspx page showing the customer info , then he must push a button a
> > > > see its accounts on a report generated by Reporting Services.
> > > > Obviously the report can not be accesed via a Customer parameters because
> > > > another users could see any customer 's accounts.
> > > > How can i solve it.
> > > >
> > > > I am thinking in generating a temp table with a random id an pass it by
> > > > parameter, but it is only so as to reduce an intruder access.
> > > >
> > > > Thanks|||Ok.
I decided to write some sample code for the solution i was thinking about
generating a temp table with a random id an pass it by
parameter, or something like this.
I 'll publish it in somewhere.
I 'll go on writing on my blog
http://msdevelopers.blogspot.com/2005/03/reporting-services-filtering.html
Fernando Hunth
Senior Developer
Huddle Group S.A.
fernando@.huddle.com.ar
Huddle Group S.A. | Enterprise Technology Services
Microsoft Certified Partner
Ciudad de Buenos Aires · Argentina
www.huddle.com.ar
"Fernando Hunth" wrote:
> I am on security doubt on how to design a report that must show the accounts
> of a customer.
> I have an aspx page showing the customer info , then he must push a button a
> see its accounts on a report generated by Reporting Services.
> Obviously the report can not be accesed via a Customer parameters because
> another users could see any customer 's accounts.
> How can i solve it.
> I am thinking in generating a temp table with a random id an pass it by
> parameter, but it is only so as to reduce an intruder access.
> Thanks

Custom Errors

Hi,

I am showing my report in the web application using Report Viewer control.In the report i have start date and End date parameters.While running the report if i give startdate as '45/66/20007' it is displaying a message which is not user friendly.Is there is any way we can handle this type of errors and display our own custom error messages.

Thanks in advance

Hello Mahima,

I was facing the same problem but I solved it through strored procedures. I passed my custom message in the RaiseError exception after the validation.

Example : This is not a solution for your problem, but I think you change the validation accordingly

IF (DATEDIFF(MM,@.StartReceivedDate,@.EndReceivedDate) > 12)

BEGIN

SET @.DateErrMsg = 'The Date Range should fall with in an year, Please correct the range.'

RAISERROR (@.DateErrMsg,18,1)

END

Hope this helps.

|||

Hi,

I try to implement that when the condition occured, it is displaying like in this way:

Query execution failed for DataSet1.

An error occured during local report processing

under this our raise error message is displaying.

Is there any way only to display our message which in raise error other than first 2 lines.

Thanks in advance

|||

I am using the (ReportErrorEventArgs e) from Microsoft.Reporting.WinForms namespace.

The way I have displayed our Raise Error exception is through the Message box.

Example:

MessageBox.Show(e.Exception.GetBaseException().Message, "Some message",MessageBoxButtons.OK, MessageBoxIcon.Error);

Then

I am hiding the " Query execution failed for DataSet1.

An error occured during local report processing

our raise error message is displaying."

with this expression : ReportErrorEventArgs.Handled = True.

In our requirement we need a messagebox to display ,but I am sure we can find a way to hide the first two lines and dispay only Raise Error execption message.

Hope this helps.....

|||

Hi,

Can you please tell me in which event you are handling this.

Can you please give me the detailed example or any other link which speciefied this event handling.

Thanks in advance

|||

It is the ReportError event for the ReportViewer.

private Microsoft.Reporting.WinForms.ReportViewer rptvSelReport;

Example:

private void rptvSelReport_ReportError(object sender, ReportErrorEventArgs e)

{

MessageBox.Show(e.Exception.GetBaseException().Message, DIALOG_FORM_CAPTION,MessageBoxButtons.OK, MessageBoxIcon.Error);

e.Handled = true;

Cursor.Current = Cursors.Arrow;

}

We have used this in our dev. environment and its working fine.

Let me know if you need any help....

|||

Hi,

Thanks for the information.I will try this and i will ask you when i have problem with this,

Thanks

|||

Custom Errors

Hi,

I am showing my report in the web application using Report Viewer control.In the report i have start date and End date parameters.While running the report if i give startdate as '45/66/20007' it is displaying a message which is not user friendly.Is there is any way we can handle this type of errors and display our own custom error messages.

Thanks in advance

Hello Mahima,

I was facing the same problem but I solved it through strored procedures. I passed my custom message in the RaiseError exception after the validation.

Example : This is not a solution for your problem, but I think you change the validation accordingly

IF(DATEDIFF(MM,@.StartReceivedDate,@.EndReceivedDate)> 12)

BEGIN

SET @.DateErrMsg ='The Date Range should fall with in an year, Please correct the range.'

RAISERROR(@.DateErrMsg,18,1)

END

Hope this helps.

|||

Hi,

I try to implement that when the condition occured, it is displaying like in this way:

Query execution failed for DataSet1.

An error occured during local report processing

under this our raise error message is displaying.

Is there any way only to display our message which in raise error other than first 2 lines.

Thanks in advance

|||

I am using the (ReportErrorEventArgs e) from Microsoft.Reporting.WinForms namespace.

The way I have displayed our Raise Error exception is through the Message box.

Example:

MessageBox.Show(e.Exception.GetBaseException().Message, "Some message",MessageBoxButtons.OK, MessageBoxIcon.Error);

Then

I am hiding the " Query execution failed for DataSet1.

An error occured during local report processing

our raise error message is displaying."

with this expression : ReportErrorEventArgs.Handled = True.

In our requirement we need a messagebox to display ,but I am sure we can find a way to hide the first two lines and dispay only Raise Error execption message.

Hope this helps.....

|||

Hi,

Can you please tell me in which event you are handling this.

Can you please give me the detailed example or any other link which speciefied this event handling.

Thanks in advance

|||

It is the ReportError event for the ReportViewer.

private Microsoft.Reporting.WinForms.ReportViewer rptvSelReport;

Example:

private void rptvSelReport_ReportError(object sender, ReportErrorEventArgs e)

{

MessageBox.Show(e.Exception.GetBaseException().Message, DIALOG_FORM_CAPTION,MessageBoxButtons.OK, MessageBoxIcon.Error);

e.Handled = true;

Cursor.Current = Cursors.Arrow;

}

We have used this in our dev. environment and its working fine.

Let me know if you need any help....

|||

Hi,

Thanks for the information.I will try this and i will ask you when i have problem with this,

Thanks

|||

Custom DataFlow Transformation not showing up in toolbox

I've created a custom data flow tranformation and it isn't showing up in the Tool Box Items to be added under the Data Flow Items tab (right click on tool box, 'Choose Items...', then clicked Data Flow Items).

I have done the following:

signed the assembly,

added to GAC,

copied the dll to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents.

It worked previously when I was just starting out, however now I cannot see it. What would cause it to not show up? Everything compiles fine. How would I determine how to fix it so that it shows up?

Apart from the component being in the GAC, there are two key steps to be taken for a custom component to be recognized by the BI Studio:

placing the binary under the folder where SSIS design time checks to enumerate pipeline components, which is at <SQL Installation Folder>\90\DTS\Pipeline Components (I'd double check this on the target machine via the reg key at the very bottom, cause it could be different on a 64bit or in a production server)

Custom Connection Manger Doesn't Show Up In VS

I have created a custom connection manager and it was showing up fine as was the UI I created for it. For some reason now no matter what I do I cannot get it to show up in the list when right-clicking and choosing New connection...

I have also created two custom tasks. One of them works perfectly. The second was working but now causes an error when you open the package. Here is the error message:

TITLE: Microsoft Visual Studio

There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

There are no additional errors in the list only this one. I have tried everything I can think of to fix these two issues. Both problems emerged at the same time so may be related but that would not explain why the second custom task I have created works fine and doesn't cause errors.

Help!

Thanks,

Dave

1. CM: have you defined a public class with DtsConnection attribute that defines ConnectionType and other properties? Once you do it, install the assembly to GAC and DTS\Connections folder, then restart SSIS service.

2. Hard to diagnose remotely :) Try attaching debugger to DevEnv.exe process, and monitor first-chance exceptions - this usually gives the hint of what's wrong.

|||I've seen that error when the task is either not installed correctly (GAC + DTS\Tasks folder) or you have changed something such that the XML will have changed, e.g. Added a new property and not handled it in Upgrade.|||

EUREKA!!

I found the problem for the errors I was receiving on my custom task.

While the SSIS persistance framework can cope with simple data type properties on your custom task, it cannot copy with things as mundane as string or integer arrays but there are no errors at design time!

It recognizes the array properties and even gives you the nice builder for them in the properties window. You can click Save and then close the package all without incident. When you reopen the package, it carks it because it can't load the values for those properties. No error saying that that is the problem. All you see is the error message that is in my original post.

Where can I file a bug report/feature request to have this fixed? It has cost me nearly a week trying to figure it out.

Dave

|||

You can make request and report bugs at http://connect.microsoft.com

If you roll your own load and save methods you can handle it quite happily, just implement IDTSComponentPersist.