report does not accept null values for its parameters

Hi all
Im using a stored procedure with in params as datasource to a report.
These params are initialized to null by default in the procedure.
When executing this datasource in the report designer's data tab
every thing works fine - i can live these parameters with nulls (or set
other values) and the procedures return appropriate results.
Then in the report parameters tab i allow these params to get null values,
with no default value.
But when executing the report in the 'preview' mode of visual studio i must
enter values for all params or else the report is not displayed at all (not
even with empty result set..).
No error massage is displayed - just the default blank background..
Thanks for your attention
ReaThe report will not execute unless you provide Default values in the Report
Parameters dialog. Without setting a default value on each and every
parameter, RS has no way of knowing what to use. It is unaware that your SP
has default values assigned.
hth
fmi see www.sqlreportingservices.net
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> Hi all
> Im using a stored procedure with in params as datasource to a report.
> These params are initialized to null by default in the procedure.
> When executing this datasource in the report designer's data tab
> every thing works fine - i can live these parameters with nulls (or set
> other values) and the procedures return appropriate results.
> Then in the report parameters tab i allow these params to get null values,
> with no default value.
> But when executing the report in the 'preview' mode of visual studio i
must
> enter values for all params or else the report is not displayed at all
(not
> even with empty result set..).
> No error massage is displayed - just the default blank background..
> Thanks for your attention
> Rea
>|||ok then how do i set null values as default values for report's parameters'
i tried : null , =null, =System.DBnull - all were rejected by compilation.
Thanks again
Rea
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:uAKFBp3YEHA.1764@.TK2MSFTNGP10.phx.gbl...
> The report will not execute unless you provide Default values in the
Report
> Parameters dialog. Without setting a default value on each and every
> parameter, RS has no way of knowing what to use. It is unaware that your
SP
> has default values assigned.
> hth
> fmi see www.sqlreportingservices.net
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > Hi all
> > Im using a stored procedure with in params as datasource to a report.
> > These params are initialized to null by default in the procedure.
> > When executing this datasource in the report designer's data tab
> > every thing works fine - i can live these parameters with nulls (or set
> > other values) and the procedures return appropriate results.
> > Then in the report parameters tab i allow these params to get null
values,
> > with no default value.
> > But when executing the report in the 'preview' mode of visual studio i
> must
> > enter values for all params or else the report is not displayed at all
> (not
> > even with empty result set..).
> > No error massage is displayed - just the default blank background..
> > Thanks for your attention
> > Rea
> >
> >
>|||=Nothing
(according to Peter)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uip9OnAZEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ok then how do i set null values as default values for report's
parameters'
> i tried : null , =null, =System.DBnull - all were rejected by compilation.
> Thanks again
> Rea
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:uAKFBp3YEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > The report will not execute unless you provide Default values in the
> Report
> > Parameters dialog. Without setting a default value on each and every
> > parameter, RS has no way of knowing what to use. It is unaware that your
> SP
> > has default values assigned.
> >
> > hth
> > fmi see www.sqlreportingservices.net
> >
> >
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > __________________________________
> >
> > "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> > news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > > Hi all
> > > Im using a stored procedure with in params as datasource to a report.
> > > These params are initialized to null by default in the procedure.
> > > When executing this datasource in the report designer's data tab
> > > every thing works fine - i can live these parameters with nulls (or
set
> > > other values) and the procedures return appropriate results.
> > > Then in the report parameters tab i allow these params to get null
> values,
> > > with no default value.
> > > But when executing the report in the 'preview' mode of visual studio i
> > must
> > > enter values for all params or else the report is not displayed at all
> > (not
> > > even with empty result set..).
> > > No error massage is displayed - just the default blank background..
> > > Thanks for your attention
> > > Rea
> > >
> > >
> >
> >
>|||Thanks i found a way:
Null values should be added to the parameter's result set/data source.
Then you allow null values for it and set for default value - none.
I hope =Nothing does not work because i spent some 2 hours figuring in
out...:))
Rea
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:ew$ZIAJZEHA.2016@.TK2MSFTNGP09.phx.gbl...
> =Nothing
> (according to Peter)
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:uip9OnAZEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > ok then how do i set null values as default values for report's
> parameters'
> > i tried : null , =null, =System.DBnull - all were rejected by
compilation.
> > Thanks again
> > Rea
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> > news:uAKFBp3YEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > The report will not execute unless you provide Default values in the
> > Report
> > > Parameters dialog. Without setting a default value on each and every
> > > parameter, RS has no way of knowing what to use. It is unaware that
your
> > SP
> > > has default values assigned.
> > >
> > > hth
> > > fmi see www.sqlreportingservices.net
> > >
> > >
> > > --
> > > ____________________________________
> > > William (Bill) Vaughn
> > > Author, Mentor, Consultant
> > > Microsoft MVP
> > > www.betav.com
> > > Please reply only to the newsgroup so that others can benefit.
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > __________________________________
> > >
> > > "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> > > news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > > > Hi all
> > > > Im using a stored procedure with in params as datasource to a
report.
> > > > These params are initialized to null by default in the procedure.
> > > > When executing this datasource in the report designer's data tab
> > > > every thing works fine - i can live these parameters with nulls (or
> set
> > > > other values) and the procedures return appropriate results.
> > > > Then in the report parameters tab i allow these params to get null
> > values,
> > > > with no default value.
> > > > But when executing the report in the 'preview' mode of visual studio
i
> > > must
> > > > enter values for all params or else the report is not displayed at
all
> > > (not
> > > > even with empty result set..).
> > > > No error massage is displayed - just the default blank background..
> > > > Thanks for your attention
> > > > Rea
> > > >
> > > >
> > >
> > >
> >
> >
>

