The SQL 2005 HTTP endpoint had been successfully created using the code and the SQL stored Procedures for the custom database had successfully been created . The connection to the HTTP URL from a remote machine was successful but when the same URL was tried from the MOSS server itself it gave a 501 error. Trying to connect to the ?WSDL link as a webservice from the SI Configurator also was unsuccessful resulting in not being able to see the Stored Procedures form the SI webparts
Title
Unable to connect to HTTP EndPoint/Unable to connect from SI config to the HTTP Endpoint as a WebDescription
Resolution
The Endpoint was dropped and then created again after making the following changes on the line
AS HTTP (PATH=N'/TestOffer', PORTS = (CLEAR), AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED), SITE=N'TestServer', CLEAR_PORT = 80, COMPRESSION=DISABLED)
to
AS HTTP (PATH=N'/TestOffer', PORTS = (CLEAR), AUTHENTICATION = (NTLM), SITE=N'TestServer', CLEAR_PORT = 80, COMPRESSION=DISABLED)
Mentioning all the three methods (NTLM, KERBEROS, INTEGRATED) for authentication was failing the connection of the SI webpart as a WebService and hence not being able to expose the Stored Procedures in SQL
Modifying it to show only '(NTLM)' resolved the issue.