UnknownSqlServerError

jterlouw

Hi guys,

I have a problem to have my webservice get to work.

I exec this very simple:

DROP ENDPOINT sql_endpoint;

GO

CREATE ENDPOINT sql_endpoint

STATE = STARTED

AS HTTP(

PATH = '/sql',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

SITE = 'SQL'

)

FOR SOAP (

WEBMETHOD 'GetSqlInfo'

(name='master.dbo.xp_msver',

SCHEMA=STANDARD ),

WEBMETHOD 'DayAsNumber'

(name='master.sys.fn_MSdayasnumber'),

WSDL = DEFAULT,

SCHEMA = STANDARD,

DATABASE = 'master',

NAMESPACE = 'http://localhost/'

);

GO

ANd receive by executing this on the http http://localhost/sql sql_endpoint the following return

- <SOAP-ENV:Fault xmlnsqlsoapfaultcode="http://schemas.microsoft.com/sqlserver/2004/SOAP/SqlSoapFaultCode">

- <SOAP-ENV:Code>

<SOAP-ENV:Value>SOAP-ENV:Receiver</SOAP-ENV:Value>

- <SOAP-ENVubcode>

<SOAP-ENV:Value>sqlsoapfaultcode:UnknownSqlServerError</SOAP-ENV:Value>

</SOAP-ENVubcode>

</SOAP-ENV:Code>

- <SOAP-ENV:Reason>

<SOAP-ENV:Text xml:lang="en-US">A server error occurred while processing SOAP request: Receiver, UnknownSqlServerError</SOAP-ENV:Text>

</SOAP-ENV:Reason>

<SOAP-ENV:Node>http://localhost:80/sql sql_endpoint</SOAP-ENV:Node>

<SOAP-ENV:Role>http://schemas.microsoft.com/sqlserver/2004/SOAP</SOAP-ENV:Role>

- <SOAP-ENVetail>

- <sqlresultstreamqlMessage xsi:type="sqlmessageqlMessage">

<sqlmessage:Class>16</sqlmessage:Class>

<sqlmessage:LineNumber>25</sqlmessage:LineNumber>

<sqlmessage:Message>An unexpected query string was passed to a Web Service Description Language (WSDL) generation procedure.</sqlmessage:Message>

<sqlmessage:Number>17885</sqlmessage:Number>

<sqlmessagerocedure>sp_http_generate_wsdl_defaultcomplexorsimple</sqlmessagerocedure>

<sqlmessageerver>MAN001</sqlmessageerver>

<sqlmessageource>Microsoft-SQL/9.0</sqlmessageource>

<sqlmessagetate>1</sqlmessagetate>

</sqlresultstreamqlMessage>

</SOAP-ENVetail>

Also the sproc exec [master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple] 65544,0,N'localhost',N'sql_endpoint',N'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.2; SIMBAR={16042FF6-D542-475f-B8DD-ACCA1BA5EDD3}; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; InfoPath.2)' gives some strange reaction.

Anyway the http://localhost/sql wsdl works

Jimmy Wu - MSFT

SQL Server Web Services endpoint supports the typical WSDL document request of http://servername/path wsdl. The query string is ' wsdl'. As the error message mentioned, "An unexpected query string was passed to a Web Service Description Language (WSDL) generation procedure.", the query string " sql_endpoint" is unexpected.

In the working scenario you stated (http://localhost/sql wsdl), the query string is " wsdl" which is that the WSDL generator expects.

Please note that looking at the above ENDPOINT syntax, the actual URL to reach this endpoint will actually be http://SQL/sql and the URL to the WSDL document is http://SQL/sql wsdl.

Specifically:

• SITE = 'SQL' --> http://SQL
• PATH = '/sql' --> /sql

combining the 2, gets you http://SQL/sql

HTH

Jimmy

jterlouw

Thanks for your response, but still no response from the webserver.

The IIS does also not show the endpoint but it only shows up in the endpoints/SOAP in SQL.

So by my point of view in your example I should have a website (local) which called SQL

Can you give me a working example on localhost

Thanks jaap

Jimmy Wu - MSFT

You are correct that these SOAP endpoints created in SQL will NOT show up in IIS.

To retrieve the WSDL document on an endpoint running on the local machine, I recommend creating the endpoint with something like:

CREATE ENDPOINT sql_endpoint

STATE = STARTED

AS HTTP(

PATH = '/sql',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

SITE = '*'

)

FOR SOAP (

WEBMETHOD 'GetSqlInfo' (name='master.dbo.xp_msver', SCHEMA=STANDARD ),

WEBMETHOD 'DayAsNumber' (name='master.sys.fn_MSdayasnumber'),

WSDL = DEFAULT,

SCHEMA = STANDARD,

DATABASE = 'master',

NAMESPACE = 'http://localhost/'

);

OR

CREATE ENDPOINT sql_endpoint

STATE = STARTED

AS HTTP(

PATH = '/sql',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

SITE = 'localhost'

)

FOR SOAP (

WEBMETHOD 'GetSqlInfo' (name='master.dbo.xp_msver', SCHEMA=STANDARD ),

WEBMETHOD 'DayAsNumber' (name='master.sys.fn_MSdayasnumber'),

WSDL = DEFAULT,

SCHEMA = STANDARD,

DATABASE = 'master',

NAMESPACE = 'http://localhost/'

);

In the first endpoint example, you will be able to retrieve the WSDL document with URL like:

http://localhost/sql wsdl

http://<machineName>/sql wsdl

For the 2nd endpoint example, you will only be able to retrieve the WSDL document with URL like:

http://localhost/sql wsdl

HTH,

Jimmy

jterlouw

Hi Jimmy,

Thanks for your response again, but try to do this http://localhost/sql GetSqlInfo and error A server error occurred while processing SOAP request: Receiver, UnknownSqlServerError.

However http://localhost/sql wsdl gives correct return, anything else I can check

R.

Jaap

Jimmy Wu - MSFT

Ah, I think I understand what you are wanting to do now.

You want to be able to use the URL to call the webmethod directly, instead of sending a full blown SOAP request. Sorry, the SQL Server 2005 Native XML Web Services feature only support proper SOAP request as specified by the SOAP 1.1 spec.

The only thing you can retrieve using on the URL is the WSDL document, as you found out using http://localhost/sql wsdl

To call the webmethods you've specified on the endpoint, you will need to send a HTTP POST request with the proper SOAP message body. Please refer to the following MSDN topic for additional information about the exact SOAP message structure supported: http://msdn2.microsoft.com/en-us/library/ms190796.aspx

For a sample on writing a SOAP client application using .Net Frameworks, please refer to http://msdn2.microsoft.com/en-us/library/ms190173.aspx

HTH,

Jimmy

jterlouw

Thanks Jimmy, got almost worried that I did something stupid.

Regards,

Jaap