Report connected with Store Procedure in asp.net

I have report which is connected with a parameterised StoreProcedure.The rpeort is called from asp.net page.

In asp.net page i have a text box where user give the date parameter, and have a button named "Download".After giving the data parameter in text box if user click on download button the report would be downloaded as per data parameter to the user selection path.

Can any one help how it should be possible.You can use SQLQuery property of the CR and write query based on the user selection and pass itsql

Report calling a Stored procedure taking too much time while

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.

Please help--Iam stumped.

There are no infinite loops in the report. I checked.

I suggest you use the SQL Server Profiler to see what queries are sent to the database. Also, take a look at the ExecutionLog table in the ReportServer database to find how much time is spent in data retrieval.|||

All the time is spent in data retrievel while the same SP in management studio takes far less time ? I can't understand whats happening

|||

Also if i cancel the running jobs from Report Manager, they automatically restart after sometime? Any clues?

|||

Perhaps, debugging the stored procedure would help to find what's happening

|||

Sounds like you're running into issues with query plan; it is not uncommon to see this difference when running in Mgmt Studio vs. running in reports.

I recommend reading up on query performance:

http://msdn2.microsoft.com/en-us/library/ms187032.aspx

There are a couple of common problems people have with queries in stored procs (especially). These can often be fixed using the WITH RECOMPILE directive. This is especially true if the size of data your return from a test query is significantly different than that in the production query.

Secondly, if you reference other database objects in your query, using fully qualified names (dbo.storedProcName), you can also get some benefits.

Hope this helps,

-Lukasz

Report calling a Stored procedure taking too much time while

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.

Please help--Iam stumped.

There are no infinite loops in the report. I checked.

I suggest you use the SQL Server Profiler to see what queries are sent to the database. Also, take a look at the ExecutionLog table in the ReportServer database to find how much time is spent in data retrieval.|||

All the time is spent in data retrievel while the same SP in management studio takes far less time ? I can't understand whats happening

|||

Also if i cancel the running jobs from Report Manager, they automatically restart after sometime? Any clues?

|||

Perhaps, debugging the stored procedure would help to find what's happening

|||

Sounds like you're running into issues with query plan; it is not uncommon to see this difference when running in Mgmt Studio vs. running in reports.

I recommend reading up on query performance:

http://msdn2.microsoft.com/en-us/library/ms187032.aspx

There are a couple of common problems people have with queries in stored procs (especially). These can often be fixed using the WITH RECOMPILE directive. This is especially true if the size of data your return from a test query is significantly different than that in the production query.

Secondly, if you reference other database objects in your query, using fully qualified names (dbo.storedProcName), you can also get some benefits.

Hope this helps,

-Lukasz

Report calling a Stored procedure taking too much time while

I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.

Please help--Iam stumped.

There are no infinite loops in the report. I checked.

I suggest you use the SQL Server Profiler to see what queries are sent to the database. Also, take a look at the ExecutionLog table in the ReportServer database to find how much time is spent in data retrieval.|||

All the time is spent in data retrievel while the same SP in management studio takes far less time ? I can't understand whats happening

|||

Also if i cancel the running jobs from Report Manager, they automatically restart after sometime? Any clues?

|||

Perhaps, debugging the stored procedure would help to find what's happening

|||

Sounds like you're running into issues with query plan; it is not uncommon to see this difference when running in Mgmt Studio vs. running in reports.

I recommend reading up on query performance:

http://msdn2.microsoft.com/en-us/library/ms187032.aspx

There are a couple of common problems people have with queries in stored procs (especially). These can often be fixed using the WITH RECOMPILE directive. This is especially true if the size of data your return from a test query is significantly different than that in the production query.

Secondly, if you reference other database objects in your query, using fully qualified names (dbo.storedProcName), you can also get some benefits.

Hope this helps,

-Lukasz

sql

report builder and stored procedure

