Consume HTTP EndPoint

Hello, I have problems consuming webservice, I was following this page.

http://codebetter.com/blogs/raymond.lewallen/archive/2005/06/23/65089.aspx

but in the intelisense the method returns an array of objects[], So I have a problem with this line.

localhost.GetEmployees sd = new localhost.GetEmployees();

sd.Credentials = System.Net.CredentialCache.DefaultCredentials;

DataSet ds = (DataSet)(sd.EmployeeList());> IT CANT CONVERT.

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

Error 1 Cannot convert type 'object[]' to 'System.Data.DataSet' c:\inetpub\wwwroot\atlas1\Default.aspx.cs 18 22 http://localhost/atlas1/

There are 2 ways to resolve this:

The example stored procedure specified on that blog site is only returning a single resultset from a SELECT statement. You can modify the CREATE ENDPOINT statement to the following:
CREATE ENDPOINT GetEmployees
STATE = STARTED
AS HTTP
(
PATH = '/Employee',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'localhost'
)
FOR SOAP
(
WEBMETHOD 'EmployeeList'
(NAME='AdventureWorks.dbo.GetEmployees', FORMAT=ROWSETS_ONLY),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://AdventureWorks/Employee'
)
go

such that the generated method signature will be:
DataSet EmployeeList();you will not need the CAST anymore;

The other solution is to modify your client code to:
object [] o = sd.EmployeeList();
DataSet ds = (DataSet)(o[0]);

Note: You really should check each of the objects in the object[] to make sure it is of the proper type before casting the object. This will prevent other cast exceptions.

Hope that helps.
Jimmy Wu

|||

I used this script

create ENDPOINT [GetEmployees]

AUTHORIZATION [LUCHO\Administrador]

STATE=STARTED

AS HTTP (PATH=N'/Employee', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED), SITE=N'localhost', CLEAR_PORT = 85, COMPRESSION=DISABLED)

FOR SOAP (

WEBMETHOD 'EmployeeList'( NAME=N'[AdventureWorks].[dbo].[GetEmployees]'

, SCHEMA=DEFAULT

, FORMAT=ROWSETS_ONLY), BATCHES=DISABLED,

WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',

SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'AdventureWorks',

NAMESPACE=N'http://AdventureWorks/Employee',

SCHEMA=STANDARD, CHARACTER_SET=XML)

Now i have an 401 Error Not Authorized? I am on windows xp not on an active directory domain, so I am the only user, and I am the administrator too.

|||

Luis,

Most likely you are hitting a WinXP issue where for "Integrated" authentication type (which supports both Kerberos and NTLM), applications running WinXP do not fall back to NTLM when Kerberos support is not available.

In your scenario, since your machine is not on a domain, it most likely is not able to contact the Kerberos SPN server.

There is 2 possible solutions:
1) Alter the endpoint to only support NTLM
eg. ALTER ENDPOINT GetEmployees
AS HTTP (
AUTHENTICATION=(NTLM)
)

2) Change your client app to only support NTLM
eg. Instead of setting the credential as sd.Credentials = System.Net.CredentialCache.DefaultCredentials;set the credentials as System.Net.CredentialCache myCreds = new System.Net.CredentialCache();
myCreds.Add(new Uri(sd.Url), "NTLM", System.Net.CredentialCache.DefaultCredentials.GetCredential(new Uri(sd.Url), "NTLM"));
sd.Credentials = myCreds;

This will force the client application to only support NTLM authentication type.

For additional information on specifying specific authentication types in the client application please refer to http://msdn2.microsoft.com/en-us/library/ms175929.aspx

Jimmy Wu

|||

I have had similiar issues as the above users. And have used the same solutions to only receive the following error:

System.Xml.Schema.XmlSchemaException: Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:int' is not declared. An error occurred at , (1, 1989).

SQL Code:

CREATE ENDPOINT GetStores

STATE = STARTED

AS HTTP

(

PATH = '/Store',

AUTHENTICATION = (INTEGRATED),

PORTS = (CLEAR),

SITE = 'localhost',

CLEAR_PORT = 81

)

