为什么我的SQL Web服务总是返回null？ [英] Why my SQL web service always returns null?

问题描述

网络服务：

Web service:

public class MyWebService : System.Web.Services.WebService
{
    public MyWebService()
    {
}

[WebMethod]
public string HelloWorld()
{
    return "Hello World";
}

存储过程：

Stored procedure:

CREATE PROC Hello

AS
    DECLARE @obj AS INT
    DECLARE @Uri AS NVARCHAR(MAX)
    DECLARE @Response AS BIT


    SET @Uri = 'http://MY IP IS HERE :9671/MyWebService.asmx/HelloWorld'
    EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
    EXEC sp_OAMethod @obj, 'open', NULL, 'POST', @Uri, false
    EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT

    SELECT @Response [response]
    EXEC sp_OADestroy @obj

RETURN

像这样执行：

Executing it like this:

EXEC Hello

调用存储过程后的输出始终为null但我想将Web服务返回值设置为输出。 （我的SQL SERVER 2012 CLR已启用）

The output after calling the stored procedure is always null but I want to set the web service return value as output. (My SQL SERVER 2012 CLR is Enable)

推荐答案

现在我看到...

您永远不会向服务发送命令.. （它应该是GET而不是POST我认为）...

这是SP ...

Now I see...
You never SEND your command to the service... (And it should be GET and not POST I think)...
Here is the SP...

CREATE PROC Hello
AS
    DECLARE @obj AS INT
    DECLARE @Uri AS NVARCHAR(MAX)
    DECLARE @Response AS BIT
 

    SET @Uri = 'http://MY IP IS HERE :9671/MyWebService.asmx/HelloWorld'
    EXEC sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
    EXEC sp_OAMethod @obj, 'open', NULL, 'POST', @Uri, false
    EXEC sp_OAMethod @obj, 'send'
    EXEC sp_OAGetProperty @obj, 'ResponseText', @Response OUTPUT
 
    SELECT @Response [response]
    EXEC sp_OADestroy @obj
 
RETURN

我今天面临同样的问题。



很明显，如果没有Peter的帖子并且添加了发送方法，它没有很大的希望它是有效的。



但这还不够。



你需要替换它：DECLARE @Uri AS NVARCHAR（MAX）

with：DECLARE @Uri AS NVARCHAR（4000）



4000是对象的潜在限制。

如果你没有将MAX改为4000，你仍然会有一个空值。



我的背景：W10 / SQL SERVER 201 6

I face the same problem today.

It's clear that without the post of Peter and adding the send method, there is not a big hope that it's works.

But it's not enough.

You need to replace this : DECLARE @Uri AS NVARCHAR(MAX)
with : DECLARE @Uri AS NVARCHAR(4000)

4000 is the underlying limitation of the object.
If you don't change MAX by 4000 you will still have a null value.

My context : W10 / SQL SERVER 2016

如果你想在sql server上执行asmx，请检查你的URL。因为区分大小写

url链接在sql server上。 （大写 - 较小的情况）

if you want to exec asmx on sql server please check your URL. Because "case sensitive"
url link on sql server. (Upper case -Lower Case)

这篇关于为什么我的SQL Web服务总是返回null？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！