I want to provide more detail to this, so as to perhaps generate more interest and possible solutions.
1. Our environment is this: We are under one AD domain and there are three servers involved. The first server is the Web Server for MOSS 2007, it uses a SQL 2005 database on another machine for it's content. We have another 2k3 server with SQL 2k5 on it for this scenario.
2. I created a SQL 2k5 web service on the third machine as follows.
CREATE TABLE DUFUS (FULLNAME VARCHAR(50)) -- JUST CREATE A TABLE
--CREATE THE STORED PROCEDURE FOR INSERT INTO DUFUS
CREATE PROCEDURE udsp_InsertNameIntoDufus
@FULLNAME VARCHAR(20)
AS
INSERT INTO DUFUS
(FULLNAME)
VALUES
(@FULLNAME)
GO
--CREATE THE STORED PROCEDURE FOR SELECTING FROM DUFUS
CREATE PROCEDURE udsp_SelectNameFromDufus
AS
SELECT FULLNAME FROM DUFUS
GO
--CREATE ENDPOINT WEB SERVICE
CREATE ENDPOINT SQL_DUFUSENDPOINT
STATE = STARTED
AS HTTP
(
PATH = '/DUFUS',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
CLEAR_PORT = 80,
SITE = 'or-viewpoint'
)
FOR SOAP
(
WEBMETHOD 'GetNameFromDufus'
(NAME = 'Viewpoint.dbo.udsp_SelectNameFromDufus'),
WEBMETHOD 'InsertNameIntoDufus'
(NAME = 'Viewpoint.dbo.udsp_InsertNameIntoDufus'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'Viewpoint',
NAMESPACE = 'http://tempURi.org'
)
--just testing the stored procedure
EXEC udsp_InsertNameIntoDufus 'YULE BRENNER'
--just testing the stored procedure
EXEC udsp_SelectNameFromDufus
3. Create an InfoPath form to talk to this web service. It's just a very simple form, one label and one text box for a name
Create the submit options by selecting Tools>>Submit Options and selecting the check box next to "Allw users to submit this form" and select "Web Service" from the drop down list. Click on "Add" to create your data connection.
Create a submit to the URL. In my case it's http://or-viewpoint/dufus?WSDL
Select the InsertNameIntoDufus method.
Map the fields
Name it something and finish and close. Time to test it in InfoPath in Design mode - click Preview
Enter a Name and click and Submit
Go back to SQL server just to make sure data was written.
And it works as expected.
NOW - LET'S PUBLISH THIS FORM!!!!
First we have to convert the data connection in InfPath to a data connections library in SharePoint.
While your viewing your form in InfoPath (Design) select tools>>Data Connections and click on the convert button
Insert the URL to the data connections library you have already created and give it a name
Then navigate to your data connections library to approve the data connection
Approve the Data Connection and click on OK.
Now the data connection is approved.
Now we can publish the form to a new document library. Back in the Design mode of your infopath form you select "Publish Form Template" in the design tasks you then get the following:
Select the first option, "To a SharePoint server with or without InfoPath Forms Services" and select next. Enter the URL to where you want to publish your form and select Next.
Select Document Library and make sure the Enable this form to be filled out by using a browser is selected, click next
We are going to create a new document library, hit next.
Give it a name and click next
Perhaps you want to view some columns in your SharePoint library, but we are not for this scenario, click next
And finally, click "publish". When it's published, select close and open the document library.
We have to modify the settings of the document library to open the form in a browser - in the document library, click Settings and Form Library Settings, then select Advanced Settings under the General Settings header, then select Display as a Web Page under the Browser-Enabled Documents section.
Select OK at the bottom of the page and navigate back to your document library and select "New" this should open your form in a browser. Enter a name into the text field and click submit.
You are now presented with an error. that simply say's "An erro occured while the form was being submitted"
Why will it work in InfoPath and not in SharePoint? This is the conundrum. We have tried signing the form with a certificate and setting the domain level to full trust, but alas, the same result. Someone please tell me what's going on..
Thanks - Kyle
(*****I have a word document with screen shots if you are interestd*****)