I have a client that needs to issue soap calls to a third-party web
service from within a DTS package. Is this even possible? I've
looked everywhere and found the sqlxml, but that looks like it
provides a soap web service, not consumes one.
Right now, we are consuming the same web service in an ASP.NET
application and would love to use the same code, but am really just
looking for the best solution. Basically, the only firm requirements
are that the client needs to provide a text file of items that need to
be posted to the third-party via their web service. Right now, we use
dts for the client to upload other text files, that's why I was hoping
to use the same procedure, but if we need to go another route, I'm
sure I can convince them.
I've tried creating several Web Service Tasks and had no problems until I came across one that requires authentication. I have a standalone program to handle this Web Service, but I'm experimenting with SSIS to see if I can replace it.
In SSIS web service task - when you specify the Service and Method in the input tab for a WSDL file being used, it seems to prompt for the parameters to be supplied only in the body of the WSDL file, and not the header.
I need to be able to provide security information (present in the SOAP header of the WSDL) like username, password etc..which is necessary to post any response to the web server, and I cannot see where I can give this in the input tab of the web service task.
Hi,This is the first time I use SOAP. Dont really know the exact pictureyet. Hope you can shed some lights on me.May I know where should I start troubleshooting if I get the followinerror message when running SOAP on Windows 2003, SQL Server 2000 ?Error Code: 0 Error Source= Microsoft VBScript runtime error ErrorDescription: ActiveX component can't create object:'MSSOAP.SOAPClient' Error on Line 1. The step failed.Thanks.
Getting this error after installing certificate. Happens when trying to access https://machinename/reports
Possible SOAP version mismatch: Envelope namespace http://schemas.xmlsoap.org/wsdl/ was unexpected. Expecting http://schemas.xmlsoap.org/soap/envelope/. SQL Server Reporting Services 2005 on Windows Server 2003 R2.
I am sending request to the third party server EX:"Http://somesight.sight.net/company.asp?AC="1000"&Item="200"&DT=05/08/2007" I am getting file in soap format
I created my SOAP endpoint in SQL 2005 sp 2, I worte an ASP.NET 2.0 app to consume the exposed webmethod but how do you add the soap headers in the asp.net app? Basically I want to expose the application name property to set it to determine the actual caller in SQL Profiler. Can it be done without modifying the generated proxy code in ASP.net or is there a workaround ?
Is it possible to render a report with out usieng the SOAP API. I need to use a WCF web service instead of the ASMX web service that is made availible in SSRS. I would like to be able to to create a WCF that calls reporting services via a class library and not the web service interface.
I have created a simple webservice with a soap header and am trying to access it using an xml datasource in reporting services. The query works but I have no idea how to pass in a value for the UID on the host header. Any help? what XML would I add for the header?
This is what I have no that allows me to pass in parameters through the editor.
We have just upgraded from an embedded browser hosted RS2000 system to the Microsoft.ReportViewer.Winforms control for RS2005 in our applications, and have encountered a SOAP / firewall problem again.
We were using an embedded browser to display the reports, as some of our client sites block or strip the SOAP wrapper from SOAP 1.1 commands that the old RS2000 control tried to use when the data passes through their firewall. We have no control over, or visability of their firewall configuration, as this is part of their own site security.
Is RS2005 still using the same older version of SOAP, or has it been moved up to version 1.2, which I believe does not trigger as many alarm bells in some firewall systems.
We have no problems makeing web service calls to our own applications through the firewall, so its only the report viewer control that gives us these problems.
I have not been able to explicitly clarify this down to the SOAP version, but it looks too close the our previous firewall problem to be anything else - comments invited !!!!!
As an aside, is there any way to trace the inbound request through IIS to ReportServer so I can see what hits the report server ? The IIS logs alone show a request has been made, but no qualifying data, and the report server logs only seem to show processing once the request has been accepted. What I want to to is see the inbound request, and then the outbound error message being returned so I can confirm my suspicions.
I have posted a similar thread on google groups for reporting services.
I posted this in the Sharepoint Designer forum, but did'nt get an answer...so trying here...Below is my soap endpoint in sql2005...works fine for InfoPath to consume the service...but when SharePoint Designer attempts to retieve data I get
"The server returned a non-specific error when trying to get data from the data source."
In SPD I can connect to the service and see the methods and parameters...but never get any data returned...wondering if it's in my endpoint setup. BTW-using Windows security by default in SQL2005...I've tried different types of logins without success...
I'm using a SQL-2005 DB to manage TFS Build Information. I need to create a SOAP web service call in response to an INSERT in the Builds table. I am, however, thoroughly confused about the most efficient way to do this. The best I can come up with is to create a CLR function that takes the build number from the DB Insert statement, creates the XML and sends the msg to my already published (and already being used) WebService. The CLR function would be called in a trigger set of by the insert.
I don't suppose there is a native way to generate outbound SOAP msgs based upon DML events?
Has anyone worked out how to trace SOAP/HTTP Endpoints in SQL 2005 SP 2?
I'm not having a particular problem, just want to know how things work, such as which layer is responsible for the HTTP Authentication. I've traced the HTTP.sys stack using logman.exe (see http://www.thelastpickle.com/2008/03/22/tracing-sql-soap-endpoints-through-httpsys/) .
I cannot see any SQL Profiler events, and I've tried setting trace 7801 and 7803 as described in http://www.sqljunkies.com/Article/5CCAC423-1407-4A36-AF71-ED6A67D9646A.scuk#_Toc52964195 but they do not result in any logging.
The web serivce for blogging to Community Server requires the username and password to be specified in the SOAP Header and not the body, I however can't access the soap header. How can I achieve this?
This used to work just fine until about a week ago. We're not sure if there was a MS update that caused this behavior. If the user saves the report to a local disk instead of opening it, they can save it successfully and open it later.
Hello All,I have a webservice uses a vb.net component that accesses thrid party dll to connect to a sqlserver dbase, username,dbase name ,server and password are passed to the third party dll for login purpose.This works fine in development evironment when pda client calls webservice, A soap error is thrown to the client "sqlserver does not exist or access denied" when the client calls the deployed webservice. I have tried the following 1. Added ASPNet user to the database 2.Added domian user to the database both has public and dbowner rightsCan any one help??[:'(]Thanks Regards
is message exchange between a .NET Webservice and a SQL stored procedure possible? And if, could you please explain me how? Or give me a tip where i can get more informations and maybe samples? thanks for your help
I have been combing the Internet trying to find a way to use the SOAP API (ReportExecution2005) web service to render a report to HTML4.0 format with toggle items. From what I have read, the new web services should provide ASP.NET server-side postbacks from the toggle item images, but so far I have seen only the same behavior as was in RS 2000, which was that the toggle item images pointed back to the ReportServer address. The following is the device info settings that i have used to get the images to show up on the aspx page:
If I switch the HTMFragment to True I don't get any toggle item images to render on the page but the report still shows up, and when I actually set the StreamRoot paramter to something like this "http://[server]/[virtualdirectory]/GetImage.aspx?reportpath=[path]&streamid=" to render the toggle item images, I get a http 400 : Bad Request error page.
Can someone please provide some information or sample code on how to render an HTML4.0 report using the SQL 2005 Reporting Services web service and getting the toggle item images to render as well as work correctly.
I have a endpoint on our SQL Server and I've written a test .Net app to access it and it all works great. It uses the Integrated Login for Authentication.
My project in the end will have connections made to the endpoint via some custom code on a Non Windows/Linux platform. My concern is that there is no Authentication method that I can use from this client. Integrated is out since it not something you log into. SSL is out since there is no Cert store. Isn't there a way to use the SQL Authentication in a Mixed mode SQL Authentication install?
What are my other options? I need to be able to get data from our SQL server from this custom platform. We looked into using TDS protocol, but would rather not have to license it and develop a client on our platform. We wanted to just generate the HTTP Packets to send and receive the data.
I have been working on a soap client project for over a week now and can not figure out what I am doing wrong. I am not an expert using Perl or SOAP so any help would be "greatly" appreciated.
I get basic errors throughout, starting with: Error - SOAP::Transport::HTTP::Client::send_receive: POST It seems as if the user access information is not carried through as it should, but I truly have no idea what the problem is.
Here is what I have put together so far.
Thanks, Mark
sub procgetmemberinfo {
my $pin = "$form{'lPin'}"; my $password = "$form{'sPassword'}";
my $soap = SOAP::Lite -> uri('https://xmlsql.XXXXX.xxx:441') -> on_action( sub { join '/', 'https://xmlsql.XXXXX.xxx:441', $_[1] } ) -> proxy('https://xmlsql.XXXXX.xxx:441/service.asmx?WSDL');
my $method = SOAP::Data->name('GetMemberInfo')->attr({xmlns => 'https://xmlsql.XXXXX.xxx:441/'});
my $result = $soap->call($method => @params); unless ($result->fault) {
my $title = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/TITLE'); my $firstname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/FIRST'); my $middlename = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/MIDDLE'); my $lastname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/LAST'); my $suffix = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/SUFFIX'); my $address1 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STREET_ADDRESS'); my $address2 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ADDRESS2'); my $city = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/CITY'); my $stateprovince = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STATE_PROV'); my $postalcode = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ZIP'); my $country = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/COUNTRY'); my $homephone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/HOME_PHONE'); my $workphone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/WORK_PHONE'); my $emailaddress = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/EMAIL');
Does anybody have any experience/advice on how to ensure that SOAP service call success/failures are returned to the calling app?
Consider a client that calls a SOAP service during which the client goes down and is unable to receive the SOAP response, the work having been done by the service. Similarly, the SOAP service may perform the task but a failure in the return makes the client think the process failed.
What would be the best way to ensure that the client is notified to avoid the call having to be made again?
Are there middleware tools that can be used to provide a form of message queuing for SOAP service calls?
I have just upgraded from Report server 2005 to 2008 r2. I have a program that calls the report server to run some reports but I have getting the error Server did not recognize the value of http header SOAPAction:
[URL] .....
I can log in to the report server and run any report that I want without any issues.
I have noticed that when I created SOAP endpoint and a webmethod uses ROWSETS_ONLY format €“ if a warning is raised in T-SQL implementing webmethod then response is being terminated. Either nothing is returned, and calling application (build with VS 2005) gets error €œThe underlying connection was closed€?, or part of response is retuned but terminated in the middle, and calling application gets error €œThere is an error in XML document (1, 8001).€? This can be caused by any warning, in my case it was €œ€¦NULL values eliminate by aggregate€¦€? warning. It will behave same way with errors. Is there any way to make SQL Server send a proper response in case of warning? Should this be reported as bug to Microsoft?
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
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.
I have a VM with SP2013 and SQL Server 2012 SP1. I have installed the powerpivot plugin SP1.But when I run the PowerPivot for SharePoint 2013 configuration tool.
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.
We are using the whole BI-package from Microsoft - from SQL, DTS-package,Raporting Service and Analyze Service.
It should be very helpful to be able to create a metadata databases where you could find all releations between different objects (tables,views,reports,cubes,DTS-package,Databases.
Just to get answer for: 'where is view xxx used', 'what are Report xxx depending upon'.
While everything exists in different SQL databases it should possible to do.
I have a initiator and a target service broker peer.
Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.
Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.
The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.
For this the C# unit test creates a Conversation off of the saved conversation handle:
Service client = new Service("cleintservicename", conn, tran);
Conversation dialog = null;
dialog = new Conversation(client, convHandle); Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>". Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR? If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether? Any help - greatly appreciated.
We seem to be being plagued by the error below by our SQL Server agent. This happens almost everytime we restart the server that has been running for a day or two.
Our SQL Server Agent uses a none expiring domain credential. I understand that this problem only happens when the profile being used by the SQL Servr Agent has changed (password change). What puzzles me is that the login is A ok and no changes has been made to it's password.
We always resolve this problem by changing the login used in the SQL Server Agent to local and after that, returning it back to it's original domain login. Unfortunately, we cant always do this everytime something goes wrong.
Can anyone please help us shed a light on this? We're using SQL2k with SP3a. Thanks!
Error:
An error 1069 - )The service did not start due to logon failure) occurred while performing this service operation on the SQLServerAgent service.
I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....
CREATE PROCEDURE [usp_ProcessMessage]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body AS VARBINARY(MAX)
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR(RECEIVE TOP (1)
@conversation_handle = conversation_handle,
@message_body = message_body
FROM [tcp://mydomain.com/INITIATE/MyQueue]
), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
END CONVERSATION @conversation_handle
IF @message_body IS NOT NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]
WITH ENCRYPTION = ON, LIFETIME = 600;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]
(@message_body);
END
COMMIT;
END
END
GO
My endpoints are created like so:
CREATE ENDPOINT MyEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)
GO
GRANT CONNECT TO CertOwner
GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner
GO
And my routes like so:
GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner
GO
CREATE REMOTE SERVICE BINDING [MyCertificateBinding]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