I am trying to build a report model. I have a stored procedure that return a
result set. I created a Named Query to exec the stored procedure. But when
I clicked OK, it gave me this error: Please help.
TITLE: Microsoft Visual Studio
--
Cannot create the named query using the specified query definition.
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
--
ADDITIONAL INFORMATION:
Incorrect syntax near the keyword 'EXEC'.
The following statement executed when creating named query:
SELECT [EBB].*
FROM
(
EXEC EDJUNK
)
AS [EBB] (Microsoft.AnalysisServices.Controls)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
--
Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
--
BUTTONS:
OK
--dear Ed when u use exec u can't use this format: SELECT * FROM (EXEC SP)
cause exec doesn't return resultset(look in BOL).
if u want 2 use SP just write the name of the sp and it work...
"Ed" wrote:
> I am trying to build a report model. I have a stored procedure that return a
> result set. I created a Named Query to exec the stored procedure. But when
> I clicked OK, it gave me this error: Please help.
> TITLE: Microsoft Visual Studio
> --
> Cannot create the named query using the specified query definition.
>
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near the keyword 'EXEC'.
> The following statement executed when creating named query:
> SELECT [EBB].*
> FROM
> (
> EXEC EDJUNK
> )
> AS [EBB] (Microsoft.AnalysisServices.Controls)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> --
> Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||Thanks...I will try
"ש×?×?×?" wrote:
> dear Ed when u use exec u can't use this format: SELECT * FROM (EXEC SP)
> cause exec doesn't return resultset(look in BOL).
> if u want 2 use SP just write the name of the sp and it work...
> "Ed" wrote:
> > I am trying to build a report model. I have a stored procedure that return a
> > result set. I created a Named Query to exec the stored procedure. But when
> > I clicked OK, it gave me this error: Please help.
> >
> > TITLE: Microsoft Visual Studio
> > --
> >
> > Cannot create the named query using the specified query definition.
> >
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Incorrect syntax near the keyword 'EXEC'.
> > The following statement executed when creating named query:
> >
> > SELECT [EBB].*
> > FROM
> > (
> > EXEC EDJUNK
> > )
> > AS [EBB] (Microsoft.AnalysisServices.Controls)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> >
> > --
> >
> > Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >|||Aloha, I tried just put in the sp name and when I clicked ok, it gave me this
error:
TITLE: Microsoft Visual Studio
--
Cannot create the named query using the specified query definition.
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
--
ADDITIONAL INFORMATION:
Line 6: Incorrect syntax near ')'.
The following statement executed when creating named query:
SELECT [Retail Items Qty By Sites].*
FROM
(
RETAIL_ITEM_QTY
)
AS [Retail Items Qty By Sites] (Microsoft.AnalysisServices.Controls)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
--
Line 6: Incorrect syntax near ')'. (Microsoft SQL Server, Error: 170)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=170&LinkId=20476
--
BUTTONS:
OK
--
"ש×?×?×?" wrote:
> dear Ed when u use exec u can't use this format: SELECT * FROM (EXEC SP)
> cause exec doesn't return resultset(look in BOL).
> if u want 2 use SP just write the name of the sp and it work...
> "Ed" wrote:
> > I am trying to build a report model. I have a stored procedure that return a
> > result set. I created a Named Query to exec the stored procedure. But when
> > I clicked OK, it gave me this error: Please help.
> >
> > TITLE: Microsoft Visual Studio
> > --
> >
> > Cannot create the named query using the specified query definition.
> >
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Incorrect syntax near the keyword 'EXEC'.
> > The following statement executed when creating named query:
> >
> > SELECT [EBB].*
> > FROM
> > (
> > EXEC EDJUNK
> > )
> > AS [EBB] (Microsoft.AnalysisServices.Controls)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> >
> > --
> >
> > Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >|||hi do u have already this sp?
is your datasource correct?
"Ed" wrote:
> Aloha, I tried just put in the sp name and when I clicked ok, it gave me this
> error:
> TITLE: Microsoft Visual Studio
> --
> Cannot create the named query using the specified query definition.
>
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> Line 6: Incorrect syntax near ')'.
> The following statement executed when creating named query:
> SELECT [Retail Items Qty By Sites].*
> FROM
> (
> RETAIL_ITEM_QTY
> )
> AS [Retail Items Qty By Sites] (Microsoft.AnalysisServices.Controls)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> --
> Line 6: Incorrect syntax near ')'. (Microsoft SQL Server, Error: 170)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=170&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> "ש×?×?×?" wrote:
> > dear Ed when u use exec u can't use this format: SELECT * FROM (EXEC SP)
> > cause exec doesn't return resultset(look in BOL).
> > if u want 2 use SP just write the name of the sp and it work...
> >
> > "Ed" wrote:
> >
> > > I am trying to build a report model. I have a stored procedure that return a
> > > result set. I created a Named Query to exec the stored procedure. But when
> > > I clicked OK, it gave me this error: Please help.
> > >
> > > TITLE: Microsoft Visual Studio
> > > --
> > >
> > > Cannot create the named query using the specified query definition.
> > >
> > >
> > > For help, click:
> > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> > >
> > > --
> > > ADDITIONAL INFORMATION:
> > >
> > > Incorrect syntax near the keyword 'EXEC'.
> > > The following statement executed when creating named query:
> > >
> > > SELECT [EBB].*
> > > FROM
> > > (
> > > EXEC EDJUNK
> > > )
> > > AS [EBB] (Microsoft.AnalysisServices.Controls)
> > >
> > > For help, click:
> > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> > >
> > > --
> > >
> > > Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
> > >
> > > For help, click:
> > > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
> > >
> > > --
> > > BUTTONS:
> > >
> > > OK
> > > --
> > >|||Yes...it even works when I clicked the run button in where I built the name
query.
"ש×?×?×?" wrote:
> hi do u have already this sp?
> is your datasource correct?
> "Ed" wrote:
> > Aloha, I tried just put in the sp name and when I clicked ok, it gave me this
> > error:
> >
> > TITLE: Microsoft Visual Studio
> > --
> >
> > Cannot create the named query using the specified query definition.
> >
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Line 6: Incorrect syntax near ')'.
> > The following statement executed when creating named query:
> >
> > SELECT [Retail Items Qty By Sites].*
> > FROM
> > (
> > RETAIL_ITEM_QTY
> > )
> > AS [Retail Items Qty By Sites] (Microsoft.AnalysisServices.Controls)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> >
> > --
> >
> > Line 6: Incorrect syntax near ')'. (Microsoft SQL Server, Error: 170)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=170&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
> >
> > "ש×?×?×?" wrote:
> >
> > > dear Ed when u use exec u can't use this format: SELECT * FROM (EXEC SP)
> > > cause exec doesn't return resultset(look in BOL).
> > > if u want 2 use SP just write the name of the sp and it work...
> > >
> > > "Ed" wrote:
> > >
> > > > I am trying to build a report model. I have a stored procedure that return a
> > > > result set. I created a Named Query to exec the stored procedure. But when
> > > > I clicked OK, it gave me this error: Please help.
> > > >
> > > > TITLE: Microsoft Visual Studio
> > > > --
> > > >
> > > > Cannot create the named query using the specified query definition.
> > > >
> > > >
> > > > For help, click:
> > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> > > >
> > > > --
> > > > ADDITIONAL INFORMATION:
> > > >
> > > > Incorrect syntax near the keyword 'EXEC'.
> > > > The following statement executed when creating named query:
> > > >
> > > > SELECT [EBB].*
> > > > FROM
> > > > (
> > > > EXEC EDJUNK
> > > > )
> > > > AS [EBB] (Microsoft.AnalysisServices.Controls)
> > > >
> > > > For help, click:
> > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> > > >
> > > > --
> > > >
> > > > Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
> > > >
> > > > For help, click:
> > > > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
> > > >
> > > > --
> > > > BUTTONS:
> > > >
> > > > OK
> > > > --
> > > >|||Hi Ed
Make sure your command type is set to Stored Procedure and not Text.
"Ed" wrote:
> Yes...it even works when I clicked the run button in where I built the name
> query.
> "ש×?×?×?" wrote:
> > hi do u have already this sp?
> > is your datasource correct?
> >
> > "Ed" wrote:
> >
> > > Aloha, I tried just put in the sp name and when I clicked ok, it gave me this
> > > error:
> > >
> > > TITLE: Microsoft Visual Studio
> > > --
> > >
> > > Cannot create the named query using the specified query definition.
> > >
> > >
> > > For help, click:
> > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> > >
> > > --
> > > ADDITIONAL INFORMATION:
> > >
> > > Line 6: Incorrect syntax near ')'.
> > > The following statement executed when creating named query:
> > >
> > > SELECT [Retail Items Qty By Sites].*
> > > FROM
> > > (
> > > RETAIL_ITEM_QTY
> > > )
> > > AS [Retail Items Qty By Sites] (Microsoft.AnalysisServices.Controls)
> > >
> > > For help, click:
> > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> > >
> > > --
> > >
> > > Line 6: Incorrect syntax near ')'. (Microsoft SQL Server, Error: 170)
> > >
> > > For help, click:
> > > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=170&LinkId=20476
> > >
> > > --
> > > BUTTONS:
> > >
> > > OK
> > > --
> > >
> > >
> > > "ש×?×?×?" wrote:
> > >
> > > > dear Ed when u use exec u can't use this format: SELECT * FROM (EXEC SP)
> > > > cause exec doesn't return resultset(look in BOL).
> > > > if u want 2 use SP just write the name of the sp and it work...
> > > >
> > > > "Ed" wrote:
> > > >
> > > > > I am trying to build a report model. I have a stored procedure that return a
> > > > > result set. I created a Named Query to exec the stored procedure. But when
> > > > > I clicked OK, it gave me this error: Please help.
> > > > >
> > > > > TITLE: Microsoft Visual Studio
> > > > > --
> > > > >
> > > > > Cannot create the named query using the specified query definition.
> > > > >
> > > > >
> > > > > For help, click:
> > > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> > > > >
> > > > > --
> > > > > ADDITIONAL INFORMATION:
> > > > >
> > > > > Incorrect syntax near the keyword 'EXEC'.
> > > > > The following statement executed when creating named query:
> > > > >
> > > > > SELECT [EBB].*
> > > > > FROM
> > > > > (
> > > > > EXEC EDJUNK
> > > > > )
> > > > > AS [EBB] (Microsoft.AnalysisServices.Controls)
> > > > >
> > > > > For help, click:
> > > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> > > > >
> > > > > --
> > > > >
> > > > > Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
> > > > >
> > > > > For help, click:
> > > > > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
> > > > >
> > > > > --
> > > > > BUTTONS:
> > > > >
> > > > > OK
> > > > > --
> > > > >|||I didn't see a place I can specify sp or text when I created the named query.
"Shawn Kralj" wrote:
> Hi Ed
> Make sure your command type is set to Stored Procedure and not Text.
> "Ed" wrote:
> > Yes...it even works when I clicked the run button in where I built the name
> > query.
> >
> > "ש×?×?×?" wrote:
> >
> > > hi do u have already this sp?
> > > is your datasource correct?
> > >
> > > "Ed" wrote:
> > >
> > > > Aloha, I tried just put in the sp name and when I clicked ok, it gave me this
> > > > error:
> > > >
> > > > TITLE: Microsoft Visual Studio
> > > > --
> > > >
> > > > Cannot create the named query using the specified query definition.
> > > >
> > > >
> > > > For help, click:
> > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> > > >
> > > > --
> > > > ADDITIONAL INFORMATION:
> > > >
> > > > Line 6: Incorrect syntax near ')'.
> > > > The following statement executed when creating named query:
> > > >
> > > > SELECT [Retail Items Qty By Sites].*
> > > > FROM
> > > > (
> > > > RETAIL_ITEM_QTY
> > > > )
> > > > AS [Retail Items Qty By Sites] (Microsoft.AnalysisServices.Controls)
> > > >
> > > > For help, click:
> > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> > > >
> > > > --
> > > >
> > > > Line 6: Incorrect syntax near ')'. (Microsoft SQL Server, Error: 170)
> > > >
> > > > For help, click:
> > > > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=170&LinkId=20476
> > > >
> > > > --
> > > > BUTTONS:
> > > >
> > > > OK
> > > > --
> > > >
> > > >
> > > > "ש×?×?×?" wrote:
> > > >
> > > > > dear Ed when u use exec u can't use this format: SELECT * FROM (EXEC SP)
> > > > > cause exec doesn't return resultset(look in BOL).
> > > > > if u want 2 use SP just write the name of the sp and it work...
> > > > >
> > > > > "Ed" wrote:
> > > > >
> > > > > > I am trying to build a report model. I have a stored procedure that return a
> > > > > > result set. I created a Named Query to exec the stored procedure. But when
> > > > > > I clicked OK, it gave me this error: Please help.
> > > > > >
> > > > > > TITLE: Microsoft Visual Studio
> > > > > > --
> > > > > >
> > > > > > Cannot create the named query using the specified query definition.
> > > > > >
> > > > > >
> > > > > > For help, click:
> > > > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476
> > > > > >
> > > > > > --
> > > > > > ADDITIONAL INFORMATION:
> > > > > >
> > > > > > Incorrect syntax near the keyword 'EXEC'.
> > > > > > The following statement executed when creating named query:
> > > > > >
> > > > > > SELECT [EBB].*
> > > > > > FROM
> > > > > > (
> > > > > > EXEC EDJUNK
> > > > > > )
> > > > > > AS [EBB] (Microsoft.AnalysisServices.Controls)
> > > > > >
> > > > > > For help, click:
> > > > > > http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Server, Error: 156)
> > > > > >
> > > > > > For help, click:
> > > > > > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=156&LinkId=20476
> > > > > >
> > > > > > --
> > > > > > BUTTONS:
> > > > > >
> > > > > > OK
> > > > > > --
> > > > > >

