Dude, very cool. Especialy the sample in VB - there's too much C# around *grin* It's a shame that it comes back as DataSet though - I wonder if there's a way to get it to send me back a DataReader instead.

Kimberly Tripp has 8 steps to improve transaction log throughput.
Thom Robbins has a video to demo...

There is no ned for IIS ? So I assume that with "http.sys module in the Win2K3 kernel" I can program my own WebService?
What will be the future of IIS, then?

Hi,

I am trying to consume an HTTP Endpoint with SQL 2005 Reporting Services.

Since reporting services can take either an XML file or a WebService, it seems to make sense that you should be able to use an HTTP Endpoint.

With a WebService.asmx, you can call the WebMethods in the following way:
http://server/Webservice.asmx/WebMethod?Param=Value


I have tried this syntax with the endpoint, but have had no luck.

Do you know how I can render out a DataSet in XML form directly from an Endpoint?

Is this what you meant in the quote below?


> Here are the other return values from the endpoint:

> Select statement – Convert to DataSet
> Select statement FOR XML – Convert to XmlElement
> Error or Message – WSDL SqlMessage
> Output parameter – WSDL SqlParameter
> Row count – WSDL SqlRowCount
> RETURN statement – Convert to Int32
> Tada! There you go! A web service straight from Sql Server!


Regards,

Michael Mileos

Hi,

I´m having some troubles when trying to consume the endpoint. I followed the sugestions and create a simple VB.Net program to test the service. But when it runs, the following error appers:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.xml.dll Additional information: There is an error in XML document

Does anybody have a clue ???

Regards
Fernando

What version of VS.NET are you using?

If you are using version before VS.NET 2005 create a web reference to the endpoint using ->

http://localhost/Employee?wsdlsimple">http://localhost/Employee?wsdlsimple

instead of:

http://localhost/Employee?wsdl

If this does not work, add the following section to your "MyTestProgram.exe.config" file to trace out the SOAP request and response to c:\soaptrace.log ->

<system.diagnostics>
<trace autoflush="true" />
<sources>
<source name="System.Net">
 <listeners>
  <add name="System.Net"/>
 </listeners>
</source>
<source name="System.Net.Sockets">
 <listeners>
  <add name="System.Net"/>
 </listeners>
</source>
<source name="System.Net.Cache">
 <listeners>
  <add name="System.Net"/>
 </listeners>
</source>
</sources>
       <sharedListeners>
               <add
                 name="System.Net"
                 type="System.Diagnostics.TextWriterTraceListener"
                 initializeData="c:\soaptrace.log"
               />
       </sharedListeners>
<switches>
 <add name="System.Net" value="Verbose" />
 <add name="System.Net.Sockets" value="Verbose" />                
 <add name="System.Net.Cache" value="Verbose" />
</switches>
</system.diagnostics>

Then post this back to this thread, thanks!

Hi,

Nice walkthrough, however when I come to implement the code for the windows application I'm having trouble with the DirectCast() function on the webmethod. The error is;

Value of type '1-dimensional array of object' cannot be converted to 'System.Data.Dataset'

Any ideas?

thanks, Nathan

Morning!

Excellent Walkthrough...  I too had the '1-dimensional array of object' cannot be converted to 'System.Data.Dataset' , but managed to get around it as follows(Datagrid view is easier to use, imho);

      ' Create a new instance of the web service

       Dim employeesProxy As boomer1.SQLEP_BoomerSelectTest1 = New boomer1.SQLEP_BoomerSelectTest1

       ' You have to pass in credentials to authenticate yourself to use the service.  We are just going to use

       '   the same credentials we have logged into our computer as.

       employeesProxy.Credentials = System.Net.CredentialCache.DefaultCredentials

       Dim goat As System.Array

       goat = employeesProxy.BoomerSelectTest1(TextBox1.Text.Trim)

       ' The result of a SELECT statement via an endpoint can be converted to a DataSet

       ListBox1.DataSource = goat(0).tables.item(0)

       ListBox1.DisplayMember = "Column 0"

       DataGridView1.DataSource = goat(0).tables.item(0)

       Label1.Text = "Returned Rowcount = " & goat(1).count.ToString

For Geoff Appleby :

You can have it returning other return type. Just his example uses a stored procdure which will return a dataset by default.

I made one CLR that invokes the endpoint webservice

But I need to have URL property as a parameter

that means that when I call with another webservice It does not work

Great Job

Regarding the the "'1-dimensional array of object' cannot be converted to 'System.Data.Dataset' " problem, here's what to do.  When creating your endpoint, you need to have this line (see sample below):  FORMAT = ROWSETS_ONLY.  If you have this line, the web service returns a dataset;  otherwise, it returns an object array.

Good luck.

Steph

Sample:

CREATE ENDPOINT SAC

STATE = STARTED

AS HTTP

(

SITE = 'localhost',

PATH = '/SAC',

AUTHENTICATION = ( INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 2000

)

FOR SOAP

(

WEBMETHOD 'GetItemAvailableQty'

(

NAME = 'SAC_BackEnd.dbo.uspGetItemAvailableQty',

SCHEMA = STANDARD,

FORMAT = ROWSETS_ONLY

),

WSDL = DEFAULT,

BATCHES = ENABLED,

DATABASE = 'SAC_BackEnd'

)

Re: the error "Value of type '1-dimensional array of object' cannot be converted to 'System.Data.Dataset'", when you create your ENDPOINT, you have to have the line "FORMAT = ROWSETS_ONLY" as shown in my example below.  This line tells the web service to return a dataset instead of an object array.

Good luck.

CREATE ENDPOINT SAC

STATE = STARTED

AS HTTP

(

SITE = 'localhost',

PATH = '/SAC',

AUTHENTICATION = ( INTEGRATED ),

PORTS = ( CLEAR ),

CLEAR_PORT = 2000

)

FOR SOAP

(

WEBMETHOD 'GetItemAvailableQty'

(

NAME = 'SAC_BackEnd.dbo.uspGetItemAvailableQty',

SCHEMA = STANDARD,

FORMAT = ROWSETS_ONLY

),

WSDL = DEFAULT,

BATCHES = ENABLED,

DATABASE = 'SAC_BackEnd'

)

change this line adding (0) after EmployeeList.

       Dim ds As System.Data.DataSet = DirectCast(employeesProxy.EmployeeList(0), DataSet)

Hi

Do you know if its possible to call a method (by interface or not) implemented in a Windows Application, developed in C#, instead of a procedure or function implemented in SQL?

I need to do a WebService like this one, with EndPoint, because my priority is to pass and receive an XML throw this WebService whitout IIS, and at the same time I need to treat all the process with log and tool for debbug, so I need to implement one Windows Application, with an Arquiteture the can make possible to do this logs and treats exceptions.

Can you help me, please?

Thanks

Only select type of sp we can able to make as webservice? or we can even insert sp also as web service?

Pingback from  the rasx() context  &raquo; Blog Archive   &raquo; Pulling a Bunch of SQL Server and LINQ Links

Thank you for your example.