passing parameter from asp.net application to reporting services report

Hi,

There is a .net application which has a screen with four options(text boxes)

Each of these should take to reports generated by SQL REp Services

This is the requirment

The School selected in the App should be passed on to the report

How should I pass the parameter in my report

so that when user selects a link or text box report for that particular school number is displayed

Thanks

sowree

Are you displaying the report in a report viewer control, or opening up a new browser window with the report URL?

BobP

|||

hi

the report is being viewed using report manager, so the report I have deployed to report server must be displayed when the user selects the menu selecting a school number in the asp app.

I need to know how i pass the parameter in my report rdl 's stored procedure to select the school number selected in the ASP application

Thanks

|||

You would create a parameter in the report rdl to accept the school number, and then use that parameter in the call to the stored proc.

For example: You create a parameter called @.SchoolNumber

exec spgSchoolInfo @.SchooNumber

To pass the parameter to the report in report manager, you would put the school number in the URL: (http://msdn2.microsoft.com/en-US/library/ms153586.aspx)

Example: http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&SchoolNumber=1234

HTH

BobP

Passing parameter from Asp.Net

Dear Sir,
How can reporting servicing receive parameter from Asp.net program?You could call a report via URL access and pass the parameter to the URL.
Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> Dear Sir,
> How can reporting servicing receive parameter from Asp.net program?
>|||Yes, via the SOAP API Render() method. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
for an example.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"yoclee" <yoclee@.discussions.microsoft.com> wrote in message
news:886688CD-0C5F-41D4-88E8-8134AD61D579@.microsoft.com...
> Can we pass the parameter using others method instead of using URL?
> "Ravi Mumulla (Microsoft)" wrote:
> > You could call a report via URL access and pass the parameter to the
URL.
> > Refer to
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_urlaccess_2v74.asp
> > for details.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > news:uvABWtmaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> > > Dear Sir,
> > > How can reporting servicing receive parameter from Asp.net program?
> > >
> > >
> >
> >
> >sql

Passing Form values from ASP to RS?

Hi everybody,
does anybody knows, if it is possible to pass input from an ASP-page (not
.NET) to RS and populate the dataset with the given values? We are trying to
use the RS so the user is able to print out, export to excel or pdf AFTER
he/she did the collection of the data. For example a search for address data
where the user types in the postal code and/or the cityname, gets the result
inside the browser and is then capable to print out the specific data. I
figured out that to use parameters for this, but as I don't know which
parameters a user typed in, I am not able to build the sql statement in
advance.
Any help would be great.
Regards
MichaelI have not tried this from a form but I have built a URL dynamically to do
the same thing. You could use some client side javascript to build the URL
dynamically or you could just build an interim ASP page with a redirect.
Good Luck
Bill
Michael Bender wrote:
>Hi everybody,
>does anybody knows, if it is possible to pass input from an ASP-page (not
>.NET) to RS and populate the dataset with the given values? We are trying to
>use the RS so the user is able to print out, export to excel or pdf AFTER
>he/she did the collection of the data. For example a search for address data
>where the user types in the postal code and/or the cityname, gets the result
>inside the browser and is then capable to print out the specific data. I
>figured out that to use parameters for this, but as I don't know which
>parameters a user typed in, I am not able to build the sql statement in
>advance.
>Any help would be great.
>Regards
>Michael
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Hi Bill,
thx for your tips. Maybe I'm not getting it, but I don't have the problem to
built the URL dynamically in the ASP page. A more exact example would be:
In a form where a user is able to select from search criteria like: First
Name, Last Name, Postal Code, Town and Country the user only chooses to look
for data based on a certain postal code.
My problem now is to use ONLY the parameter postal code in the SQL statement
to generate the report in RS. (eg: SELECT * from Table_AdressData where
postalcode = @.postalcode) I understand that I can't use all the parameters
in the sql statement (Select * from Table... where postalcode = @.postalcode
and firstname = @.firstname and etc.) because then the statement would fail
if the user does not enter every parameter as a search criteria.
Thx.
Michael
"William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
news:58c684e7207be@.uwe...
>I have not tried this from a form but I have built a URL dynamically to do
> the same thing. You could use some client side javascript to build the
> URL
> dynamically or you could just build an interim ASP page with a redirect.
> Good Luck
> Bill
> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1|||Your question really has nothing to do with integration with ASP. This is
strictly a RS question. For each of your parameters set the default to All
(the string all, if the data type is integer then set the default to an
integer that will not return data).
Then design your query like this:
select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
(field2 = @.Param2 or @.Param2 = 'All')
Then in your URL leave off the other parameters and send only the one you
care about. Or, don't have defaults and pass all the parameter but pass the
ones not filled in with the word All
One reason you might not want to use defaults is if the report is opened on
its own and all the parameters have defaults it will immediate execute.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Bender" <technik@.salescom.de> wrote in message
news:dnmn4d$lnb$03$1@.news.t-online.com...
> Hi Bill,
> thx for your tips. Maybe I'm not getting it, but I don't have the problem
> to built the URL dynamically in the ASP page. A more exact example would
> be:
> In a form where a user is able to select from search criteria like: First
> Name, Last Name, Postal Code, Town and Country the user only chooses to
> look for data based on a certain postal code.
> My problem now is to use ONLY the parameter postal code in the SQL
> statement to generate the report in RS. (eg: SELECT * from
> Table_AdressData where postalcode = @.postalcode) I understand that I can't
> use all the parameters in the sql statement (Select * from Table... where
> postalcode = @.postalcode and firstname = @.firstname and etc.) because then
> the statement would fail if the user does not enter every parameter as a
> search criteria.
> Thx.
> Michael
>
>
> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page (not
>>.NET) to RS and populate the dataset with the given values? We are trying
>>to
>>use the RS so the user is able to print out, export to excel or pdf AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>|||Thanks very much, Bruce.
Regards
Michael
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:OACNUv$$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Your question really has nothing to do with integration with ASP. This is
> strictly a RS question. For each of your parameters set the default to All
> (the string all, if the data type is integer then set the default to an
> integer that will not return data).
> Then design your query like this:
> select * from mytable where (field1 = @.Param1 or @.Param1 = 'All') and
> (field2 = @.Param2 or @.Param2 = 'All')
> Then in your URL leave off the other parameters and send only the one you
> care about. Or, don't have defaults and pass all the parameter but pass
> the ones not filled in with the word All
> One reason you might not want to use defaults is if the report is opened
> on its own and all the parameters have defaults it will immediate execute.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Bender" <technik@.salescom.de> wrote in message
> news:dnmn4d$lnb$03$1@.news.t-online.com...
>> Hi Bill,
>> thx for your tips. Maybe I'm not getting it, but I don't have the problem
>> to built the URL dynamically in the ASP page. A more exact example would
>> be:
>> In a form where a user is able to select from search criteria like: First
>> Name, Last Name, Postal Code, Town and Country the user only chooses to
>> look for data based on a certain postal code.
>> My problem now is to use ONLY the parameter postal code in the SQL
>> statement to generate the report in RS. (eg: SELECT * from
>> Table_AdressData where postalcode = @.postalcode) I understand that I
>> can't use all the parameters in the sql statement (Select * from Table...
>> where postalcode = @.postalcode and firstname = @.firstname and etc.)
>> because then the statement would fail if the user does not enter every
>> parameter as a search criteria.
>> Thx.
>> Michael
>>
>>
>> "William N via SQLMonster.com" <u3357@.uwe> schrieb im Newsbeitrag
>> news:58c684e7207be@.uwe...
>>I have not tried this from a form but I have built a URL dynamically to
>>do
>> the same thing. You could use some client side javascript to build the
>> URL
>> dynamically or you could just build an interim ASP page with a redirect.
>> Good Luck
>> Bill
>> Michael Bender wrote:
>>Hi everybody,
>>does anybody knows, if it is possible to pass input from an ASP-page
>>(not
>>.NET) to RS and populate the dataset with the given values? We are
>>trying to
>>use the RS so the user is able to print out, export to excel or pdf
>>AFTER
>>he/she did the collection of the data. For example a search for address
>>data
>>where the user types in the postal code and/or the cityname, gets the
>>result
>>inside the browser and is then capable to print out the specific data. I
>>figured out that to use parameters for this, but as I don't know which
>>parameters a user typed in, I am not able to build the sql statement in
>>advance.
>>Any help would be great.
>>Regards
>>Michael
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200512/1
>>
>sql

