DataSource and more than one recordset

I've a report that use a stored procedure as datasource.
This s.p. return 2 recordset. How to access the second recordset in
reporting services? (like Recordset.NextRecordset in VB)
--
OSVALDO COLITTIRS does not support more than one recordset being returned.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>|||Bruce is correct in that the native Data Processing Extensions do not
support stored procedures that return more than one recordset - what happens
is that the first recordset is all that your Reporting Services DataSet will
see.
However, you can certainly create a custom Data Processing Extension, and
could then gain access to the other recordsets returned by a stored
procedure in a report. Its not optimal and the best way forward is usually
to re-design the report logic if this is at all possible. Typically what you
need to do in your Data Processing Extension is archive off the recordsets
into a tables that you can then pull them down from seperately for other
Report DataSets in the Report.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>

Dataset not filled if sql-query generates error

Hi,

I've a dataset which is filled using a stored procedure in either MSSQL2005 or 2000.

The procedure may return sql-errors (f.ex 208 if a table is not found), but the procedure will anyway return data which shall populate the dataset, independent on 208 errors.

The procedure works fine if running it from f.ex. SqlMgtStudio, and the dataset is filled if the procedure triggers no errors.

However, if the procedure triggers an error like 208, then I get an exception in the application and the dataset is not populated with the data returned by the procedure.

Is there a way of telling the DataSet/SqlDataAdapter to fill the dataset even if the sql-code genererates som errors?

Regards, Guttorm

Perhaps you could wrap your statements to avoid errors. For instance, if you have a table not found error you could wrap the line that generates it with if object_id('<table name>') is not null or some other sanity check. This would just silently work through the problem if there are errors though, you could add an else of course to handle when tables don't exist to create them and such.

Hope this helps,

John (MSFT)

|||

Thank you for your response.

Your outlined workaround might have been an option if not for the fact that I do not have much control of the sql-code.

The sqlcode is indirectly built by the user (and then saved in database) and verifying it would require me to first parse it ( to find tables, views) then name resolution etc, a rather time and resource consuming process.

My current workaround is to divide the task of filling the dataset into two tasks,

1) execute a command (using SqlCommand.ExecuteNonQuery) to populate a temp-table with the result

2) fill dataset (DataSet.Fill) by selecting the result from the temp-table

This works fine but makes to code a bit more complicated and requires an intermediate store in the database.

Guttorm

sql

dataset linked to stored procedure return no data

I created a new dataset for a new report that gets data from a stored
procedure. But when I run the dataset in the Data tab, it only returns the
column names with no data. The stored procedure runs fine in the Query
Analyzer with several returned records. Any help will be appreciated!On Mar 7, 4:14 pm, obnddc <obn...@.discussions.microsoft.com> wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!
Have you verified that the command type of the dataset is set to
stored procedure (instead of text)?
Enrique Martinez
Sr. SQL Server Developer|||Since it is returning all the columns it means it has accessed stored proc,
Just check the datasource using "test connection" if possible recreate the
datasource, more over do a "Refresh". Check for the server you connected
using Query Analyzer and the datasource are same.
Amarnath
"obnddc" wrote:
> I created a new dataset for a new report that gets data from a stored
> procedure. But when I run the dataset in the Data tab, it only returns the
> column names with no data. The stored procedure runs fine in the Query
> Analyzer with several returned records. Any help will be appreciated!|||Thanks for the input, Emartinez and Amarnath!
I found out that the problem lay in the data itself. I have an input
parameter used in the where condition, like "WHERE tbl_name.customerID LIKE
@.custID"
The test data I used has several spaces after cutomerID charactors. It is
interesting to see the LIKE statement will ignore the spaces in Query
Analyzer but fail in SQL reporting services.
I tried to LTRIM and RTRIM customerID, or use @.custID+'%' but none worked.
Anyone here can help? Thanks.|||Finally I found out, it's not the spaces but the parameters.
I have begin date and end date as input parameters, and end date is
optional. When it is null, getdate(). In Query Analyzer, I left it empty, it
returned records. In SQL report, I have to enter an end date and the date I
entered happens to be the only date that has records. So no record met the
dates, no record reurned.
What I learned:
Before you conclude same process ran differently in different envirements,
make sure they ran under EXACTLY same conditions!
Thanks to all!

Datareader does not return result

the following is the code,...When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...

Connection is open and the query is simple select statement

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

Thanks

Niranch

niranch,

can you show me exact syntax of your query string within the context of how you are using it in the c# code? You may have some quotes being escaped incorrectly or something such that when run from the app code, it does not return any results and therefore you are getting your error. so, make sure that your query string is written correctly, particularly how you have used single or double quotes...--jp

|||

hi jp,

thank you for your suggestion...I just figured out the problem...

It is just that, when I run the same set of statements in my code behind file, I get the result, but when I run it in a seperated .CS file(DAL), I get the error mentioned in my post...I am trying to return datareader object to the Business tier...

I dont know why...

Thanks

Niranch

|||ahh so you are having trouble with the data moving through tiers. can you show some code of how your are interacting between the tiers?|||

hi this is my code...the same set to commands ,connections works fine in codebehind file

codebehid file

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database)

class where i have database access

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader);

|||

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader); <-- at this point is your reader null? if you put a throw statement here like if(myReader == null) throw new Exception("Null Reader") will you get an exception? is the reader already null here, or are you getting results here and just not when you set

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database) you get the null reader here? that make sense? at what point are you not getting any results? -- jp

|||

I get the problem, Exactly after executing the command

DAL Page

myreader = mycommand.executereader(), i get a null reader. "Invalid read attempt when there is no data" is the error...only in this page, i have my connection open...

i dont know if it is ok to send a datareader object as return object to another page............

|||

thankyou, i got the problem solved,,,

I transfered the contents from reader to datatable and passed as return objec to presentation tier...It worked fine.............

Datareader and arrays

I have data I am retrieving using a datareader...and SQLSERVER
It could return 1 row of information or perhaps 3 rows of information
I need to know how to use an array here I would guess so I can access each element in this row or rows.

HOw might I use reader.read and get it into the arraywhy don't you just use a DataSet to hold the data, that's like one big smart array that holds everything.|||HOw might I create such a thing using a stored procedure that uses a join..I am confused on this dataset...Dont you have to set the default table or something.....

Any coding samples...
How do you move through a dataset|||it's all in ado.net docs somewhere, search for it, I'm sure you'll come up with something.

You'll need a Connection, then a Command, then a DataAdapter, then a DataSet.|||DATATABLE!

datalength doubling values

Am I missing something? I'm trying to return the size of the data contained in a varbinary(max) column, however it appears that the value being returned is double what it should be. Is this normal, or is there something else I need to do?

Thanks,

Devin

Edit: I'm not discounting that my data may be weird, but I wanted to cast my net as wide as possible.

Perhaps the field contains trailing blanks...|||

Why do you think this is double? Can you post a snippet of code that doesn't seem to make sense? Like:

set nocount on

declare @.test varbinary(max)

set @.test = 0x12

select datalength(@.test)

set @.test = 0x1234

select datalength(@.test)

Returns:

--

1

--

2

|||

Ignore this. I was using compression on the streams as I was putting them into the column. Apparently the framework's GZipStream class has a bug that causes it to mishandle files that already have compression in them (video, jpg, pdf) so that they end up larger.

DATALENGTH was reporting the correct size for the contents of the column.

Thanks,

Devin