Passing Impersonation through the ReportViewer IFrame?

I am trying to impersonate a user through the web.config of the webapp that
I'm using to view reports. Reports are pulled back through the ReportViewer
object, and so, pulled back through an IFrame.
I impersonate the user which we set up in the report server, so that we can
bypass windows authentication dialog in the webapp and have impersonation do
this for us, so we can go straight into the report. When a user tries to go
to the service directly they get the prompt, and so are restricted. This is
what I'm after.
But, when I hit the report gen button on the Report Viewer it still prompts
for user/pass in a windows dialog. Is impersonation lost through the
IFrame? Does anyone have a better solution?
Appreciated,
Matt SIt sounds like you're creating a custom report manager. The call to the
report URL is separate from the call to the report manager; they are
separate web apps on the same server. In the Microsoft forms authentication
sample code, they deal with this issue by sharing session information
between the report manager and report server via an auth cookie.
I'm not sure exactly how this will apply to your situation, but hopefully
that helps you at least start tracking down the issues. See this article
for more information on the security interaction between the report manager
and report server:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Matt Swift" <matthewswift@.deletethisbitplshotmail.com> wrote in message
news:OuzKQpd4EHA.1524@.TK2MSFTNGP09.phx.gbl...
>I am trying to impersonate a user through the web.config of the webapp that
> I'm using to view reports. Reports are pulled back through the
> ReportViewer
> object, and so, pulled back through an IFrame.
> I impersonate the user which we set up in the report server, so that we
> can
> bypass windows authentication dialog in the webapp and have impersonation
> do
> this for us, so we can go straight into the report. When a user tries to
> go
> to the service directly they get the prompt, and so are restricted. This
> is
> what I'm after.
> But, when I hit the report gen button on the Report Viewer it still
> prompts
> for user/pass in a windows dialog. Is impersonation lost through the
> IFrame? Does anyone have a better solution?
> Appreciated,
> Matt S
>sql

passing data to SQL database

hello everybody,

i hope u can help me with this... i want to pass the data entered in a web page to a stored procedure so as to store the data in a database... am using three layered architecture... how can i do this...

thanks in advance..........

Look at my reponse in thread http://forums.asp.net/thread/1636021.aspx. There are stored procedures there to create insert, update, listall, select and delete together with the required wrapper code in either C# or vb.net. The required C# library is there. I will post an updated Vb.NET version after my MSDN subscription gets delivered.

passing credentials from asp.net app to RS

Hello All,

I am embedding reports in a Web Application using the ReportViewer control. The Web Application and the ReportServices reside on different machines on the same network. The settings on the ReportServices IIS are Windows Integrated Authentication and anonymous access is disabled.

When I access the reports from my Web Application, I get windows pop-up asking for credentials. I am using impersonation to pass the credentials to the reporting services. But somehow the credentials are not passed to the report server and the pop-up shows up always. I am trying to get rid of the pop-window. Can somebody help??

Does using any other forms of authentication help?

THanks
Imran

How are you displaying the report in your web app? Just a link, or something different? I'm thinking this might be the double hop problem, so you might want to impersonate a service account that's specific for that report.|||I am displaying the report using the reporviewer control.
I am using impersonation in my web app. The same account also exists in the Report server machine. You are right I think it is a double hop problem. But I dont know how to solve it. I think we should use the Soap method to display the reports instead of the URL Access method
Thanks
Imran|||

With the report viewer impersonation settings don't matter - the report viewer sends the client's browser directly to the report server. Is everyone on the domain? Is the client browser setup to login automatically for the zone the report server is in?

|||

What you need is called Pass Through Authentication. Try the links below for more info. Hope this helps.
http://www.iisanswers.com/articles/enablepassthrough.htm

http://www.codeproject.com/aspnet/PassThroughSecurity.asp

Passing complex parameters

