Query error during runtime

I get Invalid object name 'bstr'. when I try to run this query

Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From
(SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,

(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
) As bstr

LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:
> I get Invalid object name 'bstr'. when I try to run this query
> Select distinct c0.oid, c1.Value, c2.Value, c3.Value
> From
> (SELECT oid FROM dbo.COREAttribute
> WHERE CLSID IN (
> '{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
>...
> '{C31DB61A-5221-42CF-9A73-FE76D5158647}'
> )) AS c0 ,
> (select oid, dispid, value
> FROM dbo.COREBSTRAttribute
> WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
> ) As bstr
> LEFT JOIN bstr AS c1
> ON (c0.oid = c1.oid)
> AND c1.dispid = 28
> LEFT JOIN bstr AS c2
> ON (c0.oid = c2.oid)
> AND c2.dispid = 112
> LEFT JOIN bstr AS c3
> ON (c0.oid = c3.oid)
> AND c3.dispid = 192

You cannot refer a virtual table in this way in a query. What you are
trying is a Common Table Expression, which is a new feature in SQL 2005
(culled from ANSI SQL). There you would write:

WITH bstr AS
(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}')
Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From (SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
...
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,
LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

In SQL 2000, you will have to paste in the query in all the three
LEFT JOIN. Or put the stuff into a temp table or table variable first,
so that the query is evaluated only once. In fact this is necessary
in SQL 2005, as the nice syntax only acts as a macro definition.

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

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

query at runtime

Hi all,
Is there way in reporting service programming to give the sql select query
for a report during runtime.
Thanks
rsuserYou can have your query be an expression. Be in the generic query designer
(two panes). The button to switch to this is to the right of the ...
Put in an expression.
="select somefield, someotherfield from mytable where startdate>= '" &
parameters!ParamName & "' order by " & parameters!AnotherParam
Note that you have to put your own single quotes where appropriate. What I
do is first have a report with just a textbox with the expression so I can
see it and see if I have created the query appropriately. Then I assign it
to a dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSUser" <RSUser@.discussions.microsoft.com> wrote in message
news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> Hi all,
> Is there way in reporting service programming to give the sql select query
> for a report during runtime.
> Thanks
> rsuser|||Just adding to Bruce's description:
Once you are using an expression-based command-text, you can no longer
execute the query in the report designer data view. Also you can no longer
refresh the fields list. That's why it is important to first finish the
report design based on a constant command text and use Bruce's suggested
approach of a textbox to look at the expression results - before you change
the command text to be expression-based.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> You can have your query be an expression. Be in the generic query designer
> (two panes). The button to switch to this is to the right of the ...
> Put in an expression.
> ="select somefield, someotherfield from mytable where startdate>= '" &
> parameters!ParamName & "' order by " & parameters!AnotherParam
> Note that you have to put your own single quotes where appropriate. What I
> do is first have a report with just a textbox with the expression so I can
> see it and see if I have created the query appropriately. Then I assign it
> to a dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
>> Hi all,
>> Is there way in reporting service programming to give the sql select
>> query
>> for a report during runtime.
>> Thanks
>> rsuser
>|||thanks!! I got it working. I have other issues. Please help me on these
1)Is there a way to supress the image when the report is
displayed in browser and then make them available for printing on paper
alone? is there a way to use any javascript or any other way to do this?
2)Say i have a dot net application which will display reports using
webservices and reports are using custom assembly which requires read
permission. Then while installing the dot net application in the
end users system, how the policy files will be configured. is there a way to
set up client's report server policy files through dot net code?
Thanks,
rsuser
"Robert Bruckner [MSFT]" wrote:
> Just adding to Bruce's description:
> Once you are using an expression-based command-text, you can no longer
> execute the query in the report designer data view. Also you can no longer
> refresh the fields list. That's why it is important to first finish the
> report design based on a constant command text and use Bruce's suggested
> approach of a textbox to look at the expression results - before you change
> the command text to be expression-based.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O%23r4y5%23rFHA.304@.TK2MSFTNGP11.phx.gbl...
> > You can have your query be an expression. Be in the generic query designer
> > (two panes). The button to switch to this is to the right of the ...
> >
> > Put in an expression.
> >
> > ="select somefield, someotherfield from mytable where startdate>= '" &
> > parameters!ParamName & "' order by " & parameters!AnotherParam
> >
> > Note that you have to put your own single quotes where appropriate. What I
> > do is first have a report with just a textbox with the expression so I can
> > see it and see if I have created the query appropriately. Then I assign it
> > to a dataset.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "RSUser" <RSUser@.discussions.microsoft.com> wrote in message
> > news:347143A5-6B67-4CE3-BFBD-2E90F8E53193@.microsoft.com...
> >> Hi all,
> >>
> >> Is there way in reporting service programming to give the sql select
> >> query
> >> for a report during runtime.
> >>
> >> Thanks
> >> rsuser
> >
> >
>
>