Passing DML to From .NET SQLClient?

Hi All,
I am trying to figure out why the following fails and how to fix it:
I have a .NET Client application that passes parameters to stored procedures
and uses dynamic SQL that is passed to the Server. Everything is great. My
problem is if I want to send over to the server several DML in one string.
e.g.
----
USE Master
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
DROP PROC sp_XXXXX'
GO
Create Procedure sp_XXXXX
..
..
..
etc...
GO
----
Passing this type of string to SQL fails. Get error regarding the "GO" key
words, "Create Procedure Must be the first statement, etc...
Is it not possible to pass Multiple DML statements to SQL Server in one
string? If I send each DML statement alone, without the "GO" key word it
does work; but not all together.
Thanks for any insight,
John.GO is not a Transact-SQL statement. It's a batch delimiter used by tools
like OSQL and Query Analyzer. One method to execute scripts containing GOs
in your client app is to parse the script and execute batches individually
when a GO is encountered. See the link below for an example. Another
method is to use SQL-DMO to execute scripts.
[url]http://groups.google.com/group/comp.databases.ms-sqlserver/msg/3e7809e7eeb4cc95[/u
rl]
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <jrugo@.patmedia.net> wrote in message
news:ujWlCQZzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I am trying to figure out why the following fails and how to fix it:
> I have a .NET Client application that passes parameters to stored
> procedures and uses dynamic SQL that is passed to the Server. Everything
> is great. My problem is if I want to send over to the server several DML
> in one string.
> e.g.
> ----
> USE Master
> GO
> IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
> DROP PROC sp_XXXXX'
> GO
> Create Procedure sp_XXXXX
> ..
> ..
> ..
> etc...
> GO
> ----
> Passing this type of string to SQL fails. Get error regarding the "GO"
> key words, "Create Procedure Must be the first statement, etc...
> Is it not possible to pass Multiple DML statements to SQL Server in one
> string? If I send each DML statement alone, without the "GO" key word it
> does work; but not all together.
> Thanks for any insight,
> John.
>sql

