I would propose the following workaround ...

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

I would like to simplify my query

What I'm trying to do is get the last entry (by date) for a given entity (which should be a single row). The following returned more than one row:

select termId, entityId, max(enddate)
from Terms
where entityId = 1234
group by termId, entityId

This does return me one row:

select *
from Terms
where enddate in (select max(enddate)
from Terms
where entityId = 1234)

What I would like to do this in one select statement. Does anyone have any suggestions?

Quote:

Originally Posted by NamelessNumberheadMan

What I'm trying to do is get the last entry (by date) for a given entity (which should be a single row). The following returned more than one row:

select termId, entityId, max(enddate)
from Terms
where entityId = 1234
group by termId, entityId

This does return me one row:

select *
from Terms
where enddate in (select max(enddate)
from Terms
where entityId = 1234)

What I would like to do this in one select statement. Does anyone have any suggestions?

I think your query is correct. I cant get what you mean for simplify my query?

If you want you have to explain your requirement

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.
Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/library/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/library/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want to use multiple select queries in a stored procedure

Dear all
I actually want to query multiple tables from a single stored
procedure. Please let me know the possibility of doing this. If some
have any idea regarding this please contact me at
resume2karthik@.gmail.com.Of course it is possible, but your statement is so general that I do not
know what you want to do. For example:
1. Can you join several tables to produce a single result set? (Yes.)
2. Can you independently query several tables producing several result sets?
(Yes.)
3. Can you independently query several tables to finally producing a single
result set? (Yes.)
4. Et cetera.
Returning information from a stored procedure:
http://technet.microsoft.com/en-us/library/aa214398(SQL.80).aspx
Comments on processing multiple result sets:
http://technet.microsoft.com/en-us/...y/ms187602.aspx
The UNION command: http://technet.microsoft.com/en-us/...y/ms180026.aspx
Explain a little bit more and you will probably get a more useful answer.
RLF
<resume2karthik@.gmail.com> wrote in message
news:8cc48990-c843-4f34-b03e-b3b1d290701c@.x69g2000hsx.googlegroups.com...
> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.|||You can have multiple SELECT statements in your stored procuedre to return
the data and access each result sets using ADOs NextRecordset method.
- Sha Anand
"resume2karthik@.gmail.com" wrote:

> Dear all
> I actually want to query multiple tables from a single stored
> procedure. Please let me know the possibility of doing this. If some
> have any idea regarding this please contact me at
> resume2karthik@.gmail.com.
>

I want separate data sources for 1 RDL file