Deepak-
"Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
(1) Permissions (The data provider is probably using integrated security)
(2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
(3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
(4) Set IgnoreNamespaces="true" in the query
Hope this helps.
Jon Heide
SQL Server Reporting Services
--Original Message--
From: Deepak@.discussions.microsoft.com
Posted At: Wednesday, October 05, 2005 2:06 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Passing complex parameters
Subject: RE: Passing complex parameters
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
> >Jon,
Thanks for those tips. I've got a SharePoint Lists Web Service (using the
"GetListCollection" method without parameters) source working, but still
can't get results from other methods with parameters. Next I'm going to try
this sample "Report Manager" report from Brian Welcker's blog, which should
be similar to the SQL Server 2005 BOL XML sample:
http://home.ripway.com/2005-6/332074/ReportManager.rdl
"Jon Heide" wrote:
> Deepak-
> "Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
> (1) Permissions (The data provider is probably using integrated security)
> (2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
> (3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
> (4) Set IgnoreNamespaces="true" in the query
> Hope this helps.
> Jon Heide
> SQL Server Reporting Services
>
> --Original Message--
> From: Deepak@.discussions.microsoft.com
> Posted At: Wednesday, October 05, 2005 2:06 PM
> Posted To: microsoft.public.sqlserver.reportingsvcs
> Conversation: Passing complex parameters
> Subject: RE: Passing complex parameters
>
> 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
> > >
>

Passing an array of values for a single parameter

I am using a reportviewer control on my web form and I have created a parameter in my report. I can pass a hardcoded parameter and it works for one values at a time, but I want to send an array of values for the same parameter. For example if I have 5 different fleets of aircaft I might want to see 1,2 or all of the fleet in this report. I am kind of new to SQL reporting any help would be great.

Thanks in advance

you can pass an array but you need to do some processing in the RS designer..under the Data tab. or you can do it all in the stored proc it self by setting up the parameter as varchar and doing the splitting inside..|||

Thanks I will look into RS Designer Data Tab. Is there some sample code available the performs this particular function.

|||check out my blog..there is an article about parsing an array..there is also a link to a better article..it involves using UDFs. It is prbly a better and more efficient approach from the stored proc itself.|||

Could I use a parameter collection to pass an Array of Parameters to a report, if so how would I code that?

Thanks

|||prios did you get this working? If so, could you post your solution? I would also be interested in seeing how you passed a single parameter using the report viewer.
Thanks.|||

I found a solution...

http://odetocode.com/Articles/128.aspx

Passing a parameter into a LIKE command in a stored procedure...

Hi All,
I am having quite a bit of trouble getting a certain stored procedure to
work properly. In my web page I have a list of checkboxes that post to the
url. When I retrieve the value for the "beds" checkbox list from the url it
looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
Proc and use a LIKE command to find any property where (beds LIKE
'[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
parameter gets passed into the stored procedure from the web page, it gives
no errors and does not retrieve any properties. Please take a look at my
stored procedure below and tell me where I may be going wrong. I've
simplified it so that it is less convoluded
CREATE PROCEDURE [sp_SearchResults_v2]
(@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
nvarchar(2), @.a3 nvarchar(2),
@.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
nvarchar(2), @.a9 nvarchar(2),
@.a10 nvarchar(2), @.Kids int)
AS
--declare area variables--
DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
@.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
-- check for null entries and convert to a select all entry Area --
IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null) AND
(@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND (@.a9
is null) AND (@.a10 is null))
BEGIN
set @.area1 = N'%'
set @.area2 = N''
set @.area3 = N''
set @.area4 = N''
set @.area5 = N''
set @.area6 = N''
set @.area7 = N''
set @.area8 = N''
set @.area9 = N''
set @.area10 = N''
END
ELSE
BEGIN
set @.area1 = @.a1
set @.area2 = @.a2
set @.area3 = @.a3
set @.area4 = @.a4
set @.area5 = @.a5
set @.area6 = @.a6
set @.area7 = @.a7
set @.area8 = @.a8
set @.area9 = @.a9
set @.area10 = @.a10
END
SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag, Archive_Flag,
Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
Property_Address, Property_Country,
Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
Dwelling_Code, Pet_Type_code,
Suite_Type_Code, Date_avalible, Property_Rental_Period,
Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
Property_phone2,
Property_ext, First_name, Last_name, Email_address,
CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
Property_houseno, Property_stdesignation,
Property_stname, cross_stno, cross_stname, cross_stdesignation,
CorrectedPhone1, CorrectedPhone2,
Property_ID
FROM dbo.Propeties
WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
AND
(Property_Bedrooms LIKE @.beds)
AND
(City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code =
@.area3 OR City_Area_Code = @.area4
OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code =
@.area7 OR
City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code =
@.area10)
AND
(Kids_results = 1)
AND
(Prop_Del_Flag <> 1)
ORDER BY Rent_Amount
GONow I don't have table descripts etc. so I made a very simple proc.
create proc dbo.NEW_Procedure @.input as varchar(250)
AS
select
ArtistName
,SongName
,Genre
,FileName
,Duration
,FirstLetter
,AlbumName
from RadioFreeLlloyd.dbo.SongInfo
where ArtistName like @.input
When I input any value with either wildcards or not it works. Perhaps you
could create a test table with the same columns as you have variables and
then insert a row when a call is made to the proc. It would seem that that
the values of the parameters are perhaps not what you think.
"Fabio Papa" <fapapa@.hotmail.com> wrote in message
news:8YfPb.2394$2j7.752@.clgrps13...

quote:

---

> Hi All,
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to

---

the

quote:

---

> url. When I retrieve the value for the "beds" checkbox list from the url

---

it

quote:

---

> looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
> Proc and use a LIKE command to find any property where (beds LIKE
> '[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
> LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
> parameter gets passed into the stored procedure from the web page, it

---

gives

quote:

---

> no errors and does not retrieve any properties. Please take a look at my
> stored procedure below and tell me where I may be going wrong. I've
> simplified it so that it is less convoluded
> CREATE PROCEDURE [sp_SearchResults_v2]
> (@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
> nvarchar(2), @.a3 nvarchar(2),
> @.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
> nvarchar(2), @.a9 nvarchar(2),
> @.a10 nvarchar(2), @.Kids int)
> AS
> --declare area variables--
> DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
> nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
> @.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
> -- check for null entries and convert to a select all entry Area --
> IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null)

---

AND

quote:

---

> (@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND

---

(@.a9

quote:

---

> is null) AND (@.a10 is null))
> BEGIN
> set @.area1 = N'%'
> set @.area2 = N''
> set @.area3 = N''
> set @.area4 = N''
> set @.area5 = N''
> set @.area6 = N''
> set @.area7 = N''
> set @.area8 = N''
> set @.area9 = N''
> set @.area10 = N''
> END
> ELSE
> BEGIN
> set @.area1 = @.a1
> set @.area2 = @.a2
> set @.area3 = @.a3
> set @.area4 = @.a4
> set @.area5 = @.a5
> set @.area6 = @.a6
> set @.area7 = @.a7
> set @.area8 = @.a8
> set @.area9 = @.a9
> set @.area10 = @.a10
> END
> SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag,

---

Archive_Flag,

quote:

---

> Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
> Property_Address, Property_Country,
> Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
> Dwelling_Code, Pet_Type_code,
> Suite_Type_Code, Date_avalible,

---

Property_Rental_Period,

quote:

---

kred">
> Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
> Property_phone2,
> Property_ext, First_name, Last_name, Email_address,
> CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
> Property_houseno, Property_stdesignation,
> Property_stname, cross_stno, cross_stname, cross_stdesignation,
> CorrectedPhone1, CorrectedPhone2,
> Property_ID
> FROM dbo.Propeties
> WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
> AND
> (Property_Bedrooms LIKE @.beds)
> AND
> (City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code

---

=

quote:

---

> @.area3 OR City_Area_Code = @.area4
> OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code =
> @.area7 OR
> City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code =
> @.area10)
> AND
> (Kids_results = 1)
> AND
> (Prop_Del_Flag <> 1)
> ORDER BY Rent_Amount
> GO
>

---

|||Fabio Papa (fapapa@.hotmail.com) writes:

quote:

---

> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
> the url. When I retrieve the value for the "beds" checkbox list from
> the url it looks something like "1,3,5,7". Now I want to pass this into
> a SQL Stored Proc and use a LIKE command to find any property where
> (beds LIKE '[1,3,5,7]'). When I use the query analyzer and manually
> type "WHERE beds LIKE '[1,3,5,7]'" it works fine and pulls up
> properties, but when the parameter gets passed into the stored procedure
> from the web page, it gives no errors and does not retrieve any
> properties. Please take a look at my stored procedure below and tell me
> where I may be going wrong. I've simplified it so that it is less
> convoluded

---

Maybe you simplifed too much? Save the spelling error for the table
it looks OK, but I don't know what values you are passing.
I would suggest that you trim the conditions until you get values
back. It may not be the LIKE expression that is causing you headache.

quote:

---

> CREATE PROCEDURE [sp_SearchResults_v2]

---

Ah, by the way, the sp prefix is reserved for system procedures and
SQL Server will first look for these names in master. You should not
use it for your own names.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing a parameter into a LIKE command in a stored procedure...

Hi All,
I am having quite a bit of trouble getting a certain stored procedure to
work properly. In my web page I have a list of checkboxes that post to the
url. When I retrieve the value for the "beds" checkbox list from the url it
looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
Proc and use a LIKE command to find any property where (beds LIKE
'[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
parameter gets passed into the stored procedure from the web page, it gives
no errors and does not retrieve any properties. Please take a look at my
stored procedure below and tell me where I may be going wrong. I've
simplified it so that it is less convoluded
CREATE PROCEDURE [sp_SearchResults_v2]
(@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
nvarchar(2), @.a3 nvarchar(2),
@.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
nvarchar(2), @.a9 nvarchar(2),
@.a10 nvarchar(2), @.Kids int)
AS
--declare area variables--
DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
@.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
-- check for null entries and convert to a select all entry Area --
IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null) AND
(@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND (@.a9
is null) AND (@.a10 is null))
BEGIN
set @.area1 = N'%'
set @.area2 = N''
set @.area3 = N''
set @.area4 = N''
set @.area5 = N''
set @.area6 = N''
set @.area7 = N''
set @.area8 = N''
set @.area9 = N''
set @.area10 = N''
END
ELSE
BEGIN
set @.area1 = @.a1
set @.area2 = @.a2
set @.area3 = @.a3
set @.area4 = @.a4
set @.area5 = @.a5
set @.area6 = @.a6
set @.area7 = @.a7
set @.area8 = @.a8
set @.area9 = @.a9
set @.area10 = @.a10
END
SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag, Archive_Flag,
Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
Property_Address, Property_Country,
Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
Dwelling_Code, Pet_Type_code,
Suite_Type_Code, Date_avalible, Property_Rental_Period,
Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
Property_phone2,
Property_ext, First_name, Last_name, Email_address,
CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
Property_houseno, Property_stdesignation,
Property_stname, cross_stno, cross_stname, cross_stdesignation,
CorrectedPhone1, CorrectedPhone2,
Property_ID
FROM dbo.Propeties
WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
AND
(Property_Bedrooms LIKE @.beds)
AND
(City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code = @.area3 OR City_Area_Code = @.area4
OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code = @.area7 OR
City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code = @.area10)
AND
(Kids_results = 1)
AND
(Prop_Del_Flag <> 1)
ORDER BY Rent_Amount
GONow I don't have table descripts etc. so I made a very simple proc.
create proc dbo.NEW_Procedure @.input as varchar(250)
AS
select
ArtistName
,SongName
,Genre
,FileName
,Duration
,FirstLetter
,AlbumName
from RadioFreeLlloyd.dbo.SongInfo
where ArtistName like @.input
When I input any value with either wildcards or not it works. Perhaps you
could create a test table with the same columns as you have variables and
then insert a row when a call is made to the proc. It would seem that that
the values of the parameters are perhaps not what you think.
"Fabio Papa" <fapapa@.hotmail.com> wrote in message
news:8YfPb.2394$2j7.752@.clgrps13...
> Hi All,
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
the
> url. When I retrieve the value for the "beds" checkbox list from the url
it
> looks something like "1,3,5,7". Now I want to pass this into a SQL Stored
> Proc and use a LIKE command to find any property where (beds LIKE
> '[1,3,5,7]'). When I use the query analyzer and manually type "WHERE beds
> LIKE '[1,3,5,7]'" it works fine and pulls up properties, but when the
> parameter gets passed into the stored procedure from the web page, it
gives
> no errors and does not retrieve any properties. Please take a look at my
> stored procedure below and tell me where I may be going wrong. I've
> simplified it so that it is less convoluded
> CREATE PROCEDURE [sp_SearchResults_v2]
> (@.MinRent int, @.MaxRent int, @.beds NVarChar(250), @.a1 nvarchar(2),@.a2
> nvarchar(2), @.a3 nvarchar(2),
> @.a4 nvarchar(2), @.a5 nvarchar(2), @.a6 nvarchar(2), @.a7 nvarchar(2), @.a8
> nvarchar(2), @.a9 nvarchar(2),
> @.a10 nvarchar(2), @.Kids int)
> AS
> --declare area variables--
> DECLARE @.area1 nvarchar(2), @.area2 nvarchar(2), @.area3 nvarchar(2), @.area4
> nvarchar(2), @.area5 nvarchar(2), @.area6 nvarchar(2), @.area7 nvarchar(2),
> @.area8 nvarchar(2), @.area9 nvarchar(2), @.area10 nvarchar(2)
> -- check for null entries and convert to a select all entry Area --
> IF ((@.a1 is null) AND (@.a2 is null) AND (@.a3 is null) AND (@.a4 is null)
AND
> (@.a5 is null) AND (@.a6 is null) AND (@.a7 is null) AND (@.a8 is null) AND
(@.a9
> is null) AND (@.a10 is null))
> BEGIN
> set @.area1 = N'%'
> set @.area2 = N''
> set @.area3 = N''
> set @.area4 = N''
> set @.area5 = N''
> set @.area6 = N''
> set @.area7 = N''
> set @.area8 = N''
> set @.area9 = N''
> set @.area10 = N''
> END
> ELSE
> BEGIN
> set @.area1 = @.a1
> set @.area2 = @.a2
> set @.area3 = @.a3
> set @.area4 = @.a4
> set @.area5 = @.a5
> set @.area6 = @.a6
> set @.area7 = @.a7
> set @.area8 = @.a8
> set @.area9 = @.a9
> set @.area10 = @.a10
> END
> SELECT Kids_results, Prop_Del_flag, Identity_Code, Sig_Flag,
Archive_Flag,
> Exlusive_Code, Frequency_Code, Rent_Amount, Notes, City_Area_Code,
> Property_Address, Property_Country,
> Property_Postal_Code, Property_Cross_Street, Property_Entry_Date,
> Dwelling_Code, Pet_Type_code,
> Suite_Type_Code, Date_avalible,
Property_Rental_Period,
> Property_Bedrooms, phareacode1, Property_phone1, phareacode2,
> Property_phone2,
> Property_ext, First_name, Last_name, Email_address,
> CITY_PREFIX_CODE, Zonelocale, Sequencer, Landlord_ID, Property_suiteno,
> Property_houseno, Property_stdesignation,
> Property_stname, cross_stno, cross_stname, cross_stdesignation,
> CorrectedPhone1, CorrectedPhone2,
> Property_ID
> FROM dbo.Propeties
> WHERE (Rent_Amount BETWEEN @.MinRent AND @.MaxRent)
> AND
> (Property_Bedrooms LIKE @.beds)
> AND
> (City_Area_Code LIKE @.area1 OR City_Area_Code = @.area2 OR City_Area_Code
=> @.area3 OR City_Area_Code = @.area4
> OR City_Area_Code = @.area5 OR City_Area_Code = @.area6 OR City_Area_Code => @.area7 OR
> City_Area_Code = @.area8 OR City_Area_Code = @.area9 OR City_Area_Code => @.area10)
> AND
> (Kids_results = 1)
> AND
> (Prop_Del_Flag <> 1)
> ORDER BY Rent_Amount
> GO
>|||Fabio Papa (fapapa@.hotmail.com) writes:
> I am having quite a bit of trouble getting a certain stored procedure to
> work properly. In my web page I have a list of checkboxes that post to
> the url. When I retrieve the value for the "beds" checkbox list from
> the url it looks something like "1,3,5,7". Now I want to pass this into
> a SQL Stored Proc and use a LIKE command to find any property where
> (beds LIKE '[1,3,5,7]'). When I use the query analyzer and manually
> type "WHERE beds LIKE '[1,3,5,7]'" it works fine and pulls up
> properties, but when the parameter gets passed into the stored procedure
> from the web page, it gives no errors and does not retrieve any
> properties. Please take a look at my stored procedure below and tell me
> where I may be going wrong. I've simplified it so that it is less
> convoluded
Maybe you simplifed too much? Save the spelling error for the table
it looks OK, but I don't know what values you are passing.
I would suggest that you trim the conditions until you get values
back. It may not be the LIKE expression that is causing you headache.
> CREATE PROCEDURE [sp_SearchResults_v2]
Ah, by the way, the sp prefix is reserved for system procedures and
SQL Server will first look for these names in master. You should not
use it for your own names.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Passing a fields.name.value in the jump to url

I have a web page (asp) that I want to pass the field. value to the url.
ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
this works but i want them to click on a field
="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
no error but no hyper link on the field cellOk i got it figured out the cianum is a (int)field when I "cast(cianum as
varchar(15))as cianum" in my query it worked fine. Is this a bug with
reporting services?
"ljhopkins_LOST and Cant find My Way" wrote:
> I have a web page (asp) that I want to pass the field. value to the url.
> ie. http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum=4285
> this works but i want them to click on a field
> ="http://someinet/Corp/QMS/application/CIA/aspfiles/CIAView.asp?CIANum="+Fields!cianum.Value
> no error but no hyper link on the field cell

Passing a dataset as a parameter?

Can you build a dataset in a web application and send it as a parameter
through the report viewer control and have reporting services use this
dataset as the binding dataset for your table in Reporting Services? IF so,
can you help me or direct me to some article that will show me how to do
this.RS (both 2000 and 2005) don't support binding to applcation datasets
natively. If you need to pass a dataset to a server-generated report, you
may find my custom extension useful
(http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
If you RS 2005 and the Report Viewers, you can bind the dataset to a local
report.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> Can you build a dataset in a web application and send it as a parameter
> through the report viewer control and have reporting services use this
> dataset as the binding dataset for your table in Reporting Services? IF
> so,
> can you help me or direct me to some article that will show me how to do
> this.|||The posts that I am seeing in your article looks like people are having
problems with doing this in Reporting Services 2005. At least the referencing
portion. Could you update with how this is done in 2005 or post where the
AWC.RS.Extensions.dll
is copied in 2005.
Thanks in advance
"Teo Lachev [MVP]" wrote:
> RS (both 2000 and 2005) don't support binding to applcation datasets
> natively. If you need to pass a dataset to a server-generated report, you
> may find my custom extension useful
> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
> If you RS 2005 and the Report Viewers, you can bind the dataset to a local
> report.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> > Can you build a dataset in a web application and send it as a parameter
> > through the report viewer control and have reporting services use this
> > dataset as the binding dataset for your table in Reporting Services? IF
> > so,
> > can you help me or direct me to some article that will show me how to do
> > this.
>
>|||I was also reading that someone was having trouble with using a dataset that
used SQL Query joins with multiple tables or is this not a problem?
"Teo Lachev [MVP]" wrote:
> RS (both 2000 and 2005) don't support binding to applcation datasets
> natively. If you need to pass a dataset to a server-generated report, you
> may find my custom extension useful
> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
> If you RS 2005 and the Report Viewers, you can bind the dataset to a local
> report.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> > Can you build a dataset in a web application and send it as a parameter
> > through the report viewer control and have reporting services use this
> > dataset as the binding dataset for your table in Reporting Services? IF
> > so,
> > can you help me or direct me to some article that will show me how to do
> > this.
>
>|||One day I will probably get to it :-) It shouldn't be that terribly
difficult to upgrade the changed interfaces.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:74683164-56A9-44BD-806D-7C9DBBCAA339@.microsoft.com...
> The posts that I am seeing in your article looks like people are having
> problems with doing this in Reporting Services 2005. At least the
> referencing
> portion. Could you update with how this is done in 2005 or post where the
> AWC.RS.Extensions.dll
> is copied in 2005.
> Thanks in advance
> "Teo Lachev [MVP]" wrote:
>> RS (both 2000 and 2005) don't support binding to applcation datasets
>> natively. If you need to pass a dataset to a server-generated report, you
>> may find my custom extension useful
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> If you RS 2005 and the Report Viewers, you can bind the dataset to a
>> local
>> report.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
>> > Can you build a dataset in a web application and send it as a
>> > parameter
>> > through the report viewer control and have reporting services use this
>> > dataset as the binding dataset for your table in Reporting Services? IF
>> > so,
>> > can you help me or direct me to some article that will show me how to
>> > do
>> > this.
>>|||While the sky is the limit what your CDE can do, RS expects a
two-dimensional resultset.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:3CC6145C-449B-4C11-B5DF-A7A4F1275375@.microsoft.com...
>I was also reading that someone was having trouble with using a dataset
>that
> used SQL Query joins with multiple tables or is this not a problem?
> "Teo Lachev [MVP]" wrote:
>> RS (both 2000 and 2005) don't support binding to applcation datasets
>> natively. If you need to pass a dataset to a server-generated report, you
>> may find my custom extension useful
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> If you RS 2005 and the Report Viewers, you can bind the dataset to a
>> local
>> report.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
>> > Can you build a dataset in a web application and send it as a
>> > parameter
>> > through the report viewer control and have reporting services use this
>> > dataset as the binding dataset for your table in Reporting Services? IF
>> > so,
>> > can you help me or direct me to some article that will show me how to
>> > do
>> > this.
>>

pass user name as a parameter in a query

just getting started with my first db driven web project...

I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice.

How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following:

<asp:SqlDataSourceID="dsCourses"runat="server"ConnectionString="<%$ ConnectionStrings:xxx %>"
ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"
SelectCommand="SELECT c.CourseName FROM courses c, enrolments e
WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'">
</asp:SqlDataSource>

<asp:DataListID="DataList1"runat="server"DataSourceID="dsCourses">
<ItemTemplate>
<asp:HyperLinkID="HyperLink1"runat="server"NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br/>
</ItemTemplate>
</asp:DataList> </p>

However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement.

Suggestions pleeeeeese!!

You need to use ProfileParameter for the SQL SelectParameters on SqlDataSource.

<asp:SqlDataSourceID="dsCourses"runat="server"ConnectionString="<%$ ConnectionStrings:xxx %>"
ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"
SelectCommand="SELECT c.CourseName FROM courses c, enrolments e
WHERE c.CourseID=e.CourseID AND e.Username=@.userName">
<SelectParameters>
<asp:ProfileParameter Name="userName" PropertyName="UserName" />
</SelectParameters>
</asp:SqlDataSource
where Name is the name of the parameter and PropertyName is the name of the profile property( current user)

Thanks

Pass user credentials RS web service?

I'm using the RS web service to pull back customized reports via a C#
app, which is running under a service account.
I need to pass in the user's credentials to the Render method to make
sure the user has been given access to the report on the Security tab
of the front end.
If I pass the default credentials, the service account's credentials
are used.
Can anyone help? Many thanks.
BurtYou need to create an instance of System.Net.NetworkCredential.
Try:
rs.Credentials = new System.Net.NetworkCredential(UserName, Password);
instead of:
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
"Burt" wrote:
> I'm using the RS web service to pull back customized reports via a C#
> app, which is running under a service account.
> I need to pass in the user's credentials to the Render method to make
> sure the user has been given access to the report on the Security tab
> of the front end.
> If I pass the default credentials, the service account's credentials
> are used.
> Can anyone help? Many thanks.
> Burt
>|||David,
Thanks, but how do I get the current user's password? I'm using windows
authentication on this intranet app.
Burt|||FYI, the solution was:
WindowsImpersonationContext windowsImpersonationContext = null;
WindowsIdentity currentIdentity =(WindowsIdentity)Thread.CurrentPrincipal.Identity;
windowsImpersonationContext = currentIdentity.Impersonate();
MyService.Credentials =System.Net.CredentialCache.DefaultCredentials;
windowsImpersonationContext.Undo();
windowsImpersonationContext = null;

Pass a "Begin...End" Block from ASP

Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
I
have a situation where one of my tables contains the column names that I nee
d
to select from another table. I have always used two separate select
statements (with two separate trips to the db) to get the values I need, but
I recently found that I can accomplish the same thing by passing a
Begin...End block like this...
begin
declare @.col_list varchar(8000)
select @.col_list = coalesce(@.col_list + ', ', '') +
approverlabel from approvers
where formid=6 order by approverorder
exec('select ' + @.col_list + ' from formconfigs where pid=2701')
end
Is there a reason why this should not be done? I realize this would be
better if it was implemented in a stored procedure.Yes this would be best in a stored procedure so it can reuse the query plan.
But in any case you don't need a BEGIN - END. If you send it as one batch
it will work fine.
Andrew J. Kelly SQL MVP
"creed1" <creed1@.discussions.microsoft.com> wrote in message
news:75660A09-951F-4D34-88BE-BFC26E13FED7@.microsoft.com...
> Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
> I
> have a situation where one of my tables contains the column names that I
> need
> to select from another table. I have always used two separate select
> statements (with two separate trips to the db) to get the values I need,
> but
> I recently found that I can accomplish the same thing by passing a
> Begin...End block like this...
> begin
> declare @.col_list varchar(8000)
> select @.col_list = coalesce(@.col_list + ', ', '') +
> approverlabel from approvers
> where formid=6 order by approverorder
> exec('select ' + @.col_list + ' from formconfigs where pid=2701')
> end
> Is there a reason why this should not be done? I realize this would be
> better if it was implemented in a stored procedure.