Report based on temporary table

Hi
I need to create a report based on temporary table in oracle, table does not exist until stored procedure is executed. i am outputing a ref cursor but it shows no fields when i am creating the report.
help needed.
Regards
S RaheemEnter the correct values for which stored procedure will take|||hi am doing something like this

PROCEDURE Missing_Document(d IN OUT docs)
IS
BEGIN
EXECUTE IMMEDIATE 'create global temporary table testing(name varchar2(30))';
EXECUTE IMMEDIATE 'insert into testing values(''dddd'')';
EXECUTE IMMEDIATE 'insert into testing values(''eeee'')';
EXECUTE IMMEDIATE 'insert into testing values(''fffffff'')';
OPEN d FOR 'select * from testing';
END Missing_Document;

docs is a ref cursor|||hi am doing something like this

PROCEDURE Missing_Document(d IN OUT docs)
IS
BEGIN
EXECUTE IMMEDIATE 'create global temporary table testing(name varchar2(30))';
EXECUTE IMMEDIATE 'insert into testing values(''dddd'')';
EXECUTE IMMEDIATE 'insert into testing values(''eeee'')';
EXECUTE IMMEDIATE 'insert into testing values(''fffffff'')';
OPEN d FOR 'select * from testing';
END Missing_Document;

docs is a ref cursor

