I'm assuming it's possible to call a web service asynchronously from a SQL CLR function, is this correct or is there a fundamental flaw in calling the asynchronous methods?
I've tried coding a sample and despite creating a new thread, which in turn calls the web service asynchronously and using newthread.join I can never get the webmethodasync_completed sub to be called.
In my shared hosting environment where I am making large volumes of database calls, I am very anxious to preserve my heavily throttled threads-I presume when I make a database call, that it is going to a separate server and any threads that server may be using are in addition to my " hosting plan ".
My question (s) is, is it therefore a no-brainer to always make calls to:Dim oResult As IAsyncResult = iDBCmd.BeginExecuteReader()arWaitHandles(0) = oResult.AsyncWaitHandleiIndex = WaitHandle.WaitAny(arWaitHandles, 60000, False)
i.e. run all calls to the database asynchronously?
Is it really releasing a thread back to my asp.net App while it is waiting? (I.e. Is it worth doing)
Yes, I know there is at least one other thread about this type of thing, but my error is a bit different............
I have been trying to call a webservice from within a trigger and i keep receiving the following error:
Msg 6522, Level 16, State 1, Procedure wepTestTrigger, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'wepTestTrigger':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.CredentialCache.get_DefaultCredentials()
at Triggers.wepTestTrigger()
.
The statement has been terminated.
How or what do I need to do in order to call this webservice?
Thank you for any help.
Wayne E. Pfeffer
All -
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
Thanks.
Hey guy and gals,
I need to connect to a web service from a Sproc, can this be done? I've google and found out about Httpendpoints but that is running the web service on the server, not connecting to one from the server in a sproc. Any ideas or site links would be helpful. TIA.
Jason
I have a lengthy stored procedure that does archiving that I'd just like to start and let it run, quickly returning control back to the ASP.net web page. To accomplish this my research shows that I should be passing the "adAsyncExecute" option but all of the examples I've been able to find don't jive with the way I execute stored procedures. Shown below is the code I'm using. How would I introduce "adAsyncExecute" ? public static int DoStoredProcedure(SqlConnection aConn, string procName, params SqlParameter[] theParams) { int retVal = 0; SqlCommand aComm = new SqlCommand(procName, aConn); aComm.CommandType = CommandType.StoredProcedure; foreach(SqlParameter aParam in theParams) { aComm.Parameters.Add(aParam); } SqlParameter returnValue = aComm.Parameters.Add("@ReturnValue", SqlDbType.Int); returnValue.Direction = ParameterDirection.ReturnValue; try { aConn.Open(); aComm.ExecuteNonQuery(); retVal = (int) returnValue.Value; } catch(Exception ex) { Debug.Fail("Error running SQL Query: " + ex.Message, "DBTools.DoStoredProcedure"); throw; } finally { aConn.Close(); } return retVal; }
View 6 Replies View RelatedI'm working with ADO 2.8 en C++ with Visual Studio 2005. I want to perform a "select" in asynchronous mode. I don't really understand the logical of the recordset events. For example, I received a number of MoveComplete event higher than the number of rows in my recordset. It is really not clear for me ...
Does someone knows where I can find a a good example of C++ (or VB) code to manage select statements in asynchronous mode ?
Thanks in advance for your help.
François.
Hello, I appeal to all to save me from this situation.
I have a WebService in VB.net by IIS and a operation to connect to the SQL server and run a query and others operations. All fine...
But after a certain time only the SQL operations don't respond nothing, the others are OK. So the problem is not in the IIS i think and something between the IIS and the SQL Server.
Can you give me some light about this PLEASEEEE :D ?
Thanks
If any of you need the source say it.
Thanks
If the web service is talking to a SQL Server database and the authentication used is SSPI (Security Support Provider Interface
) / windows authentication, does the ASP.Net user or machine id user
need to be added to the list of allowed ids to the database server? Or
am I misunderstanding the security constraints?
If I run my code through an Nunit test conduit, I can retrieve data,
but If I run it through my webservice it fails authentication.
advTHANKSance
Hi Experts,
I have to create Installer or setup for my project.
My project Containg 2 webservices and one applicationProject.
I donot Know About Installer How To create SetUp For AboveProject . I want In setup wizard shows in grid it shows the 2 Webservices and PrudentialApplications. and How To add a sqlserver Bootstrapper in this application.
Regards
Venkat.
Hi,
I need to expose data in a SQL server 2005 database as a webservices. I have 2 options to choose. I can create an End point in sql or build a webservices in .NET.
So what are de advantages and disadvantages of those 2 approaches ?
Of course they want the webservices secure. I know that authentication is not a problem since we use windows integrated security for the intranet. But what will happen if we move to an extranet or the internet ?
But how do you enable authorization ? and Context security ? Is context security better done in .NET then in SQL ?
Thanks in advance.
Within a stored procedure, is it possible to call multiple other stored procedures asychronously? For example, I'd like to execute both local and remote stored procedures, but don't want/need to wait for the output while the original stored procedure continues to execute each subsequent command.
View 1 Replies View RelatedI have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
-Jeremy
___________________________
Geek At Large
Is it possible to execute a child package from a parent package ashnchronously.
I have a SQL Server table containing a list of packages to execute. I want to create a master package that will query this table, and execute each of the packages asynchronously.
I've tried using the Execute Package task and also executing packages programtically from a Script task, but these only seem to work synchronously.
I have also seen suggestions about executing packages asynchronously from T-SQL by starting a job, however, I'd rather not have to dynamically create jobs for each package I want to execute.
Any ideas would be welcome. Or even an answer of "No this is not possible".
Thanks in advance
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
Hello everyone,
I am newbie in SSIS and I have one problem.
I have defined in Control Flow one WebService Task, specified the WSDL-File.
I have chosen one method from WS as Input. But this method requires one parameter of the ArrayOf_xsd_string type.
If I try to choose it, I receive following error:
===================================
This complex type does not contain any properties. (Microsoft.SqlServer.WebServiceTaskUI)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.CustomGridControl.HandleEllipseBtn(Int64 row, Int32 col)
in my WSDL-File it already defined:
Code Snippet<complexType name="ArrayOf_xsd_string"><complexContent><restriction base="soapenc:Array"><attribute ref="soapenc:arrayType" wsdl:arrayType="xsd:string[]"/>
Could you help me? Where I can define this ArrayOf_xsd_string type, or how can I correct my problem?
Thanks a lot for your Help!
Regards,
Question is in the subject.
The reason I'm asking is because I want a workaround to a problem that a guy is having here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404349&SiteID=1
-Jamie
I developed a java proxy application for SSRS(Sql Server Reporting services) and able to render the reports by making webservice calls to SSRS webservices. But I am getting the data for the complete report. How can I get only a partial report( like page by page as we get in the Report Manager)? Which parameters should I pass.
As some of the reports are too big, the java application is getting memory issues and I want to limi the data that I get from SSRS.
Appreciate your suggestions.
Hi !!!
I have to use 2 web services !
One (WebServices A) which contains methods I need to call, and the other (WebServices B) which contains a method who returns a autenticationCookie, that I have to use in parameter for call method from WebServices A ?
It is clear ? I hope...
So my question is how I can do ?
I know I can create few dataSet, but how can I pass a data from on dataSet (from my WebServices B) to the query on an other dataSet (from my WebServices A) ?
Thank your help !
if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.
View 4 Replies View RelatedI have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.
There are two ways I can approach it
1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.
2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.
Hi there, I am just trying to create an ENDPOINT to define a Web Service in Sql
server 2005. Here is my code
Create ENDPOINT AWService STATE = STARTED As HTTP ( PATH = '/ADVENTUREWORKS', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'DEVISQLDM2005' ) FOR SOAP ( WEBMETHOD 'GetProductList' (NAME = 'AdventureWorks.Dbo.GetProductList'), DATABASE = 'AdventureWorks', WSDL = DEFAULT )
and when i run this T-SQL its complaining about
Msg 7807, Level 16, State 1, Line 1 An error ('0x80070057') occurred while attempting to register the endpoint 'AWService'.
I am trying this code to run on Windows XP pro and Sql Server 2005. Any
clue why its complaining about this ?
Thanks -D
I developed one DLL in vb .Net which call two web services (sql endpoints) I wish to use this solution but for hundreds of servers (endpoints) I want to ask if I could do that with dynamic parameters passed to de DLL CALL in sql server clr.
Actually when I compile de DLL I need to have referenced (hardcoded) the webservices (endpoints) and when I need a brand new endpoint I need to reference it, compile the dll and release.
I cannot find how to invoke the service within the DLL with the namespace or webservice name as parameter.
Mi code to invoke now my referenced end point is: (note that my endpoint name is rperez)
Dim CallWebService As New rperez.IntelisisServiceEndPoint
CallWebService.Credentials = System.Net.CredentialCache.DefaultCredentials
Call CallWebService.IntelisisService(ArchivoOriginal)
My class rperez.IntelisisServiceEndPoint is referenced (hardcoded) in the dll project I need it dynamic
Do I need to change my code to PROXY object ?
I have requirement to update/insert the DPID based on the address which are passed as an input values.There are more than one address at the same time and I configured to get the address from the query which are correct and output of the address values will be stored as system object variable.I am then passing the system object variable to for each loop container and I have configured the collection and variable mappings as a variable for each input value.
when I pass the value manually to the web service task it works correctly.When I pass it as a variable to web service task it doesn't return any value.I have a data flow task which converts the ouput from web service task using the xml source converts it to oledb destination.I don't see any rows being written to the target table.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedHow can I get only one page of data each time when I call the render menthod from the SSRS webservice
View 3 Replies View RelatedHi all, i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy. I need to use ActiveX to add a standard name to the last column in the destination table. the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently. If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work. Can anyone tell me what i may be doing wrong. heres the calling code from C# if(f.Name.Substring(13,7).ToLower() == "product")
{
try
{
activity.Log("Starting Product DTS Package...");
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer(
"192.168.8.8",
"username",
"thepassword",
Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,
null,
null,
null,
(string)ConfigurationSettings.AppSettings["productDTSPackage"],
ref pVarPersistStgOfHost);
package.GlobalVariables.Item(1).Value = f.FullName.ToString();
package.Execute();
package.UnInitialize();
//force release of COM object
System.Runtime.InteropServices.Marshal.ReleaseComObject(package);
package = null;
}
catch(Exception e)
{
activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message);
} and the activex i tried to use for the transformations is: '**********************************************************************' Visual Basic Transformation Script'************************************************************************' Copy each source column to the destination columnFunction Main() DTSDestination("Yesmail Id") = DTSSource("product series") DTSDestination("Customer CKM CustId") = DTSSource("product family") DTSDestination("Product SKU") = DTSSource("transaction date") DTSDestination("product model name") = DTSSource("serial number") DTSDestination("serial number") = DTSSource("product model name") DTSDestination("transaction date") = DTSSource("Product SKU") DTSDestination("product family") = DTSSource("Customer CKM CustId") DTSDestination("product series") = DTSSource("Yesmail Id") DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value <-- this is why im using the activex for this field to get a Global Variable. Main = DTSTransformStat_OKEnd Functionthe weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor? BTW: i do have my assembly signed in C# for the COM wrapper.thanks in advance, mcm
Hello,
I have done extensive work with Classic ASP for 9 years now. Working with Stored Proceedures etc
However now am working in c# am adviced by a friend that its best to use the
1. The "Object data source" to call SP
2. Are there any documentations pointers on best practices, how its done ?
I want to use the "on click event" in my class file.
thanks
Ehi
Hello,
are there any sample codes that show you how to execute a SP in .net
1. Using a class
2. Calling the class in the onlick_button function ?
thanks
Ehi
I'm developing a new Stored Proc that will be taking information in to enter customer and order info. Looking at the current Stored Proc its using alot of If statements and I'm thinking of breaking the sp up into different Stored Procs and calling them all from One main stored proc. I know how to do this but I was wondering how it would effect performance. So should I simply keep all the stuff in one proc or module it out into multiple ones to make it easier to follow and read?
Thanks
Does anyone have an example of a Stored Procedure calling
another stored procedure and passing it parms.
What I would like to do is: Have a stored procedure select
data from a table and pass that data to another stored
procedure.
Thanks in advance,
Rodney
Hi,
Can I call SP in the Trigger? Googled for this but din gt any satisfactory answer.
Thnkx,
Rahul Jha
I'm working in SQL Server 2005. I have an existing SP that does exactly what I need (it's the aspnet_UsersInRoles_IsUserInRole SP). I want to reuse this SP and use it's return value as a field in the SP I'm writing. How do I go about doing this? I could take the logic out of the called SP and wrap it in a function; but, I would really like to reuse the SP aspnet_UsersInRoles_IsUserInRole. Thanks in advance for any assistance.
View 7 Replies View Related