Passing DataSet's into Custom Code

Is it possible to send a DataSet to a Custom code from the report. I am
planning to use .net dll's in my report as a custom code.
ThanksIf you mean an RS dataset you can't because RS datasets are not exposed
programatically. Some workarounds you may consider:
1. Retrieve an ADO.NET dataset in your code using the same query. In other
words, clone the dataset.
2. If the report has a matrix region, you can redirect the matrix region to
read its values from an embedded function. In this way you can populate a
custom data structure, e.g. an Array or an ADO.NET dataset, which you can
pass the external assembly. As a practical example, you may want to look at
my report sample "Sales by Product Category" which collects the matrix
region data in an array and sends it off to an external assembly to get
forecasted sales.
http://www.manning-sandbox.com/thread.jspa?threadID=10383&tstart=0
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"aplearner" <aplearner@.discussions.microsoft.com> wrote in message
news:0DC175E9-0ECE-47D1-A266-EA39E238F998@.microsoft.com...
> Is it possible to send a DataSet to a Custom code from the report. I am
> planning to use .net dll's in my report as a custom code.
> Thanks|||What are you trying to accomplish? If you want to calculate additional columns based on data in
previous columns within the same row, look into calculated fields. If you want more control over
the dataset manipulation, you can write your own Data Processing Extension which reads in the
dataset and performs whatever logic you want to do on it prior to submitting the data to the report.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"aplearner" <aplearner@.discussions.microsoft.com> wrote in message
news:0DC175E9-0ECE-47D1-A266-EA39E238F998@.microsoft.com...
> Is it possible to send a DataSet to a Custom code from the report. I am
> planning to use .net dll's in my report as a custom code.
> Thanks|||For creating your own DPE, start by searching Google Groups [1]. I know there are a few samples out
there.
Without writing a DPE, if you have a dataset with two columns returned (Quantity and Price) and you
need a third column computed (Total), you can either add a column to your table with this
expression:
=Fields!Quantity.Value * Fields!Price.Value
or you can right-click in the Fields window in Report Designer and choose Add Field. You can create
a new computed field named Total and tell it to always have a value equal to the above expression.
To sort, you should attempt to sort the data in the database prior to being sent to the report. If
that isn't possible, look in our Books Online for more information on how to Sort rows shown in
tables.
[1]
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.reportingsvcs
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"aplearner" <aplearner@.discussions.microsoft.com> wrote in message
news:995C993F-D0CE-4224-8F25-29EFE1E555F3@.microsoft.com...
> Donovan
> I have two ques,
> Ques1:
> Can you explain me how to do this[you can write your own Data Processing
> Extension]. My situation is, my dataset would return 6 rows. Before display
> these rows in my report using table, i have to assign one of the column
> values in 6 text boxes.
> Ques2:
> Is it possible to suffle/sort my dataset by perticluar column value[s] prior
> to submitting the data to the report ?
> Thanks in advance
>
> "Donovan R. Smith [MSFT]" wrote:
> > What are you trying to accomplish? If you want to calculate additional columns based on data in
> > previous columns within the same row, look into calculated fields. If you want more control
over
> > the dataset manipulation, you can write your own Data Processing Extension which reads in the
> > dataset and performs whatever logic you want to do on it prior to submitting the data to the
report.
> >
> > --
> > Thanks.
> >
> > Donovan R. Smith
> > Software Test Lead
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "aplearner" <aplearner@.discussions.microsoft.com> wrote in message
> > news:0DC175E9-0ECE-47D1-A266-EA39E238F998@.microsoft.com...
> > > Is it possible to send a DataSet to a Custom code from the report. I am
> > > planning to use .net dll's in my report as a custom code.
> > >
> > > Thanks
> >
> >
> >sql

