Reporting Services and Linked Server - can't stop execution of pro

Hello,
I have the same problem with Reporting Services and Linked Server.
The Reporting Services receive data source from stored procedure, which
executes inside it stored procedure on the Linked Server. If Reporting
Service successfully forms the report, then everything is OK, but if there
are some troubles (e.g. I close the browser page where report was forming), I
have the problem: the process on the Linked Server doesnâ't stop itself. Itâ's
continuing executing for eternity and locks other processes. The only way to
stop it â' is to kill it. Iâ'll try to set timeouts in the configuration of
Reporting Service and Query Timeout in the Server Options of Linked Server,
but it doesnâ't help. This problem shows itself only if I work through Linked
Server.
Thanks for your help.
Boris.The default timeout for linked server queries is 600 seconds, i.e. 10
minutes. This can be changed in SQL Server using an sp_configure option
"remote query timeout".
Yes, you're right, this is a problem which has nothing to do with Reporting
Services. You may want to repost the relevant part of your question to a SQL
Server forum. You may get more advice on the issue.
HTH
Charles Kangai, MCT, MCDBA
"Boris" wrote:
> Hello,
> I have the same problem with Reporting Services and Linked Server.
> The Reporting Services receive data source from stored procedure, which
> executes inside it stored procedure on the Linked Server. If Reporting
> Service successfully forms the report, then everything is OK, but if there
> are some troubles (e.g. I close the browser page where report was forming), I
> have the problem: the process on the Linked Server doesnâ't stop itself. Itâ's
> continuing executing for eternity and locks other processes. The only way to
> stop it â' is to kill it. Iâ'll try to set timeouts in the configuration of
> Reporting Service and Query Timeout in the Server Options of Linked Server,
> but it doesnâ't help. This problem shows itself only if I work through Linked
> Server.
> Thanks for your help.
> Boris.
>sql

Reporting Services 2005-Stored procs with multiple results

Hello
In Reporting Services 2000, only the first result set returned from
stored procedure is recognized. Does anyone know if this is stil
true in Reporting Services 2005? It appears to be in beta 2, but
just want to make sure I'm not missing anything. Has anyone heare
that using multiple result sets from a stored procedure will be a
added featureI have not heard anything about that. If you are seeing the same
functionality in Beta 2 then my guess is that nothing has changed in this
area.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"erobert1" <robclark496@.msn-dot-com.no-spam.invalid> wrote in message
news:LN-dnWugPNVOC0HfRVn_vg@.giganews.com...
> Hello,
> In Reporting Services 2000, only the first result set returned from a
> stored procedure is recognized. Does anyone know if this is still
> true in Reporting Services 2005? It appears to be in beta 2, but I
> just want to make sure I'm not missing anything. Has anyone heared
> that using multiple result sets from a stored procedure will be an
> added feature?
>|||Hi
I logged a suggestion with Microsoft on this and will post thei
answer if I get one. In the mean time if anyone else has informatio
on this please post it here too|||Hi
I got a response from my post on the Microsoft product feedbac
center. It will be considered for a future release. If this featur
is important to you, you can vote for it at
http://lab.msdn.microsoft.com/productfeedback/viewFeedback.aspx?feedbackid=38a81133-5e82-4a31-a6b3-2877929373c

Reporting Services 2005 (VS 2005) using Oracle 10g stored procedur

