Trying To Call The Function A Web Service From Transact-SQL
Jun 2, 2006
I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.
I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?
Thanks for any help.
ALTER PROCEDURE [dbo].[pTAPServiceWeb]
@sUrl varchar(200),
@response varchar(8000) out
AS
DECLARE @obj int
DECLARE @hr int
DECLARE @status int
DECLARE @msg varchar(255)
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
IF @hr < 0
BEGIN
RAISERROR('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1)
RETURN
END
EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod Open failed'
GOTO err
END
EXEC @hr = sp_OAMethod @obj, 'send'
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod Send failed'
GOTO err
END
EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod read status failed'
GOTO err
END
-- IF @status <> 200
-- BEGIN
-- SET @msg = 'sp_OAMethod http status ' + str(@status)
-- GOTO err
-- END
EXEC @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod read response failed'
GOTO err
END
EXEC @hr = sp_OADestroy @obj
RETURN
err:
EXEC @hr = sp_OADestroy @obj
RAISERROR(@msg, 16, 1)
RETURN
GO
View 3 Replies
ADVERTISEMENT
May 20, 2015
I have a function like below
CREATE FUNCTION [dbo].[UDF_GetCode]
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)
[code]...
This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.
View 3 Replies
View Related
May 18, 2007
Hello! I have the following problem. I developed CLR Stored Procedure "StartNotification" and deploy it on db. This sp calls external web service. Furthermore, this sp is called according with SQL Server Agent Job's schedule. On my PC SQL Server works under Local System account and this web service is called correctly (Executed as user: NT AUTHORITYSYSTEM). But on ther other server the following exception is raised during job running:
Date 17.04.2007 16:42:10
Log Job History (FailureNotificationJob)
Step ID 1
Server MSK-CDBPO-01
Job Name FailureNotificationJob
Step Name MainStep
Duration 00:00:00
Sql Severity 16
Sql Message ID 6522
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CORPmssqlserver.
A .NET Framework error occurred during execution
of user defined routine or aggregate 'StartNotification':
System.Security.SecurityException: Request for the permission of type
'System.Net.WebPermission, System, 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. The step failed.
What is the reason of this behaviour? Unfortunately I do not have direct access to this server.
I have the following guesses:
1) CORPmssqlserver may have not enough permissions to call web service
2) Something wrong with SQL Server account's permissions
2) Something wrong with SQL Server Agent account's permissions
I will take the will for the deed. Thanks.
View 1 Replies
View Related
May 4, 2006
With the new improvments to the web service task, we can now use variables as arguments in web service calls. I am trying to setup a call to the amazon web service ECS. I am trying to do a simple sellerlookup. I have played with the settings and gotten nowhere. I get one of two error when I try to execute. I can always use a scripting task or write my own task, but I would like to use the built in task if it is possible. Has anyone used AWS with SSIS?
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
or
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Method 'ProxyNamespace.AWSECommerceService.SellerLookup' not found.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
View 6 Replies
View Related
Jul 20, 2005
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies
View Related
Sep 14, 2006
I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.
View 4 Replies
View Related
Feb 16, 2008
With regular .net web services in vb.net I can expose the service and call it like this:
http://server/EnrollmentLookup/EnrollmentService.asmx/GetS3MonitorLogs?StartDate=02/02/08&EndDate=02/05/08
But when I create a SQL XML Webservice via an endpoint all I can find on the net is how to show the wsdl.
http://localhost/Contacts?wsdl
Ive created a method in the "Contacts" webservice called GetContacts. Why cant I construct a similar url to execute that method without having to write a vb.net wrapper for it?
View 6 Replies
View Related
Mar 28, 2006
can i make a function call from stored procedure
View 3 Replies
View Related
Feb 26, 2008
Does anybody knows how to call a function from one VB source file to another VB source file??
I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??
View 1 Replies
View Related
May 3, 2004
Hi,
I have a situation where I need to call a web service from the database (by an insert trigger on a table). Please let me know how I could do this.
Thanks in advance
Raj
View 1 Replies
View Related
May 3, 2004
Hi,
I have a situation where I need to call a web service from the database (by an insert trigger on a table). Please let me know how I could do this.
Thanks in advance
Raj
View 1 Replies
View Related
May 21, 2007
It is possible to call WCF from SQL CLR, can I add a web reference or use the proxy created with svcutil ?
View 1 Replies
View Related
Feb 25, 2008
Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in.
1 ALTER PROCEDURE [dbo].[GetPortalSettings]2 (3 @PartyId uniqueidentifier,45 AS6 SET NOCOUNT ON7 CREATE TABLE #Groups8 (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)
View 4 Replies
View Related
May 9, 2006
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function
SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
View 1 Replies
View Related
Dec 11, 2001
Hi,
Does anyone know if/how SQL server can call a function in a C++ library?
Cheers,
Xiaobing
View 4 Replies
View Related
Aug 1, 2006
Hi,
I want to write one function like that
dbo.function( number , 1 , 2 ) ,
but I would like to overload, and send char or number
dbo.function( number, 'abx' , ' xpto' ).
I would like to keep the same name, Can I do this? or Do I need to write to differents functions
thanks,
View 11 Replies
View Related
Aug 10, 2007
I have a procedure which has query
like Query 1.
Query 1
Select Clinetid
from clinet
inner join {
select centerid from GetChildCenter(@Centerid)
union
select centerid from getParentCenter(@Centerid)
} as Center c
on c.Centerid = client.Centerid
Query 2
declare @Center table ( centerid int)
insert into @Center
select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)
Select Clinetid
from clinet
inner join @Center c on c.Centerid = client.Centerid
I just want to know which one is better performance wise..
because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()
View 1 Replies
View Related
Mar 5, 2007
I would like to call a web service from a sql server 2000 s.proc/sql job, but if there is a better way please let me know. I'm using Captaris Workflow to create and manipulate workflow processes. I have a sql job that will create some pdf's. I want to call the web service that creates a new process in a step from that sql job. I have read about doing this with extended s.procs, but I'm trying to find any possible solutions. Essentially, the web method I want to call will insert several records into sql so I know I can at least do that. I would still like to know if I can make a call to the web service from the sql job. Thanks for any help.
View 1 Replies
View Related
Mar 19, 2008
Hello,
I have a microsoft report that uses a dll to calculate a value. If I hard code the value, my report shows it. My method in the dll is connecting to a database in order to calculate a result. However, in order to connect to the database, I am using Microsoft.Practices.EnterpriseLibrary.
The error in my report is:
Could not load file or assembly 'Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
I have added a reference in the report to the EnterpriseLibrary but this has not made a difference.
Many thanks!
View 1 Replies
View Related
Oct 6, 2006
Hi,
I'm hoping someone has tried something similar before. I am trying to run an integration from a remote computer using a web service. I have set up the web service and the bog standard launch package routine works fine. What i'd like to be able to do is to launch a package and also specify any parameters associated with it. I have the following code which is identcal to the main launch sub with the exception of an extra argument (jagged array of varible name and value variablePair[4][2]). It also processes the variable array and sets them on the DTSPackage object.
[code]
[WebMethod]
public int LaunchSSISPackageWithVariables(string sourceType, string sourceLocation, string packageName, string[][] variableArray)
{
string packagePath;
Package myPackage;
Application integrationServices = new Application();
// Combine path and file name.
packagePath = Path.Combine(sourceLocation, packageName);
switch (sourceType)
{
case "file":
// Package is stored as a file.
// Add extension if not present.
if (String.IsNullOrEmpty(Path.GetExtension(packagePath)))
{
packagePath = String.Concat(packagePath, ".dtsx");
}
if (File.Exists(packagePath))
{
myPackage = integrationServices.LoadPackage(packagePath, null);
}
else
{
throw new ApplicationException("Invalid file location: " + packagePath);
}
break;
case "sql":
// Package is stored in MSDB.
// Combine logical path and package name.
//if (integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty))
if (integrationServices.ExistsOnSqlServer(packagePath, ".", "executeSSIS", "p4ssw0rd"))
{
//myPackage = integrationServices.LoadFromSqlServer(packageName, "(local)", String.Empty, String.Empty, null);
myPackage = integrationServices.LoadFromSqlServer(packageName, "(local)", "executeSSIS", "p4ssw0rd", null);
}
else
{
throw new ApplicationException("Invalid package name or location: " + packagePath);
}
break;
case "dts":
// Package is managed by SSIS Package Store.
// Default logical paths are File System and MSDB.
if (integrationServices.ExistsOnDtsServer(packagePath, "."))
{
myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", null);
}
else
{
throw new ApplicationException("Invalid package name or location: " + packagePath);
}
break;
default:
throw new ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.");
}
//Variables var = myPackage.Variables;
//foreach (string key in variablePairs.Keys)
//{
// var[key].Value = variablePairs[key].ToString();
//}
Variables var = myPackage.Variables;
for (int i = 0; i < variableArray.Length; i++)
{
var[variableArray[0]].Value = variableArray[1].ToString();
}
return (Int32)myPackage.Execute();
}
[/code]
For some reason the above code runs and passes back the value that it succeeded. Yet the package doesn't do what it should. Does anyone have any ideas as to why this might be the case or even a better way to pass the variables.
Many thanks,
Grant
View 5 Replies
View Related
Feb 9, 2007
Appreciate if anyone can show me the code to call a web service from Script Component ?
I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.
Thanks !!
View 11 Replies
View Related
Jul 2, 2007
I am trying to call a web service using the web service task and passing it variables, but even when I set variables to be Strings/any other data type, they always get passed out as objects. The call is then rejected based on the fact that there is no web service that accepts those parameter types. If there is a workaround to this problem, that would be great, otherwise I think I'm going to have to call the web services in a script task. Would someone please give me an example of the steps necessary to call a web service from the script task?
Thank you,
James Mac William
View 10 Replies
View Related
Jan 11, 2007
Hi,
I m trying to use the web services task, but when i try to run it i got the following error
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
This is probably because i never worked with this task, even so, does anyone knows what might be wrong?
I got a connection a wsdl, i can configure the service, method and variables(i use fixed values), and i configure the output to a variable with the type object(i have tryed string and int)
I even can download the wsdl file, but the error seems to be in the connection
Thanks
View 7 Replies
View Related
Apr 2, 2007
We have a requirement to call a Web Service method from SSIS where the method accepts a single parameter that is derived from a database recordset and can exceed 4k characters. From the little we know about the Web Service task it is limited to only accepting direct input and variables. Does anyone have any suggestions to overcoming this issue? Any detailed information in possibly calling the Web Service from script or using an object variable would be extremely appreciated.
Thank you in advance for your help.
View 2 Replies
View Related
Feb 7, 2007
Hi Remus,
I just started looking into SB about a week ago, so my question is likely to be pretty lame. However, that's not going to stop me from asking it :-)
We're trying to do something similar to what you're describing here. How do you "reset" a dialog timer? Why would you need to deal with resetting timers (or with timers at all for that matter) in the event of a success? Couldn't you rewrite the logic to be as follows?
begin transaction
receive message
if message is web request
save state of request (http address, caller etc)
else if message is retry timer message
load state of request
endif
commit
do the web request (no transaction open)
if success
begin transaction
send back response
end conversation
commit
else
set a retry timer on the dialog (say 1 minute) using BEGIN DIALOG TIMER
endif
Also, when you set the retry timer you have to associate it with the saved request state, right? Otherwise, how will the service know which request to load on receipt of the timer message?
TIA.
View 6 Replies
View Related
Jan 19, 2007
Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis
View 2 Replies
View Related
Sep 17, 2007
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
View 6 Replies
View Related
Sep 12, 2012
How to call a sql function in stored procedure using Sqlserver 2008?
View 4 Replies
View Related
Aug 27, 2007
Hello All,
How do i call a user defined function from within a stored procedure,
I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string.
That part works.
declare @fullname varchar(400)
@fullName=getFullName(@firstname,@lastname)
As always thanks for all your input
View 4 Replies
View Related
Jul 23, 2005
Hi all, I want to use a function with a tabel object as parameter. Doessomeone know a method to do this. I have read that a table as parameteris invalid.
View 4 Replies
View Related
Nov 13, 2007
Hi,is there any method to call a store procedure into a function?ThanksFabio
View 1 Replies
View Related
Nov 21, 2007
Hi:
We found the problem that when the SP call function,there must have 'dbo.' before the function.Does it necessarily?Can delete 'dbo.' schema when call function in SP?
View 1 Replies
View Related
Oct 15, 2007
how can you call a sql function in data flow? I have a function that calculate age base on the data in two columns . I would like to call this function in data flow to calculate the age..
View 3 Replies
View Related