Passing complex parameters

Is it possible to pass a .net DataSet to a Report, and have that used as
data within the report?
I have a dynamically generated list of items in a .net DataSet that I
need to pass to a top-level report which then calls subreports for each
item.
I'm not sure how you can process such a parameter in the report -
obviously its just an xml string, but how can it be re-rendered to data.
Alternatively I could pass a comma separated string, but the same
question arises. An example would be great (he said hopefully).
Also, is there a limit to the size of a string parameter?
brian smithI saw someone doing something similar at the PASS conference last week...
I'll tell you what I remember and hope it helps... They were using a Web
Service as a data source.
They chose XML as the data provider, then in the connect string pointed to
the Web service.. asmx
Then in the query for the data source, they did an XML query, passing in the
SOAP query, which was copied from the web services description...It worked
for him, but I suspect it would take me a while to duplicate what he had
done...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Brian Smith" wrote:
> Is it possible to pass a .net DataSet to a Report, and have that used as
> data within the report?
> I have a dynamically generated list of items in a .net DataSet that I
> need to pass to a top-level report which then calls subreports for each
> item.
> I'm not sure how you can process such a parameter in the report -
> obviously its just an xml string, but how can it be re-rendered to data.
> Alternatively I could pass a comma separated string, but the same
> question arises. An example would be great (he said hopefully).
> Also, is there a limit to the size of a string parameter?
> brian smith
>|||This is in 2005. The only way in 2000 is to write a data extension.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D9FA7760-2834-4F09-A4D1-2649FFCB89F5@.microsoft.com...
>I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
> Then in the query for the data source, they did an XML query, passing in
> the
> SOAP query, which was copied from the web services description...It
> worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Brian Smith" wrote:
>> Is it possible to pass a .net DataSet to a Report, and have that used as
>> data within the report?
>> I have a dynamically generated list of items in a .net DataSet that I
>> need to pass to a top-level report which then calls subreports for each
>> item.
>> I'm not sure how you can process such a parameter in the report -
>> obviously its just an xml string, but how can it be re-rendered to data.
>> Alternatively I could pass a comma separated string, but the same
>> question arises. An example would be great (he said hopefully).
>> Also, is there a limit to the size of a string parameter?
>> brian smith|||Hi Wayne,
I was at PASS last week as well, and saw those demos. This SQL Server 2005
Books Online entry discusses XML data sources for Reporting Services, but I
can't get the example at the end (which simply lists reports from the Report
Server) to work. The dataset query returns an error like "Failed to execute
web request for the specified URL". Maybe Brian Welcker or Bruce L-C can
comment/help?
- Deepak
http://msdn2.microsoft.com/en-us/library/ms228288
SQL Server 2005 Books Online
Retrieving XML Data
XML documents are a supported data source type for reports that you build
and run in SQL Server 2005 Reporting Services. An XML data processing
extension is provided to retrieve and process the data.
...
Example: Retrieving XML Data from a Web service
To build the query, use the generic query designer. Use the following steps
to learn how to specify the connection and return XML data from a Web
service. In this example, the dataset returns items from the report server
database.
...
"Wayne Snyder" wrote:
> I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
> Then in the query for the data source, they did an XML query, passing in the
> SOAP query, which was copied from the web services description...It worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Brian Smith" wrote:
> > Is it possible to pass a .net DataSet to a Report, and have that used as
> > data within the report?
> > I have a dynamically generated list of items in a .net DataSet that I
> > need to pass to a top-level report which then calls subreports for each
> > item.
> > I'm not sure how you can process such a parameter in the report -
> > obviously its just an xml string, but how can it be re-rendered to data.
> > Alternatively I could pass a comma separated string, but the same
> > question arises. An example would be great (he said hopefully).
> >
> > Also, is there a limit to the size of a string parameter?
> >
> > brian smith
> >|||Thanks, but as other have commented, thats for SQL2005. In fact, its not
quite what I want - the datasource is SQL Server, I just want to pass a
list of values as a parameter. Seems SQL2005 will support multi-value
parameters, which will do what I want, but that's no help at present.
I've solved the problem by saving the parameter data into a work table
in the database. The report can then query this using a single passed id
parameter.
brian smith
Wayne Snyder wrote:
> I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
> Then in the query for the data source, they did an XML query, passing in the
> SOAP query, which was copied from the web services description...It worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>|||You can download the dataset extension as described belo
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp
But again the paramters are still an issue the BoL does not identifues clearly
"Brian Smith" wrote:
> Is it possible to pass a .net DataSet to a Report, and have that used as
> data within the report?
> I have a dynamically generated list of items in a .net DataSet that I
> need to pass to a top-level report which then calls subreports for each
> item.
> I'm not sure how you can process such a parameter in the report -
> obviously its just an xml string, but how can it be re-rendered to data.
> Alternatively I could pass a comma separated string, but the same
> question arises. An example would be great (he said hopefully).
> Also, is there a limit to the size of a string parameter?
> brian smith
>

