have a Java Class that I want to be able to access from within a SQL Server Stored procedure. I know we can access C# and other .NET Framework code, but how can you access a Java Class?
How would you access a Java class from "normal" .NET code?Niels
Hi, I have to researching of how to accesss package tasks and component using the SqlServer.Dts.Runtime class and so far, I havent found any solution. For example, if you package has a scriptiong task and a data flow task(<- which contains a data source component). Is it possible to use the scripting component to access the data source component in the data flow task and manipulate its properites like sqlcommand etc.
Emmanuel
An easier and more direct way to do this is to store the sqlcommand for the datasource in a package variable and set the variable value in the script task or even at the time of executing the package.
|||I know that can be done using variables and expressions and all that but i want to know where its possible to access component and dask of a package in a scripting task using a SqlServer.Runtime library. I know you can pass variable around be I want to know how to control component properties in a scripting task or component if possible. If anyone know how to do that or can direct me to the right resource, I will be glad. Thanks.|||Nope, the tasks and components can't directly access each other at runtime.I need to access a billing webservice from SQL. I createde a new c# class project and made a web refrence to the web service "ProdBilling".
Here is the code of my assembly
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace PaymentProc
{
public class PaymentProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ChargeCard(int account, int amount)
{
string Response;
ProdBilling.Service serv = new ProdBilling.Service();
Response = serv.ChargeCard(account, amount);
SqlContext.Pipe.Send(Response);
}
}
}
I then ran WSDL
wsdl /o
Then compliled
csc /target:library PaymentProc.cs
and added the assembly
CREATE ASSEMBLY PaymentProc from 'D:\ProdCode\PaymentProc.dll' WITH
PERMISSION_SET = UNSAFE
I cannot figure out how to refrence the chargecard method
I have tried
CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
It seems wsdl.exe put all this serialization code
namespace PaymentProc {
using System.Diagnostics;
using System.Web.Services;
using System.ComponentModel;
using System.Web.Services.Protocols;
using System;
using System.Xml.Serialization;
///
[System.CodeDom.Compiler.GeneratedCodeAttribute("wsdl", "2.0.50727.42")]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Web.Services.WebServiceBindingAttribute(Name="ServiceSoap", Namespace="http://ProdWeb1/PaymentProc")]
public partial class PaymentProc : System.Web.Services.Protocols.SoapHttpClientProtocol {
private System.Threading.SendOrPostCallback ChargeCardOperationCompleted;
///
public PaymentProc()
{
this.Url = "http://ProdWeb1/PaymentProc/PaymentProc.asmx";
}
///
public event ChargeCardCompletedEventHandler ChargeCardCompleted;
///
[System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ProdWeb1/PaymentProc/ChargeCard", RequestNamespace="http://ProdWeb1/PaymentProc", ResponseNamespace="http://ProdWeb1/PaymentProc", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
public string ChargeCard(int account, int amount) {
object[] results = this.Invoke("ChargeCard", new object[] {
account,
amount});
return ((string)(results[0]));
}
.................
When I run
CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
I get error
Method, property or field 'ChargeCard' of class 'PaymentProc.PaymentProc' in assembly 'PaymentProc' is not static.
Any ideas? This seemsed so straitforward in the beginning.
Change your ChargeCard CLR method (the one you are marking as a proc) to some other name, and then change the CREATE PROCEDURE statement to use that changed name. That should hopefully do it.Niels
|||
I think my problem originates from the fact that you cannot complie an assembly using a web refrence using csc. I redesigned my assembly with a web refrence to http://ProdWeb1/PaymentProc/PaymentProc.asmx called ProdBilling which I can test operation ChargeCard successfully.
using System;
using System.Collections.Generic;
using System.Text;
namespace PayProcAssembly
{
public class PaymentProcessing
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static string ChargeCard(int Account, float Amount)
{
ProdBilling.PayProcessing serv = new ProdBilling.PayProcessing();
string result = serv.ChargeCard(Account, Amount);
SqlContext.Pipe.Send(result);
}
}
}
When I run
csc /target:library PaymentProcessing.cs
I get
Error: The type or namespace name 'ProdBilling could not be found (are you missing a using directive or an assembly reference?)
I was told I needed to create a proxy using WSDL.exe but it seems when I ran
wsdl /o PaymentProc.cs /n PaymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx
It messed up my code. I cant even find the web refrence anymore. Is this correct that I have to use wsdl or is there an easier way?
I need to access a billing webservice from SQL. I createde a new c# class project and made a web refrence to the web service "ProdBilling".
Here is the code of my assembly
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace PaymentProc
{
public class PaymentProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ChargeCard(int account, int amount)
{
string Response;
ProdBilling.Service serv = new ProdBilling.Service();
Response = serv.ChargeCard(account, amount);
SqlContext.Pipe.Send(Response);
}
}
}
I then ran WSDL
wsdl /o
Then compliled
csc /target:library PaymentProc.cs
and added the assembly
CREATE ASSEMBLY PaymentProc from 'D:\ProdCode\PaymentProc.dll' WITH
PERMISSION_SET = UNSAFE
I cannot figure out how to refrence the chargecard method
I have tried
CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
It seems wsdl.exe put all this serialization code
namespace PaymentProc {
using System.Diagnostics;
using System.Web.Services;
using System.ComponentModel;
using System.Web.Services.Protocols;
using System;
using System.Xml.Serialization;
///
[System.CodeDom.Compiler.GeneratedCodeAttribute("wsdl", "2.0.50727.42")]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Web.Services.WebServiceBindingAttribute(Name="ServiceSoap", Namespace="http://ProdWeb1/PaymentProc")]
public partial class PaymentProc : System.Web.Services.Protocols.SoapHttpClientProtocol {
private System.Threading.SendOrPostCallback ChargeCardOperationCompleted;
///
public PaymentProc()
{
this.Url = "http://ProdWeb1/PaymentProc/PaymentProc.asmx";
}
///
public event ChargeCardCompletedEventHandler ChargeCardCompleted;
///
[System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ProdWeb1/PaymentProc/ChargeCard", RequestNamespace="http://ProdWeb1/PaymentProc", ResponseNamespace="http://ProdWeb1/PaymentProc", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
public string ChargeCard(int account, int amount) {
object[] results = this.Invoke("ChargeCard", new object[] {
account,
amount});
return ((string)(results[0]));
}
.................
When I run
CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard
I get error
Method, property or field 'ChargeCard' of class 'PaymentProc.PaymentProc' in assembly 'PaymentProc' is not static.
Any ideas? This seemsed so straitforward in the beginning.
Change your ChargeCard CLR method (the one you are marking as a proc) to some other name, and then change the CREATE PROCEDURE statement to use that changed name. That should hopefully do it.Niels
|||
I think my problem originates from the fact that you cannot complie an assembly using a web refrence using csc. I redesigned my assembly with a web refrence to http://ProdWeb1/PaymentProc/PaymentProc.asmx called ProdBilling which I can test operation ChargeCard successfully.
using System;
using System.Collections.Generic;
using System.Text;
namespace PayProcAssembly
{
public class PaymentProcessing
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static string ChargeCard(int Account, float Amount)
{
ProdBilling.PayProcessing serv = new ProdBilling.PayProcessing();
string result = serv.ChargeCard(Account, Amount);
SqlContext.Pipe.Send(result);
}
}
}
When I run
csc /target:library PaymentProcessing.cs
I get
Error: The type or namespace name 'ProdBilling could not be found (are you missing a using directive or an assembly reference?)
I was told I needed to create a proxy using WSDL.exe but it seems when I ran
wsdl /o PaymentProc.cs /n PaymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx
It messed up my code. I cant even find the web refrence anymore. Is this correct that I have to use wsdl or is there an easier way?
Hay there,
I have an asp.net project website with an sql Database. I need to access this same database from another project (a class library that needs to access this database). In fact i want to be able to use the tableadapters that I have implemented for my database in this new class library..
I guess it has to do something with making my database not part of the website but and independemt entitiy that can be seen by others.... I cannot seem to find a way to do that can anyone help meee ??
Thanks for your time!
If its a database then how is it tied to application.
Atleast It must have the mdf files which you can create a real independent entity and then play wiith it using connection strings.
|||
All your databases resides in a databse server (e.g. MS SQL Server 2005).
What you need is to point your ConnectionString from your application to that database.
My guess is, you have two projects (e.g. ProjectA and ProjectB) and thay are using diffrent databases but in some part of ProjectB needs to use the same database of ProjectA.
So, you should have two ConnectionStrings for ProjectB. One you will use it for most part of the project and it is specific to ProjectB (keep it in Web.Config file) while you need another ConnectionString that will access the database (which ProjectA is using it) from ProjectB.
Here, have another ConnectionString in the web.config file of ProjectB and lets it point to the same database used by ProjectA.
Good luck.
|||Hi,
Thanks for your very clear reply and clarification.
I can't seem to apply your reply to my situation as My project B is just a class library i.e. I have no webconfig ...
Note that project A and Project B are part of the same solution too.
Thanks for your help.
Hi ekosha,
Why you have the databse within the project itself?!
|||
Well good question, Well I was just implementing this application then and didn't think I would need to use it from another project. seems that was a wrong decision any idea how I can fix that?
ekosha:
Well good question, Well I was just implementing this application then and didn't think I would need to use it from another project. seems that was a wrong decision any idea how I can fix that?
This database has a file with .mdf extension, take that file and attached to a new database in MS SQL Server.
Now, you can use that database for all your projects (even if they are within one solution).
Good luck.
I'd like to do some database access from a class library. All of the ways I'm familiar with for adding database connectivity revolve around a Windows Form and dragging stuff onto it. These facilities don't exist for a class library really.
What is the prefered way to do this? Should I create a dummy winform for the sole purpose of hosting the database objects? Or are there ways to manually create the database objects in the library itself. The thing that seems to tripping me up however is that all of the classes get customized and generated when you drag them over and that aspect would be missing if I were to "roll it by hand"
Thanks
Bill
There are a number of examples of providing data access from a class library.
I believe this article on creating strongly typed datasets, will work within a class library project as well.
Hi,
for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).
For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?
Any hints?
Thanks.
Thorsten
I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.tviel wrote:
Hi,
for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).
For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?
Any hints?
Thanks.
Thorsten
I hope this helps.
|||If you look at the two objects and what they inherit or implement, you can see that will never happen.
You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.
Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.
Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.
|||Hi Darren,
thanks for your reply, maybe there is another alternative for generating a solution.
I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.
Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.
For so, i have the ability to configure some values of similar tasks by one.
Hope this makes things clear.
Thanks.
PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.
|||I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.
Example:
I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.
I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx
Thanks,
Graham
|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||Hi,
understandable.
But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.
OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.
Thanks
Thorsten
|||OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.
This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx
|||Hi Michael,
fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)
Thanks
Thorsten
|||What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?
Thank you,
Graham
|||If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.
If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.
|||Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )
class CustomTask : Task
{
private static string topMostPackage = null;
private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;
//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}
Hi,
for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).
For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?
Any hints?
Thanks.
Thorsten
I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.Hi,
for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).
For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?
Any hints?
Thanks.
Thorsten
I hope this helps.
|||If you look at the two objects and what they inherit or implement, you can see that will never happen.
You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.
Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.
Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.
|||Hi Darren,
thanks for your reply, maybe there is another alternative for generating a solution.
I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.
Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.
For so, i have the ability to configure some values of similar tasks by one.
Hope this makes things clear.
Thanks.
PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.
|||I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.
Example:
I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.
I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx
Thanks,
Graham
|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||Hi,
understandable.
But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.
OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.
Thanks
Thorsten
|||OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.
This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx
|||Hi Michael,
fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)
Thanks
Thorsten
|||What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?
Thank you,
Graham
|||If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.
If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.
|||Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )
class CustomTask : Task
{
private static string topMostPackage = null;
private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;
//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}
Hi, I'm using Reporting Services on my website and for some reports, i get the timeout error after 30 secs.
I declared my object using the Reporting tools and I am using ObjectDataSource.
My report is declared in my aspx page:
<rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt" Width="100%" Height="600px">
My ObjectDataSource is declared in my aspx page under my report tag:
<asp:ObjectDataSource ID="ObjectDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="ReportsTableAdapters.ViewingTotalsTableAdapter">
My TableAdapter is declare in my Reports.xsd file:
<TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="ViewingTotalsTableAdapter" GeneratorDataComponentClassName="ViewingTotalsTableAdapter" Name="ViewingTotals" UserDataComponentName="ViewingTotalsTableAdapter">
I am trying to access the TableAdapter object from my WebPage Class on the Page_Load event , so I can modify the Command.CommandTimeout. Unfortunately, I cannot find a way to have access to the TableAdater object.
Anyone has any suggestion ?
Thanks,
Richard
Did you ever get a response to this? I am having the same problem.
Hi, I'm using Reporting Services on my website and for some reports, i get the timeout error after 30 secs.
I declared my object using the Reporting tools and I am using ObjectDataSource.
My report is declared in my aspx page:
<rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt" Width="100%" Height="600px">
My ObjectDataSource is declared in my aspx page under my report tag:
<asp:ObjectDataSource ID="ObjectDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="ReportsTableAdapters.ViewingTotalsTableAdapter">
My TableAdapter is declare in my Reports.xsd file:
<TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="ViewingTotalsTableAdapter" GeneratorDataComponentClassName="ViewingTotalsTableAdapter" Name="ViewingTotals" UserDataComponentName="ViewingTotalsTableAdapter">
I am trying to access the TableAdapter object from my WebPage Class on the Page_Load event , so I can modify the Command.CommandTimeout. Unfortunately, I cannot find a way to have access to the TableAdater object.
Anyone has any suggestion ?
Thanks,
Richard
Did you ever get a response to this? I am having the same problem.