Hello all,
Here's my situation:
I have a single RDL file that describes a certain report.
I have different databases on different computers with the same tables
but different data in each table.
I need people to go to each computer, run the same RDL file, but have
it show the data that is specific to their database.
For instance, there is a single .rdl file which describes a personnel
report, and i want people at site A to only see info about site A's
personnel, and people at site B to only see info about site B's
personnel.
Is this possible? If so, is there any advice you can give me to help do
this? Thank you.
Kevin Green
Software Engineer
Pragmatech SoftwareOn each computer have an rds file specifying the connection properties for
that computer.
"KevinGreen24@.gmail.com" wrote:
> Hello all,
> Here's my situation:
> I have a single RDL file that describes a certain report.
> I have different databases on different computers with the same tables
> but different data in each table.
> I need people to go to each computer, run the same RDL file, but have
> it show the data that is specific to their database.
> For instance, there is a single .rdl file which describes a personnel
> report, and i want people at site A to only see info about site A's
> personnel, and people at site B to only see info about site B's
> personnel.
> Is this possible? If so, is there any advice you can give me to help do
> this? Thank you.
> Kevin Green
> Software Engineer
> Pragmatech Software
>|||Well we're working of a SaaS model, so every end user is running a thin
client, so we're not installing anything on the end user's computer.
magendo_man wrote:
> On each computer have an rds file specifying the connection properties for
> that computer.
> "KevinGreen24@.gmail.com" wrote:
> > Hello all,
> >
> > Here's my situation:
> >
> > I have a single RDL file that describes a certain report.
> >
> > I have different databases on different computers with the same tables
> > but different data in each table.
> >
> > I need people to go to each computer, run the same RDL file, but have
> > it show the data that is specific to their database.
> >
> > For instance, there is a single .rdl file which describes a personnel
> > report, and i want people at site A to only see info about site A's
> > personnel, and people at site B to only see info about site B's
> > personnel.
> >
> > Is this possible? If so, is there any advice you can give me to help do
> > this? Thank you.
> >
> > Kevin Green
> > Software Engineer
> > Pragmatech Software
> >
> >|||Are the databases on two different SQL servers? How do the users run the
report? From Report Manager, or embedded in a web page?
If they are running the reports from Report Manager on different servers
then you need to set up data sources (i.e. rds files) on each, with the same
name, but pointing to the site-specific database.
"KevinGreen24@.gmail.com" wrote:
> Well we're working of a SaaS model, so every end user is running a thin
> client, so we're not installing anything on the end user's computer.
> magendo_man wrote:
> > On each computer have an rds file specifying the connection properties for
> > that computer.
> >
> > "KevinGreen24@.gmail.com" wrote:
> >
> > > Hello all,
> > >
> > > Here's my situation:
> > >
> > > I have a single RDL file that describes a certain report.
> > >
> > > I have different databases on different computers with the same tables
> > > but different data in each table.
> > >
> > > I need people to go to each computer, run the same RDL file, but have
> > > it show the data that is specific to their database.
> > >
> > > For instance, there is a single .rdl file which describes a personnel
> > > report, and i want people at site A to only see info about site A's
> > > personnel, and people at site B to only see info about site B's
> > > personnel.
> > >
> > > Is this possible? If so, is there any advice you can give me to help do
> > > this? Thank you.
> > >
> > > Kevin Green
> > > Software Engineer
> > > Pragmatech Software
> > >
> > >
>|||Also in RS 2005 you can dynamically change the datasource based on the user.
I haven't done this but it is one of the capabilities.
Bruce Loehle-Conger
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> Are the databases on two different SQL servers? How do the users run the
> report? From Report Manager, or embedded in a web page?
> If they are running the reports from Report Manager on different servers
> then you need to set up data sources (i.e. rds files) on each, with the
> same
> name, but pointing to the site-specific database.
> "KevinGreen24@.gmail.com" wrote:
>> Well we're working of a SaaS model, so every end user is running a thin
>> client, so we're not installing anything on the end user's computer.
>> magendo_man wrote:
>> > On each computer have an rds file specifying the connection properties
>> > for
>> > that computer.
>> >
>> > "KevinGreen24@.gmail.com" wrote:
>> >
>> > > Hello all,
>> > >
>> > > Here's my situation:
>> > >
>> > > I have a single RDL file that describes a certain report.
>> > >
>> > > I have different databases on different computers with the same
>> > > tables
>> > > but different data in each table.
>> > >
>> > > I need people to go to each computer, run the same RDL file, but have
>> > > it show the data that is specific to their database.
>> > >
>> > > For instance, there is a single .rdl file which describes a personnel
>> > > report, and i want people at site A to only see info about site A's
>> > > personnel, and people at site B to only see info about site B's
>> > > personnel.
>> > >
>> > > Is this possible? If so, is there any advice you can give me to help
>> > > do
>> > > this? Thank you.
>> > >
>> > > Kevin Green
>> > > Software Engineer
>> > > Pragmatech Software
>> > >
>> > >
>>|||We host 1 report server which each client browser will access. We host
1 database server which has databases which hold the information for
each company that uses our product. So basically we host the data for
each company and we host the report server.
Each client browser will connect to the report server, and at that time
the appropriate company database will be accessed and the information
retrieved, along with the common .rdl file, and the appropriate report
rendered.
So there's 1 .rdl file which describes the form of the report, and
there are N databases for the N companies that use the product, which
hold the data. I need the 1 .rdl file to be able to display information
from any of the N databases that we have, based on the company that's
trying to access the report.
Does this make sense?
magendo_man wrote:
> Are the databases on two different SQL servers? How do the users run the
> report? From Report Manager, or embedded in a web page?
> If they are running the reports from Report Manager on different servers
> then you need to set up data sources (i.e. rds files) on each, with the same
> name, but pointing to the site-specific database.
> "KevinGreen24@.gmail.com" wrote:
> > Well we're working of a SaaS model, so every end user is running a thin
> > client, so we're not installing anything on the end user's computer.
> >
> > magendo_man wrote:
> > > On each computer have an rds file specifying the connection properties for
> > > that computer.
> > >
> > > "KevinGreen24@.gmail.com" wrote:
> > >
> > > > Hello all,
> > > >
> > > > Here's my situation:
> > > >
> > > > I have a single RDL file that describes a certain report.
> > > >
> > > > I have different databases on different computers with the same tables
> > > > but different data in each table.
> > > >
> > > > I need people to go to each computer, run the same RDL file, but have
> > > > it show the data that is specific to their database.
> > > >
> > > > For instance, there is a single .rdl file which describes a personnel
> > > > report, and i want people at site A to only see info about site A's
> > > > personnel, and people at site B to only see info about site B's
> > > > personnel.
> > > >
> > > > Is this possible? If so, is there any advice you can give me to help do
> > > > this? Thank you.
> > > >
> > > > Kevin Green
> > > > Software Engineer
> > > > Pragmatech Software
> > > >
> > > >
> >
> >|||That's exactly what i need, but i can't figure out how!
It also has to be secure of course.
Bruce L-C [MVP] wrote:
> Also in RS 2005 you can dynamically change the datasource based on the user.
> I haven't done this but it is one of the capabilities.
> Bruce Loehle-Conger
> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> > Are the databases on two different SQL servers? How do the users run the
> > report? From Report Manager, or embedded in a web page?
> >
> > If they are running the reports from Report Manager on different servers
> > then you need to set up data sources (i.e. rds files) on each, with the
> > same
> > name, but pointing to the site-specific database.
> >
> > "KevinGreen24@.gmail.com" wrote:
> >
> >> Well we're working of a SaaS model, so every end user is running a thin
> >> client, so we're not installing anything on the end user's computer.
> >>
> >> magendo_man wrote:
> >> > On each computer have an rds file specifying the connection properties
> >> > for
> >> > that computer.
> >> >
> >> > "KevinGreen24@.gmail.com" wrote:
> >> >
> >> > > Hello all,
> >> > >
> >> > > Here's my situation:
> >> > >
> >> > > I have a single RDL file that describes a certain report.
> >> > >
> >> > > I have different databases on different computers with the same
> >> > > tables
> >> > > but different data in each table.
> >> > >
> >> > > I need people to go to each computer, run the same RDL file, but have
> >> > > it show the data that is specific to their database.
> >> > >
> >> > > For instance, there is a single .rdl file which describes a personnel
> >> > > report, and i want people at site A to only see info about site A's
> >> > > personnel, and people at site B to only see info about site B's
> >> > > personnel.
> >> > >
> >> > > Is this possible? If so, is there any advice you can give me to help
> >> > > do
> >> > > this? Thank you.
> >> > >
> >> > > Kevin Green
> >> > > Software Engineer
> >> > > Pragmatech Software
> >> > >
> >> > >
> >>
> >>|||The below is from help. Note that they show using a parameter but you could
also use the result of a query that uses the global variable for user (I
believe it is User!Username). Have a table that provides the mapping from
user to database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
>>>>>>>>
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/4d8f0ae1-102b-4b3d-9155-fa584c962c9e.htm
Data Source Expressions
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
a.. Design the report using a static connection string. A static
connection string refers to a connection string that is not set through an
expression (for example, when you follow the steps for creating a
report-specific or shared data source, you are defining a static connection
string). Using a static connection string allows you to connect to the data
source in Report Designer so that you can get the query results you need to
create the report.
b.. When defining the data source connection, do not use a shared data
source. You cannot use a data source expression in a shared data source. You
must define a report-specific data source for the report.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. Add a report parameter to specify a data source. For parameter values,
you can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
e.. Be sure that the list of data sources share the same database schema.
All report design begins with schema information. If there is a mismatch
between the schema used to define the report and the actual schema used by
the report at run time, the report might not run.
f.. Before publishing the report, replace the static connection string
with an expression. Wait until you are finished designing the report before
you replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>>>
<KevinGreen24@.gmail.com> wrote in message
news:1153261522.566759.269720@.h48g2000cwc.googlegroups.com...
> That's exactly what i need, but i can't figure out how!
> It also has to be secure of course.
> Bruce L-C [MVP] wrote:
>> Also in RS 2005 you can dynamically change the datasource based on the
>> user.
>> I haven't done this but it is one of the capabilities.
>> Bruce Loehle-Conger
>> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
>> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
>> > Are the databases on two different SQL servers? How do the users run
>> > the
>> > report? From Report Manager, or embedded in a web page?
>> >
>> > If they are running the reports from Report Manager on different
>> > servers
>> > then you need to set up data sources (i.e. rds files) on each, with the
>> > same
>> > name, but pointing to the site-specific database.
>> >
>> > "KevinGreen24@.gmail.com" wrote:
>> >
>> >> Well we're working of a SaaS model, so every end user is running a
>> >> thin
>> >> client, so we're not installing anything on the end user's computer.
>> >>
>> >> magendo_man wrote:
>> >> > On each computer have an rds file specifying the connection
>> >> > properties
>> >> > for
>> >> > that computer.
>> >> >
>> >> > "KevinGreen24@.gmail.com" wrote:
>> >> >
>> >> > > Hello all,
>> >> > >
>> >> > > Here's my situation:
>> >> > >
>> >> > > I have a single RDL file that describes a certain report.
>> >> > >
>> >> > > I have different databases on different computers with the same
>> >> > > tables
>> >> > > but different data in each table.
>> >> > >
>> >> > > I need people to go to each computer, run the same RDL file, but
>> >> > > have
>> >> > > it show the data that is specific to their database.
>> >> > >
>> >> > > For instance, there is a single .rdl file which describes a
>> >> > > personnel
>> >> > > report, and i want people at site A to only see info about site
>> >> > > A's
>> >> > > personnel, and people at site B to only see info about site B's
>> >> > > personnel.
>> >> > >
>> >> > > Is this possible? If so, is there any advice you can give me to
>> >> > > help
>> >> > > do
>> >> > > this? Thank you.
>> >> > >
>> >> > > Kevin Green
>> >> > > Software Engineer
>> >> > > Pragmatech Software
>> >> > >
>> >> > >
>> >>
>> >>
>|||Right. That's similar what I did to solve the problem this morning:
1. Create the master .rdl file that has a hidden parameter called
"DbName"
2. Make the connection string in the data source dynamic:
="Data Source=(local);Initial Catalog=" & Parameters!DbName.Value
3. Create a linked report(s)
4. Using the report manager, set the default value of DbName according
to the company that will own each linked report.
This way, the parameter can be set for the report based on the company
it belongs to, and it's also secure because role-based security can be
set up at the linked report level.
Bruce L-C [MVP] wrote:
> The below is from help. Note that they show using a parameter but you could
> also use the result of a query that uses the global variable for user (I
> believe it is User!Username). Have a table that provides the mapping from
> user to database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> >>>>>>>>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/4d8f0ae1-102b-4b3d-9155-fa584c962c9e.htm
> Data Source Expressions
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the following
> guidelines when defining a data source expression:
> a.. Design the report using a static connection string. A static
> connection string refers to a connection string that is not set through an
> expression (for example, when you follow the steps for creating a
> report-specific or shared data source, you are defining a static connection
> string). Using a static connection string allows you to connect to the data
> source in Report Designer so that you can get the query results you need to
> create the report.
>
> b.. When defining the data source connection, do not use a shared data
> source. You cannot use a data source expression in a shared data source. You
> must define a report-specific data source for the report.
>
> c.. Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
>
> d.. Add a report parameter to specify a data source. For parameter values,
> you can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
>
> e.. Be sure that the list of data sources share the same database schema.
> All report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
>
> f.. Before publishing the report, replace the static connection string
> with an expression. Wait until you are finished designing the report before
> you replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
> >>>>>>>>>
> <KevinGreen24@.gmail.com> wrote in message
> news:1153261522.566759.269720@.h48g2000cwc.googlegroups.com...
> > That's exactly what i need, but i can't figure out how!
> >
> > It also has to be secure of course.
> >
> > Bruce L-C [MVP] wrote:
> >> Also in RS 2005 you can dynamically change the datasource based on the
> >> user.
> >>
> >> I haven't done this but it is one of the capabilities.
> >>
> >> Bruce Loehle-Conger
> >>
> >> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> >> news:BCD91F31-A84B-40D2-8B00-2FB36C53CD52@.microsoft.com...
> >> > Are the databases on two different SQL servers? How do the users run
> >> > the
> >> > report? From Report Manager, or embedded in a web page?
> >> >
> >> > If they are running the reports from Report Manager on different
> >> > servers
> >> > then you need to set up data sources (i.e. rds files) on each, with the
> >> > same
> >> > name, but pointing to the site-specific database.
> >> >
> >> > "KevinGreen24@.gmail.com" wrote:
> >> >
> >> >> Well we're working of a SaaS model, so every end user is running a
> >> >> thin
> >> >> client, so we're not installing anything on the end user's computer.
> >> >>
> >> >> magendo_man wrote:
> >> >> > On each computer have an rds file specifying the connection
> >> >> > properties
> >> >> > for
> >> >> > that computer.
> >> >> >
> >> >> > "KevinGreen24@.gmail.com" wrote:
> >> >> >
> >> >> > > Hello all,
> >> >> > >
> >> >> > > Here's my situation:
> >> >> > >
> >> >> > > I have a single RDL file that describes a certain report.
> >> >> > >
> >> >> > > I have different databases on different computers with the same
> >> >> > > tables
> >> >> > > but different data in each table.
> >> >> > >
> >> >> > > I need people to go to each computer, run the same RDL file, but
> >> >> > > have
> >> >> > > it show the data that is specific to their database.
> >> >> > >
> >> >> > > For instance, there is a single .rdl file which describes a
> >> >> > > personnel
> >> >> > > report, and i want people at site A to only see info about site
> >> >> > > A's
> >> >> > > personnel, and people at site B to only see info about site B's
> >> >> > > personnel.
> >> >> > >
> >> >> > > Is this possible? If so, is there any advice you can give me to
> >> >> > > help
> >> >> > > do
> >> >> > > this? Thank you.
> >> >> > >
> >> >> > > Kevin Green
> >> >> > > Software Engineer
> >> >> > > Pragmatech Software
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >

I want duplicates in my matrix!

I have a very simple report that allows users to extract data to an excel
spreadsheet and work with it. It consists of a single matrix with 3 row
groups, 1 column group and 1 "fact" cell. When it is run, either in preview
or deploy mode, the report is hiding duplicate values in the first column in
the row groups. (The left-most row group.) "Hide duplicates" is most
definitely *not* checked on the properties page.
I read somewhere of a developer experiencing this problem in Visual Studio
and having the problem disappear when the report is deployed. This is not
happening for me; the report hides duplicates all the time. Did I mention
that "Hide duplicates" is definitely *not* checked? [Tongue]
I've tried re-writing the report from scratch: no joy.
Any help appreciated, as the end-users don't like having to put the
duplicate values back in so that they can work with the data in Excel.
MikeWhen you say you want to show duplicates, what do you mean by a duplicate?
For the range you are interested in, are all rows (excluding fact cell) of
the same value?
"Mike Austin" wrote:
> I have a very simple report that allows users to extract data to an excel
> spreadsheet and work with it. It consists of a single matrix with 3 row
> groups, 1 column group and 1 "fact" cell. When it is run, either in preview
> or deploy mode, the report is hiding duplicate values in the first column in
> the row groups. (The left-most row group.) "Hide duplicates" is most
> definitely *not* checked on the properties page.
> I read somewhere of a developer experiencing this problem in Visual Studio
> and having the problem disappear when the report is deployed. This is not
> happening for me; the report hides duplicates all the time. Did I mention
> that "Hide duplicates" is definitely *not* checked? [Tongue]
> I've tried re-writing the report from scratch: no joy.
> Any help appreciated, as the end-users don't like having to put the
> duplicate values back in so that they can work with the data in Excel.
> Mike