Passing Column Name as parameter to sql store procedure

i am using asp.net 2005 with sql server 2005. in my database table contains

Table Name : Page_Content

Page_Id

1011021AbcPqr2Lmnoiu

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,@.aFrompage_content

end

Here in this above store procedure i want to pass 101 to @.lang_code

here is my output, but this is wrong output

Page_Id

Column111012101

but i want following output

Page_Id

1011021AbcPqr2Lmnoiu

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

|||

aadreja:

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

The above code is subject to sql injection attacks. Query on sql injection attacks if you don't know what they are.

As coded, someone could force your page to reveal sensitive data in other tables, or alter or destroy data in your database in ways you do not want to allow.

Given that a column name has very specific naming rules, you can test that the value you get in @.a is a plausible, safe column name.

If @.a has any character other than a letter from a-z, A-Z or 0-9, you should trap that and raise an error.

One way to test is to make a copy of @.a and remove all the valid characters. If nothing is left in the copy, it's a safe column name to process.

|||

I know you could do this with dynamic sql, but that's not always the best solution.

I think you could use a CASE/WHEN block to do what you are wanting. Each field would have to be known in advance, this wouldn't work "on the fly" if you add new columns to the table without updating the SP

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,
CASE
WHEN @.a = '101' THEN 101
WHEN @.a = '102' THEN 102
ELSE 101 -- you don't need an else, but this query will fail in a syntax error if the input doesn't match one of your defined values.
END Frompage_content

end

|||

I agree!

Benefits of your approach:

Sql Injection safe

passing boolean to stored proc as SQLDBtype.bit not working

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@.bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.If you can execute the stored procedure with bit value in database, that means the value passed from application is not right. You may check the input value by inserting into a temp. table or as a return value to the calling app.|||thank you, that is what I thought.

I have printed out the value of the param in the trace and it is 0 which I find odd as that should be accepted as a valid bit, unless I'm totally missing something.

Is there not a way to pass 'true' or 'false' to the SQLDBtype.bit and have SQL Server convert it to 1 or 0?

still working on it here.
thanks again for your input|||What kind of control is "rblProMembCSNM"?|||thank you,

I took your advice and created a temp table and insert all of my values, after all that it was a different parameter I was passing and I was not escaping the ' ...... very frustrating to find that out after everything but I did learn quite a bit in my research.

passing an int parameter

Hello,

This is the way I call report viewer in my asp.net application.

this.ReportViewer1.ServerUrl=serverUrl;

this.ReportViewer1.ReportPath=repName;

this.ReportViewer1.Parameters=Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

this.ReportViewer1.SetQueryParameter("MyName",myname);

this.ReportViewer1.SetQueryParameter("MyID",myID);

MyName is a string and MyID is an int. Without MyID parameter it is working fine, however, when I add MyID it does not work, so how can I pass an int parameter to reporting services.