I got it working now thanks all, but i got a new error in oracle temp tables..

Report based on Stored Procedure

Hi,
I have a report based on a stored procedure.
The stored procedure does some calculations and finally returns a query
based on a temporary table:
Select * from #Tmp
The problem is Reporting Services doesn't see the results based on a
#temporary table (no error, no rows!). If I change the stored procedure to
return an actual table in database, reporting services can see the results.
Ho can I have a report based on a stored procedure that returns a query
based on a temporary table?
Any help would be appreciated,
AlanAre you using the report designer and the data tab? Does the stored
procedure execute and return data from the data tab? If so, sometimes
executing the stored procedure does not fill the field list. Try
clicking on
the refresh fields button (look to the right of the ... , it looks like
the
fresh button for IE)|||You know, I have a similar problem. We have a stored procedure which returns
what is essentially a "data dictionary" with extended properties in a
temporary table. If I click the ! in the data tab, I see the full results of
the stored procedure. If I go to the preview tab, RS only sees the first
table and the first column of that table. I am jumping through hoops here
trying to get RS to see everything from this stored procedure.
I did see a note in a RS book somewhere that says RS can't see anything
beyond one record in a multi-record returned set. If that is so, though, why
can I see everything on the data tab? My next attempts have centered around
trying to create cascading parameters, but though the first stored procedure
returns me a drop down table list, the next stored procedure which requires a
tablename for input and outputs a column list for that table keeps saying
there are no fields for me to choose from to place on the report. Not to
mention that I can't figure out how to get the table name from the first
dataset to the second dataset for input.
ARGH! Can anyone give me some ideas or point me in a better direction than
these lousy RS Online help files? They're practically worthless! They don't
even have a searchable Index tab like BOL has.
Thanks in advance,
Catadmin
"A.M" wrote:
> Hi,
>
> I have a report based on a stored procedure.
> The stored procedure does some calculations and finally returns a query
> based on a temporary table:
>
> Select * from #Tmp
>
> The problem is Reporting Services doesn't see the results based on a
> #temporary table (no error, no rows!). If I change the stored procedure to
> return an actual table in database, reporting services can see the results.
>
> Ho can I have a report based on a stored procedure that returns a query
> based on a temporary table?
>
> Any help would be appreciated,
> Alan
>
>|||RS can handle one resultset. When you click on the !, the resultset you see
is what you have to work with. On the left should be a field list, if the
fields are not showing there that is the first thing you need to do (try the
refresh fields button, to the right of the ...). The dataset needs to be
associated with something. Drag a table onto the form and then drag fields
over to the table columns.
I think you need to back up and just try some simple queries and make sure
you know how to create a report. SP complicate things and you are try to
simultaneously learn too many things at once.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:5058D8C3-F2B9-4964-9D64-96849FEFC3DE@.microsoft.com...
> You know, I have a similar problem. We have a stored procedure which
returns
> what is essentially a "data dictionary" with extended properties in a
> temporary table. If I click the ! in the data tab, I see the full results
of
> the stored procedure. If I go to the preview tab, RS only sees the first
> table and the first column of that table. I am jumping through hoops here
> trying to get RS to see everything from this stored procedure.
> I did see a note in a RS book somewhere that says RS can't see anything
> beyond one record in a multi-record returned set. If that is so, though,
why
> can I see everything on the data tab? My next attempts have centered
around
> trying to create cascading parameters, but though the first stored
procedure
> returns me a drop down table list, the next stored procedure which
requires a
> tablename for input and outputs a column list for that table keeps saying
> there are no fields for me to choose from to place on the report. Not to
> mention that I can't figure out how to get the table name from the first
> dataset to the second dataset for input.
> ARGH! Can anyone give me some ideas or point me in a better direction
than
> these lousy RS Online help files? They're practically worthless! They
don't
> even have a searchable Index tab like BOL has.
> Thanks in advance,
> Catadmin
> "A.M" wrote:
> > Hi,
> >
> >
> >
> > I have a report based on a stored procedure.
> >
> > The stored procedure does some calculations and finally returns a query
> > based on a temporary table:
> >
> >
> >
> > Select * from #Tmp
> >
> >
> >
> > The problem is Reporting Services doesn't see the results based on a
> > #temporary table (no error, no rows!). If I change the stored procedure
to
> > return an actual table in database, reporting services can see the
results.
> >
> >
> >
> > Ho can I have a report based on a stored procedure that returns a query
> > based on a temporary table?
> >
> >
> >
> > Any help would be appreciated,
> >
> > Alan
> >
> >
> >|||Bruce,
Thank you for your response. You are correct, I am trying to learn the
whole thing at once. My problem is that I went from one job that used
Crystal to another job that doesn't, they only use RS, and I'm expected to
finish this project that the previous DBA left unfinished. Given the time
limit I'm on, I'm not sure they're going to let me take my time and learn it.
Also, I can do simple queries. I just did two of them today. I think my
problem is in the grouping, if that makes sense. In Crystal, you could
create groups to do for a report what a cursor does for SQL, loop back and
catch the rest of the data. I've seen how to do so using the Report Wizard,
but, as I posted in another post just a little while ago, I can't figure out
how to do groups from a blank report screen. On top of that, I'm learning
how to use the fn_listextendedproperty function to pull the table and column
descrips, and my day has been a serious exercise in frustration. I think I'll
go try to drown myself in the gigantic fountain out front of the office. @.=P
If I can just figure out how to pull the fn_listextendedproperty function
into the report with the other fields without having to use the stored
procedures, AND/OR how to group on a blank report, I might have a work around
that should keep the boss happy for a while so I can go and learn things the
proper way.
Ah, well. Such is the life... @.=) Thanks, again.
Catadmin
"Bruce L-C [MVP]" wrote:
> RS can handle one resultset. When you click on the !, the resultset you see
> is what you have to work with. On the left should be a field list, if the
> fields are not showing there that is the first thing you need to do (try the
> refresh fields button, to the right of the ...). The dataset needs to be
> associated with something. Drag a table onto the form and then drag fields
> over to the table columns.
> I think you need to back up and just try some simple queries and make sure
> you know how to create a report. SP complicate things and you are try to
> simultaneously learn too many things at once.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
> news:5058D8C3-F2B9-4964-9D64-96849FEFC3DE@.microsoft.com...
> > You know, I have a similar problem. We have a stored procedure which
> returns
> > what is essentially a "data dictionary" with extended properties in a
> > temporary table. If I click the ! in the data tab, I see the full results
> of
> > the stored procedure. If I go to the preview tab, RS only sees the first
> > table and the first column of that table. I am jumping through hoops here
> > trying to get RS to see everything from this stored procedure.
> >
> > I did see a note in a RS book somewhere that says RS can't see anything
> > beyond one record in a multi-record returned set. If that is so, though,
> why
> > can I see everything on the data tab? My next attempts have centered
> around
> > trying to create cascading parameters, but though the first stored
> procedure
> > returns me a drop down table list, the next stored procedure which
> requires a
> > tablename for input and outputs a column list for that table keeps saying
> > there are no fields for me to choose from to place on the report. Not to
> > mention that I can't figure out how to get the table name from the first
> > dataset to the second dataset for input.
> >
> > ARGH! Can anyone give me some ideas or point me in a better direction
> than
> > these lousy RS Online help files? They're practically worthless! They
> don't
> > even have a searchable Index tab like BOL has.
> >
> > Thanks in advance,
> >
> > Catadmin
> >
> > "A.M" wrote:
> >
> > > Hi,
> > >
> > >
> > >
> > > I have a report based on a stored procedure.
> > >
> > > The stored procedure does some calculations and finally returns a query
> > > based on a temporary table:
> > >
> > >
> > >
> > > Select * from #Tmp
> > >
> > >
> > >
> > > The problem is Reporting Services doesn't see the results based on a
> > > #temporary table (no error, no rows!). If I change the stored procedure
> to
> > > return an actual table in database, reporting services can see the
> results.
> > >
> > >
> > >
> > > Ho can I have a report based on a stored procedure that returns a query
> > > based on a temporary table?
> > >
> > >
> > >
> > > Any help would be appreciated,
> > >
> > > Alan
> > >
> > >
> > >
>
>|||I have some udf's but I have only used them from within my sp. I'll try
using one from RS query window and let you know how it goes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:EE98488F-5D23-4BA6-9239-AB950052B508@.microsoft.com...
> Bruce,
> Thank you for your response. You are correct, I am trying to learn the
> whole thing at once. My problem is that I went from one job that used
> Crystal to another job that doesn't, they only use RS, and I'm expected to
> finish this project that the previous DBA left unfinished. Given the time
> limit I'm on, I'm not sure they're going to let me take my time and learn
> it.
> Also, I can do simple queries. I just did two of them today. I think my
> problem is in the grouping, if that makes sense. In Crystal, you could
> create groups to do for a report what a cursor does for SQL, loop back and
> catch the rest of the data. I've seen how to do so using the Report
> Wizard,
> but, as I posted in another post just a little while ago, I can't figure
> out
> how to do groups from a blank report screen. On top of that, I'm learning
> how to use the fn_listextendedproperty function to pull the table and
> column
> descrips, and my day has been a serious exercise in frustration. I think
> I'll
> go try to drown myself in the gigantic fountain out front of the office.
> @.=P
> If I can just figure out how to pull the fn_listextendedproperty function
> into the report with the other fields without having to use the stored
> procedures, AND/OR how to group on a blank report, I might have a work
> around
> that should keep the boss happy for a while so I can go and learn things
> the
> proper way.
> Ah, well. Such is the life... @.=) Thanks, again.
> Catadmin
> "Bruce L-C [MVP]" wrote:
>> RS can handle one resultset. When you click on the !, the resultset you
>> see
>> is what you have to work with. On the left should be a field list, if the
>> fields are not showing there that is the first thing you need to do (try
>> the
>> refresh fields button, to the right of the ...). The dataset needs to be
>> associated with something. Drag a table onto the form and then drag
>> fields
>> over to the table columns.
>> I think you need to back up and just try some simple queries and make
>> sure
>> you know how to create a report. SP complicate things and you are try to
>> simultaneously learn too many things at once.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
>> news:5058D8C3-F2B9-4964-9D64-96849FEFC3DE@.microsoft.com...
>> > You know, I have a similar problem. We have a stored procedure which
>> returns
>> > what is essentially a "data dictionary" with extended properties in a
>> > temporary table. If I click the ! in the data tab, I see the full
>> > results
>> of
>> > the stored procedure. If I go to the preview tab, RS only sees the
>> > first
>> > table and the first column of that table. I am jumping through hoops
>> > here
>> > trying to get RS to see everything from this stored procedure.
>> >
>> > I did see a note in a RS book somewhere that says RS can't see anything
>> > beyond one record in a multi-record returned set. If that is so,
>> > though,
>> why
>> > can I see everything on the data tab? My next attempts have centered
>> around
>> > trying to create cascading parameters, but though the first stored
>> procedure
>> > returns me a drop down table list, the next stored procedure which
>> requires a
>> > tablename for input and outputs a column list for that table keeps
>> > saying
>> > there are no fields for me to choose from to place on the report. Not
>> > to
>> > mention that I can't figure out how to get the table name from the
>> > first
>> > dataset to the second dataset for input.
>> >
>> > ARGH! Can anyone give me some ideas or point me in a better direction
>> than
>> > these lousy RS Online help files? They're practically worthless! They
>> don't
>> > even have a searchable Index tab like BOL has.
>> >
>> > Thanks in advance,
>> >
>> > Catadmin
>> >
>> > "A.M" wrote:
>> >
>> > > Hi,
>> > >
>> > >
>> > >
>> > > I have a report based on a stored procedure.
>> > >
>> > > The stored procedure does some calculations and finally returns a
>> > > query
>> > > based on a temporary table:
>> > >
>> > >
>> > >
>> > > Select * from #Tmp
>> > >
>> > >
>> > >
>> > > The problem is Reporting Services doesn't see the results based on a
>> > > #temporary table (no error, no rows!). If I change the stored
>> > > procedure
>> to
>> > > return an actual table in database, reporting services can see the
>> results.
>> > >
>> > >
>> > >
>> > > Ho can I have a report based on a stored procedure that returns a
>> > > query
>> > > based on a temporary table?
>> > >
>> > >
>> > >
>> > > Any help would be appreciated,
>> > >
>> > > Alan
>> > >
>> > >
>> > >
>>

Report based on Oracle Package

I want to use the results of a stored procedure from an Oracle package as my source for a report. But the stored procedure isn't listed on the dropdown when I try to add it, even though it's listed under "Procedures" in the server explorer with the [package name].[procedure name] format, but when I try typing that I just get a procedure does not exist message.You can do this programatically by loading the results into a dataset, and loading the dataset into the report. Cheers!|||Thakns. I'm pulling from five tables, so I guess I'd best get started.|||

I've created the dataset, and I can bind it to a grid, but I'm having trouble using it as a datasource for my report.

I used

ReportViewer2.LocalReport.DataSources.Add(New ReportDataSource("ds"))ReportViewer2.DataBind()

but then if I use =!Fields.LAST_NAME.Value in my table on my report I get a "report has no dataset" message and if I use =(!Fields.LAST_NAME.Value, "ds") I get the same.

Suggestions?