I really need a debate! Type - Attributes vs Super Type - Sub Types

I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!

Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:

tbAutombbileCategories
CategoryID | Category
----------
1 | Car
2 | Truck
3 | Motorcycle

tbAutomobileAttributes
AttributeID | fkCategoryID | Attribute
--------------
1 | 1 (car) | Doors
2 | 2 (truck) | Cab
3 | 2 (truck) | Capacity

tbAutomobile
VIN | Category | Make | Model
------------
1 | 1 | Honda | Accord
2 | 2 | Ford | F150

tbAutomobileAttributeValues
fkVIN | fkAttributeID | Value
----------
1 | 1 | 2
2 | 1 | 0
2 | 2 | 1000

Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?

tbCategories
CategoryID | Category
--------
1 | Cars
2 | Trucks

tbAutomobile (Super Type)
VIN | fkCategoryID | Make | Model
------------
1 | 1 |Honda | Accord

tbCars
fkVIN | Doors |
------
1 | 2

tbTrucks
fkVIN | Cab | Capacity
--------
2 | 0 | 1000

Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.

Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...

Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.

E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?

tbTrucks
fkVIN | Cab | Capacity | fkCapID
------------
2 | Y | 1000 | 1

tbCaps
CapID | Vendor | Price | et...

Any thoughts at all? I thought this would have been a pretty damn hot topic!

