Why my SQL web service always returns null?

Question

0.00/5 (No votes)

See more:

Web service:

C#

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

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

Stored procedure:

SQL

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:

SQL

EXEC Hello

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)

Posted 2-Sep-14 16:51pm

Roozbeh Amiressami

Updated 2-Jul-18 4:23am

Add a Solution

Comments

Suvabrata Roy 3-Sep-14 1:04am

I think sp_OACreate is unable to create the object

Try this code

@hr=sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
raiserror('Error Creating COM Component 0x%x, %s, %s',16,1, @hr, @src, @desc)
RETURN
END;

Roozbeh Amiressami 3-Sep-14 3:32am

when I trace your code if condition never true and I got this xml code and I think @hr is always null.

can you explain me?


<string xmlns="http://www.webserviceX.NET/"><StockQuotes><Stock><Symbol>MSFT</Symbol><Last>45.09</Last><Date>9/2/2014</Date><Time>4:15pm</Time><Change>-0.34</Change><Open>45.43</Open><High>45.46</High><Low>44.85</Low><Volume>22976776</Volume><MktCap>371.5B</MktCap><PreviousClose>45.43</PreviousClose><PercentageChange>-0.75%</PercentageChange><AnnRange>30.95 - 45.71</AnnRange><Earns>2.63</Earns><P-E>17.27</P-E><Name>Microsoft Corpora</Name></Stock></StockQuotes>

Suvabrata Roy 3-Sep-14 4:44am

Is this the reply of your web service ?

Roozbeh Amiressami 3-Sep-14 5:04am

yes

Suvabrata Roy 3-Sep-14 6:01am

Your code and output not matched so please check you service URL and Operation Name

Nitin S 3-Sep-14 1:06am

http://MY IP IS HERE:9671
Have you deployed your web service properly? have you tried accessing it through web browser?

Roozbeh Amiressami 3-Sep-14 3:12am

hiMy ip is a valid IP on dedicated server on internet
yes I did try that and I got "HelloWord" XML String on browser bot when I ecec my proc the sql result is always null

Kornfeld Eliyahu Peter 3-Sep-14 4:23am

If you open your URL - http://MY IP IS HERE :9671/MyWebService.asmx/HelloWorld - in a browser, what do you get?

Roozbeh Amiressami 3-Sep-14 5:05am

this value:
(I removed some of tag signs to code project not convert it as a tag)
!--?xml version="1.0" encoding="UTF-8"?->
string xmlns="http://tempuri.org/">Hello World /string

Kornfeld Eliyahu Peter 3-Sep-14 5:08am

That's good...Now. Can you run your SP line by line to see every possible error you may have?

Roozbeh Amiressami 3-Sep-14 5:12am

I couldn't find any error in my SP
can you trace it for me?

Kornfeld Eliyahu Peter 3-Sep-14 5:15am

No. Please do not look for errors in the SP but run it's content line-by-line...

Roozbeh Amiressami 3-Sep-14 5:22am

ok dear
I did that @hr is 0 till end
@object got the 16711422 value
but @Response never got a value in command line 9
(EXEC sp_OAGetProperty @obj, 'Response', @Response OUTPUT)
Exec @hr=sp_OACreate 'MSXML2.XMLHTTP', @Object OUT

Roozbeh Amiressami 3-Sep-14 7:05am

Thank you so much you helped me when I was in a very bad situation

I am so sorry for this but can you see my other problem about this problem?

http://www.codeproject.com/Questions/814699/Why-My-Webservice-Trow-Eeption-When-I-Execute-My-S

4 solutions

Solution 2

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

Posted 8-Aug-17 7:27am

EtcetDev

Comments

CHill60 9-Aug-17 14:33pm

Really? Rubbish!

Solution 3

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)

Posted 19-Dec-17 3:12am

yaman2222

Comments

Richard Deeming 19-Dec-17 11:29am

Asked, answered, and solved over THREE YEARS AGO.

And your answer is nonsense. URLs on Windows are not case-sensitive.

Solution 4

I think you can do the following:
replace                         DECLARE @Response AS BIT  
by                              DECLARE @Response VARCHAR(8000)


ALTER PROC Hello
AS
    DECLARE @obj AS INT
    DECLARE @Uri AS NVARCHAR(MAX)
    DECLARE @Response VARCHAR(8000)
 

    SET @Uri = 'http://localhost:51017/WebService.asmx?op=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

GO

Posted 2-Jul-18 4:23am

Châu Minh Hùng

Comments

CHill60 3-Jul-18 5:48am

Essentially the same as Solution 1 posted over 4 years ago.
Stick to answering new posts where the OP still needs help and make sure you are bringing something new to the thread

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2014-09-02T23:33:00

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...

SQL

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