Microsoft InfoPath 2007 and web services

InfoPath 2007 is an amazing product that comes loaded with features right out of the box. You can accomplish a tremendous amount of work without ever writing code. That being said, there are still times where you need to delve a little deeper into InfoPath to accomplish your goals. Using SQL Server and web services offers a lot of flexibility when working with databases and in this tutorial it will show you how to use these features.

I will be using InfoPath 2007, SQL Server 2008, and Windows Server 2008 R2. There are essentially three steps involved and they are :

1. Creating a stored procedure
   
2. Creating the endpoint/webservice
   
3. Connecting to it in InfoPath
   

Creating the stored procedure

 

The first step is to create the stored procedure. The store procedure is a reusable, ready built command that SQL Server holds on to for us until we're ready to use it. If you're a programmer, you can think of an SP as a function. The way we build is SP is as follows:

CREATE
PROCEDURE spNameOfProcedure @myDate datetime, @myName varchar(255)

AS

SELECT
*
from myTable where TheDateColumn = @myDate and TheNameColumn = @myName

@myDate and @myName are variables that you tell the stored procedure to expect every time it is used.

Using this stored procedure would look like this:

    EXEC spNameOfProcedure
'12/1/2010/, 'Elliott'

You can go here if you are still struggling with this.

Creating the Endpoint/Web Service

 

The next step is to create the endpoint and web service. You can think of the endpoint as the doorway into SQL Server. InfoPath will knock on the door, say "hey I need to use one of your web services and see what happens". SQL Server will respond with a list of available web services and depending on the web service that InfoPath chooses, it will run the respective stored procedure. Please keep in mind that this opens up your computer to the world and if not used correctly, can be a security risk.

Below is an example of the SQL statement that creates one endpoint with two web services:

    CREATE
ENDPOINT [SomeNameForTheEndPoint]

    AUTHORIZATION [myUserName]

    STATE=STARTED

    AS
HTTP (PATH=N'/theServiceName',
PORTS
=
(CLEAR), AUTHENTICATION =
(NTLM), SITE=N'myUserName',
CLEAR_PORT
= 80,
COMPRESSION=DISABLED)

    FOR
SOAP (

WEBMETHOD
'WebServiceName1'( NAME=N'[DataBaseName].[dbo].[spSomeStoredProcedure]'

,
SCHEMA=DEFAULT

, FORMAT=ROWSETS_ONLY),

WEBMETHOD
'WebServiceName2'( NAME=N'[DataBaseName].[dbo].[spSomeOtherStoredProcedure]'

,
SCHEMA=DEFAULT

, FORMAT=ROWSETS_ONLY),

BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'Some Database',
NAMESPACE=N'http://tempuri.org',
SCHEMA=STANDARD, CHARACTER_SET=XML)

 

GO

 

You can visit this site for more information on creating an endpoint.

In order for users to be able to access the web service, you need to give them permissions within SQL Server to do so. Otherwise InfoPath will essentially knock on the door and SQL Server will tell it to go away.

 

Connecting to it in InfoPath

 

    Now we go to InfoPath and add a new connection:

    

Next we want to choose SOAP request

        

    Next we enter the URL of the web service we created. Mine is on an intranet so I just enter the name of my computer and then the name of the endpoint.

    

    Now you should see the web services we created with the endpoint. Select one and click next.

    

Here you can see the two parameters that we told the stored procedure to expect. You can click 'Set Sample Value' and just enter anything because we will be changing this in a minute. Then Click next. You can then click your way through the rest of the prompts until you press finish.

    

Now you are able to use this data connection like any other data connection in InfoPath. The tricky part however, is assigning the variables that you pass to the stored procedure. To do this, we create a rule that "Sets A Value" and we change the sample value from above:

Then set the myDate variable to whatever you want.

If you did everything correctly you should be able to assign a value to all the queryFields expected by the web service and retrieve the data. Just remember that after you set the variables you will need to refresh the connection in order for the new information to populate.

Happy InfoPathing!