Mike Bhttp://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever|||Originally posted by Brett Kaiser
http://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever
Your last link is invalid.

Anyway, why did you post all the articles on BCNF? Aren't both examples I have illustrated normalized? If they aren't what rules are broken?

I am a newbie, so any explanation would be appreciated!

Also, what method would you use Brett?

Mike B

I need to uninstall Instance2 on the server keeping 1 and 3

Here is the scenario.
Installed 3 instances of SQL Server 2005 on a single server (windows
2003 standard) say Instance1, instance2 and Instance3.
I need to uninstall Instance2 on the server keeping 1 and 3 .
Any tools to achieve this or any scripts to write ...
Karan Lobana
Installation and uninstallation is done at the instance level. So you should be able to uninstall
only instance 2, and then install it again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<karanlobana@.gmail.com> wrote in message
news:1181236891.209440.200070@.k79g2000hse.googlegr oups.com...
> Here is the scenario.
> Installed 3 instances of SQL Server 2005 on a single server (windows
> 2003 standard) say Instance1, instance2 and Instance3.
> I need to uninstall Instance2 on the server keeping 1 and 3 .
> Any tools to achieve this or any scripts to write ...
> Karan Lobana
>
|||On Jun 7, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Installation and uninstallation is done at the instance level. So you should be able to uninstall
> only instance 2, and then install it again.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <karanlob...@.gmail.com> wrote in message
> news:1181236891.209440.200070@.k79g2000hse.googlegr oups.com...
>
>
>
>
> - Show quoted text -
The tool is Add\Remove programs. Select SQL Server 2005 and you will
be prompted to remove only the instance(s) desired. In earlier
version, each instance is listed seperatley in Add\Remove programs.
Terry