Thanks,

Cast its type as an integer

Passing ADO.Net dataset to RS 2005

Hi,
I would like to know how to pass ado.net dataset to reporting services 9.
I´ve found this article
http://msdn.microsoft.com/data/archive/default.aspx?pull=/library/en-us/dnsql2k/html/rsdsetex3.asp
but, acording to the sumary, it does apply to "SQL 2000 - Reporting
Services".
Could anyone help me'
Any help will be appreciated.
thanx a lotHi Ricardo,
the same article code works for RS 2005 also.
There will be only few changes like pointing to 2005 RS assemblie references..
just point the Microsoft.ReportingServices.Interfaces.dll to the 2005RS dll
and it will work..
-Bava
"Ricardo" wrote:
> Hi,
> I would like to know how to pass ado.net dataset to reporting services 9.
> I´ve found this article
> http://msdn.microsoft.com/data/archive/default.aspx?pull=/library/en-us/dnsql2k/html/rsdsetex3.asp
> but, acording to the sumary, it does apply to "SQL 2000 - Reporting
> Services".
> Could anyone help me'
> Any help will be appreciated.
> thanx a lot
>
>

Passing ADO.Net Dataset to Reporting Service

Hi...
Is the Reporting Service support ADO.NET dataset that pass from application
(like Crystal Report)?
Thanks
NickyIn RS 2000 you would have to write a data processing extension. In RS 2005
you can get a winform and webform control via Visual Studio 2005. You can
use this control either in local or remote mode. Server mode uses reports on
the server and you get the whole Report Manager user interface. If you use
it in local mode you provide the recordset, you also handle all the user
interface for the parameters. I am using this some and it works well but it
is definitely more work than use Reporting Services either directly using
the portal (Report Manager) that ships with the product or using the new
controls in server mode.
The best way I believe to develop with reporting services is to consider RS
to be a service you are using. The service encapsulates and handles
everything you need. You provide the queries. If it is more difficult then
instead of queries use stored procedures. Let Reporting Services handle
things. RS does things in a different way that Crystal but once you get used
to it you should find that RS can be quite fast and powerful for you.
So, the answer is yes you can with the new controls but you should first
evaluate whether you should.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicky" <Nicky@.discussions.microsoft.com> wrote in message
news:D72F6395-C241-4014-A330-DAA933FFF3EB@.microsoft.com...
> Hi...
> Is the Reporting Service support ADO.NET dataset that pass from
> application
> (like Crystal Report)?
> Thanks
> Nicky|||Hi Bruce,
Thanks for reply.
My case is like this, I need to pass in a dataset from my application to my
report. and view the report over my application.
The problem is,
1. I need to write my own Xml to built that report by coding? Or, is that
other way I can built up the report like normal, and just pass the dataset to
the to my report?
2. How to open a report from the application without go through the Report
Manager?
3. Do you have any example about this 2 question?
Thanks
Regards,
Nicky|||You do not need to create the report on the fly. Much harder and
unnecessary. Two ways to do this. One, is you create a data processing
extension. This is non-trivial but definitely doable and works for 2000 and
2005. In 2005 the easiest thing you could do is to use the new controls from
Visual Studio in local mode. You hand the control a report and a dataset
(really a table from the dataset) and away it goes. There is more work
involved. For instance, dealing with subreports, any jump to reports etc.
As far as opening the report without going through report manager, you can
use URL integration, you can roll your own using web services or you can use
the new controls from VS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicky" <Nicky@.discussions.microsoft.com> wrote in message
news:3DCCBA4C-DF03-40B1-AF56-045DF85DF88B@.microsoft.com...
> Hi Bruce,
> Thanks for reply.
> My case is like this, I need to pass in a dataset from my application to
> my
> report. and view the report over my application.
> The problem is,
> 1. I need to write my own Xml to built that report by coding? Or, is that
> other way I can built up the report like normal, and just pass the dataset
> to
> the to my report?
> 2. How to open a report from the application without go through the Report
> Manager?
> 3. Do you have any example about this 2 question?
> Thanks
> Regards,
> Nicky
>|||Also, is there a reason that you have to hand the recordset to the report.
Unless you are doing some major business logic to obtain the recordset I
recomment having a query using parameters and then you pass the parameters
to the report (you can do this via the controls (winform and webform) or
using URL integration. RS retrieves and renders the data. Many times people
want to pass the recordset just because that is how they are used to doing
things. It is better to solve the problem using RS the way it is designed if
at all possible. No reason to make matters more complicated. RS is designed
to be tightly integrated with your app, that is not a problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicky" <Nicky@.discussions.microsoft.com> wrote in message
news:3DCCBA4C-DF03-40B1-AF56-045DF85DF88B@.microsoft.com...
> Hi Bruce,
> Thanks for reply.
> My case is like this, I need to pass in a dataset from my application to
> my
> report. and view the report over my application.
> The problem is,
> 1. I need to write my own Xml to built that report by coding? Or, is that
> other way I can built up the report like normal, and just pass the dataset
> to
> the to my report?
> 2. How to open a report from the application without go through the Report
> Manager?
> 3. Do you have any example about this 2 question?
> Thanks
> Regards,
> Nicky
>|||Hi Bruce
thanks for your recommendation. But, I need to pass in my own dataset
through my application because it include too many business logic.
Anywhere, i have able to find the example:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
thanks.
Nicky

passing a binary value to sql server 2005

I have been pulling my hair out on this.

I am trying to pass a binary value from an ASP.NET app to a VERY simple stored proc, and I can NOT get it to work!

Here is some of my code:

Code Snippet

ALTERPROCEDURE [dbo].[sp_SelectAppointments] @.PID VarChar(50)

AS

BEGIN

--set @.PID = 0x00000000000000B1

--PLEASE NOTE HERE THAT THE ABOVE VALUE(WHEN UNCOMMENTED) RETURNS RECORDS

--WHEN THE VALUE IS IN QUOTES (AS IT WOULD BE) IT DOES NOT WORK

SETNOCOUNTON;

SELECT Appt_Date, Appt_Description, Rn_Appointments_Id

FROM Rn_Appointments

WHERE project = @.PID --Project is a binary field.

END

I created a Dataset in VS2005, and here is the code that passes the value to the Dataset:

Code Snippet

Dim PID As String = Request.QueryString("pid")

PID = "0x00000000000000B1"

Dim da2 AsNew sp_SelectAppointmentsTableAdapter

GridView1.DataSource = da2.GetData2(PID)

GridView1.DataBind()

What am I missing? Can anybody please help me out here?

Thank you.

Steve

You are capturing the incoming parameter as varchar() and they attempting to equate a varchar() to a binary in the WHERE clause filter.

Have you attempted with a binary input parameter datatype?

Or, convert the parameter to binary inside the procedure?

ALTERPROCEDURE [dbo].[sp_SelectAppointments] @.PID binary

or

WHERE project = cast( @.PID as binary )

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Pass UserName to the SQL Select ? (VB)

VB ASP.NET 2.0

How do I get the Authenticated UserName passed to a Select Command of a data control ?

I'm not clear on how to get the logged in username fromUser.Identity.Name as a string or how to pass it to the Select Command of a control.

I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL.

I want to show the user data from a database, based on who is logged in .

Help, greatly appreciated.

Chris

User.Identity.Name will hold the value of the currently authenticated user. How you deal with parameters depends on how you approach it. with a straightforward Sql command, the following is a guide:

Dim query As String = "Select * From Users WHERE UserName = @.UserName"
Dim conn As New SqlConnection(myconnectionstring)
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@.UserName",User.Identity.Name)
conn.Open()
Dim rdr As New SqlDataReader = cmd.ExecuteReader()
While rdr.Read()...

If you want to use a SqlDataSource, this article should help:http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

|||

valenumr:

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

But that'll get the SQL user, which probably won't be the same as the user logged on to the website..

Regards

Fredr!k

|||

Very true... it will work if your DB is using windows login's, and you don't have any impersonation going on in your web server... so I guess, I should have mentioned that.

|||

Thanks to all the rapid replies.

An error had lead me to believe that the User.Identity.Name did not return a string, but further research says it should.

I should be clearer about the approach I was attempting. Given that I'm struggling to learn .Net and ASP.Net I'm struggling to keep the issues clear.

I was trying to find a way to do the assignment in the Mark Up for the server controls.

I have found that you can also create a Parameter in the mark up for the SqlSourceControl section under the <SelectParameters> section that you can then use in the Select Command.

Still trying to tie that info together to do this.

BUT , your cmd.Parameters.AddWithValue method is eye opening. When I start working with code, I'm still trying to learn the models to understand whichevents to put such code.

(Anyone know a concise reference for this ? )

I want a dropdown box to be filled with a set of items from a database that are particular to the User that's logged on.

(ps. I find from my research that you can use My.User.Name , also to get the username)

Many Thanks for all the help !

Chris9876

|||

I'm not at my computer to verify, but try User.Identity.Name.tostring If that doesn't work, I will check when I get home as I do this exact thing, but with the userid and not the username.

|||

This is from memory, but you can create a parameter in your sqldatasource (Let's call it @.Username).

Then in the SqlDataSource1_Selecting event, just put:

e.Commad.Parameters("@.UserName").Value= My.User.Name

Then whenever the sqldatasource is about to do a select, you set the username parameter to whomever is currently logged it. Simple and easy.

If you are changing the output based on the persons roles, or if they are currently logged in, you might want to just use a loginview control.

|||

The following Markup and Event Code works great. (DropdownList is filled with data from MyField whereUserName=@.UserName)

Thank you all very much.

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"DataTextField="ClassCode"DataValueField="ClassCode">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [MyField] FROM [MyView] WHERE ([UserName] = @.UserName)">

<SelectParameters>

<asp:ParameterName="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)Handles SqlDataSource1.Selecting

e.Command.Parameters("@.UserName").Value =My.User.Name

EndSub

pass string to stored procedure

I have a table that has the follow format
PageID (integer)
Member_Id (varchar(20))
in my VB.net application, i have two strings that that take the following
format
Pages="2,2,3,4,4"
Members= "member1,member2,member3,member4,member5"
is there any way to pass the string variables to a stored procedure and
perform some type of insert into statement?
i would want to do something like this in a stored procedure
insert into AccessTablePages (Member_Id,PageID)
values (@.Members, @.Pages)You should be able to adapt the methods in Erland Sommarskog's article here:
http://www.sommarskog.se/arrays-in-sql.html
Steve Kass
Drew University
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:8EB1C476-23D7-4D56-9480-E69431317A46@.microsoft.com...
>I have a table that has the follow format
> PageID (integer)
> Member_Id (varchar(20))
> in my VB.net application, i have two strings that that take the following
> format
> Pages="2,2,3,4,4"
> Members= "member1,member2,member3,member4,member5"
> is there any way to pass the string variables to a stored procedure and
> perform some type of insert into statement?
> i would want to do something like this in a stored procedure
>
> insert into AccessTablePages (Member_Id,PageID)
> values (@.Members, @.Pages)
>|||Looks promising.
thanks1
"Steve Kass" wrote:

> You should be able to adapt the methods in Erland Sommarskog's article her
e:
> http://www.sommarskog.se/arrays-in-sql.html
> Steve Kass
> Drew University
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:8EB1C476-23D7-4D56-9480-E69431317A46@.microsoft.com...
>
>

pass large xmlstring to sql server 2000 stored procedure

Hi,
Can I use DBType.XML in .net 2.0 to pass xml string to a stored procedure in sql server 2000 ?
It's just that I know sql 2000 does not have xml type. If this is true then how is it possible to pass very large xml string to a stored procedure parameter? Remember that the xml strings may be more than 10000 characters.
At present, using .net 2.0, I am passing xml string using DbType.AnsiString with length of 50,000 and the stored procedure parameters that accepts the xmlstring is of type text.
I tried using DbType.XML in .net 2.0 but noticed that sql 2000 does not accept it.
Do you also know the max length of AnsiString?
Your thoughts please?

Thanks you

Hi,

Is there a specific reason why you need to pass the xml as one big string? i.e. Are you just writing the xml data to be stored as an xml document as a whole or can you break it up into "chunks"?

Breaking it up would require more time for the multiple calls to the database, but would solve the problem of length of string size as even 2005 has a max length.

Chris

|||Hi,
I do not wish to break the xml into smaller pieces.
Please note that my question is just to do with passing a large xmlstring.
If it is ok, please read my first post to see exactly what I am after.
Thanks for replying

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.

Pass a parameter to SSIS from .NET App

I'd like to pass a parameter value from a ASP.NET application to an SSIS package. My SSIS package pulls data from SQL and loads it a flat file based on the parameter value.

Is this possible?

Very much possible.

Check out the example @. http://www.codeproject.com/useritems/CallSSISFromCSharp.asp

More details on "Loading and Running a Package Programmatically " @. http://msdn2.microsoft.com/en-us/library/ms136090.aspx

Thanks,
Loonysan

|||Sweet! Thanks :)