FOR SOAP

(

WEBMETHOD 'StoreList'

(

NAME='AdventureWorks.dbo.SalesStoreProc',

FORMAT = ROWSETS_ONLY

),

BATCHES = DISABLED,

WSDL = DEFAULT,

DATABASE = 'AdventureWorks',

NAMESPACE = 'http://AdventureWorks/Store'

)

GO

GRANT CONNECT ON ENDPOINT::[GetStores] TO PUBLIC

VB.NET Code

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Create an instance of the Proxy class

Dim ws As New StoreList.localhost.GetStores

ws.Credentials = System.Net.CredentialCache.DefaultCredentials

'Bind the results of GetStores to dgStoreList

Me.dgStoreList.DataSource = ws.StoreList()

Me.dgStoreList.DataBind()

End Sub

Versions:

VS2003

SQL 2K5 sp1

WINXP sp2

|||

You mentioned that you are using Visual Studio 2003. Unfortunately, VS2003's DataSet object is not fully supported by SQL 2005 Native Web Services. For the richest programming experience, it is recommended to use Visual Studio 2005 when programming against SQL 2005 Native Web Services.

When using Visual Studio 2003 to design and implement a client application against SQL 2005 Native Web Services, the recommendation is to make the "Web Reference" against the Simple WSDL instead of the Default WSDL document. Retrieval of the Simple WSDL document is done by specifying "?wsdlsimple" as the suffix of the URL.

ie. http://myserver/myendpoint?wsdlsimple

Since you have already created the client application, it is also possible to just compile your client application with the .Net Frameworks 2.0 compiler that is installed as part of SQL 2005, or part of .Net Frameworks 2.0 (download available at http://msdn.microsoft.com/netframework/downloads/updates/default.aspx)

Jimmy Wu

|||i've been trying to make an easy mobile application(mobile5.0) using vs2005, that consume http endpoint from sql server, but each time the application try to make a connection to the endpoint, it asks for a username and password. i thought, i'll be able to solve this by granting an sql login user, using this..

USE master
GRANT CONNECT ON ENDPOINT::SQLEP_VerlagDB TO [sqlLoginUser]

.. but it didnt do much help. the endpoint also asks for user and password, if i try to open the wsdl site using browsers like firefox. where can i get this user/pass value?

and one more thing, i've tried to consume this endpoint by creating windows-app and web-app. it works! any idea how i can consume this endpoint in a mobile-app?

thanks in advance..

|||

I'm not familiar with the differences between .Net Compact Frameworks and Mobile 5.0, if there's any. SQL 2005 Native Web Services require all requests to be authenticated, this includes retrieving the WSDL document as well as SOAP requests.

If you are using .Net Compact Frameworks 2.0, you should be able to effectively re-compile your working windows-app or web-app with .Net Compact Frameworks 2.0 and everything should work.

Since you said that you have a working windows-app, I am assuming that your working app already contains code to specify the user credentials, such as:

SqlWebReference.SQLEP_VerlagDB wReq = new SQLEP_VerlagDB();
CredentialCache myCache = new CredentialCache();
myCache.Add(new Uri(wReq.Url),"NTLM",new NetworkCredential(UserName,SecurelyStoredPassword));
wReq.Credentials = myCache;

or

wReq.Credentials = System.Net.CredentialCache.DefaultCredentials;

This should continue to work, if not it is working, then it is likely to be an issue with the .Net Compact Frameworks.

If you are using SQL user login credentials to connect to the endpoint, please refer to http://msdn2.microsoft.com/en-us/library/ms180919.aspx for additional information.

If your scenario allows you to use a hard coded local machine user, alternatively, you can use Basic authentication over SSL. In this case, your application will look like:

SqlWebReference.SQLEP_VerlagDB wReq = new SQLEP_VerlagDB();
CredentialCache myCache = new CredentialCache();
myCache.Add(new Uri(wReq.Url),"Basic",new NetworkCredential(UserName,Password));
wReq.Credentials = myCache;

HTH,
Jimmy Wu