Consuming webservices in a view

Hi
Is there a way in sql server 2005 or sql server 2000 to cosume a webservice
over a view?
The aim is to have a view which other appliactions can working with.
The data from the view should not come from a table, it should come per
realtime from a webservice.
thank's
Michel> Is there a way in sql server 2005 or sql server 2000 to cosume a
> webservice over a view?
In SQL 2005, I think you ought to be able to create a CLR function that you
can include in your view. I haven't personally done this, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:e28g7DFgIHA.4396@.TK2MSFTNGP04.phx.gbl...
> Hi
> Is there a way in sql server 2005 or sql server 2000 to cosume a
> webservice over a view?
> The aim is to have a view which other appliactions can working with.
> The data from the view should not come from a table, it should come per
> realtime from a webservice.
> thank's
> Michel
>

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx

If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.

Jimmy

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx

If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.

Jimmy

Consuming secured webservice in SSRS

Hi All,

We have a webservice, which we are able to consume in the SSRS report.

Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.

We did this and just changed the connection string from http to https.

But when we try to run the report, we get the following error message. Copied from BIDS.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to prepare web request for the specified URL.

ADDITIONAL INFORMATION:

Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)

The remote certificate is invalid according to the validation procedure. (System)

BUTTONS:

OK

Can anybody help in this.

Thanks in advance.

Virendra

Can anybody help to guide on the changes required for achieving this?

Virendra

|||

Hi ,

Has anybody used the secured webservice successfull as data source in the SSRS?

Any pointers to the above problem?

Virendra

|||

Hi All (looking at this post),

The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.

Virendra

Consuming secured webservice in SSRS

Hi All,

We have a webservice, which we are able to consume in the SSRS report.

Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.

We did this and just changed the connection string from http to https.

But when we try to run the report, we get the following error message. Copied from BIDS.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to prepare web request for the specified URL.

ADDITIONAL INFORMATION:

Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)

The remote certificate is invalid according to the validation procedure. (System)

BUTTONS:

OK

Can anybody help in this.

Thanks in advance.

Virendra

Can anybody help to guide on the changes required for achieving this?

Virendra

|||

Hi ,

Has anybody used the secured webservice successfull as data source in the SSRS?

Any pointers to the above problem?

Virendra

|||

Hi All (looking at this post),

The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.

Virendra

Consuming secured webservice in SSRS

Hi All,

We have a webservice, which we are able to consume in the SSRS report.

Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.

We did this and just changed the connection string from http to https.

But when we try to run the report, we get the following error message. Copied from BIDS.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to prepare web request for the specified URL.

ADDITIONAL INFORMATION:

Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)

The remote certificate is invalid according to the validation procedure. (System)

BUTTONS:

OK

Can anybody help in this.

Thanks in advance.

Virendra

Can anybody help to guide on the changes required for achieving this?

Virendra

|||

Hi ,

Has anybody used the secured webservice successfull as data source in the SSRS?

Any pointers to the above problem?

Virendra

|||

Hi All (looking at this post),

The problem is resolved. It was a trivial mistake done on my side. The "Windows Authentication" was enabled on the web service and the properties of the data source were set to No credentials in SSRS. After resolving this, it started working.

Virendra

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

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