I have a website of about 200 pages. The problem is 180 of the pages are store in a database hosted by a third party on their domain (they are my software supplier).
I want to build a sitemap with links to the product pages so the search engine spider can find them, but I as I understand it, it will be a waste of time unless my database pages are stored under my own domain.
I don't believe it's a waste to build a sitemap file, necessarily. Are you able to set up an application on your domain that can 301 redirect to the 3rd party domain? If so, you could then build a sitemap file for these pages using URLs on your own domain, and 301 redirect all of the URLs to their final destination. That might work.
Alternatively, you could build webpages on your domain that contain links directly to all the pages on the external domain. In this way, you could expose the URLs to be spidered by the search engines and indexed. Note: your URLs still should be optimized to some degree, meaning that longer URLs with more name/value parameters in query strings tend to not perform as well in search engines.