How To Call Webservice From Tsql?
May 24, 2007Hello!
From an stored proc I want to call an existing web-service.
How to do that??
Must it be done using CLR-procs or is there some way in TSQL?
Greetings
Bjorn
ADVERTISEMENT
Call Webservice From Trigger/stored Proc
Sep 17, 2004Is it possible to call an external web service from a SQL Server trigger or stored procedure?
View 6 Replies View RelatedTSQL Command To Call *.sql Files
Jul 20, 2005I have several *.sql files with schema/data changes to be applied to ourcurrent database. Is there a way to create a TSQL script that could be runfrom the SQL Query Analyzer that would sequentially call the *.sql files?i.e.call schemaVersionCheck.sqlcall addFieldToLoanTable.sqlcall updateLoanTrigger.sqlcall updateSchemaVersiontia.dynoweb
View 2 Replies View RelatedI Just Want One Entry For Each Call, With SLA Status 'Breach' If Any Of The Stages For The Call Were Out Of SLA.
Mar 19, 2008Hi,
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!!!
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Nov 19, 2007Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
Why To Use A Webservice?
Feb 20, 2008Please tell me why to use a webservice? Which factor outperform webservice?
Sqldatasource And Webservice
Jan 10, 2008hi
after connecting webservice with my vs.net2005 using sqldatasource in webservice for getting data's ,i faced following errors:-
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataSet'.
Code which i used in Webservice is as follows:-
<WebMethod()> _Public Function getData() As DataSetDim ds As New DataSet
Dim sds As New SqlDataSourcesds.ConnectionString = ConfigurationManager.ConnectionStrings.Item("HRMSConnectionString").ToString
sds.DataSourceMode = SqlDataSourceMode.DataSet
sds.SelectCommand = "select * from EpmEmployeeDetail"ds = CType(sds.Select(DataSourceSelectArguments.Empty()), DataSet)
Return ds
End Function
Thnks
mic
Connecting To Webservice From DTS
Jul 23, 2005I created a custom DTS object that does its work by calling awebservice. When you create the object in DTS, it lets you set theuser/pw you want to use to authenticate to the webservice. The problemI am having is that once I authenticate to the webservice within DTS,it always uses the same authentication when contacting the service.So, for example, I drop a new instance of my object into a package. Itconnects to the webservice as me. I set up the parameters of the task,including telling it to connect as User="test". If I run it, my codesets the credentials correctly on the webservice, but the webservicestill gets called as me.If I exit out of SQL Server and go back in, and run it, it correctlyconnects as User="test". But then if I try to edit the task, and givemy own user and password, it still always tries to connect as "test".It seems whoever I initially connect to the webservice as, that is theinformation that DTS will use for the entire session.Is there any way to do what I want to do?thanks
View 1 Replies View RelatedUsing WebService Task
Jul 29, 2005Hello,
View 10 Replies View RelatedCalling Webservice From CLR
Aug 30, 2007I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.
Any help would be appreciated
Aaron
WCF Webservice Through SSIS
Apr 30, 2008Hi
I need to populate a table B by selecting a from table A but for every single record selected from Table A for some of the feilds i need to pass it to a WCF webservice and do some tasks.
Can somebody show me an example of this i am completely clueless on how to invoke a WCF webservice for every single row fetched from a database.
A step by step example would be of great help.
regards
Hrishy
WebService Only Available To LocalHost
Apr 8, 2008After creating a WebService using SQL Server 2005 HTTP Endpoints I can only browse to the wsdl file using localhost address and not via the machine name
For example http://MACHINENAME/SomeWebService?wsdl will ask for some authentication that can never be determined while
http://localhost/SomeWebService?wsdl will successfully generate the WSDL file
This happens on the actual machine and on all other machines in the domain which pretty much defeats the purpose of using Web Services to expose functionality.
Does anyone have any idea regarding
(a) what security features are at work here,
(b) Where are they documented
(c) How to set or use them in such a manner as to actually be able to use web services.
While investigating I have noticed that all examples conveniently use localhost and so either avoid or never address this issue.
Any suggestions appreciated.
Nadreck
Bug With Report Using XML Webservice
Apr 25, 2008
I have noticed what seems to be a bug in Reports using an XML Webservice for a datasource.
I have the data source setup and working to query an XML webservice. All fields aren't always populated - so after the intial step that automatically builds your dataset and sets up the field names - I went in and manually added the remaining missing fields (which were blank).
Then I setup the report - dragging/dropping fields from the dataset.
I thought everything was working fine, UNTIL i noticed that IF the FIRST record contains ANY blank fields - ALL of those fields (on every record returned) show blank on the report.
For example my webservice might return something like this:
Code Snippet
<Root>
<Parent>
<Child_Name>Joe</Child_Name>
<Child_ID></Child_ID>
</Parent>
<Parent>
<Child_Name>Tim</Child_Name>
<Child_ID>123456</Child_ID>
</Parent>
</Root>
But when my report actually runs, it doesn't show any value for the second record:
My Report
Child_Name Child_ID
Joe
Tim
In this case, Tim should actually show the Child_ID value, but apparently because the first record didn't have a value - the report assumes NO RECORDS will have a value and doesn't bother printing them.
But if I run the report so that the first record DOES contain that field - then everything seems fine.
I can see that my Dataset hasn't changed - as it still contains all the fieldnames (even the ones that sometimes return empty).
Am I doing something wrong?
Can SSIS Package Be Run From WebService?
Feb 15, 2006Dear,
Can SSIS package be run from WebService?
P.S. SSIS package and Webservice are located in the same computer.
Thanks much!
Calling Webservice From A Trigger
Jun 26, 2007Is it possible to call/fire a method in a webservice (.asmx) from a trigger in MS SQL 2005? I would like to send out a notification to all the admins whenever a new row is inserted into a table in our db. If possible, can someone show me an example of how to?
View 1 Replies View RelatedRS Webservice Delete Folder
Apr 21, 2008
Hi,
Is there any way to delete folder in reportserver using RS webservice API?
I know there is a method: DeleteItem ( Item As string )
But I tried it and it didn't work for folders.
Thanks
JY
I Can't Connect To SQL Server From WebService.
Apr 24, 2008I just try to implement the 3 tire architacture. So, I developed the one web project for web ui and one web service and one dall project in one solution.
On dll project put the bll, connect to sql server and return the result set to web service.
Form web service, get the result from dll and transfer to web ui.
When I call class from dll, i just overrid the connection string.
Actually it's wrok properly in my development pc and in my local webserver.
As our usual, when i publish to real web server i got the following error.
Server was unable to process request. ---> An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Anybody plz help and direct how should i fixed this problem.
Thanks.
Soe Thiha
Using Webservice In ReportViewer Control
Dec 26, 2006HI
I want to use ReportViewer Control to show the data.
Can I render report using Sql Server 2005 Reporting web service call. I didn't get any sample which allow me to host report on reporting server but view them using ReportViewer Control using webservice call rather than using URL.
I need to hide Reporting server URL location. And If you show report using ReportViewer Control using URL mechanism, anybody can see the report server url.
Invoking Webservice Using SQL CLR Procedure
Jan 27, 2008Hi,
I have written a SQL CLR procedure, which will be invoking the webservice..I developed the application locally and it works fine, I am able to invoke the webservice using the SQL CLR procedure present in my database. But when i hosted the webservice in App server and executed the SQL CLR procedure in DB Server.
From DB Server, I am not able to invoke the webservice present in the app server. But i am able to browse the webservice from my db server.
I am getting the foolowing error message
A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_LoadView':
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
at StoredProcedures.usp_LoadView(String ConnectionString, String WebserviceUrl, String ColumnMappingsXml, SqlXml AddressXml, SqlXml& ExceptionSqlXml, Int32& ErrorStatus)
I goggled and tried out various options, like increasing €œwebservice timeout€? and increasing €œexecutiontimeout€? for HttpRuntime, but none seems to be working. Please provide me your suggestions to how to fix this..
SSIS To Run A Webservice Times Out
Oct 24, 2006We have a package that runs a webservice. The webservice connects to exchange for multiple mail accounts so it sometimes takes awhile to run.
If run from SQL Server Management Studio, it usually completes, but may occassionally time out.
If run from a job (i.e. we want this scheduled) then it NEVER completes successfully - althought there is very little information about what the error was. It seems to fail too quickly to be a time out error...
Any idea why this is happening? Or how to prevent it from timing out? Is there a way to initiate the webservice asynchronously from SSIS?
Array List In WebService
Jul 13, 2007Hi,
How i can get the FileNames that are stored in Web Server.
Dim strFiles As String = Server.MapPath("~/UploadedFiles/")
Dim dirinfo As New DirectoryInfo(strFiles)
dirinfo.GetFiles("*.doc")
The above Method Iam writing in Web Service. These files will display in a DataGrid in Windows Application.
Importing Data Using WebService
Jul 15, 2005Hello,
View 7 Replies View RelatedBinding WebService As Datasource
Feb 22, 2007I've painstakingly managed to get a XmlDocument from a webservice and run this to retrieve data.
However, i cannot bind any of the returned elements to anything. Any help appreciated.
Webservice Task Input
Aug 8, 2005I want to dynamically pass values to the input value of a Web method to return values to a webservice.
View 7 Replies View RelatedUsing A WCF WebService As Report Datasource
Jul 25, 2007Hello all,
When trying to configure a WCF service as a report data source I have these following questions:
1. What do I need to provide for the connection string?
I currently wrote http://localhost:8003/ServiceName/ which returns the WSDL. Is that correct?
2. I don't know what to write inside the Query defintion. I currently wrote:
<Query>
<SoapAction>
http://tempuri.org/ServiceContractInterface/MethodName
</SoapAction>
<Method Namespace="http://tempuri.org/ServiceContractInterface"
Name="MethodName">
</Method>
</Query>
and the result is:
Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions).
Can any one please point me in the right direction?!
Thanks.
Exception While Calling The Webservice From CLR
Sep 7, 2007
Hi,
I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.
At the final execution i am getting some security exception
The following is the exception I am getting€¦.
CREATE PROCEDURE GetProductVendorDetails(@ProductID int)
AS
EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService
GO
EXECUTE GetProductVendorDetails 2
Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":
System.InvalidOperationException: There is an error in XML document (1, 281). ---> System.Security.SecurityException: That assembly does not allow partially trusted callers.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()
at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
System.InvalidOperationException:
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)
at StoredProcedures.CallWebService(Int32 ProductID)
.
My Web Service Method €¦.
[WebMethod]
private void GetProductDetails(int ProductID)
{
// String ProductVendorDetail="";
//Set the connection string for the database
string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";
//Create Connection and open it
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
//Create the command object
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"
+ " FROM Production.Product P "
+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "
+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "
+ " WHERE P.ProductID =" + ProductID.ToString();
SqlDataReader thisReader = comm.ExecuteReader();
while (thisReader.Read())
{
//Console.WriteLine(myReader["Column1"].ToString());
//Console.WriteLine(myReader["Column2"].ToString());
pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());
pvinfo.ProductName = thisReader["ProductName"].ToString();
pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();
pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();
pvinfo.VendorName = thisReader["VendorName"].ToString(); ;
}
thisReader.Close();
conn.Close();
}
[WebMethod]
public ProductVendorInfo GetProductVendorDetails(int ProductID)
{
GetProductDetails(ProductID);
ProductVendorInfo pvi = new ProductVendorInfo();
pvi.ProductID = pvinfo.ProductID;
pvi.ProductName = pvinfo.ProductName;
pvi.ProductNumber = pvinfo.ProductNumber;
pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;
pvi.VendorName = pvinfo.VendorName;
return pvi;
}
My CLR Procedure code is as follows€¦.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(int ProductID)
{
// Put your code here
ProductVendorInfoService S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;
ProductVendorInfo pvi = new ProductVendorInfo();
pvi = S.GetProductVendorDetails(ProductID);
String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";
using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}
};
Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?
Thanks
Raj
Getting Webservice Results Into A View
Mar 25, 2008We have a large number of applications that use various sql databases. They need to validate and do lookups of information such as employees and addresses. Currently, we load data from oracle into our main sql server and push the data out to various servers. This could perhaps be simplified with replication, but is now done with some dts packages.
We are wondering if there could be a better way to do this. We would like to go directly against oracle for the address data and to sql for the employee info.
An idea I had was to have our programmers create a web service that could be called from our .net applications. That works fine for in house applications, but some vendor applications need to see a table or view. We can write a CLR proc that can connect to the web service and provide data in table format I believe.
How could we get this into a view? I've tried creating a function that calls the proc and returns the table-valued variable to the view, but it seems the functions doesn't like using 'exec myProc' in the select_statement parameter.
I suppose we could have them make an additional connection to the lookup information, stored on a central database...they are probably doing this anyway to get to the validation db on the same server.
Access Webservice From A Stored Procedure.
Apr 1, 2008Hi everybody,
How can I access a webservice from inside a stored procedure? any help is greatly appriciated.
How To Invoke SSIS Package As A Webservice
May 31, 2007Hi Guys,
Anyone can tell me, How to invoke ssis package as webservice ?
Is it possible ? If yes How ?
Yogesh V. Desai. | SQLDBA|
Calling External Webservice Using Certificate
Apr 28, 2006I'm trying to call an external webservice which use https and certificate. I load certificate into MSIE and then made *.cer file. The problem is that if I compile simple console program which use this certificate - than all is OK. But when I compile simple rutine for SQL server and trying to run it - an exception is issued after this line:
service.ClientCertificates.Add(X509Certificate.CreateFromCertFile(certFile));
Exception is:
Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Do you have any advices?
MiSu
Problem Processing Return Value Of Webservice
Jul 26, 2007Hi guys,
I have a webservice task which calls a method ( C#) that returns a bool, so i declared a variable in my package of type boolean and set the output of the webservice task to this variable. When executing the task it fails with:
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The type of the value being assigned to variable "User::WebserviceResult" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ---> System.Runtime.InteropServices.COMException (0xC001F009): The type of the value being assigned to variable "User::WebserviceResult" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariable90.set_Value(Object pvValue) at Microsoft.SqlServer.Dts.Runtime.Variable.set_Value(Object value) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Variable.set_Value(Object value) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.saveOutputToVariable(Object output) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.saveOutput(Object output) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
so i changed the Datatype of my package variable to String... the task then succeeds, but the Variable is empty?
Edit: i changed the return type of the webmethod to int and tried to receive the return value into int16, int32 and int64 package variables.. none of it worked...same exception as above
thanks for your help
bye
as
Passing User Name From SSRS To A Webservice
Dec 17, 2007
Hi!
I have created a report based on xml datasource using windows authentication as its credentials. Now the report calls a webservice to retrieve data from the database, I would like to pass the user name who will run the repoty to the web service, Is there a way to retrieve the user name from the webservice.
Regards,
HG
Returning Data From Webservice To SSIS
Jul 14, 2007Hello,
I am trying to use the data returned by a webservice that our application team has built.
What method should they use to return the data to the caller ?
If they try to retun a data set the SSIS is saying that it is not a well formated XML.
The webservice is using an API call to HIS, can I make this call using C# or some other mechanisam directly from SSIS?