I need to uninstall Instance2 on the server keeping 1 and 3

Here is the scenario.
Installed 3 instances of SQL Server 2005 on a single server (windows
2003 standard) say Instance1, instance2 and Instance3.
I need to uninstall Instance2 on the server keeping 1 and 3 .
Any tools to achieve this or any scripts to write ...
Karan LobanaInstallation and uninstallation is done at the instance level. So you should be able to uninstall
only instance 2, and then install it again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<karanlobana@.gmail.com> wrote in message
news:1181236891.209440.200070@.k79g2000hse.googlegroups.com...
> Here is the scenario.
> Installed 3 instances of SQL Server 2005 on a single server (windows
> 2003 standard) say Instance1, instance2 and Instance3.
> I need to uninstall Instance2 on the server keeping 1 and 3 .
> Any tools to achieve this or any scripts to write ...
> Karan Lobana
>|||On Jun 7, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Installation and uninstallation is done at the instance level. So you should be able to uninstall
> only instance 2, and then install it again.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <karanlob...@.gmail.com> wrote in message
> news:1181236891.209440.200070@.k79g2000hse.googlegroups.com...
>
> > Here is the scenario.
> > Installed 3 instances of SQL Server 2005 on a single server (windows
> > 2003 standard) say Instance1, instance2 and Instance3.
> > I need to uninstall Instance2 on the server keeping 1 and 3 .
> > Any tools to achieve this or any scripts to write ...
> > Karan Lobana- Hide quoted text -
> - Show quoted text -
The tool is Add\Remove programs. Select SQL Server 2005 and you will
be prompted to remove only the instance(s) desired. In earlier
version, each instance is listed seperatley in Add\Remove programs.
Terry

I need to uninstall Instance2 on the server keeping 1 and 3

Here is the scenario.
Installed 3 instances of SQL Server 2005 on a single server (windows
2003 standard) say Instance1, instance2 and Instance3.
I need to uninstall Instance2 on the server keeping 1 and 3 .
Any tools to achieve this or any scripts to write ...
Karan LobanaInstallation and uninstallation is done at the instance level. So you should
be able to uninstall
only instance 2, and then install it again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<karanlobana@.gmail.com> wrote in message
news:1181236891.209440.200070@.k79g2000hse.googlegroups.com...
> Here is the scenario.
> Installed 3 instances of SQL Server 2005 on a single server (windows
> 2003 standard) say Instance1, instance2 and Instance3.
> I need to uninstall Instance2 on the server keeping 1 and 3 .
> Any tools to achieve this or any scripts to write ...
> Karan Lobana
>|||On Jun 7, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Installation and uninstallation is done at the instance level. So you shou
ld be able to uninstall
> only instance 2, and then install it again.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <karanlob...@.gmail.com> wrote in message
> news:1181236891.209440.200070@.k79g2000hse.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
The tool is Add\Remove programs. Select SQL Server 2005 and you will
be prompted to remove only the instance(s) desired. In earlier
version, each instance is listed seperatley in Add\Remove programs.
Terry