For this article, we will use the pubs sample database for SQL Server.  For the purpose of this report we create a report showing the sales of each book, including information from where the book was manufactured.   To create this report, we will use the following tables:

·         Authors

·         Publishers

·         Title/author

·         Titles

·         Sales

·         Stores

See Figure 1 for a graphic of those tables.  One thing that is not included in the pubs database is a manufacturing ID.  We will need that to join our Web Service data to our data from this database.  So we will need to add that field to a table.  Normally it might be included in the actual orders table, but for the purposes of this article, we will add this to the Sales table.  We are creating this ID so that there we can link between the manufacturing information in our "ERP" system and the data in our Database.

Figure 1

To create the new column called "mfgid" we need to go into the query analyzer and execute the following commands:

Listing 1

Use pubs
alter Table dbo.sales add mfgid int

These add the foreign key that we will connect our Web Service to.