Sending SMS from SQL Server 2014 Issu Identification

Question

I am using this following code to send sms from SQL Server 2014. It used to work properly but for few days it is not working. I also executed the following code in another SQL Server and it is working perfectly. But for some reason not working in SQL Server 2014. I also try ed sending trough using url in an browser and it also works. but some how i does not work via stored procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--EXEC sp_SendSmsSQL '*****','Today is Saturday',''

ALTER procedure [dbo].[sp_SendSmsSQL] 
    @MobileNo varchar(max), 
    @smstext as varchar(300), 
    @sResponse varchar(8000) OUT 
as 
BEGIN 
   DECLARE @iReq int,@hr int 
   DECLARE @sUrl as varchar(500) 
   DECLARE @errorSource VARCHAR(8000)
   DECLARE @errorDescription VARCHAR(8000) 

   -- Create Object for XMLHTTP 
   EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT

   --EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHTTP', @iReq OUT   
   print '*'
   print 'hr : ' + cast(@hr as varchar)

   if @hr <> 0 
    Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)

    set @sUrl='****'
    set @sUrl=REPLACE(@sUrl,'#MobNo#',@MobileNo) 
    set @sUrl=REPLACE(@sUrl,'#Msg#',@smstext) 

    print @sUrl 


   -- sms code start 
   EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl, true 
   print '**'
   print @hr 

   if @hr <> 0 
      Raiserror('sp_OAMethod Open FAILED!', 16, 1) 

   EXEC @hr = sp_OAMethod @iReq, 'Send' 
   select @iReq
   print '***'
   print 'hr : ' + cast(@hr as varchar)

   if @hr <> 0 
   Begin 
       EXEC sp_OAGetErrorInfo @iReq, @errorSource OUTPUT, @errorDescription OUTPUT

       SELECT [Error Source] = @errorSource, [Description] = @errorDescription

       Raiserror('sp_OAMethod Send FAILED!', 16, 1) 
   end 
    else 
    Begin
        EXEC @hr = sp_OAGetProperty @iReq,'responseText', @sResponse OUT 

        print @hr
        print '****'
        print @sresponse


    end

    EXEC @hr=sp_OADestroy @iReq
    print @hr 

    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
END

Following the results

    *
hr : 0
url ='******'
**
0

(1 row(s) affected)
***
hr : 0
-2147483638
****

0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Mar 18 '17 at 09:10

score 1 · Accepted Answer · edited May 23 '17 at 12:17

You didn't supply the error message returned by SQL server, but this sure sounds like a security issue. DBCC FREEPROCCACHE requires Alter server state permission. Additionally the sp_oa... methods need to be enabled and the users must have access to run them (SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'). Lastly, I'm not sure why you are running the DBCC commands in the stored procedure. FREEPROCCACHE has limitations but you can call it with options (https://msdn.microsoft.com/en-us/library/ms174283.aspx). DROPCLEANBUFFERS is really meant to test query performance. If your DBCC commands are there just for testing, remove them from the stored procedure and put them right after the call to the stored procedure.

Sending SMS from SQL Server 2014 Issu Identification

1 Answers1