Sqldatasource And Webservice
Jan 10, 2008
hi
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
View 1 Replies
ADVERTISEMENT
Feb 20, 2008
Please tell me why to use a webservice? Which factor outperform webservice?
View 2 Replies
View Related
Jul 23, 2007
What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
View 7 Replies
View Related
Jul 23, 2005
I 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 Related
Aug 30, 2007
I 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
View 1 Replies
View Related
Apr 30, 2008
Hi
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
View 6 Replies
View Related
Apr 8, 2008
After 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
View 7 Replies
View Related
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?
View 3 Replies
View Related
Feb 15, 2006
Dear,
Can SSIS package be run from WebService?
P.S. SSIS package and Webservice are located in the same computer.
Thanks much!
View 9 Replies
View Related
Jun 26, 2007
Is 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 Related
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
View 1 Replies
View Related
Apr 24, 2008
I 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
View 5 Replies
View Related
Dec 26, 2006
HI
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.
View 3 Replies
View Related
Jan 27, 2008
Hi,
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..
View 4 Replies
View Related
Oct 24, 2006
We 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?
View 4 Replies
View Related
Jul 13, 2007
Hi,
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.
View 1 Replies
View Related
Feb 22, 2007
I'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.
View 1 Replies
View Related
Aug 8, 2005
I want to dynamically pass values to the input value of a Web method to return values to a webservice.
View 7 Replies
View Related
Jul 25, 2007
Hello 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.
View 4 Replies
View Related
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
View 9 Replies
View Related
Mar 25, 2008
We 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.
View 5 Replies
View Related
May 24, 2007
Hello!
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
View 11 Replies
View Related
Apr 1, 2008
Hi everybody,
How can I access a webservice from inside a stored procedure? any help is greatly appriciated.
View 3 Replies
View Related
May 31, 2007
Hi Guys,
Anyone can tell me, How to invoke ssis package as webservice ?
Is it possible ? If yes How ?
Yogesh V. Desai. | SQLDBA|
View 2 Replies
View Related
Apr 28, 2006
I'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
View 1 Replies
View Related
Jul 26, 2007
Hi 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
View 7 Replies
View Related
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
View 1 Replies
View Related
Jul 14, 2007
Hello,
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?
View 3 Replies
View Related
Oct 6, 2006
Hi,
I need to call a web service (consume a webservice)from a T-SQL stored procedure. Is there a way to do this. If not is there a way to make a simple http request, something like a utl_http in oracle.
At the moment iam using a MSSOAP30.SOAPCLIENT object created using sp_OACreate to make this call. However this means that the soap toolkit be installed on the pc on which SQL server is installed. I was hoping to find a completely independent way.
Also when i call sp_OACreate where does sqlserver 2005 look to find that object. Iam thinking of putting the MSSOAP30.dll on that machine, if all else fails.
Ahmad
View 1 Replies
View Related
Nov 8, 2006
Can you have a CRL stored procedure call a webservice that returns a dataset?
View 7 Replies
View Related
May 14, 2008
Hello there.
Here's my situation.
My Reports should be able to switch to different SharedDataSources.
I'm using the SetReportDataSources-method to chang it. It works fine. After that i render the report and write the stream to database.
But. Now I developed a report using a SubReport and i want the SubReport to use the same data source like MainReport. But the reportserver doesnt overwrite the datasource to the subreport.
The prefered way would be to get a list of all SubReports existing in the MainReport so i can set the
DataSource using my program.
Do you have any ideas?
Thanks in advance.
View 1 Replies
View Related
