Invalid Object Name

Can anyone help me out with an error that I get when
attempting to create a dataset that uses a stored
procedure? I wrote a moderately complex sp that utilizes
temporary tables. In fact, the result set returned is
that of a select statement against a temp table. However,
when I try to define a new dataset for a report that I am
writing, I get the following error message:
Could not generate a list of fields for the query
Check the query syntax or click refresh fields on the
query toolbar
Invalid object name '#Tmp'
Of course #Tmp is the name of the temporary table that I
am selecting from to return the result set.
Any help would be appreciated. I am using a fresh install
of reporting services without any service packs applied.
Thanks!
TimTry declaring a table variable instead.
"Tim" wrote:
> Can anyone help me out with an error that I get when
> attempting to create a dataset that uses a stored
> procedure? I wrote a moderately complex sp that utilizes
> temporary tables. In fact, the result set returned is
> that of a select statement against a temp table. However,
> when I try to define a new dataset for a report that I am
> writing, I get the following error message:
> Could not generate a list of fields for the query
> Check the query syntax or click refresh fields on the
> query toolbar
> Invalid object name '#Tmp'
> Of course #Tmp is the name of the temporary table that I
> am selecting from to return the result set.
> Any help would be appreciated. I am using a fresh install
> of reporting services without any service packs applied.
> Thanks!
> Tim
>|||Tim:
Your solution to the temp table is better than mine and I would like to learn more about the local variable of type Table. Would you PLEASE share it with me.
I don't understand what do you mean by local variable of type Table. May I PLEASE have the URL to this local variable.
Thanks!
Augusta
"Tim" wrote:
> Can anyone help me out with an error that I get when
> attempting to create a dataset that uses a stored
> procedure? I wrote a moderately complex sp that utilizes
> temporary tables. In fact, the result set returned is
> that of a select statement against a temp table. However,
> when I try to define a new dataset for a report that I am
> writing, I get the following error message:
> Could not generate a list of fields for the query
> Check the query syntax or click refresh fields on the
> query toolbar
> Invalid object name '#Tmp'
> Of course #Tmp is the name of the temporary table that I
> am selecting from to return the result set.
> Any help would be appreciated. I am using a fresh install
> of reporting services without any service packs applied.
> Thanks!
> Tim
>

Invalid Dataset Referenced Field?

here's my mdx code for my parameter (CrashVehicleVehicleType)

WITH MEMBER [Measures].[ParameterCaption] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Crash Vehicle].[Vehicle Type].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Crash Vehicle].[Vehicle Type].ALLMEMBERS ON ROWS FROM [SDMTest]

why do i keep getting this error if i have a parameter value and caption

[rsInvalidDataSetReferenceField] The report parameter ‘CrashVehicleVehicleType’ uses the field ‘ParameterValue’ in a data set reference, but the data set ‘CrashVehicleVehicleType’ does not contain that field.

Check the XML Code behind page of each report.

http://jhermiz.googlepages.com|||

this is the xml behind the report im working on.....It says the value and caption is in these datasets?! i just dont get it.

</DataSet>

<DataSet Name="FromDateCalendarDate">

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</CommandText>

<DataSourceName>NewCrashTest</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items /></Select><From>SDMTest</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

</DataSet>

<DataSet Name="ToDateCalendarDate">

<Query>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</CommandText>

<DataSourceName>NewCrashTest</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

<rd:MdxQuery><QueryDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items /></Select><From>SDMTest</From></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Calendar Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Calendar Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Calendar Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Date].ALLMEMBERS ON ROWS FROM [SDMTest]</Statement></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

<Fields>

Intersection of N sets

Imagine a table that enumerates membership of items to some set:
create table sets
(
setId int not null,
itemId int not null
);
some data:
set 1 = {1,3,5,7,9}
set 2 = {1,2,3,4,5}
set 3 = {4,5,6,7}
translated to this model:
setId, itemId
1,1
1,3
1,5
1,7
1,9
2,1
2,2
2,3
2,4
2,5
3,4
3,5
3,6
3,7
Consider another table containing an enumeration of sets to calculate the
intersection of:
create table setIntersection
(
setId int not null
);
If the setIntersection table contains the records {1,2,3}, I'd like to calcu
late
the intersection of the items contained in the sets 1, 2 and 3.
In the example above, this would be the {5}.
While this is trivial to do in a typical imperative fashion (looping inside
an SP), I'm wondering if it's possible to perform this operation in a single
query, perhaps using an CTE.Essentially, it would be a join on the the tables, each representing a set.
SELECT t1.itemid -- or t2.itemid or t3.itemid
FROM tbl t1,
tbl t2,
tbl t3
WHERE t1.itemid = t2.itemid
AND t2.itemid = t3.itemid
AND t1.setid = 1
AND t2.setid = 2
AND t3.setid = 3 ;
Anith|||Taras Tielkes (taras.tielkes@.gmail.com) writes:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
>...
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to
> calculate the intersection of the items contained in the sets 1, 2 and
> 3. In the example above, this would be the {5}.
SELECT itemId
FROM sets s
WHERE EXISTS (SELECT *
FROM setIntersection sI
WHERE sI.setId = s.setId)
GROUP BY itemId
HAVING COUNT(*) = (SELECT COUNT(*) FROM setIntersection)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ah, but I meant to ask the question in a generic way: "how can I writer a
query that will perform the required operation for an arbitrary number of
sets in the example model. Say 100 defined sets :-)

> Essentially, it would be a join on the the tables, each representing a
> set.
> SELECT t1.itemid -- or t2.itemid or t3.itemid
> FROM tbl t1,
> tbl t2,
> tbl t3
> WHERE t1.itemid = t2.itemid
> AND t2.itemid = t3.itemid
> AND t1.setid = 1
> AND t2.setid = 2
> AND t3.setid = 3 ;|||In that case, Erland's response should help. You may also want to search the
archives for "relational division" to find some related examples.
Anith|||Taras Tielkes wrote:
> Imagine a table that enumerates membership of items to some set:
> create table sets
> (
> setId int not null,
> itemId int not null
> );
> some data:
> set 1 = {1,3,5,7,9}
> set 2 = {1,2,3,4,5}
> set 3 = {4,5,6,7}
> translated to this model:
> setId, itemId
> 1,1
> 1,3
> 1,5
> 1,7
> 1,9
> 2,1
> 2,2
> 2,3
> 2,4
> 2,5
> 3,4
> 3,5
> 3,6
> 3,7
> Consider another table containing an enumeration of sets to calculate the
> intersection of:
> create table setIntersection
> (
> setId int not null
> );
> If the setIntersection table contains the records {1,2,3}, I'd like to cal
culate
> the intersection of the items contained in the sets 1, 2 and 3.
> In the example above, this would be the {5}.
> While this is trivial to do in a typical imperative fashion (looping insid
e
> an SP), I'm wondering if it's possible to perform this operation in a sing
le
> query, perhaps using an CTE.
It's relational division:
sets/setIntersection