Problem Processing Return Value Of Webservice

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


ADVERTISEMENT

SoapException While Rendering Report Through WebService - Error During Processing Of ‘ReportParameterTime’ Report Parameter

May 5, 2008

I am extracting RS reports through a WebService:
report = execService.Render("EXCEL", null, out extension, out mimeType, out encoding, out warnings, out streamIds);

Some works fine but some are generating the following SoapException:

Error during processing of €˜ReportParameterTime€™ report parameter. ---> Error during processing of €˜ReportParameterTime€™ report parameter. ---> Error during processing of €˜ReportParameterTime€™ report parameter.
I set required parameters to their default values. In this specific report I have two required parameters:
1: ReportParameterTime, String
2: ReportParameter1, String

I set them to their default values (ReportParameterTime to "[Czas].[Kalendarzowy].[Rok].&[2007]" - means
Time Calendar Year 2007) but I am getting the exception above. I set parameters using this code:

execService.SetExecutionParameters(parameterValues.ToArray(), CultureInfo.CurrentCulture.Name);

Where parameterValues is a List<ReportExecution2005.ParameterValue>.

When I am using Microsoft.Reporting.WinForms.ReportViewer the report is generated without
any problems. I can see one parameter to choose: "Kalendarzowy" (means adj. Calendar) and can
select years, the default value is "2007".

What am I doing wrong and how can I fix this error ? Does anybody know ?

Regards,
Daniel

View 15 Replies View Related

Why To Use A Webservice?

Feb 20, 2008

Please tell me why to use a webservice? Which factor outperform webservice?


View 2 Replies View Related

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 View Related

Connecting To Webservice From DTS

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

Using WebService Task

Jul 29, 2005

Hello,

View 10 Replies View Related

Calling Webservice From CLR

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

WCF Webservice Through SSIS

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

WebService Only Available To LocalHost

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

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?

View 3 Replies View Related

Can SSIS Package Be Run From WebService?

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

Calling Webservice From A Trigger

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

RS 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

View 1 Replies View Related

I Can't Connect To SQL Server From WebService.

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

Using Webservice In ReportViewer Control

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

Invoking Webservice Using SQL CLR Procedure

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

SSIS To Run A Webservice Times Out

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

Array List In WebService

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

Importing Data Using WebService

Jul 15, 2005

Hello,

View 7 Replies View Related

Binding WebService As Datasource

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

Webservice Task Input

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

Using A WCF WebService As Report Datasource

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

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

View 9 Replies View Related

Getting Webservice Results Into A View

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

How To Call Webservice From Tsql?

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

Access Webservice From A Stored Procedure.

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

How To Invoke SSIS Package As A Webservice

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

Calling External Webservice Using Certificate

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

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

View 1 Replies View Related

Returning Data From Webservice To SSIS

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

Calling Webservice From A T-SQl Stored Procedure

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

CLR Stored Procedure Consume Webservice

Nov 8, 2006

Can you have a CRL stored procedure call a webservice that returns a dataset?

View 7 Replies View Related

Get Subreport Names Using RS WebService (ReportService)

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







Copyrights 2005-15 www.BigResource.com, All rights reserved