Hi,
I want to get data from Oracle 10g Stored procedure to Reporting Services
2005. I could pass a SQL text and get a record set, but I want to execute a
store proc and get the record set.
1. Add New Data Source
2. Choose Type : Oracle and connection tested OK
3. { call Test_Package.Test_Procedure(?) } is it wrong... how to write?
There is an error in the query. ORA-00911: invalid character
Also make sure that you use the text-based generic query designer (2 panes
!) instead of the visual query designer (4 panes) - you can switch between
them through an icon on the toolbar in the data view of report designer.
Question : I tried many methods but unable to solve it...
create or replace
PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
END Test_Package;
create or replace PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE
)
AS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
END Test_Procedure;
The below site gave some example but i could not solve it... any suggestions
greatly appreciated...
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
I want to know HOW to call Oracle stored procedure with parameter using
Reporting Services (VS 2005)... that's all...
thanks
ramaYour issue is the difference between OLEDB provider (4 pane) versus the
dotnet provider (2 pane). The below explains what is happening in detail:
Oracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter. Finally, in the
generic query designer, just specify the name of the stored procedure
without arguments and the parameters should get detected automatically.
/End Snip
And more from Robert:
/Snip
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rama2007" <rama2007@.discussions.microsoft.com> wrote in message
news:E3A84017-C16A-4AF2-8409-BB2D050D7BDD@.microsoft.com...
> Hi,
> I want to get data from Oracle 10g Stored procedure to Reporting Services
> 2005. I could pass a SQL text and get a record set, but I want to execute
> a
> store proc and get the record set.
> 1. Add New Data Source
> 2. Choose Type : Oracle and connection tested OK
> 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to
> write?
> There is an error in the query. ORA-00911: invalid character
>
> Also make sure that you use the text-based generic query designer (2 panes
> !) instead of the visual query designer (4 panes) - you can switch between
> them through an icon on the toolbar in the data view of report designer.
> Question : I tried many methods but unable to solve it...
> create or replace
> PACKAGE Test_Package
> AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
> END Test_Package;
> create or replace PROCEDURE Test_Procedure (
> Test_Cursor IN OUT Test_Package.Test_Type,
> Test_Parameter IN Test_Table.ID%TYPE
> )
> AS
> BEGIN
> OPEN Test_Cursor FOR
> SELECT *
> FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
> END Test_Procedure;
> The below site gave some example but i could not solve it... any
> suggestions
> greatly appreciated...
> http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
> I want to know HOW to call Oracle stored procedure with parameter using
> Reporting Services (VS 2005)... that's all...
> thanks
> rama|||Thanks Bruce...
Thank you for detailed explanation. Appreciate it..
Somehow, i tried to use the text-based query designer (2 panes) to settle
the problem, i 'm still having the difficulty.. i will try again and get back
to you.. any other suggestions'
thanks.
rama arumugam
"Bruce L-C [MVP]" wrote:
> Your issue is the difference between OLEDB provider (4 pane) versus the
> dotnet provider (2 pane). The below explains what is happening in detail:
> Oracle has a few unique things going on. First, my recommendation is to use
> the generic data designer (2 panes). The button to switch to this is to the
> right of the ...
>
> Second, because the development environment was not designed for managed
> providers they got tricky with what is used under the covers (hence my
> recommendation to use the generic designer). Here is a description from
> Robert Bruckner [MSFT].
> /Snip
> Note: the behavior of PREVIEW in Report Designer is identical to the
> ReportServer behavior! However the DATA view in Report Designer is
> different for the visual designer: * the visual query designer with 4 panes
> will internally always use OleDB providers for verifying and executing
> queries directly in "Data" view. (Main reason: the visual query designer
> does not work with managed providers). Example: if you choose "Oracle" in
> the data source dialog, the Data view has to use the OleDB provider for
> Oracle behind the scenes, but Preview and Server will use the managed Oracle
> provider. The generic text-based query designer (2 panes) will _always_ use
> the data provider you specified.
> /End Snip
>
> Just a little background for you. OK, now, from the generic query designer.
> He then had this to say about stored procedures:
> /Snip
> In addition, how do you return the data from your stored procedure? Note:
> only an out ref cursor is supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter. Finally, in the
> generic query designer, just specify the name of the stored procedure
> without arguments and the parameters should get detected automatically.
> /End Snip
> And more from Robert:
> /Snip
> Managed Oracle provider (named parameters):
> select * from table where ename = :parameter
> OleDB for Oracle (unnamed parameters):
> select * from table where ename = ?
> The managed Oracle data provider uses a ':' to mark named parameters
> (instead of '@.'); the OleDB provider for Oracle only allows unnamed
> parameters (using '?'). The following KB article explains more details:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> /End Snip
> Hope that helps. Definitely not intuitive but it works.
> One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
> client installed for it to work.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "rama2007" <rama2007@.discussions.microsoft.com> wrote in message
> news:E3A84017-C16A-4AF2-8409-BB2D050D7BDD@.microsoft.com...
> > Hi,
> >
> > I want to get data from Oracle 10g Stored procedure to Reporting Services
> > 2005. I could pass a SQL text and get a record set, but I want to execute
> > a
> > store proc and get the record set.
> >
> > 1. Add New Data Source
> > 2. Choose Type : Oracle and connection tested OK
> > 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to
> > write?
> > There is an error in the query. ORA-00911: invalid character
> >
> >
> > Also make sure that you use the text-based generic query designer (2 panes
> > !) instead of the visual query designer (4 panes) - you can switch between
> > them through an icon on the toolbar in the data view of report designer.
> > Question : I tried many methods but unable to solve it...
> >
> > create or replace
> > PACKAGE Test_Package
> > AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
> > END Test_Package;
> >
> > create or replace PROCEDURE Test_Procedure (
> > Test_Cursor IN OUT Test_Package.Test_Type,
> > Test_Parameter IN Test_Table.ID%TYPE
> > )
> > AS
> > BEGIN
> > OPEN Test_Cursor FOR
> > SELECT *
> > FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
> > END Test_Procedure;
> >
> > The below site gave some example but i could not solve it... any
> > suggestions
> > greatly appreciated...
> >
> > http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
> >
> > http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
> >
> > I want to know HOW to call Oracle stored procedure with parameter using
> > Reporting Services (VS 2005)... that's all...
> >
> > thanks
> > rama
>
>|||All I can tell you is that I know it is possible to get this to work. The
stored proc has to be written correctly. You need to configure the oracle
client properly. One suggestion, using the two pane generic query designer
make sure that you can query the Oracle database. Then you know that part of
the setup is working. Deploy it and make sure that for a very simple query
that you have the client and oracle.net provider all working. Then get it
working with the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rama2007" <rama2007@.discussions.microsoft.com> wrote in message
news:CE9BE8A3-47E0-4739-BC31-E7F80B4E460E@.microsoft.com...
> Thanks Bruce...
> Thank you for detailed explanation. Appreciate it..
> Somehow, i tried to use the text-based query designer (2 panes) to settle
> the problem, i 'm still having the difficulty.. i will try again and get
> back
> to you.. any other suggestions'
> thanks.
> rama arumugam
>
> "Bruce L-C [MVP]" wrote:
>> Your issue is the difference between OLEDB provider (4 pane) versus the
>> dotnet provider (2 pane). The below explains what is happening in detail:
>> Oracle has a few unique things going on. First, my recommendation is to
>> use
>> the generic data designer (2 panes). The button to switch to this is to
>> the
>> right of the ...
>>
>> Second, because the development environment was not designed for managed
>> providers they got tricky with what is used under the covers (hence my
>> recommendation to use the generic designer). Here is a description from
>> Robert Bruckner [MSFT].
>> /Snip
>> Note: the behavior of PREVIEW in Report Designer is identical to the
>> ReportServer behavior! However the DATA view in Report Designer is
>> different for the visual designer: * the visual query designer with 4
>> panes
>> will internally always use OleDB providers for verifying and executing
>> queries directly in "Data" view. (Main reason: the visual query designer
>> does not work with managed providers). Example: if you choose "Oracle" in
>> the data source dialog, the Data view has to use the OleDB provider for
>> Oracle behind the scenes, but Preview and Server will use the managed
>> Oracle
>> provider. The generic text-based query designer (2 panes) will _always_
>> use
>> the data provider you specified.
>> /End Snip
>>
>> Just a little background for you. OK, now, from the generic query
>> designer.
>> He then had this to say about stored procedures:
>> /Snip
>> In addition, how do you return the data from your stored procedure? Note:
>> only an out ref cursor is supported. Please follow the guidelines in the
>> following article on MSDN (scroll down to the section where it talks
>> about
>> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
>> To use a stored procedure with regular out parameters, you should either
>> remove the parameter (if it is possible) or write a little wrapper around
>> the original stored procedure which checks the result of the out
>> parameter
>> and just returns the out ref cursor but no out parameter. Finally, in
>> the
>> generic query designer, just specify the name of the stored procedure
>> without arguments and the parameters should get detected automatically.
>> /End Snip
>> And more from Robert:
>> /Snip
>> Managed Oracle provider (named parameters):
>> select * from table where ename = :parameter
>> OleDB for Oracle (unnamed parameters):
>> select * from table where ename = ?
>> The managed Oracle data provider uses a ':' to mark named parameters
>> (instead of '@.'); the OleDB provider for Oracle only allows unnamed
>> parameters (using '?'). The following KB article explains more details:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>> /End Snip
>> Hope that helps. Definitely not intuitive but it works.
>> One last thing. The MS managed provider for Oracle need 8.1.7 or higher
>> (8i)
>> client installed for it to work.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "rama2007" <rama2007@.discussions.microsoft.com> wrote in message
>> news:E3A84017-C16A-4AF2-8409-BB2D050D7BDD@.microsoft.com...
>> > Hi,
>> >
>> > I want to get data from Oracle 10g Stored procedure to Reporting
>> > Services
>> > 2005. I could pass a SQL text and get a record set, but I want to
>> > execute
>> > a
>> > store proc and get the record set.
>> >
>> > 1. Add New Data Source
>> > 2. Choose Type : Oracle and connection tested OK
>> > 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to
>> > write?
>> > There is an error in the query. ORA-00911: invalid character
>> >
>> >
>> > Also make sure that you use the text-based generic query designer (2
>> > panes
>> > !) instead of the visual query designer (4 panes) - you can switch
>> > between
>> > them through an icon on the toolbar in the data view of report
>> > designer.
>> > Question : I tried many methods but unable to solve it...
>> >
>> > create or replace
>> > PACKAGE Test_Package
>> > AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
>> > END Test_Package;
>> >
>> > create or replace PROCEDURE Test_Procedure (
>> > Test_Cursor IN OUT Test_Package.Test_Type,
>> > Test_Parameter IN Test_Table.ID%TYPE
>> > )
>> > AS
>> > BEGIN
>> > OPEN Test_Cursor FOR
>> > SELECT *
>> > FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
>> > END Test_Procedure;
>> >
>> > The below site gave some example but i could not solve it... any
>> > suggestions
>> > greatly appreciated...
>> >
>> > http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17
>> >
>> > http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures
>> >
>> > I want to know HOW to call Oracle stored procedure with parameter using
>> > Reporting Services (VS 2005)... that's all...
>> >
>> > thanks
>> > rama
>>|||Hi i have a sp which returns Ref cursr.
var x refcursor;
exec am.GET_test(1,146,14,51,285, :x);
print x;
I tried this and its working form sql plus. What thst syntax for the the
same from SSRS

Reporting Services - use of temp tables

Hello all,

I need to create a report based on a stored procedure. This stored procedure contains temp tables. At the time of creating the report, I receive a message mentioning - There is an error in the query. Invalid object name '<temp table name'>

Doesn't Reporting Services support temp tables?

Thanks all,
SauravYou can use temporary tables. I tend to do it like...

-- Create temporary tables
declare @.MyTempTable table
(
field1 int,
field2 smalldatetime,
field3 int,
field4 smallint
)

You can then use @.MyTempTable for your SP.

Hope that helps.sql

Reporting Services

Hi,
I am new for the Reporting Services. I need to generate a reports
based on a stored procedure. When we call the stored procedure it says
Invalid object name '#LType'. (Microsoft SQL SERVER, Error:208)
If we see the stored procedure, we find that there is a temporary table
'#LType' where we are storing the data and applying some condition for
fatching the records from the table. At the bottom of the stored
procedure we are also dropping the temporary table.The stored procedure
looks like:
alter procedure Test4 .
@.UID int,@.varSourceType varchar(10)=null
......
......
as
set quoted_identifier off
set nocount on
declare @.BranchIDsql varchar(1000),@.varleveltype varchar
.....
....
Begin
create table <b> #LType (LevelType varchar(4)) </b>
insert into #LType select ltrim(rtrim(UL.varleveltype)) from
tbluserlevel UL inner join tblUserProfile UP on
UL.intUserProfileID=UP.intUserProfileID where UP.intUserLoginID=@.UID
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[BID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BID]
create table BID (intUlBranchID int)
if ((select count(Leveltype) from #LType where LevelType='CORP')>0)
Begin
set @.BranchIDsql="insert into BID select tblULBranch.intUlBranchID
from tblULBranch where intULBranchGroupID in
(select intULBranchGroupID from tblULBranchGroup where intULCountyID
in
(select intULCountyID from tblULCounty where intULRegionID in
(select intULRegionID from tblULRegion where intULCompanyID in
(select intULCompanyID from tblULCompany where intULCompanyGroupID
in
(select intULCompanyGroupID from tblULCompanyGroup where
intULCorporateID in
(select intlevelvalue from tblUserLevel where intUserProfileID in
(select intUserProfileID from tblUserProfile where intUserLoginID in
("+convert(varchar,@.UID)+"))))))))"
exec(@.BranchIDsql)
End
............
............
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[#LType]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[#LType]
Thanks in Advance
Munna Kumar Singh
Bangalore
IndiaHi
Your procedure is not returning a result set!
Why are you dropping the BID table, this could cause issues on a multiuser
system?
Maybe something like the following (untested!) may be what you want?
ALTER PROCEDURE Test4 @.UID INT,@.varSourceType VARCHAR(10)=NULL
......
......
AS
SET NOCOUNT ON
DECLARE @.BranchIDsql VARCHAR(1000),@.varleveltype VARCHAR(4)
.....
....
BEGIN
IF EXISTS ( SELECT * FROM
dbo.tbluserlevel UL
JOIN dbo.tblUserProfile UP ON UL.intUserProfileID=UP.intUserProfileID
WHERE UP.intUserLoginID=@.UID
AND LTRIM(RTRIM(UL.varleveltype)) = 'CORP'
)
BEGIN
SELECT DISTINCT B.intUlBranchID
FROM tblULBranch B
JOIN tblULBranchGroup G ON B.intULBranchGroupID = G.intULBranchGroupID
JOIN tblULCounty C ON G.intULCountyID = C.intULCountyID
JOIN tblULRegion R ON R.intULRegionID = C.intULRegionID
JOIN tblULCompany Y ON Y.intULCompanyID = R.intULCompanyID
JOIN tblULCompanyGroup Z ON Y.intULCompanyGroupID = Z.intULCompanyGroupID
JOIN tblUserLevel U ON U.intlevelvalue = Z.intULCorporateID
WHERE U.intUserLoginID = @.UID
END
ELSE
SELECT NULL AS intUlBranchID
END
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
example data to get a more precise reply. Also posting expected results from
example data is very useful.
John
<dreammunna@.gmail.com> wrote in message
news:1135665571.048020.288240@.g43g2000cwa.googlegroups.com...
> Hi,
> I am new for the Reporting Services. I need to generate a reports
> based on a stored procedure. When we call the stored procedure it says
> Invalid object name '#LType'. (Microsoft SQL SERVER, Error:208)
> If we see the stored procedure, we find that there is a temporary table
> '#LType' where we are storing the data and applying some condition for
> fatching the records from the table. At the bottom of the stored
> procedure we are also dropping the temporary table.The stored procedure
> looks like:
>
> alter procedure Test4 .
> @.UID int,@.varSourceType varchar(10)=null
> ......
> ......
> as
> set quoted_identifier off
> set nocount on
> declare @.BranchIDsql varchar(1000),@.varleveltype varchar
> .....
> ....
> Begin
> create table <b> #LType (LevelType varchar(4)) </b>
> insert into #LType select ltrim(rtrim(UL.varleveltype)) from
> tbluserlevel UL inner join tblUserProfile UP on
> UL.intUserProfileID=UP.intUserProfileID where UP.intUserLoginID=@.UID
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[BID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[BID]
> create table BID (intUlBranchID int)
> if ((select count(Leveltype) from #LType where LevelType='CORP')>0)
> Begin
> set @.BranchIDsql="insert into BID select tblULBranch.intUlBranchID
> from tblULBranch where intULBranchGroupID in
> (select intULBranchGroupID from tblULBranchGroup where intULCountyID
> in
> (select intULCountyID from tblULCounty where intULRegionID in
> (select intULRegionID from tblULRegion where intULCompanyID in
> (select intULCompanyID from tblULCompany where intULCompanyGroupID
> in
> (select intULCompanyGroupID from tblULCompanyGroup where
> intULCorporateID in
> (select intlevelvalue from tblUserLevel where intUserProfileID in
> (select intUserProfileID from tblUserProfile where intUserLoginID in
> ("+convert(varchar,@.UID)+"))))))))"
> exec(@.BranchIDsql)
> End
> ............
> ............
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[#LType]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[#LType]
>
> Thanks in Advance
> Munna Kumar Singh
> Bangalore
> India
>

Reporting Service Q: stored procedure works fine, but preview doesnt

Hello

I got an error when doing preview of my report.
The data set 'dsTransTime' contains a definition for the field 'Trans_Type'. This field is missing from the returned result set from the data source.

The data set is populated by stored procedure. The data set works fine when I run it seperately at the Data view in Visual Studio 2003. But it doesn't work on Preview.

Please help!, thanks,

Spent few hours, finally find the solution.
Just click the 'Refresh' button on the Data view.

Reporting off of stored procedures

Hi, I'm new to SQL Reporting Services. I want to create a report from a dataset where the source is a stored procedure. The sp does several things to generate the ultimate reporting rows; temp table(s) are created and updated, joined with other tables and views, etc. The final line of the sp is: select * from #temptable. I'm getting an error that the temp table is an invalid object. Also, this sp used to have input parameters which caused errors also, so I just hard coded values to get past that. Any ideas here? Thanks, Marta.

You might want to put the data you are ultimately querying in a permanent table, not a temp table. It may be that when you return the cursor, the temp table is no longer available and when the report is going through the cursor and trying to get records, the data is not available anymore.

|||

Hi,

The problem must be elsewhere. I am doing this without problem.

Did you set the report dataset to type StoredProcedure?

Did you try to call your sp from a query window?

Did you create the datasource as to be of type Microsoft SQL Server?

Also, I do not like the " Select * " try to specify the columns if you can.

As Jayplus said, a permanent table would be better (if the data will remain unchanged for a while).

Philippe

Reporting off both a Stored Procedure and Table

Hi All,

I am using a Stored Procedure in the report. In the details section,
I have a formula for calculating End_date.This formula works fine and displays the data.But I want to check if this END_Date lies in the list of holidays which is given in another table 'tbHolidays'.
If it does lie in the list of Holidays then I need to add 1 to End Date.

Do I need to write a SQL expression for fetching the data from 'tbHolidays'? Can anybody out there please help me??

Thanks

Rashmias far as i know, if u use stored procedure u can't use SQL Expression.

r u using ref cursor in stored procedure?|||Thanks Raheem...
Yes I am using cursors in the SP. The cursor is used to fetch the records from join of two tables and then calculations are done for the fetched data. Then the data is inserted into a temporary table and a SELECT query is there at the end to fetch the rows.

Is there any other way to fetch the records from the table?Also I am not sure if a simple comparison of End Date with the data from tbHolidays would work...Helppppp!!!

Rashmi