Call .NET Web Service From SQL Server
May 3, 2004Hi,
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
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
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.
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 RelatedDear fellow developers,
I have successfully developed a Transact-SQL stored procedure which calls a CLR function, which calls a web service. I am using a Visual Studio 2005 SQL Server Project to perform the necessary magic.
Is there a neat way to deploy this stuff to a production server?
It is bad enough deploying the sp and function, but the web server proxy also needs to be changed to refer to the production web service.
This all looks very nasty and complicated.
Am I missing something?
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
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()".
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 RelatedWith 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?
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
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 RelatedHello,
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!
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
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
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 !!
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
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
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.
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.
Hello ,
I get this error (connection closed) in web applications, whenever SQL Server database Service is restarted. When I run the application again, the error disappears. Granted that there are lots of users and Database gets restarted every night is not helping.
Is there any setting in the JDBC driver properties, where I can tell it to open a closed connection (like a retry count), rather than fail the first time and work the second time.
We use
1. SQL SERVER 2005 SP2,
2. Websphere Protal Server 5.1
3. SQL SERVER 2005 JDBC driver 1.1(com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource.)
I am wondering if connection pool driver is causing this problem. I do not care about connection pooling, but Websphere doen not seem to like any other JDBC Driver. I don't mind switching to another driver as the last option.
Error is :
[11/27/07 17:01:59:331 MST] 7202440d WSRdbManagedC W DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.getCatalog(Unknown Source)
at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.getCatalog(WSRdbManagedConnectionImpl.java:676)
at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.introspectSelf(WSRdbManagedConnectionImpl.java:808)
at com.ibm.ws.rsadapter.FFDCLogger.introspect(FFDCLogger.java:169)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.introspectSelf(WSJdbcConnection.java:1253)
at com.ibm.ws.ffdc.IntrospectionLevelMember.getNextMembers(IntrospectionLevelMember.java(Compiled Code))
at com.ibm.ws.ffdc.IntrospectionLevel.getNextLevel(IntrospectionLevel.java:181)
at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:67)
at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:51)
at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWrite(IncidentStreamImpl.java:396)
at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWriteLine(IncidentStreamImpl.java:632)
at com.ibm.ws.ffdc.DiagnosticEngine.dumpObjectAndStack(DiagnosticEngine.java:301)
at com.ibm.ws.ffdc.DiagnosticEngine.processIncident(DiagnosticEngine.java:147)
at com.ibm.ws.ffdc.FFDCFilter.filterEngine(FFDCFilter.java:428)
.......lots of lines
Regards
Bobba
Is there a way to modify the RSReportServer.config file with a Web Service call. We want to install an app and have it modify the Renders. We want to remove the Tiff export for example. We also want to add simple headers to Excel. Right now we have to open the file and then make our edits.
View 1 Replies View RelatedHi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
I have an almost virgin install of SQLExpres running on a WIN2K Pro system.
Have been able to create and connect db to Access 2000 without problem.
Now I wish to extend to remote connections. Using Surface Area Configuration tool, I changed Remote Connections to Local and Remote.
Whenever this setting contains TCP/IP and I try to restart the service I receive the following errors:
System Log:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 10013.
Application Log:
Server TCP provider failed to listen on [ 'any' <ipv4> 0]. Tcp port is already in use.
TDSSNIClient initialization failed with error 0x271d, status code 0xa.
TDSSNIClient initialization failed with error 0x271d, status code 0x1.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
If I change back to Local Connections Only or Local and Remote using named pipes only, the service starts up again without a problem.
After five days of investigating, researching, reinstalling and waiting I have to ask for help.
Any suggestions or leads?
Thank You in advance.
Hi all,
I do understand that it is highly recomended to have aserprate user (perfered a domain user account) for each of the SQL Server service and SQL Agent service.
What is the reason behind that? (Someone told me to not run the service with an account that has a powerul privilegs! - I don't undrstanmd this point can you explain it please?)
What is the diffrent between: 1- Local System account 2 -Network Service account
Thanks in advanced!
CS4Ever
Hi All,
View 14 Replies View RelatedHi,
View 36 Replies View RelatedI have a sproc that will generate a dynamic call (and this must be done in a stored procedure),... Anyway the dynamic call I am having problems with is that I need to dynamically create a statement to backup a database. It works as long as the database is on the same server / instance as the stored procedure. I want to be able to from server/instance A create a command that does a backup of a database that is on server B. I just can't figure out the syntax for a database backup where the database is on another server.
Trying something like [ServerName].[DatabaseName] does not work...
Hi,
Does anyone know if/how SQL server can call a function in a C++ library?
Cheers,
Xiaobing
Hi,
I have two SQL SERVER server1 and server2. A server1's stored procedure should call server2's stored procedure. how can i do this.please explain me in detail.i am new to this concept.
VENKAT
Can I call a csharp method of my custom class from Sql-Server Stored Procedure?
If it's possible can anyone provide some sample code or the configuration required?
Thanks
dear friends,
It's possible to call a view from a different SQL Server? I need to create a connection inside one view from the first server to call the second server...
Thanks
Hello
I'm trying to call a UDF from another server with the following command:
select i from openquery([10.0.10.240], '[survey].[dbo].[ufnGetAxis_Ana] as i')
error:
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.
Can someone give me some advice on how to pass username and password to access an object on another server?
Many thanks!
I am trying to call a view that is already in SQL Server 2005 from my ASP.NET 2.0 application. I get the following error message:
The request for procedure 'vwMyView failed because 'vwMyView' is a view object.
I thought I could call a view the same way that I call a stored procedure but I guess I was wrong. How can I fix this?
Here is some sample code too:
Dim Results As DataSetDim Cmd As IDbCommand
Dim Adapter As IDbDataAdapter Results = New DataSet
Cmd = DB.CreateCommandCmd.CommandText = "vwMyView"
Cmd.CommandType = CommandType.StoredProcedure
Adapter = DataSource.CreateDataAdapter
Adapter.SelectCommand = Cmd
DB.Open()
Cmd.Connection = DB
Adapter.Fill(Results)
Cmd.Dispose()
DB.Close()
Return Results
Hi,
I have a VB program to sit somethere. I like to call that program with passing two parameters from a trigger on the SQL Server. The parameters are the contents of the table which the trigger is created on.
Is there any way to achieve that?
Thanks a lot!
Regards,
Kevin Jin