Does anyone know how to pass a parameter from a stored
procedure to a DataAdapter?
the following is my code:
i used the DataAdapter wizard to set its properties with
my stored procedure.
//create a data adapter
SqlDataAdapter da = sqlDataAdapter1;
//create a data set and fill it by
calling Fill method
DataSet ds = new DataSet("Cust");
da.Fill(ds,"Customers");
//attach data set's default view
to the data grid control
DataGrid1.DataSource = ds;
DataGrid1.DataMember = "Customers";
DataGrid1.DataBind();
**When i run it, it asks for the stored procedure's
parameter which is the value of a text field in my asp.net
application.For performance reasons, you don't want to use the wizards to generate
code in ADO.NET -- you'd be much better off just deleting all the
wizard-generated stuff and starting from scratch. I'd recommend
getting a copy of ADO.NET by David Sceppa, Microsoft Press.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Wed, 12 Nov 2003 14:33:58 -0800, "zoe"
<anonymous@.discussions.microsoft.com> wrote:
>Does anyone know how to pass a parameter from a stored
>procedure to a DataAdapter?
>the following is my code:
>i used the DataAdapter wizard to set its properties with
>my stored procedure.
> //create a data adapter
> SqlDataAdapter da =>sqlDataAdapter1;
> //create a data set and fill it by
>calling Fill method
> DataSet ds = new DataSet("Cust");
> da.Fill(ds,"Customers");
>
> //attach data set's default view
>to the data grid control
> DataGrid1.DataSource = ds;
> DataGrid1.DataMember = "Customers";
> DataGrid1.DataBind();
>
>**When i run it, it asks for the stored procedure's
>parameter which is the value of a text field in my asp.net
>application.
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Calling stored procedure from a C application
I have an ODBC/C program and I want to call the stored procedures
sp_addlogin and sp_adduser in it. Can someone please provide me with
some sample code showing the best way to do this.[posted and mailed, please reply in news]
sp_addlogin and sp_adduser in it. Can someone please provide me with
some sample code showing the best way to do this.[posted and mailed, please reply in news]
Jocky MacLochray (ian.lochray@.ssi-world.com) writes:
> I have an ODBC/C program and I want to call the stored procedures
> sp_addlogin and sp_adduser in it. Can someone please provide me with
> some sample code showing the best way to do this.
http://www.microsoft.com/downloads/...&DisplayLang=en
These samples were also in the original Books Online that came with SQL
Server, but was removed from later versions. The above link gives you
revised samples. I believe the main difference is that the revised samples
uses Windows authentication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns955E841276F14Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Jocky MacLochray (ian.lochray@.ssi-world.com) writes:
> > I have an ODBC/C program and I want to call the stored procedures
> > sp_addlogin and sp_adduser in it. Can someone please provide me with
> > some sample code showing the best way to do this.
> http://www.microsoft.com/downloads/...&DisplayLang=en
> These samples were also in the original Books Online that came with SQL
> Server, but was removed from later versions. The above link gives you
> revised samples. I believe the main difference is that the revised samples
> uses Windows authentication.
Hi,
Did you try using EXEC sp_addlogin from C program.
Calling Stored Procedure
I am trying to set up a call to a Stored Procedure to do an Insert. Here is my code snippet:
<%@. Page Language="VB" %>
<%@. import Namespace="System" %>
<%@. import Namespace="System.Data.SqlClient" %>
.
.
.
Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdInsert as New SQLCommand("AdminUser_Insert", loConn)
cmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = cmdInsert
cmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "Account_Number"))
cmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.varchar, 50, "UserID"))
cmdInsert.Parameters("@.UserID").Value = Request("UserID")
cmdInsert.Parameters.Add(New SqlParameter("@.Password", SqlDbType.varchar, 32, "Password"))
cmdInsert.Parameters("@.Password").Value = Request("Password")
cmdInsert.Parameters.Add(New SqlParameter("@.First_Name", SqlDbType.varchar, 32, "First_Name"))
cmdInsert.Parameters("@.First_Name").Value = Request("FirstName")
cmdInsert.Parameters.Add(New SqlParameter("@.Middle_Name", SqlDbType.varchar, 32, "Middle_Name"))
cmdInsert.Parameters("@.Middle_Name").Value = Request("MiddleName")
cmdInsert.Parameters.Add(New SqlParameter("@.Last_Name", SqlDbType.varchar, 32, "Last_Name"))
cmdInsert.Parameters("@.Last_Name").Value = Request("LastName")
loConn.Open()
command.ExecuteNonQuery()
loConn.Close()
I get the following error:
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30451: Name 'CommandType' is not declared.
This error happens on the line: cmdInsert.CommandType = CommandType.StoredProcedure
Any help would be appreciated,
GregTry importing System.data. I think you're just missing that|||Ugh. That did it. Thank you very much. Been staring at it for a while.
Gregsql
Calling SSIS packages from within a Script task
Hello,
I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?
In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.
I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.
Any help would be greatly appreciated.
Thank you,
Gordon Radley
Sample code to execute a Package from Script task
Public Sub Main()
Dim pkg As String = "C:\SSISPackage\Package.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Execute()
Dts.TaskResult = Dts.Results.Success
End Sub
Hope this helps.
Thanks,
Loonysan
Yes - it definately helps. Thanks for the quick reply.
Do I need to add any additional references if I'm running this from within a Script task in SSIS?
Thanks again!
|||The Runtime reference is sufficient. You'll want to make sure that you have the Imports statement in your code.
As you've notice, the "Load" methods are now more logically methods of the SSIS Application rather than of the Package itself.
For what it's worth, the documentation team is fleshing out the section on how packages can be loaded, executed, enumerated, and managed programmatically, and how their output can be loaded directly into other applications by using the DtsClient DLL.
-Doug
|||Doug,
We have discovered issues with DTSClient. Basically you don't get much information out of it. e.g. If a package fails to validate the only message you get is "The package failed to validate" rather than which task failed to validate.
Darren has uncompiled the managed code and noticed some shortcoming in the way it hasbeen implemented. This is wa too low level for me but there are some easy fixes to this. I'm sure Darren would be happy to share his experiences I'm sure.
-Jamie
|||
Darren would never do such a thing, but either way this is not DtsClient. This thread is all about loading and executing a package, directly. For general info (not Jamie) DtsClient does this, but wraps it all up such that you can consume data exposed from the DataReader destination, see the BOL for samples of consuming SSIS from extrenal applications.
When loading and executing you can capture.events, by passing in an implementation of IDTSEvents, see the overloaded methods. If you don't then if anything goes wrong all you really know is it went wrong. By examining the events you get to know why. The package failed validation is a start, but it failed validation because it could not make a connection to SQL Server XYZ in Connection "ABC" is somewhat more helpfull. I strongly suggest you look further at this and implement IDTSEvents.
Jamie got the two confused because DtsClient does not do what I have suggested, and as such is really a nightmare to use real world. Being told something failed is better than not, but if you don't know what failed in a large package you can waste some serious amounts of time trying to find out, we have! For the DtsClient authrors to have not captued the events and exposed the information is just shockingly poor I find, and we no longer use it for that reason.
So to sum up, if you are loading and executing packages then have a look at the overloaded methods that accept IDTSEvents, it will be worth your while when things go wrong. If using DtsClient, I hope it doesn't go wrong for you!
=== Edited by DarrenSQLIS @. 04 Mar 2006 10:17 PM UTC===
Umm... re-reading the thread I now see Doug raised DtsClient, so I was more confused than anyone. Comments still stand, but I'm confused not Jamie.
I have tried as you explained. Nothing happened. I have a top package from here I call another package which contains only script task.
In this script task, the package is called as below.
Dim Lpkg As String = "C:\User Entry Window\LongitudinalLoadingData.dtsx"
Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
Dim pL As Package = app.LoadPackage(Lpkg, Nothing)
If CStr(Dts.Variables("TypeOfSurvey").Value) = "L" Then
pL.Execute()
else
...Call another package.
End if.
I set the MsgBox message inside the LongitudinalLoadingData, but even this message will not appear. Do I need to add something?
Calling SSIS packages from within a Script task
Hello,
I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?
In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.
I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.
Any help would be greatly appreciated.
Thank you,
Gordon Radley
Sample code to execute a Package from Script task
Public Sub Main()
Dim pkg As String = "C:\SSISPackage\Package.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Execute()
Dts.TaskResult = Dts.Results.Success
End Sub
Hope this helps.
Thanks,
Loonysan
Yes - it definately helps. Thanks for the quick reply.
Do I need to add any additional references if I'm running this from within a Script task in SSIS?
Thanks again!
|||The Runtime reference is sufficient. You'll want to make sure that you have the Imports statement in your code.
As you've notice, the "Load" methods are now more logically methods of the SSIS Application rather than of the Package itself.
For what it's worth, the documentation team is fleshing out the section on how packages can be loaded, executed, enumerated, and managed programmatically, and how their output can be loaded directly into other applications by using the DtsClient DLL.
-Doug
|||Doug,
We have discovered issues with DTSClient. Basically you don't get much information out of it. e.g. If a package fails to validate the only message you get is "The package failed to validate" rather than which task failed to validate.
Darren has uncompiled the managed code and noticed some shortcoming in the way it hasbeen implemented. This is wa too low level for me but there are some easy fixes to this. I'm sure Darren would be happy to share his experiences I'm sure.
-Jamie
|||Darren would never do such a thing, but either way this is not DtsClient. This thread is all about loading and executing a package, directly. For general info (not Jamie) DtsClient does this, but wraps it all up such that you can consume data exposed from the DataReader destination, see the BOL for samples of consuming SSIS from extrenal applications.
When loading and executing you can capture.events, by passing in an implementation of IDTSEvents, see the overloaded methods. If you don't then if anything goes wrong all you really know is it went wrong. By examining the events you get to know why. The package failed validation is a start, but it failed validation because it could not make a connection to SQL Server XYZ in Connection "ABC" is somewhat more helpfull. I strongly suggest you look further at this and implement IDTSEvents.
Jamie got the two confused because DtsClient does not do what I have suggested, and as such is really a nightmare to use real world. Being told something failed is better than not, but if you don't know what failed in a large package you can waste some serious amounts of time trying to find out, we have! For the DtsClient authrors to have not captued the events and exposed the information is just shockingly poor I find, and we no longer use it for that reason.
So to sum up, if you are loading and executing packages then have a look at the overloaded methods that accept IDTSEvents, it will be worth your while when things go wrong. If using DtsClient, I hope it doesn't go wrong for you!
=== Edited by DarrenSQLIS @. 04 Mar 2006 10:17 PM UTC===
Umm... re-reading the thread I now see Doug raised DtsClient, so I was more confused than anyone. Comments still stand, but I'm confused not Jamie.
I have tried as you explained. Nothing happened. I have a top package from here I call another package which contains only script task.
In this script task, the package is called as below.
Dim Lpkg As String = "C:\User Entry Window\LongitudinalLoadingData.dtsx"
Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
Dim pL As Package = app.LoadPackage(Lpkg, Nothing)
If CStr(Dts.Variables("TypeOfSurvey").Value) = "L" Then
pL.Execute()
else
...Call another package.
End if.
I set the MsgBox message inside the LongitudinalLoadingData, but even this message will not appear. Do I need to add something?
Sunday, March 25, 2012
Calling SQL DMO to backup / restore DB via C#
Does anyone have any sample code, or references to help when calling SQL DMO
to backup / restore DB via C#
Thanks
hi Paul,
Paul Aspinall wrote:
> Does anyone have any sample code, or references to help when calling
> SQL DMO to backup / restore DB via C#
> Thanks
please have a look at http://www.sqldev.net/sqldmo/SQL-DMO.NETEventsCS.htm
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
to backup / restore DB via C#
Thanks
hi Paul,
Paul Aspinall wrote:
> Does anyone have any sample code, or references to help when calling
> SQL DMO to backup / restore DB via C#
> Thanks
please have a look at http://www.sqldev.net/sqldmo/SQL-DMO.NETEventsCS.htm
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Calling sp in an expression
Hi All,
Is it possible to call a stored procedure (or a dataset that is based on an
sp) from the expression property in a report?
Can the "Code" tab be used to call the stored procedure and that "code" be
referred to from the expression property in a report (if the above is not
possible)?
This is what I am doing (or want to do):
I have a custom assembly that I don't want to put in the
MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in the
folder of my choice and under my control since the folder structure of
Microsoft for SQL Server 2005 (CTP July version) has changed.
So I created an Assembly in SQL Server 2005 by referring to this DLL. I
created a stored procedure that calls the assembly. Now I want to call this
stored procedure from the expression property of the report since I will be
passing the arguments from here.
Any help is appreciated. Thanks.No. The only place that a stored procedure can be called (unless you use a
custom assembly which is what you are trying to get away from) from is by
creating a dataset. The dataset query parameter mapping can use an
expression. If you have just a single record coming back then you can use
First aggregate to refer to it in your report. I do this all the time. Not
sure if that solves your problem or not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> Hi All,
> Is it possible to call a stored procedure (or a dataset that is based on
> an
> sp) from the expression property in a report?
> Can the "Code" tab be used to call the stored procedure and that "code" be
> referred to from the expression property in a report (if the above is not
> possible)?
> This is what I am doing (or want to do):
> I have a custom assembly that I don't want to put in the
> MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> the
> folder of my choice and under my control since the folder structure of
> Microsoft for SQL Server 2005 (CTP July version) has changed.
> So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> created a stored procedure that calls the assembly. Now I want to call
> this
> stored procedure from the expression property of the report since I will
> be
> passing the arguments from here.
> Any help is appreciated. Thanks.|||Thanks for you prompt reply.
Can you please explain with an example - may be this will work.
"Bruce L-C [MVP]" wrote:
> No. The only place that a stored procedure can be called (unless you use a
> custom assembly which is what you are trying to get away from) from is by
> creating a dataset. The dataset query parameter mapping can use an
> expression. If you have just a single record coming back then you can use
> First aggregate to refer to it in your report. I do this all the time. Not
> sure if that solves your problem or not.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> > Hi All,
> > Is it possible to call a stored procedure (or a dataset that is based on
> > an
> > sp) from the expression property in a report?
> >
> > Can the "Code" tab be used to call the stored procedure and that "code" be
> > referred to from the expression property in a report (if the above is not
> > possible)?
> >
> > This is what I am doing (or want to do):
> > I have a custom assembly that I don't want to put in the
> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> > the
> > folder of my choice and under my control since the folder structure of
> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >
> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> > created a stored procedure that calls the assembly. Now I want to call
> > this
> > stored procedure from the expression property of the report since I will
> > be
> > passing the arguments from here.
> >
> > Any help is appreciated. Thanks.
>
>|||Which part is confusing to you? Mapping a query parameter to an expression?
Using the First aggragate?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> Thanks for you prompt reply.
> Can you please explain with an example - may be this will work.
> "Bruce L-C [MVP]" wrote:
>> No. The only place that a stored procedure can be called (unless you use
>> a
>> custom assembly which is what you are trying to get away from) from is by
>> creating a dataset. The dataset query parameter mapping can use an
>> expression. If you have just a single record coming back then you can use
>> First aggregate to refer to it in your report. I do this all the time.
>> Not
>> sure if that solves your problem or not.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> > Hi All,
>> > Is it possible to call a stored procedure (or a dataset that is based
>> > on
>> > an
>> > sp) from the expression property in a report?
>> >
>> > Can the "Code" tab be used to call the stored procedure and that "code"
>> > be
>> > referred to from the expression property in a report (if the above is
>> > not
>> > possible)?
>> >
>> > This is what I am doing (or want to do):
>> > I have a custom assembly that I don't want to put in the
>> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
>> > in
>> > the
>> > folder of my choice and under my control since the folder structure of
>> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >
>> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
>> > created a stored procedure that calls the assembly. Now I want to call
>> > this
>> > stored procedure from the expression property of the report since I
>> > will
>> > be
>> > passing the arguments from here.
>> >
>> > Any help is appreciated. Thanks.
>>|||Mapping a query parameter to an expression.
"Bruce L-C [MVP]" wrote:
> Which part is confusing to you? Mapping a query parameter to an expression?
> Using the First aggragate?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> > Thanks for you prompt reply.
> >
> > Can you please explain with an example - may be this will work.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No. The only place that a stored procedure can be called (unless you use
> >> a
> >> custom assembly which is what you are trying to get away from) from is by
> >> creating a dataset. The dataset query parameter mapping can use an
> >> expression. If you have just a single record coming back then you can use
> >> First aggregate to refer to it in your report. I do this all the time.
> >> Not
> >> sure if that solves your problem or not.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> >> > Hi All,
> >> > Is it possible to call a stored procedure (or a dataset that is based
> >> > on
> >> > an
> >> > sp) from the expression property in a report?
> >> >
> >> > Can the "Code" tab be used to call the stored procedure and that "code"
> >> > be
> >> > referred to from the expression property in a report (if the above is
> >> > not
> >> > possible)?
> >> >
> >> > This is what I am doing (or want to do):
> >> > I have a custom assembly that I don't want to put in the
> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
> >> > in
> >> > the
> >> > folder of my choice and under my control since the folder structure of
> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >> >
> >> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> >> > created a stored procedure that calls the assembly. Now I want to call
> >> > this
> >> > stored procedure from the expression property of the report since I
> >> > will
> >> > be
> >> > passing the arguments from here.
> >> >
> >> > Any help is appreciated. Thanks.
> >>
> >>
> >>
>
>|||RS makes this concept murky because it automatically creates a Report
Parameter for every query parameter. However, you can map query parameters
to the same report parameter or to an expression. For instance, let's say I
have two datasets, they both need from and to dates. Let's further say that
the query parameters are named differently. For instance @.FromDate and
@.StartDate. Reporting services will create two report parameters. However
you can map both @.FromDate and @.StartDate to the same report parameter.
Click on the ... in the dataset tab, Parameters tab. On the left is the
query parameter on the right is what it is mapped to. That is where you
would map both to the same report parameter. OK, now, mapping to an
expression, you can click on the combo box and set expression.
For instance this calls some code behind report:
=Code.SomecodeOfMine(Parameters!Param1.Value)
This sets to the current day/time
=Now
Etc. Your expression can be pretty much anything.
When you pick expression you go to the expression builder.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:74430973-4547-419F-AFFC-29CA4CE68667@.microsoft.com...
> Mapping a query parameter to an expression.
> "Bruce L-C [MVP]" wrote:
>> Which part is confusing to you? Mapping a query parameter to an
>> expression?
>> Using the First aggragate?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
>> > Thanks for you prompt reply.
>> >
>> > Can you please explain with an example - may be this will work.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No. The only place that a stored procedure can be called (unless you
>> >> use
>> >> a
>> >> custom assembly which is what you are trying to get away from) from is
>> >> by
>> >> creating a dataset. The dataset query parameter mapping can use an
>> >> expression. If you have just a single record coming back then you can
>> >> use
>> >> First aggregate to refer to it in your report. I do this all the time.
>> >> Not
>> >> sure if that solves your problem or not.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> >> > Hi All,
>> >> > Is it possible to call a stored procedure (or a dataset that is
>> >> > based
>> >> > on
>> >> > an
>> >> > sp) from the expression property in a report?
>> >> >
>> >> > Can the "Code" tab be used to call the stored procedure and that
>> >> > "code"
>> >> > be
>> >> > referred to from the expression property in a report (if the above
>> >> > is
>> >> > not
>> >> > possible)?
>> >> >
>> >> > This is what I am doing (or want to do):
>> >> > I have a custom assembly that I don't want to put in the
>> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to
>> >> > be
>> >> > in
>> >> > the
>> >> > folder of my choice and under my control since the folder structure
>> >> > of
>> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >> >
>> >> > So I created an Assembly in SQL Server 2005 by referring to this
>> >> > DLL. I
>> >> > created a stored procedure that calls the assembly. Now I want to
>> >> > call
>> >> > this
>> >> > stored procedure from the expression property of the report since I
>> >> > will
>> >> > be
>> >> > passing the arguments from here.
>> >> >
>> >> > Any help is appreciated. Thanks.
>> >>
>> >>
>> >>
>>
Is it possible to call a stored procedure (or a dataset that is based on an
sp) from the expression property in a report?
Can the "Code" tab be used to call the stored procedure and that "code" be
referred to from the expression property in a report (if the above is not
possible)?
This is what I am doing (or want to do):
I have a custom assembly that I don't want to put in the
MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in the
folder of my choice and under my control since the folder structure of
Microsoft for SQL Server 2005 (CTP July version) has changed.
So I created an Assembly in SQL Server 2005 by referring to this DLL. I
created a stored procedure that calls the assembly. Now I want to call this
stored procedure from the expression property of the report since I will be
passing the arguments from here.
Any help is appreciated. Thanks.No. The only place that a stored procedure can be called (unless you use a
custom assembly which is what you are trying to get away from) from is by
creating a dataset. The dataset query parameter mapping can use an
expression. If you have just a single record coming back then you can use
First aggregate to refer to it in your report. I do this all the time. Not
sure if that solves your problem or not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> Hi All,
> Is it possible to call a stored procedure (or a dataset that is based on
> an
> sp) from the expression property in a report?
> Can the "Code" tab be used to call the stored procedure and that "code" be
> referred to from the expression property in a report (if the above is not
> possible)?
> This is what I am doing (or want to do):
> I have a custom assembly that I don't want to put in the
> MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> the
> folder of my choice and under my control since the folder structure of
> Microsoft for SQL Server 2005 (CTP July version) has changed.
> So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> created a stored procedure that calls the assembly. Now I want to call
> this
> stored procedure from the expression property of the report since I will
> be
> passing the arguments from here.
> Any help is appreciated. Thanks.|||Thanks for you prompt reply.
Can you please explain with an example - may be this will work.
"Bruce L-C [MVP]" wrote:
> No. The only place that a stored procedure can be called (unless you use a
> custom assembly which is what you are trying to get away from) from is by
> creating a dataset. The dataset query parameter mapping can use an
> expression. If you have just a single record coming back then you can use
> First aggregate to refer to it in your report. I do this all the time. Not
> sure if that solves your problem or not.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> > Hi All,
> > Is it possible to call a stored procedure (or a dataset that is based on
> > an
> > sp) from the expression property in a report?
> >
> > Can the "Code" tab be used to call the stored procedure and that "code" be
> > referred to from the expression property in a report (if the above is not
> > possible)?
> >
> > This is what I am doing (or want to do):
> > I have a custom assembly that I don't want to put in the
> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> > the
> > folder of my choice and under my control since the folder structure of
> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >
> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> > created a stored procedure that calls the assembly. Now I want to call
> > this
> > stored procedure from the expression property of the report since I will
> > be
> > passing the arguments from here.
> >
> > Any help is appreciated. Thanks.
>
>|||Which part is confusing to you? Mapping a query parameter to an expression?
Using the First aggragate?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> Thanks for you prompt reply.
> Can you please explain with an example - may be this will work.
> "Bruce L-C [MVP]" wrote:
>> No. The only place that a stored procedure can be called (unless you use
>> a
>> custom assembly which is what you are trying to get away from) from is by
>> creating a dataset. The dataset query parameter mapping can use an
>> expression. If you have just a single record coming back then you can use
>> First aggregate to refer to it in your report. I do this all the time.
>> Not
>> sure if that solves your problem or not.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> > Hi All,
>> > Is it possible to call a stored procedure (or a dataset that is based
>> > on
>> > an
>> > sp) from the expression property in a report?
>> >
>> > Can the "Code" tab be used to call the stored procedure and that "code"
>> > be
>> > referred to from the expression property in a report (if the above is
>> > not
>> > possible)?
>> >
>> > This is what I am doing (or want to do):
>> > I have a custom assembly that I don't want to put in the
>> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
>> > in
>> > the
>> > folder of my choice and under my control since the folder structure of
>> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >
>> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
>> > created a stored procedure that calls the assembly. Now I want to call
>> > this
>> > stored procedure from the expression property of the report since I
>> > will
>> > be
>> > passing the arguments from here.
>> >
>> > Any help is appreciated. Thanks.
>>|||Mapping a query parameter to an expression.
"Bruce L-C [MVP]" wrote:
> Which part is confusing to you? Mapping a query parameter to an expression?
> Using the First aggragate?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> > Thanks for you prompt reply.
> >
> > Can you please explain with an example - may be this will work.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No. The only place that a stored procedure can be called (unless you use
> >> a
> >> custom assembly which is what you are trying to get away from) from is by
> >> creating a dataset. The dataset query parameter mapping can use an
> >> expression. If you have just a single record coming back then you can use
> >> First aggregate to refer to it in your report. I do this all the time.
> >> Not
> >> sure if that solves your problem or not.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> >> > Hi All,
> >> > Is it possible to call a stored procedure (or a dataset that is based
> >> > on
> >> > an
> >> > sp) from the expression property in a report?
> >> >
> >> > Can the "Code" tab be used to call the stored procedure and that "code"
> >> > be
> >> > referred to from the expression property in a report (if the above is
> >> > not
> >> > possible)?
> >> >
> >> > This is what I am doing (or want to do):
> >> > I have a custom assembly that I don't want to put in the
> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
> >> > in
> >> > the
> >> > folder of my choice and under my control since the folder structure of
> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >> >
> >> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> >> > created a stored procedure that calls the assembly. Now I want to call
> >> > this
> >> > stored procedure from the expression property of the report since I
> >> > will
> >> > be
> >> > passing the arguments from here.
> >> >
> >> > Any help is appreciated. Thanks.
> >>
> >>
> >>
>
>|||RS makes this concept murky because it automatically creates a Report
Parameter for every query parameter. However, you can map query parameters
to the same report parameter or to an expression. For instance, let's say I
have two datasets, they both need from and to dates. Let's further say that
the query parameters are named differently. For instance @.FromDate and
@.StartDate. Reporting services will create two report parameters. However
you can map both @.FromDate and @.StartDate to the same report parameter.
Click on the ... in the dataset tab, Parameters tab. On the left is the
query parameter on the right is what it is mapped to. That is where you
would map both to the same report parameter. OK, now, mapping to an
expression, you can click on the combo box and set expression.
For instance this calls some code behind report:
=Code.SomecodeOfMine(Parameters!Param1.Value)
This sets to the current day/time
=Now
Etc. Your expression can be pretty much anything.
When you pick expression you go to the expression builder.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:74430973-4547-419F-AFFC-29CA4CE68667@.microsoft.com...
> Mapping a query parameter to an expression.
> "Bruce L-C [MVP]" wrote:
>> Which part is confusing to you? Mapping a query parameter to an
>> expression?
>> Using the First aggragate?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
>> > Thanks for you prompt reply.
>> >
>> > Can you please explain with an example - may be this will work.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No. The only place that a stored procedure can be called (unless you
>> >> use
>> >> a
>> >> custom assembly which is what you are trying to get away from) from is
>> >> by
>> >> creating a dataset. The dataset query parameter mapping can use an
>> >> expression. If you have just a single record coming back then you can
>> >> use
>> >> First aggregate to refer to it in your report. I do this all the time.
>> >> Not
>> >> sure if that solves your problem or not.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> >> > Hi All,
>> >> > Is it possible to call a stored procedure (or a dataset that is
>> >> > based
>> >> > on
>> >> > an
>> >> > sp) from the expression property in a report?
>> >> >
>> >> > Can the "Code" tab be used to call the stored procedure and that
>> >> > "code"
>> >> > be
>> >> > referred to from the expression property in a report (if the above
>> >> > is
>> >> > not
>> >> > possible)?
>> >> >
>> >> > This is what I am doing (or want to do):
>> >> > I have a custom assembly that I don't want to put in the
>> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to
>> >> > be
>> >> > in
>> >> > the
>> >> > folder of my choice and under my control since the folder structure
>> >> > of
>> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >> >
>> >> > So I created an Assembly in SQL Server 2005 by referring to this
>> >> > DLL. I
>> >> > created a stored procedure that calls the assembly. Now I want to
>> >> > call
>> >> > this
>> >> > stored procedure from the expression property of the report since I
>> >> > will
>> >> > be
>> >> > passing the arguments from here.
>> >> >
>> >> > Any help is appreciated. Thanks.
>> >>
>> >>
>> >>
>>
calling reports from forms
I have been trying to call my report from form through this code but neither it gives me any error nor its showing any report.
declare
report_id Report_Object;
report_job_id VARCHAR2(100);
BEGIN
report_id:= find_report_object('REPORT5');
report_JOB_ID:=run_report_object(report_id);
END;
Comm Mode : async
desctype : screen
Ex mode: runtime
file name : F:\Docs\Forms\vivek.rdf
report5 is the name of report object in the navigator.
What could be the problem??Hi,
Does Your report works fine? Is it fetching the required records ?|||Yep perfectly !
Originally posted by satish_ct
Hi,
Does Your report works fine? Is it fetching the required records ?|||Try using RUN_REPORT(<<filename>>)|||Originally posted by shelva
Try using RUN_REPORT(<<filename>>)
RUN_REPORT runs only from report as SRW.RUN_REPORT and not from form.|||Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms
Or in ur method, just give full path of the reports file name|||Originally posted by shelva
Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms
Or in ur method, just give full path of the reports file name
Never heard of it and neither it works.|||Oye again typing mistake
its CALL_REPORT.. sorry again
declare
report_id Report_Object;
report_job_id VARCHAR2(100);
BEGIN
report_id:= find_report_object('REPORT5');
report_JOB_ID:=run_report_object(report_id);
END;
Comm Mode : async
desctype : screen
Ex mode: runtime
file name : F:\Docs\Forms\vivek.rdf
report5 is the name of report object in the navigator.
What could be the problem??Hi,
Does Your report works fine? Is it fetching the required records ?|||Yep perfectly !
Originally posted by satish_ct
Hi,
Does Your report works fine? Is it fetching the required records ?|||Try using RUN_REPORT(<<filename>>)|||Originally posted by shelva
Try using RUN_REPORT(<<filename>>)
RUN_REPORT runs only from report as SRW.RUN_REPORT and not from form.|||Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms
Or in ur method, just give full path of the reports file name|||Originally posted by shelva
Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms
Or in ur method, just give full path of the reports file name
Never heard of it and neither it works.|||Oye again typing mistake
its CALL_REPORT.. sorry again
Calling reports from C#
I am sure there is a way to call reports from C# code and store them as a snapshot on the SQL Reporting Server. Can anybody refer me to an article or website on how this is done?
Thanks for the information.
Have you tried using a reportviewer component? I'm not exactly sure what you're asking for.
http://www.microsoft.com/downloads/details.aspx?familyid=F38F7037-B0D1-47A3-8063-66AF555D13D9&displaylang=en
|||I know what you are saying, but we have a batch process that uses stored procedures that will calculate aggregates (we are not using OLAP right now). This is a process that will probably take overnight to complete. We are calling this process from code and on completion of the process, we would like the report to be automatically generated and stored as a snapshot. We need all of the pre-processing to happen during this batch process. We don't want the report to be generating the aggregates. A couple of these reports will be about 1,000 pages long. They are yearly summary reports.
The UI will have a button to Queue the batch process and store it in a queue with other batch processes. I just need to figure out how to have the code call the report and store it as a snapshot when the process is complete.
Thanks for the information.
|||So you want the snapshot funtionality that report manager uses to be run from your code.
http://technet.microsoft.com/en-us/library/ms159217.aspx
Calling report from classic ASP
I don't even _have_ VS 6.0 on a machine anywhere, so I can't muck around
with it, so I'm hoping someone can supply sample code and/or links to
examples or other info that can show how to execute a report from "Classic"
ASP (not .NET). We have some legacy apps that aren't being migrated to .NET
and are being maintained off-shore, but want to get Crystal Reports out of
the picture.
Any help will be appreciated.
Thanks,
--StanOn Mar 30, 9:17 am, "Stan Spotts" <sspo...@.community.nospam> wrote:
> I don't even _have_ VS 6.0 on a machine anywhere, so I can't muck around
> with it, so I'm hoping someone can supply sample code and/or links to
> examples or other info that can show how to execute a report from "Classic"
> ASP (not .NET). We have some legacy apps that aren't being migrated to .NET
> and are being maintained off-shore, but want to get Crystal Reports out of
> the picture.
> Any help will be appreciated.
> Thanks,
> --Stan
One option would be to provide a direct link/hyperlink to the Report
Manager from the Classic ASP application. In my experiences, Reporting
Services is not quite as flexible w/Legacy systems as Crystal Reports
is. In .NET, there are about the same.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Stan,
For your classic ASP application, is it an public internet application or
for intranet only? As for SQL Server reporting service, if your classic
ASP application's client user can also directly access the reporting
service server, you can directly use "URL ACCESS" to let your asp page
redirect client user to the report server url so as to view the report.
Here is the BOL reference discussing on this:
#SQL Server 2005 Books Online Using URL Access in a Web Application
http://msdn2.microsoft.com/en-us/library/ms153563.aspx
If the SSRS report server is not able to directly access by client user(of
the classic ASP app), I think you can consider the following approach:
** develop a separate ASP.NET web application(a simple .NET application or
component) which access the reporting service webservice and output a
certain report as image or other binary stream.
** your classic ASP page communicate with that .NET based application to
query the binary content of the report and expose it to the end user.
How do you think? Please feel free to post here if you have any further
concerns.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Stan,
Have you got any progress or idea on this issue or does the suggestion in
our reply helps some? If there is anything else we can help, please feel
free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Steven - thanks, we actually did resort to using url access. I'm not sure
why the developers went that way rather than figurung out how to call the
web services and having it send back HTML, etc., but it was the quick and
dirty way that worked.
Thanks,
--Stan
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:otVgSQPdHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi Stan,
> For your classic ASP application, is it an public internet application or
> for intranet only? As for SQL Server reporting service, if your classic
> ASP application's client user can also directly access the reporting
> service server, you can directly use "URL ACCESS" to let your asp page
> redirect client user to the report server url so as to view the report.
> Here is the BOL reference discussing on this:
> #SQL Server 2005 Books Online Using URL Access in a Web Application
> http://msdn2.microsoft.com/en-us/library/ms153563.aspx
> If the SSRS report server is not able to directly access by client user(of
> the classic ASP app), I think you can consider the following approach:
> ** develop a separate ASP.NET web application(a simple .NET application or
> component) which access the reporting service webservice and output a
> certain report as image or other binary stream.
> ** your classic ASP page communicate with that .NET based application to
> query the binary content of the report and expose it to the end user.
> How do you think? Please feel free to post here if you have any further
> concerns.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
>|||Hi,
I have Same issue , but my Classic ASP Application user is not a valid user
on SRS Server , so a challenge page shows up when I do Response.Redirect ,
how get around this , is there a way I can Impersonate as valid SRS USR
just before Redirect ?
"Stan Spotts" wrote:
> Steven - thanks, we actually did resort to using url access. I'm not sure
> why the developers went that way rather than figurung out how to call the
> web services and having it send back HTML, etc., but it was the quick and
> dirty way that worked.
> Thanks,
> --Stan
> "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
> news:otVgSQPdHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> > Hi Stan,
> >
> > For your classic ASP application, is it an public internet application or
> > for intranet only? As for SQL Server reporting service, if your classic
> > ASP application's client user can also directly access the reporting
> > service server, you can directly use "URL ACCESS" to let your asp page
> > redirect client user to the report server url so as to view the report.
> > Here is the BOL reference discussing on this:
> >
> > #SQL Server 2005 Books Online Using URL Access in a Web Application
> > http://msdn2.microsoft.com/en-us/library/ms153563.aspx
> >
> > If the SSRS report server is not able to directly access by client user(of
> > the classic ASP app), I think you can consider the following approach:
> >
> > ** develop a separate ASP.NET web application(a simple .NET application or
> > component) which access the reporting service webservice and output a
> > certain report as image or other binary stream.
> >
> > ** your classic ASP page communicate with that .NET based application to
> > query the binary content of the report and expose it to the end user.
> >
> > How do you think? Please feel free to post here if you have any further
> > concerns.
> >
> > Sincerely,
> >
> > Steven Cheng
> >
> > Microsoft MSDN Online Support Lead
> >
> >
> >
> > ==================================================> >
> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications.
> >
> >
> >
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > http://msdn.microsoft.com/subscriptions/support/default.aspx.
> >
> > ==================================================> >
> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> >
> >
> >
> >
> >
> >
>
>sql
with it, so I'm hoping someone can supply sample code and/or links to
examples or other info that can show how to execute a report from "Classic"
ASP (not .NET). We have some legacy apps that aren't being migrated to .NET
and are being maintained off-shore, but want to get Crystal Reports out of
the picture.
Any help will be appreciated.
Thanks,
--StanOn Mar 30, 9:17 am, "Stan Spotts" <sspo...@.community.nospam> wrote:
> I don't even _have_ VS 6.0 on a machine anywhere, so I can't muck around
> with it, so I'm hoping someone can supply sample code and/or links to
> examples or other info that can show how to execute a report from "Classic"
> ASP (not .NET). We have some legacy apps that aren't being migrated to .NET
> and are being maintained off-shore, but want to get Crystal Reports out of
> the picture.
> Any help will be appreciated.
> Thanks,
> --Stan
One option would be to provide a direct link/hyperlink to the Report
Manager from the Classic ASP application. In my experiences, Reporting
Services is not quite as flexible w/Legacy systems as Crystal Reports
is. In .NET, there are about the same.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Stan,
For your classic ASP application, is it an public internet application or
for intranet only? As for SQL Server reporting service, if your classic
ASP application's client user can also directly access the reporting
service server, you can directly use "URL ACCESS" to let your asp page
redirect client user to the report server url so as to view the report.
Here is the BOL reference discussing on this:
#SQL Server 2005 Books Online Using URL Access in a Web Application
http://msdn2.microsoft.com/en-us/library/ms153563.aspx
If the SSRS report server is not able to directly access by client user(of
the classic ASP app), I think you can consider the following approach:
** develop a separate ASP.NET web application(a simple .NET application or
component) which access the reporting service webservice and output a
certain report as image or other binary stream.
** your classic ASP page communicate with that .NET based application to
query the binary content of the report and expose it to the end user.
How do you think? Please feel free to post here if you have any further
concerns.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Stan,
Have you got any progress or idea on this issue or does the suggestion in
our reply helps some? If there is anything else we can help, please feel
free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Steven - thanks, we actually did resort to using url access. I'm not sure
why the developers went that way rather than figurung out how to call the
web services and having it send back HTML, etc., but it was the quick and
dirty way that worked.
Thanks,
--Stan
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:otVgSQPdHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi Stan,
> For your classic ASP application, is it an public internet application or
> for intranet only? As for SQL Server reporting service, if your classic
> ASP application's client user can also directly access the reporting
> service server, you can directly use "URL ACCESS" to let your asp page
> redirect client user to the report server url so as to view the report.
> Here is the BOL reference discussing on this:
> #SQL Server 2005 Books Online Using URL Access in a Web Application
> http://msdn2.microsoft.com/en-us/library/ms153563.aspx
> If the SSRS report server is not able to directly access by client user(of
> the classic ASP app), I think you can consider the following approach:
> ** develop a separate ASP.NET web application(a simple .NET application or
> component) which access the reporting service webservice and output a
> certain report as image or other binary stream.
> ** your classic ASP page communicate with that .NET based application to
> query the binary content of the report and expose it to the end user.
> How do you think? Please feel free to post here if you have any further
> concerns.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
>|||Hi,
I have Same issue , but my Classic ASP Application user is not a valid user
on SRS Server , so a challenge page shows up when I do Response.Redirect ,
how get around this , is there a way I can Impersonate as valid SRS USR
just before Redirect ?
"Stan Spotts" wrote:
> Steven - thanks, we actually did resort to using url access. I'm not sure
> why the developers went that way rather than figurung out how to call the
> web services and having it send back HTML, etc., but it was the quick and
> dirty way that worked.
> Thanks,
> --Stan
> "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
> news:otVgSQPdHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> > Hi Stan,
> >
> > For your classic ASP application, is it an public internet application or
> > for intranet only? As for SQL Server reporting service, if your classic
> > ASP application's client user can also directly access the reporting
> > service server, you can directly use "URL ACCESS" to let your asp page
> > redirect client user to the report server url so as to view the report.
> > Here is the BOL reference discussing on this:
> >
> > #SQL Server 2005 Books Online Using URL Access in a Web Application
> > http://msdn2.microsoft.com/en-us/library/ms153563.aspx
> >
> > If the SSRS report server is not able to directly access by client user(of
> > the classic ASP app), I think you can consider the following approach:
> >
> > ** develop a separate ASP.NET web application(a simple .NET application or
> > component) which access the reporting service webservice and output a
> > certain report as image or other binary stream.
> >
> > ** your classic ASP page communicate with that .NET based application to
> > query the binary content of the report and expose it to the end user.
> >
> > How do you think? Please feel free to post here if you have any further
> > concerns.
> >
> > Sincerely,
> >
> > Steven Cheng
> >
> > Microsoft MSDN Online Support Lead
> >
> >
> >
> > ==================================================> >
> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications.
> >
> >
> >
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > http://msdn.microsoft.com/subscriptions/support/default.aspx.
> >
> > ==================================================> >
> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> >
> >
> >
> >
> >
> >
>
>sql
Thursday, March 22, 2012
Calling normal stored procedures from Extended Procedures
Hi
I want to call a database stored procedure from an extended procedure. Can some one provide snippet of code (either in C or C++) to achieve the same ?
Thanks & Regards
ImtiazYou have to make connection to server using ODBC or OLEDB to do this. Please take a look at the call escape sequence topic in Books Online. The url is mk:@.MSITStore:D:\Program%20Files\Microsoft%20SQL%20Server%20Books%20Online\1033\odbcsql.chm::/od_6_035_5dnp.htm. You may have to change the path depending on your installation. You can also find samples in the SQL Server CD.
Calling native code from a trigger.
I've been searching for a bit but I can't seem to find a difinitive answer.
Can an SQL Server trigger call native C/C++ functions? If so, what is the mechanism?
Thanks in advance.
My knee-jerk reaction is that this sounds like a very bad idea, but perhaps not. What are you trying to accomplish?|||Moving thread to the more appropriate forum.|||You could do that by creating a CLR procedure or function that wrapped the Win32 API calls.
What are you attemping to accomplish?
|||I was thinking of having a trigger that would call a native function that could do some needed tasks outside the SQL server. Things like post a message to an open mail slot on either the same or a different server so that another service can perform some work. After talking with on of the other developers, we will probably send the messages from his app. that is connected to the SQL server. I was thinking of having a trigger do it because it would remove him from having to manage the messages. Also a thought for future development.|||Personally, I wouldn't have a TRIGGER engage any outside service. It provides too many additional surfaces for disruption -the TRIGGER hangs waiting for the outside service ...
A better approach is to have the TRIGGER insert the relevant data into a queueing table.
Then have a separate SQL Agent Job that constantly runs checking the queue, and upon finding a row of data, processes that row in whatever fashion is appropriate (sending mail, logging, etc.)
|||In my case, it would just post a message into a queue and a thread would be watching the queue. The processing would happen on another process or service running multi threaded code. The trigger would probably return in a few ms. This would probably be faster than a lot of ODBC calls and the other service can simply wait on an event using no CPU cycles until an event is signalled.
Different ways to skin the cat I guess. Coders like to have code do the work and DBA's like the DBMS to do the work. Also a factor of what you are comfortable with.
Calling native code from a trigger.
I've been searching for a bit but I can't seem to find a difinitive answer.
Can an SQL Server trigger call native C/C++ functions? If so, what is the mechanism?
Thanks in advance.
My knee-jerk reaction is that this sounds like a very bad idea, but perhaps not. What are you trying to accomplish?|||Moving thread to the more appropriate forum.|||You could do that by creating a CLR procedure or function that wrapped the Win32 API calls.
What are you attemping to accomplish?
|||I was thinking of having a trigger that would call a native function that could do some needed tasks outside the SQL server. Things like post a message to an open mail slot on either the same or a different server so that another service can perform some work. After talking with on of the other developers, we will probably send the messages from his app. that is connected to the SQL server. I was thinking of having a trigger do it because it would remove him from having to manage the messages. Also a thought for future development.|||Personally, I wouldn't have a TRIGGER engage any outside service. It provides too many additional surfaces for disruption -the TRIGGER hangs waiting for the outside service ...
A better approach is to have the TRIGGER insert the relevant data into a queueing table.
Then have a separate SQL Agent Job that constantly runs checking the queue, and upon finding a row of data, processes that row in whatever fashion is appropriate (sending mail, logging, etc.)
|||In my case, it would just post a message into a queue and a thread would be watching the queue. The processing would happen on another process or service running multi threaded code. The trigger would probably return in a few ms. This would probably be faster than a lot of ODBC calls and the other service can simply wait on an event using no CPU cycles until an event is signalled.
Different ways to skin the cat I guess. Coders like to have code do the work and DBA's like the DBMS to do the work. Also a factor of what you are comfortable with.
sqlCalling my web service from the custom code is failing
This is what I'm trying to achieve. I want to call into my custom
assembly from the reports, and that assembly in turn calls a web
service. The reason I have to do that is because I think we can't
directly call a web service from the reports. To complicate the things
further I also have Forms Authentication enabled on Reporting
services.
Forms authentication enables me to use User!UserID which I pass on to
my custom assembly. The assembly in turn passes the parameter to web
service. To test the functionality I was trying to return simply
"Hello " + parameter value from the web service.
I have copied the custom assembly code to report manager's bin and
report server's bin directories. I have also added CodeGroup to
rssvrpolicy.config file to give a "FullTrust" permission on my custom
assembly.
The reports works as expected in Preview tab in VS.NET designer, but
when accessed from report manager or from the report server via URL,
the expression in text box shows #Error.
Please help to explain how to debug and fix the problem. Thanks much.
YogeshI have finally managed to get web service call working. I was able to
do this by asserting System.Net.WebPermission. Here's code snippet:
//Add following at the top of the class file
using System.Net;
using System.Security.Permissions;
//Add these lines just before instantiating web service proxy class
WebPermission webPerm = new
WebPermission(PermissionState.Unrestricted);
webPerm.Assert();
Remember to add exception handling code otherwise when functions in
custom code fails you will get "#Error" in the textbox in the report
without explaining what went wrong. If your methods return strings
then for debugging purposes you can return ex.ToString() which will at
least let you know what went wrong.
Yogesh|||Hi Yogesh,
Help..
Tried all of the above and still getting the #error on my report - I can
call a different function in the dll (not using a web service) and that is
fine..
Do I need to set up any policies in the rssvrpolicy.config file ?
I have added the reference in my code Web.Assert code - so it looks like I
still have problems with the config files ..
"Yogesh" wrote:
> This is what I'm trying to achieve. I want to call into my custom
> assembly from the reports, and that assembly in turn calls a web
> service. The reason I have to do that is because I think we can't
> directly call a web service from the reports. To complicate the things
> further I also have Forms Authentication enabled on Reporting
> services.
> Forms authentication enables me to use User!UserID which I pass on to
> my custom assembly. The assembly in turn passes the parameter to web
> service. To test the functionality I was trying to return simply
> "Hello " + parameter value from the web service.
> I have copied the custom assembly code to report manager's bin and
> report server's bin directories. I have also added CodeGroup to
> rssvrpolicy.config file to give a "FullTrust" permission on my custom
> assembly.
> The reports works as expected in Preview tab in VS.NET designer, but
> when accessed from report manager or from the report server via URL,
> the expression in text box shows #Error.
> Please help to explain how to debug and fix the problem. Thanks much.
> Yogesh
>
assembly from the reports, and that assembly in turn calls a web
service. The reason I have to do that is because I think we can't
directly call a web service from the reports. To complicate the things
further I also have Forms Authentication enabled on Reporting
services.
Forms authentication enables me to use User!UserID which I pass on to
my custom assembly. The assembly in turn passes the parameter to web
service. To test the functionality I was trying to return simply
"Hello " + parameter value from the web service.
I have copied the custom assembly code to report manager's bin and
report server's bin directories. I have also added CodeGroup to
rssvrpolicy.config file to give a "FullTrust" permission on my custom
assembly.
The reports works as expected in Preview tab in VS.NET designer, but
when accessed from report manager or from the report server via URL,
the expression in text box shows #Error.
Please help to explain how to debug and fix the problem. Thanks much.
YogeshI have finally managed to get web service call working. I was able to
do this by asserting System.Net.WebPermission. Here's code snippet:
//Add following at the top of the class file
using System.Net;
using System.Security.Permissions;
//Add these lines just before instantiating web service proxy class
WebPermission webPerm = new
WebPermission(PermissionState.Unrestricted);
webPerm.Assert();
Remember to add exception handling code otherwise when functions in
custom code fails you will get "#Error" in the textbox in the report
without explaining what went wrong. If your methods return strings
then for debugging purposes you can return ex.ToString() which will at
least let you know what went wrong.
Yogesh|||Hi Yogesh,
Help..
Tried all of the above and still getting the #error on my report - I can
call a different function in the dll (not using a web service) and that is
fine..
Do I need to set up any policies in the rssvrpolicy.config file ?
I have added the reference in my code Web.Assert code - so it looks like I
still have problems with the config files ..
"Yogesh" wrote:
> This is what I'm trying to achieve. I want to call into my custom
> assembly from the reports, and that assembly in turn calls a web
> service. The reason I have to do that is because I think we can't
> directly call a web service from the reports. To complicate the things
> further I also have Forms Authentication enabled on Reporting
> services.
> Forms authentication enables me to use User!UserID which I pass on to
> my custom assembly. The assembly in turn passes the parameter to web
> service. To test the functionality I was trying to return simply
> "Hello " + parameter value from the web service.
> I have copied the custom assembly code to report manager's bin and
> report server's bin directories. I have also added CodeGroup to
> rssvrpolicy.config file to give a "FullTrust" permission on my custom
> assembly.
> The reports works as expected in Preview tab in VS.NET designer, but
> when accessed from report manager or from the report server via URL,
> the expression in text box shows #Error.
> Please help to explain how to debug and fix the problem. Thanks much.
> Yogesh
>
calling javascript on OnSelectedIndexChanged of a drop downlistbox
I have a dropdownlist and a text box.
Once i select the item on a dropdownlist, the control should skip to text box.
Here is the code and i get the error.
'onFocus' is not a member of 'ASP.test_aspx'.
can u plz help me out.
<htmlxmlns="http://www.w3.org/1999/xhtml">
<headrunat="server">
<title>Untitled Page</title>
<scriptlanguage="javascript">
function onFocus()
{
var control = document.getElementById("TextBox1");
if( control != null ) control.focus();
}
</script>
</head>
<body>
<formid="form1"runat="server">
<div>
<asp:DropDownListID="DropDownList1"runat="server"Width="161px"OnSelectedIndexChanged="onFocus()">
<asp:ListItem>Javed</asp:ListItem>
<asp:ListItem>Abid</asp:ListItem>
<asp:ListItem>sajid</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox></div>
</form>
</body>
</html>
The reason you are getting this type of error is because you try to associate (link) JS function with ASP event. when you declare event such as OnSelectedIndexChanged ASP searchs the code behind or C#/VB section for a handler to invoke once this event fires.
A very simple way to diffrentiate between HTML DOM events and ASP events is the case. Note that ASP is always MixCase.
sqlCalling Java Application from PL/SQL code
Colleagues,
I am not an expert in PL/SQL neither Java, so I require a help here:
What I am planning to do is to use PL/SQL to call a Java application (pass 3 arguments).
The Java application is used to make call to an API that resides on a Bea Weblogic application/web server (its not used to access database).
I got already the PL/SQL Trigger, but I need to add to it the call to the Java application (the Java application already exists)
Simply, would any one provide an example of how to make that call.
Do I need to load Java classes into the Oracle database or not?
I am using Oracle 9i database, and the Java application is compiled using Java SDK 1.4.2
Many thanksHi,
To call java class from a pl/sql, you need to follow the steps.
1. First create a java file and compile it.
2. Load the java class file to the database using 'loadjava' utility.
Ex: loadjava -u scott/tiger abc.class
3. Create a pl/sql wrapper aroung the java class like
CREATE OR REPLACE PROCEDURE test (a1 VARCHAR2) AS LANGUAGE JAVA name 'abc.main(java.lang.String[])';
4. Then execute the stored procedure
exec test(<input>);|||HI,
Invoking the java stored procedures from any PL/SQL block or stored module works the same way it would for any other PL/SQL stored function.
For example:
SQLPLUS> set serveroutput on size 5000;
SQLPLUS> declare
2> temp varchar2(100);
3> begin
4> temp := ConcatTwo('Hello','World');
5> DBMS_OUTPUT.PUT_LINE(temp);
6> end;
7> /|||Thanks... suneel.kumar and satish_ct
you realy give me some ideas...|||These examples provide the capability to load specific "classes" to the
database and most will work however Oracle will have problems with
classes that import from GUI packages such as AWT or Swing.|||HI,
Did U come across any such error?|||My class is only initiate a request or call to another system API (my class doesn't import from any GUI Packages).
Many thanks to you all|||when I've tried to load the java class, but I got the following error:
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","unknown object","joxs heap init","ioc_allocate_pal")
would some one told me what I should do.
I think that the shared pool is out of memory so should I consult the DBA, system Admin or is it from my PC, where I am making a conection to the server/Database.
I am not an expert in PL/SQL neither Java, so I require a help here:
What I am planning to do is to use PL/SQL to call a Java application (pass 3 arguments).
The Java application is used to make call to an API that resides on a Bea Weblogic application/web server (its not used to access database).
I got already the PL/SQL Trigger, but I need to add to it the call to the Java application (the Java application already exists)
Simply, would any one provide an example of how to make that call.
Do I need to load Java classes into the Oracle database or not?
I am using Oracle 9i database, and the Java application is compiled using Java SDK 1.4.2
Many thanksHi,
To call java class from a pl/sql, you need to follow the steps.
1. First create a java file and compile it.
2. Load the java class file to the database using 'loadjava' utility.
Ex: loadjava -u scott/tiger abc.class
3. Create a pl/sql wrapper aroung the java class like
CREATE OR REPLACE PROCEDURE test (a1 VARCHAR2) AS LANGUAGE JAVA name 'abc.main(java.lang.String[])';
4. Then execute the stored procedure
exec test(<input>);|||HI,
Invoking the java stored procedures from any PL/SQL block or stored module works the same way it would for any other PL/SQL stored function.
For example:
SQLPLUS> set serveroutput on size 5000;
SQLPLUS> declare
2> temp varchar2(100);
3> begin
4> temp := ConcatTwo('Hello','World');
5> DBMS_OUTPUT.PUT_LINE(temp);
6> end;
7> /|||Thanks... suneel.kumar and satish_ct
you realy give me some ideas...|||These examples provide the capability to load specific "classes" to the
database and most will work however Oracle will have problems with
classes that import from GUI packages such as AWT or Swing.|||HI,
Did U come across any such error?|||My class is only initiate a request or call to another system API (my class doesn't import from any GUI Packages).
Many thanks to you all|||when I've tried to load the java class, but I got the following error:
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","unknown object","joxs heap init","ioc_allocate_pal")
would some one told me what I should do.
I think that the shared pool is out of memory so should I consult the DBA, system Admin or is it from my PC, where I am making a conection to the server/Database.
Calling GetReportParameters within a report Code block?
I want to write a Code block routine that returns the parameter Prompts
and ValidValue that was selected from within the report. Can I access
the ReportParameters collection (not the parameters collection, but the
actual parameter definitions collection) from within a Code block?
Thanks,
--jasonDo you know that you can get both of the things you want without doing
anything special? Parameters!parametername.Value and
Parameters!parametername.Label Lots of people don't realize you can use
the .Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<burt4684@.gmail.com> wrote in message
news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> I want to write a Code block routine that returns the parameter Prompts
> and ValidValue that was selected from within the report. Can I access
> the ReportParameters collection (not the parameters collection, but the
> actual parameter definitions collection) from within a Code block?
> Thanks,
> --jason
>|||Thanks, Bruce. I didn't know about the .Label property. That's good to
know. My only issue with .Value is some of my parameters are primary keys
and I need a good way to display the lookup values instead of the actual key
values. I was thinking I could iterate through the parameter.ValidValues
collection and display the ValidValue.Name that corresponds to the
parameter.Value that was selected. Do you know if this is possible?
Thanks,
--jason
"Bruce L-C [MVP]" wrote:
> Do you know that you can get both of the things you want without doing
> anything special? Parameters!parametername.Value and
> Parameters!parametername.Label Lots of people don't realize you can use
> the .Label
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <burt4684@.gmail.com> wrote in message
> news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > I want to write a Code block routine that returns the parameter Prompts
> > and ValidValue that was selected from within the report. Can I access
> > the ReportParameters collection (not the parameters collection, but the
> > actual parameter definitions collection) from within a Code block?
> > Thanks,
> >
> > --jason
> >
>
>|||Create another dataset that accepts the parameter as it's queryparameter.
You will end up with a single record (i.e. it has done your lookup), then
anywhere you need to use this value use this expression: =First(Fields!fieldname.value,"DatasetName")
Note that when you create a queryparameter RS adds a report parameter. If
you have the queryparameter with the same name as the reportparameter you
already have (must match case exactly since it is case sensitive) then your
query parameter and report parameter will already be mapped correctly.
This should do exactly what you need to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> Thanks, Bruce. I didn't know about the .Label property. That's good to
> know. My only issue with .Value is some of my parameters are primary keys
> and I need a good way to display the lookup values instead of the actual
key
> values. I was thinking I could iterate through the parameter.ValidValues
> collection and display the ValidValue.Name that corresponds to the
> parameter.Value that was selected. Do you know if this is possible?
> Thanks,
> --jason
> "Bruce L-C [MVP]" wrote:
> > Do you know that you can get both of the things you want without doing
> > anything special? Parameters!parametername.Value and
> > Parameters!parametername.Label Lots of people don't realize you can
use
> > the .Label
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > <burt4684@.gmail.com> wrote in message
> > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > I want to write a Code block routine that returns the parameter
Prompts
> > > and ValidValue that was selected from within the report. Can I access
> > > the ReportParameters collection (not the parameters collection, but
the
> > > actual parameter definitions collection) from within a Code block?
> > > Thanks,
> > >
> > > --jason
> > >
> >
> >
> >|||As always, there's more than one way to skin a cat. Great idea. Thanks for
the help!
--jason
"Bruce L-C [MVP]" wrote:
> Create another dataset that accepts the parameter as it's queryparameter.
> You will end up with a single record (i.e. it has done your lookup), then
> anywhere you need to use this value use this expression: => First(Fields!fieldname.value,"DatasetName")
> Note that when you create a queryparameter RS adds a report parameter. If
> you have the queryparameter with the same name as the reportparameter you
> already have (must match case exactly since it is case sensitive) then your
> query parameter and report parameter will already be mapped correctly.
> This should do exactly what you need to do.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
> news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> > Thanks, Bruce. I didn't know about the .Label property. That's good to
> > know. My only issue with .Value is some of my parameters are primary keys
> > and I need a good way to display the lookup values instead of the actual
> key
> > values. I was thinking I could iterate through the parameter.ValidValues
> > collection and display the ValidValue.Name that corresponds to the
> > parameter.Value that was selected. Do you know if this is possible?
> >
> > Thanks,
> >
> > --jason
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Do you know that you can get both of the things you want without doing
> > > anything special? Parameters!parametername.Value and
> > > Parameters!parametername.Label Lots of people don't realize you can
> use
> > > the .Label
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > <burt4684@.gmail.com> wrote in message
> > > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > > I want to write a Code block routine that returns the parameter
> Prompts
> > > > and ValidValue that was selected from within the report. Can I access
> > > > the ReportParameters collection (not the parameters collection, but
> the
> > > > actual parameter definitions collection) from within a Code block?
> > > > Thanks,
> > > >
> > > > --jason
> > > >
> > >
> > >
> > >
>
>
and ValidValue that was selected from within the report. Can I access
the ReportParameters collection (not the parameters collection, but the
actual parameter definitions collection) from within a Code block?
Thanks,
--jasonDo you know that you can get both of the things you want without doing
anything special? Parameters!parametername.Value and
Parameters!parametername.Label Lots of people don't realize you can use
the .Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<burt4684@.gmail.com> wrote in message
news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> I want to write a Code block routine that returns the parameter Prompts
> and ValidValue that was selected from within the report. Can I access
> the ReportParameters collection (not the parameters collection, but the
> actual parameter definitions collection) from within a Code block?
> Thanks,
> --jason
>|||Thanks, Bruce. I didn't know about the .Label property. That's good to
know. My only issue with .Value is some of my parameters are primary keys
and I need a good way to display the lookup values instead of the actual key
values. I was thinking I could iterate through the parameter.ValidValues
collection and display the ValidValue.Name that corresponds to the
parameter.Value that was selected. Do you know if this is possible?
Thanks,
--jason
"Bruce L-C [MVP]" wrote:
> Do you know that you can get both of the things you want without doing
> anything special? Parameters!parametername.Value and
> Parameters!parametername.Label Lots of people don't realize you can use
> the .Label
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <burt4684@.gmail.com> wrote in message
> news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > I want to write a Code block routine that returns the parameter Prompts
> > and ValidValue that was selected from within the report. Can I access
> > the ReportParameters collection (not the parameters collection, but the
> > actual parameter definitions collection) from within a Code block?
> > Thanks,
> >
> > --jason
> >
>
>|||Create another dataset that accepts the parameter as it's queryparameter.
You will end up with a single record (i.e. it has done your lookup), then
anywhere you need to use this value use this expression: =First(Fields!fieldname.value,"DatasetName")
Note that when you create a queryparameter RS adds a report parameter. If
you have the queryparameter with the same name as the reportparameter you
already have (must match case exactly since it is case sensitive) then your
query parameter and report parameter will already be mapped correctly.
This should do exactly what you need to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> Thanks, Bruce. I didn't know about the .Label property. That's good to
> know. My only issue with .Value is some of my parameters are primary keys
> and I need a good way to display the lookup values instead of the actual
key
> values. I was thinking I could iterate through the parameter.ValidValues
> collection and display the ValidValue.Name that corresponds to the
> parameter.Value that was selected. Do you know if this is possible?
> Thanks,
> --jason
> "Bruce L-C [MVP]" wrote:
> > Do you know that you can get both of the things you want without doing
> > anything special? Parameters!parametername.Value and
> > Parameters!parametername.Label Lots of people don't realize you can
use
> > the .Label
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > <burt4684@.gmail.com> wrote in message
> > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > I want to write a Code block routine that returns the parameter
Prompts
> > > and ValidValue that was selected from within the report. Can I access
> > > the ReportParameters collection (not the parameters collection, but
the
> > > actual parameter definitions collection) from within a Code block?
> > > Thanks,
> > >
> > > --jason
> > >
> >
> >
> >|||As always, there's more than one way to skin a cat. Great idea. Thanks for
the help!
--jason
"Bruce L-C [MVP]" wrote:
> Create another dataset that accepts the parameter as it's queryparameter.
> You will end up with a single record (i.e. it has done your lookup), then
> anywhere you need to use this value use this expression: => First(Fields!fieldname.value,"DatasetName")
> Note that when you create a queryparameter RS adds a report parameter. If
> you have the queryparameter with the same name as the reportparameter you
> already have (must match case exactly since it is case sensitive) then your
> query parameter and report parameter will already be mapped correctly.
> This should do exactly what you need to do.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
> news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> > Thanks, Bruce. I didn't know about the .Label property. That's good to
> > know. My only issue with .Value is some of my parameters are primary keys
> > and I need a good way to display the lookup values instead of the actual
> key
> > values. I was thinking I could iterate through the parameter.ValidValues
> > collection and display the ValidValue.Name that corresponds to the
> > parameter.Value that was selected. Do you know if this is possible?
> >
> > Thanks,
> >
> > --jason
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Do you know that you can get both of the things you want without doing
> > > anything special? Parameters!parametername.Value and
> > > Parameters!parametername.Label Lots of people don't realize you can
> use
> > > the .Label
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > <burt4684@.gmail.com> wrote in message
> > > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > > I want to write a Code block routine that returns the parameter
> Prompts
> > > > and ValidValue that was selected from within the report. Can I access
> > > > the ReportParameters collection (not the parameters collection, but
> the
> > > > actual parameter definitions collection) from within a Code block?
> > > > Thanks,
> > > >
> > > > --jason
> > > >
> > >
> > >
> > >
>
>
Calling functions in an expression
I have a function in the Report Code of one of my reports and an expression
that references it. Here is the expression that is in the details row:
=Iif(Code.Check(Fields!fullname.Value), 0,
Code.AddClient(Fields!fullname.Value))
The Check function basically looks in an array to see if the name is in the
array already. If it is, the expression should return 0. If it is not in the
array, it should call AddClient() to add the client and increment the
variable Total (Which is also declared in the Report Code). However, whenever
this expression runs, Total gets incrememnted no matter what. Even if the IIf
condition is false.
Is there something wrong with the way I am doing this?It is ok, is there any error in that case what exactly is the error?
Amarnath
"Matt M" wrote:
> I have a function in the Report Code of one of my reports and an expression
> that references it. Here is the expression that is in the details row:
> =Iif(Code.Check(Fields!fullname.Value), 0,
> Code.AddClient(Fields!fullname.Value))
> The Check function basically looks in an array to see if the name is in the
> array already. If it is, the expression should return 0. If it is not in the
> array, it should call AddClient() to add the client and increment the
> variable Total (Which is also declared in the Report Code). However, whenever
> this expression runs, Total gets incrememnted no matter what. Even if the IIf
> condition is false.
> Is there something wrong with the way I am doing this?|||There is no error, but rather this is what is being displayed in the report:
0 Case1
1 Case2
2 Case3
3 Case4
4 Case5
The first column displays the Total variable that gets incremented by the
AddClient() function. It should only be incremented if the Check() returns
true in any of the Cases. But instead, it gets incremented every row.
"Amarnath" wrote:
> It is ok, is there any error in that case what exactly is the error?
> Amarnath
> "Matt M" wrote:
> > I have a function in the Report Code of one of my reports and an expression
> > that references it. Here is the expression that is in the details row:
> >
> > =Iif(Code.Check(Fields!fullname.Value), 0,
> > Code.AddClient(Fields!fullname.Value))
> >
> > The Check function basically looks in an array to see if the name is in the
> > array already. If it is, the expression should return 0. If it is not in the
> > array, it should call AddClient() to add the client and increment the
> > variable Total (Which is also declared in the Report Code). However, whenever
> > this expression runs, Total gets incrememnted no matter what. Even if the IIf
> > condition is false.
> >
> > Is there something wrong with the way I am doing this?|||You still need to evaluate the expression... (i.e. you need an = sign)
change it to:
=Iif(Code.Check(Fields!fullname.Value) = 1, 0,
Code.AddClient(Fields!fullname.Value))
and I am assuming that you are returning a zero for false and 1 for true...
=-Chris
"Matt M" <MattM@.discussions.microsoft.com> wrote in message
news:2FE6A608-EE89-407D-AAED-A17385D6952F@.microsoft.com...
>I have a function in the Report Code of one of my reports and an expression
> that references it. Here is the expression that is in the details row:
> =Iif(Code.Check(Fields!fullname.Value), 0,
> Code.AddClient(Fields!fullname.Value))
> The Check function basically looks in an array to see if the name is in
> the
> array already. If it is, the expression should return 0. If it is not in
> the
> array, it should call AddClient() to add the client and increment the
> variable Total (Which is also declared in the Report Code). However,
> whenever
> this expression runs, Total gets incrememnted no matter what. Even if the
> IIf
> condition is false.
> Is there something wrong with the way I am doing this?|||I'm actually returning a boolean true or false with it. I tried returning a 0
or a 1 and still does the same thing.
"Chris Conner" wrote:
> You still need to evaluate the expression... (i.e. you need an = sign)
> change it to:
> =Iif(Code.Check(Fields!fullname.Value) = 1, 0,
> Code.AddClient(Fields!fullname.Value))
> and I am assuming that you are returning a zero for false and 1 for true...
> =-Chris
> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> news:2FE6A608-EE89-407D-AAED-A17385D6952F@.microsoft.com...
> >I have a function in the Report Code of one of my reports and an expression
> > that references it. Here is the expression that is in the details row:
> >
> > =Iif(Code.Check(Fields!fullname.Value), 0,
> > Code.AddClient(Fields!fullname.Value))
> >
> > The Check function basically looks in an array to see if the name is in
> > the
> > array already. If it is, the expression should return 0. If it is not in
> > the
> > array, it should call AddClient() to add the client and increment the
> > variable Total (Which is also declared in the Report Code). However,
> > whenever
> > this expression runs, Total gets incrememnted no matter what. Even if the
> > IIf
> > condition is false.
> >
> > Is there something wrong with the way I am doing this?
>
>|||It means it is always returning true... Try hard code just for testing
purpose. hard code it to false and see whether it increments.
Amarnath
"Matt M" wrote:
> There is no error, but rather this is what is being displayed in the report:
> 0 Case1
> 1 Case2
> 2 Case3
> 3 Case4
> 4 Case5
> The first column displays the Total variable that gets incremented by the
> AddClient() function. It should only be incremented if the Check() returns
> true in any of the Cases. But instead, it gets incremented every row.
>
> "Amarnath" wrote:
> > It is ok, is there any error in that case what exactly is the error?
> >
> > Amarnath
> >
> > "Matt M" wrote:
> >
> > > I have a function in the Report Code of one of my reports and an expression
> > > that references it. Here is the expression that is in the details row:
> > >
> > > =Iif(Code.Check(Fields!fullname.Value), 0,
> > > Code.AddClient(Fields!fullname.Value))
> > >
> > > The Check function basically looks in an array to see if the name is in the
> > > array already. If it is, the expression should return 0. If it is not in the
> > > array, it should call AddClient() to add the client and increment the
> > > variable Total (Which is also declared in the Report Code). However, whenever
> > > this expression runs, Total gets incrememnted no matter what. Even if the IIf
> > > condition is false.
> > >
> > > Is there something wrong with the way I am doing this?
that references it. Here is the expression that is in the details row:
=Iif(Code.Check(Fields!fullname.Value), 0,
Code.AddClient(Fields!fullname.Value))
The Check function basically looks in an array to see if the name is in the
array already. If it is, the expression should return 0. If it is not in the
array, it should call AddClient() to add the client and increment the
variable Total (Which is also declared in the Report Code). However, whenever
this expression runs, Total gets incrememnted no matter what. Even if the IIf
condition is false.
Is there something wrong with the way I am doing this?It is ok, is there any error in that case what exactly is the error?
Amarnath
"Matt M" wrote:
> I have a function in the Report Code of one of my reports and an expression
> that references it. Here is the expression that is in the details row:
> =Iif(Code.Check(Fields!fullname.Value), 0,
> Code.AddClient(Fields!fullname.Value))
> The Check function basically looks in an array to see if the name is in the
> array already. If it is, the expression should return 0. If it is not in the
> array, it should call AddClient() to add the client and increment the
> variable Total (Which is also declared in the Report Code). However, whenever
> this expression runs, Total gets incrememnted no matter what. Even if the IIf
> condition is false.
> Is there something wrong with the way I am doing this?|||There is no error, but rather this is what is being displayed in the report:
0 Case1
1 Case2
2 Case3
3 Case4
4 Case5
The first column displays the Total variable that gets incremented by the
AddClient() function. It should only be incremented if the Check() returns
true in any of the Cases. But instead, it gets incremented every row.
"Amarnath" wrote:
> It is ok, is there any error in that case what exactly is the error?
> Amarnath
> "Matt M" wrote:
> > I have a function in the Report Code of one of my reports and an expression
> > that references it. Here is the expression that is in the details row:
> >
> > =Iif(Code.Check(Fields!fullname.Value), 0,
> > Code.AddClient(Fields!fullname.Value))
> >
> > The Check function basically looks in an array to see if the name is in the
> > array already. If it is, the expression should return 0. If it is not in the
> > array, it should call AddClient() to add the client and increment the
> > variable Total (Which is also declared in the Report Code). However, whenever
> > this expression runs, Total gets incrememnted no matter what. Even if the IIf
> > condition is false.
> >
> > Is there something wrong with the way I am doing this?|||You still need to evaluate the expression... (i.e. you need an = sign)
change it to:
=Iif(Code.Check(Fields!fullname.Value) = 1, 0,
Code.AddClient(Fields!fullname.Value))
and I am assuming that you are returning a zero for false and 1 for true...
=-Chris
"Matt M" <MattM@.discussions.microsoft.com> wrote in message
news:2FE6A608-EE89-407D-AAED-A17385D6952F@.microsoft.com...
>I have a function in the Report Code of one of my reports and an expression
> that references it. Here is the expression that is in the details row:
> =Iif(Code.Check(Fields!fullname.Value), 0,
> Code.AddClient(Fields!fullname.Value))
> The Check function basically looks in an array to see if the name is in
> the
> array already. If it is, the expression should return 0. If it is not in
> the
> array, it should call AddClient() to add the client and increment the
> variable Total (Which is also declared in the Report Code). However,
> whenever
> this expression runs, Total gets incrememnted no matter what. Even if the
> IIf
> condition is false.
> Is there something wrong with the way I am doing this?|||I'm actually returning a boolean true or false with it. I tried returning a 0
or a 1 and still does the same thing.
"Chris Conner" wrote:
> You still need to evaluate the expression... (i.e. you need an = sign)
> change it to:
> =Iif(Code.Check(Fields!fullname.Value) = 1, 0,
> Code.AddClient(Fields!fullname.Value))
> and I am assuming that you are returning a zero for false and 1 for true...
> =-Chris
> "Matt M" <MattM@.discussions.microsoft.com> wrote in message
> news:2FE6A608-EE89-407D-AAED-A17385D6952F@.microsoft.com...
> >I have a function in the Report Code of one of my reports and an expression
> > that references it. Here is the expression that is in the details row:
> >
> > =Iif(Code.Check(Fields!fullname.Value), 0,
> > Code.AddClient(Fields!fullname.Value))
> >
> > The Check function basically looks in an array to see if the name is in
> > the
> > array already. If it is, the expression should return 0. If it is not in
> > the
> > array, it should call AddClient() to add the client and increment the
> > variable Total (Which is also declared in the Report Code). However,
> > whenever
> > this expression runs, Total gets incrememnted no matter what. Even if the
> > IIf
> > condition is false.
> >
> > Is there something wrong with the way I am doing this?
>
>|||It means it is always returning true... Try hard code just for testing
purpose. hard code it to false and see whether it increments.
Amarnath
"Matt M" wrote:
> There is no error, but rather this is what is being displayed in the report:
> 0 Case1
> 1 Case2
> 2 Case3
> 3 Case4
> 4 Case5
> The first column displays the Total variable that gets incremented by the
> AddClient() function. It should only be incremented if the Check() returns
> true in any of the Cases. But instead, it gets incremented every row.
>
> "Amarnath" wrote:
> > It is ok, is there any error in that case what exactly is the error?
> >
> > Amarnath
> >
> > "Matt M" wrote:
> >
> > > I have a function in the Report Code of one of my reports and an expression
> > > that references it. Here is the expression that is in the details row:
> > >
> > > =Iif(Code.Check(Fields!fullname.Value), 0,
> > > Code.AddClient(Fields!fullname.Value))
> > >
> > > The Check function basically looks in an array to see if the name is in the
> > > array already. If it is, the expression should return 0. If it is not in the
> > > array, it should call AddClient() to add the client and increment the
> > > variable Total (Which is also declared in the Report Code). However, whenever
> > > this expression runs, Total gets incrememnted no matter what. Even if the IIf
> > > condition is false.
> > >
> > > Is there something wrong with the way I am doing this?
Calling External Code that returns a Dataset
I know there are ways to embed custom code into these reports, but the
examples I've seen call the custom code outside the context of
acquiring a resultset.
Suppose I've created a class/assembly that has a shared method which
returns a dataset. Is it possible to use this in my report to
generate the record set rather than calling a stored prodedure or SQL
from within the report?
RobRob,
Yes, the Microsoft sample dataset extension as explained in the product
documentation does this. If you want a custom applicaton to bind to the
dataset, you may find my dataset extension useful.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
<rhoeting@.hotmail.com> wrote in message
news:1098317642.985613.50720@.c13g2000cwb.googlegroups.com...
> I know there are ways to embed custom code into these reports, but the
> examples I've seen call the custom code outside the context of
> acquiring a resultset.
> Suppose I've created a class/assembly that has a shared method which
> returns a dataset. Is it possible to use this in my report to
> generate the record set rather than calling a stored prodedure or SQL
> from within the report?
> Rob
>|||Teo,
I will check into this. Actually, I've built some stronly typed
collections (using Rocky Lhotka's CSLA framework), which can be easily
converted to Datasets. Currently they are used to display results in
a bindable listview on a heavy client. My goal is to "reuse" these
classes as the engine for the reports (or simply the "print" version of
the listview). Can these reports bind to a strongly typed collection,
or does it need to be a dataset?
BTW, you book came in the mail today and it looks fantastic.
Thanks,
Rob|||Teo is off at a book signing and can't reply (okay, I lied!).
Reporting Services is extensible in the area of data providers. You would
have to write your own data provider though.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"rhoeting" <rhoeting@.hotmail.com> wrote in message
news:1098325731.315791.23320@.f14g2000cwb.googlegroups.com...
> Teo,
> I will check into this. Actually, I've built some stronly typed
> collections (using Rocky Lhotka's CSLA framework), which can be easily
> converted to Datasets. Currently they are used to display results in
> a bindable listview on a heavy client. My goal is to "reuse" these
> classes as the engine for the reports (or simply the "print" version of
> the listview). Can these reports bind to a strongly typed collection,
> or does it need to be a dataset?
> BTW, you book came in the mail today and it looks fantastic.
> Thanks,
> Rob
>|||I am back... woof, my hands hurt :-)
Your custom dataset extension can report off anything that can expose its
data in a tabular fashion.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TIM ELLISON" <TimEllison@.direcway.com> wrote in message
news:OL%23cxv1tEHA.948@.tk2msftngp13.phx.gbl...
> Teo is off at a book signing and can't reply (okay, I lied!).
> Reporting Services is extensible in the area of data providers. You would
> have to write your own data provider though.
> --
> Regards,
> Tim Ellison, MCP
> Ironworks Consulting, LLC
> (m) 804.405.4874
> "rhoeting" <rhoeting@.hotmail.com> wrote in message
> news:1098325731.315791.23320@.f14g2000cwb.googlegroups.com...
> > Teo,
> >
> > I will check into this. Actually, I've built some stronly typed
> > collections (using Rocky Lhotka's CSLA framework), which can be easily
> > converted to Datasets. Currently they are used to display results in
> > a bindable listview on a heavy client. My goal is to "reuse" these
> > classes as the engine for the reports (or simply the "print" version of
> > the listview). Can these reports bind to a strongly typed collection,
> > or does it need to be a dataset?
> >
> > BTW, you book came in the mail today and it looks fantastic.
> > Thanks,
> >
> > Rob
> >
>|||I am new to Reporting Services but mayby someone can help me.
I am trying to get a pdf directly from a report with an external dataset. The external dataset works fine if I read it directly from an xml file in my compiled dataset extension. But when I try to do that whith a parameter wich contains xml , it isn't possible to create a dataset from my parameter(xml).
ERR=An attempt was made to set a report parameter 'xmlData' that is not defined in this report. But it is defined with my parameters and also one default in my report parameters
some code:
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null;
ParameterValue[] optionalParams = null;
Warning[] optionalWarnings = null;
DataSet ds=new DataSet();
ds.ReadXml("C:\\RS\\C#\\test.xml");
string Param=ds.GetXml().ToString();
ParameterValue[] pv = new ParameterValue[1];
pv[0] = new ParameterValue();
pv[0].Name = "xmlData";
pv[0].Label = "xmlDataLabel";
pv[0].Value = Param;
ResultStream = rs.Render("/test/Custom", "PDF", null, "some xml",
pv,null, null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
Response.BinaryWrite(ResultStream);
this code requests a PDF from the report /test/Custom wich use an external dataset
I calls my external dataset but I cant find my parameters
some code external dataset
public Microsoft.ReportingServices.DataProcessing.IDataParameterCollection
Parameters
{
get { return _parameters; }
}
public class DSXParameter : IDataParameter
{
string _parameterName;
object _parameterValue;
// Default constructor
public DSXParameter()
{
}
// Constructor to accept parameter name & value.
public DSXParameter(string parameterName, object value)
{
_parameterName = parameterName;
_parameterValue = value;
}
public String ParameterName
{
get { return _parameterName; }
set { _parameterName = value; }
}
public object Value
{
get
{
return _parameterValue;
}
set
{
_parameterValue = value;
}
}
}
--
Doesn't reach this method ExecuteReader
--
public Microsoft.ReportingServices.DataProcessing.IDataReader ExecuteReader
(Microsoft.ReportingServices.DataProcessing.CommandBehavior behavior)
{
DSXExtensionDataReader testReader = new DSXExtensionDataReader(this.Parameters);
testReader.CreateDataSet();//_connection._xmlSchema
return testReader;
}
Hope you can help me
Thanks in advance
]]>
From http://www.developmentnow.com/g/115_2004_10_0_0_452155/Calling-External-Code-that-returns-a-Dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com
examples I've seen call the custom code outside the context of
acquiring a resultset.
Suppose I've created a class/assembly that has a shared method which
returns a dataset. Is it possible to use this in my report to
generate the record set rather than calling a stored prodedure or SQL
from within the report?
RobRob,
Yes, the Microsoft sample dataset extension as explained in the product
documentation does this. If you want a custom applicaton to bind to the
dataset, you may find my dataset extension useful.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
<rhoeting@.hotmail.com> wrote in message
news:1098317642.985613.50720@.c13g2000cwb.googlegroups.com...
> I know there are ways to embed custom code into these reports, but the
> examples I've seen call the custom code outside the context of
> acquiring a resultset.
> Suppose I've created a class/assembly that has a shared method which
> returns a dataset. Is it possible to use this in my report to
> generate the record set rather than calling a stored prodedure or SQL
> from within the report?
> Rob
>|||Teo,
I will check into this. Actually, I've built some stronly typed
collections (using Rocky Lhotka's CSLA framework), which can be easily
converted to Datasets. Currently they are used to display results in
a bindable listview on a heavy client. My goal is to "reuse" these
classes as the engine for the reports (or simply the "print" version of
the listview). Can these reports bind to a strongly typed collection,
or does it need to be a dataset?
BTW, you book came in the mail today and it looks fantastic.
Thanks,
Rob|||Teo is off at a book signing and can't reply (okay, I lied!).
Reporting Services is extensible in the area of data providers. You would
have to write your own data provider though.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"rhoeting" <rhoeting@.hotmail.com> wrote in message
news:1098325731.315791.23320@.f14g2000cwb.googlegroups.com...
> Teo,
> I will check into this. Actually, I've built some stronly typed
> collections (using Rocky Lhotka's CSLA framework), which can be easily
> converted to Datasets. Currently they are used to display results in
> a bindable listview on a heavy client. My goal is to "reuse" these
> classes as the engine for the reports (or simply the "print" version of
> the listview). Can these reports bind to a strongly typed collection,
> or does it need to be a dataset?
> BTW, you book came in the mail today and it looks fantastic.
> Thanks,
> Rob
>|||I am back... woof, my hands hurt :-)
Your custom dataset extension can report off anything that can expose its
data in a tabular fashion.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TIM ELLISON" <TimEllison@.direcway.com> wrote in message
news:OL%23cxv1tEHA.948@.tk2msftngp13.phx.gbl...
> Teo is off at a book signing and can't reply (okay, I lied!).
> Reporting Services is extensible in the area of data providers. You would
> have to write your own data provider though.
> --
> Regards,
> Tim Ellison, MCP
> Ironworks Consulting, LLC
> (m) 804.405.4874
> "rhoeting" <rhoeting@.hotmail.com> wrote in message
> news:1098325731.315791.23320@.f14g2000cwb.googlegroups.com...
> > Teo,
> >
> > I will check into this. Actually, I've built some stronly typed
> > collections (using Rocky Lhotka's CSLA framework), which can be easily
> > converted to Datasets. Currently they are used to display results in
> > a bindable listview on a heavy client. My goal is to "reuse" these
> > classes as the engine for the reports (or simply the "print" version of
> > the listview). Can these reports bind to a strongly typed collection,
> > or does it need to be a dataset?
> >
> > BTW, you book came in the mail today and it looks fantastic.
> > Thanks,
> >
> > Rob
> >
>|||I am new to Reporting Services but mayby someone can help me.
I am trying to get a pdf directly from a report with an external dataset. The external dataset works fine if I read it directly from an xml file in my compiled dataset extension. But when I try to do that whith a parameter wich contains xml , it isn't possible to create a dataset from my parameter(xml).
ERR=An attempt was made to set a report parameter 'xmlData' that is not defined in this report. But it is defined with my parameters and also one default in my report parameters
some code:
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null;
ParameterValue[] optionalParams = null;
Warning[] optionalWarnings = null;
DataSet ds=new DataSet();
ds.ReadXml("C:\\RS\\C#\\test.xml");
string Param=ds.GetXml().ToString();
ParameterValue[] pv = new ParameterValue[1];
pv[0] = new ParameterValue();
pv[0].Name = "xmlData";
pv[0].Label = "xmlDataLabel";
pv[0].Value = Param;
ResultStream = rs.Render("/test/Custom", "PDF", null, "some xml",
pv,null, null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
Response.BinaryWrite(ResultStream);
this code requests a PDF from the report /test/Custom wich use an external dataset
I calls my external dataset but I cant find my parameters
some code external dataset
public Microsoft.ReportingServices.DataProcessing.IDataParameterCollection
Parameters
{
get { return _parameters; }
}
public class DSXParameter : IDataParameter
{
string _parameterName;
object _parameterValue;
// Default constructor
public DSXParameter()
{
}
// Constructor to accept parameter name & value.
public DSXParameter(string parameterName, object value)
{
_parameterName = parameterName;
_parameterValue = value;
}
public String ParameterName
{
get { return _parameterName; }
set { _parameterName = value; }
}
public object Value
{
get
{
return _parameterValue;
}
set
{
_parameterValue = value;
}
}
}
--
Doesn't reach this method ExecuteReader
--
public Microsoft.ReportingServices.DataProcessing.IDataReader ExecuteReader
(Microsoft.ReportingServices.DataProcessing.CommandBehavior behavior)
{
DSXExtensionDataReader testReader = new DSXExtensionDataReader(this.Parameters);
testReader.CreateDataSet();//_connection._xmlSchema
return testReader;
}
Hope you can help me
Thanks in advance
]]>
From http://www.developmentnow.com/g/115_2004_10_0_0_452155/Calling-External-Code-that-returns-a-Dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com
Tuesday, March 20, 2012
Calling DTS package from VB/Ms-Access 2000
Hi All,
My name is kanishk. I am facing some issue in calling the DTS package from Visual Basic code.
The brief history of issue is :
We have a application which uses Ms-Access as front end and MS-Sql server 2000 as backend. Perviously we has MS-Access 97 version and MS-Sql 2000 .In this comination the DTS package was working fine.
Now We have changes the fornt end to MS-Access 2002. Here the DTS package is not working .
Can you please help me out to get the proper syntax to call the DTS package from VB code.What's your current syntax and what SP you have on the server?|||The DTS package is called from VB code. There is no SP at server side for DTS calling.
The Syntax in MS-Access(VB code )in version 97 is :
Sub RunDTS(strServer As String, strUser As String, strPassword As String, strDTS As String)
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer _
ServerName:=strServer, _
ServerUserName:=strUser, _
ServerPassword:=strPassword, _
PackageName:=strDTS
dtsp.Execute
DoEvents
End Sub|||First make sure you have : Microsoft Dtspackage Object library activated in visual basic references.
Here is a visual basic code template :
Dim cn as ADODB.connection
Dim oPKG As DTS.Package
dim i as integer
SET cn = currentproject.connection
Set oPKG = New DTS.Package
if cn.Properties("Integrated Security") <> "SSPI" Then
'oPKG.LoadFromSQLServer cn.Properties("Data Source"), cn.Properties("USER ID"), cn.Properties("PASSWORD"), DTSSQLStgFlag_Default, , , , "DtsName"
Else
'oPKG.LoadFromSQLServer cn.Properties("Data Source"), ,, DTSSQLStgFlag_UseTrustedConnection, , , , "DtsName"
End If
On oPKG.FailOnError GoTo HandleErr
' -------- Facultatif , Use only in case you want to determine source file location at runtime
i = 1
For i = 1 To oPKG2.Connections.Count
FileSourceName = oPKG.Connections.Item(i).DataSource
oPKG.Connections.Item(i).DataSource = Me.FileLocation '( source file full name )
Next i
' -------
oPKG.Execute
oPKG.UnInitialize
Set oPKG = Nothing
ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & vbCrLf & vbLf & Err.Source & vbCrLf & vbLf & Err.Description, vbCritical
Resume ExitHere
My name is kanishk. I am facing some issue in calling the DTS package from Visual Basic code.
The brief history of issue is :
We have a application which uses Ms-Access as front end and MS-Sql server 2000 as backend. Perviously we has MS-Access 97 version and MS-Sql 2000 .In this comination the DTS package was working fine.
Now We have changes the fornt end to MS-Access 2002. Here the DTS package is not working .
Can you please help me out to get the proper syntax to call the DTS package from VB code.What's your current syntax and what SP you have on the server?|||The DTS package is called from VB code. There is no SP at server side for DTS calling.
The Syntax in MS-Access(VB code )in version 97 is :
Sub RunDTS(strServer As String, strUser As String, strPassword As String, strDTS As String)
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer _
ServerName:=strServer, _
ServerUserName:=strUser, _
ServerPassword:=strPassword, _
PackageName:=strDTS
dtsp.Execute
DoEvents
End Sub|||First make sure you have : Microsoft Dtspackage Object library activated in visual basic references.
Here is a visual basic code template :
Dim cn as ADODB.connection
Dim oPKG As DTS.Package
dim i as integer
SET cn = currentproject.connection
Set oPKG = New DTS.Package
if cn.Properties("Integrated Security") <> "SSPI" Then
'oPKG.LoadFromSQLServer cn.Properties("Data Source"), cn.Properties("USER ID"), cn.Properties("PASSWORD"), DTSSQLStgFlag_Default, , , , "DtsName"
Else
'oPKG.LoadFromSQLServer cn.Properties("Data Source"), ,, DTSSQLStgFlag_UseTrustedConnection, , , , "DtsName"
End If
On oPKG.FailOnError GoTo HandleErr
' -------- Facultatif , Use only in case you want to determine source file location at runtime
i = 1
For i = 1 To oPKG2.Connections.Count
FileSourceName = oPKG.Connections.Item(i).DataSource
oPKG.Connections.Item(i).DataSource = Me.FileLocation '( source file full name )
Next i
' -------
oPKG.Execute
oPKG.UnInitialize
Set oPKG = Nothing
ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & vbCrLf & vbLf & Err.Source & vbCrLf & vbLf & Err.Description, vbCritical
Resume ExitHere
Subscribe to:
Posts (Atom)