Is it possible to access the @@RowCount from a Sproc in a report

Hi ,

I am trying to access the @.@.RowCount that is been returned by my stored procedure in a report. Can you please tell me how can i access it, This is my Stored procedure

USE [ICCStatements]GO/****** Object: StoredProcedure [dbo].[rpt_SelectInvestments] Script Date: 08/03/2007 11:54:47 ******/SET ANSI_NULLSOFFGOSET QUOTED_IDENTIFIEROFFGOALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanIdAS integer)ASBEGIN-- History-- 08/17/2004svanpatter/JSWCOinitial version created-- 08/30/2004svanpatter/JSWCOadd-- Select available fundsDeclare @.CountintSELECT [ClientPlan].PlanId, [ClientPlan].PlanName,-- Fund.[FundName] AS InvestmentName,CASEWHEN PlanFund.PlanFundDisplayNameISNULLTHEN Fund.ShortNameELSE PlanFund.PlanFundDisplayNameEND InvestmentName,'F'AS InvestmentType,--PlanFund.PlanId As InvestmentID PlanFund.IsPortfolioFundOnly, PlanFund.FundDisplayOrderAs InvestmentIDFROM [ClientPlan]--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdINNERJOIN PlanFundON [ClientPlan].PlanId = PlanFund.PlanIdAnd IsPortfolioFundOnly = "0"INNERJOIN FundON PlanFund.FundId = Fund.FundId--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperIDWHERE[ClientPlan].PlanId = @.PlanIdUNION-- Select PortfoliosSELECT [ClientPlan].PlanId, [ClientPlan].PlanName, PlanPortfolio.PortfolioNameAS InvestmentName,'P'AS InvestmentType,NULL,PlanPortfolio.PortfolioIdAs InvestmentIDFROM [ClientPlan]INNERJOIN PlanPortfolioON [ClientPlan].PlanId = PlanPortfolio.PlanId--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdWHERE[ClientPlan].PlanId = @.PlanIdORDER BYInvestmentType, InvestmentIDSet @.Count =@.@.RowCountReturn @.CountEND

Can some one pls tell me how can i access @.Count from the report.

Any help will be appreciated..

Regards,

Karen

If you meant Crystal Reports then I don't understand why you need this as in CR you already have such function that returns number of rows returned by SP.

|||

I meant in SQl server Reporting Services.

|||

=Count(Field!somefieldname.Value)

|||

lavanya,

Is it possible to access the no. of rows returned by the stored procedure....

Regards

Karen

|||

You shouldn't need to return the RowCount from the stored procedure. In SQL Reporting Services there is a CountRows() function that can be used to return the number of rows in the datasource. To use it you would call it like so: =CountRows("DatasourceName"). The "DatasourceName" obviously being the name of the datasource that calls your stored procedure.

Also, the = sign is only needed if it is the only item, or first item, in a text field in the report.

Is it possible to access data associated with a hyperlink in SSIS?

I need to be able to to extract the data that is returned by clicking a hyperlink. I click the link and it displays the data.

I know that SSIS has a web services task, and that appears to be a neat feature, but the link I am connecting to does not have a WSDL file or anything like that. So I think I cannot use this task.

Does anyone have a suggestion about how I might do this?

Thanks for your help,

Harold

What data that is returned by clicking a hyperlink?

What is the hyperlink hosted in?

What defines or restricts this data?

What do you expect to happen to this extracted data?

SSIS is a backend ETL tool, I cannot work out what you would expect it to do for you, in what I am guessing is some kind of web UI

|||

Hello Darren,

Thanks for responding to my post.

I want to read the data that are displayed when I click a hyperlink.

When the package is executed, it should go to the hyperlink (URL) and get the data it displays.

The data that are displayed are:

var imgRates = {"ThirtyFixed":"6.298","FifteenFixed":"5.926","ThirtyFixedJumbo":"6.339","FiveOneARM":"5.831"};

I want to read that string into my package. I will then parse and store it.

Do you know if this can be done?

Thanks,

Harold

|||Your data looks to be a line of code. It doesn;t look very big, and not really what i woudl expct for an ETL source. Just write this in your programming language of choice, forget about SSIS.|||Here is some code that will go to a URL that you provide and read the content into a string.

Code Snippet

Dim webRequest As System.Net.WebRequest = System.Net.HttpWebRequest.Create(Dts.Variables("Url").Value.ToString())
Dim webResponse As System.Net.WebResponse = webRequest.GetResponse()
Dim stream As System.IO.Stream = webResponse.GetResponseStream()
Dim streamReader As New System.IO.StreamReader(stream)
Dim content As String = streamReader.ReadToEnd()

|||

Hello Jay,

The code snippet was just what I needed!

Thanks for your help, and for the others who took time to repond.

Harold

is it possibl to avoid cursor (returned rows)

Normal set up

cursor
Select ( a bunch of crap..)

Usually I have to use the cursor to go through each record and do more crap with each record, for example I may email each user returned in the select. Is it possible to somehow use a stored procedure instead of a cursor or any other way to make this more efficient?

It's a batch job (run at night) and it'll return about 7,000 rows roughly each time so it's not terrible to the point where i need it to be super efficient, i'd just like to keep as little load on the sql server as much as possible. I assume it's one of those situations where I just can't avoid using the cursor.If your select is simple, you can define a variable to hold the table key(s) and use a while loop to walk through the table.

If your select is complex you could dump the result set of the select into a table and then use a while loop/variable to walk the table.

declare @.au_id varchar(11)
select @.au_id = min(au_id) from pubs.dbo.authors
while @.au_id is not null begin
select * from authors where au_id = @.Au_id
select @.au_id = min(au_id) from pubs.dbo.authors where au_id > @.au_id
end

granted this is simple but it ilistrattes the point

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write   directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the   code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions?
Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that   will be HTML coded as   int
the client code.
HTH, Jens Suessmeyer.
|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegro ups.com...
>I am making a web Application, where non breakable space (&nbsp;) is
> crucial to the layout. When I write &nbsp; directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the &nbsp; code it is rendered to the browser
> like this:
> King&nbsp;Kong
> instead of
> King Kong
> Any solutions?
>
|||thanks Guys!
yes the hml code formats the retrieved string as KING&nbsp;KONG. I
will try to make a serverside function that exchanges all spaces in the
string with &nbsp;=20
I=B4ll let you know the result in 2 minnutes
|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", "&nbsp;");
}

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions'Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that will be HTML coded as int
the client code.
HTH, Jens Suessmeyer.|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegroups.com...
>I am making a web Application, where non breakable space ( ) is
> crucial to the layout. When I write directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the code it is rendered to the browser
> like this:
> King Kong
> instead of
> King Kong
> Any solutions'
>|||thanks Guys!
yes the hml code formats the retrieved string as KING KONG. I
will try to make a serverside function that exchanges all spaces in the
string with =20
I=B4ll let you know the result in 2 minnutes|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", " ");
}

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions'Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that will be HTML coded as &nbsp; int
the client code.
HTH, Jens Suessmeyer.|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegroups.com...
>I am making a web Application, where non breakable space ( ) is
> crucial to the layout. When I write directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the code it is rendered to the browser
> like this:
> King Kong
> instead of
> King Kong
> Any solutions'
>|||thanks Guys!
yes the hml code formats the retrieved string as KING&nbsp;KONG. I
will try to make a serverside function that exchanges all spaces in the
string with
I=B4ll let you know the result in 2 minnutes|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", " ");
}