Using .Net Class in Sql Server 2008R2

Yes, that can be easily achieved.

1 - Turn on CLR Integration (https://msdn.microsoft.com/en-us/library/ms131048.aspx)

EXEC sp_configure ‘show advanced options’ , ‘1’;
reconfigure;

EXEC sp_configure ‘clr enabled’ , ‘1’ ;
reconfigure;

EXEC sp_configure ‘show advanced options’ , ‘0’;
reconfigure;

Select allOpen in new window

2 - Create a .NET library that calls this SMS library
Follow instructions here:
http://geekswithblogs.net/frankw/archive/2008/05/03/a-quick-walk-through-of-clr-integration-with-sql-server.aspx
http://www.codeproject.com/Articles/37377/SQL-Server-CLR-Functions

I am getting  "deploy failed" when trying to deploy vs2010

When checked in output, error was

 Could not connect to server xx.xx.xx.xx Dbname : Login failed for user 'yy'.  I am not able to edit connection string. when I tried to edit and enter pwd ,pwd portion gets cleared

I copied the dll to local folder of the server and used the following command

CREATE ASSEMBLY KH_CLRAPP from 'F:\KH_CLRAPP.dll' WITH PERMISSION_SET = SAFE

it executed and am able to see the assembly under  assemblies node.  but sp is not visible in stored procedure node.

My question is

1. how to resolve vsstudio deployment error.
2. how to execute clr stored procedure in the assembly.

Which connection strong are you trying to edit? You should generally just use new SqlConnection("context connection=true") since the stored procedure runs in whatever database you are when you call it

Which stored procedure are you looking for? If you can see the assembly, then you are good to go. You can call is functions already

It is Project connection settings.  

My SP in  CLR SP is as mentioned below

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)

        ' Add your code here

        Dim ObjTranSms As ClsSms

        ObjTranSms = New ClsSms

        Try


            Dim strmsg As String = ObjTranSms.SendSMS("", "", m_mobileno1, m_smstext1)

            SqlContext.Pipe.Send(strmsg)

        Catch ex As Exception

            SqlContext.Pipe.Send(ex.Message)

        Finally

            ObjTranSms = Nothing

        End Try

    End Sub
End Class

"ClsSMS"  has method which uses SMS service provider gateway to send the sms to the recipient mobile number.

I have to loop through the  mobileno & sms text from  the table in the DB  and execute this clr sp .

Oops! I misunderstood your meaning earlier.

1. how to resolve vsstudio deployment error.
I'd imagine you are putting in the wrong username and password in the connecion dialog.

2. how to execute clr stored procedure in the assembly.
Apart from creating the assembly, you also need to create the procedure:

CREATE PROCEDURE [dbo].[SendSms]
@m_mobileno1 NVARCHAR (MAX), @m_smstext1 NVARCHAR (MAX)
AS EXTERNAL NAME [KH_CLRAPP].[KH_CLRAPP.StoredProcedures].[SendSms]

Select allOpen in new window

And then you can execute the stored procedure as normal

EXEC [dbo].[SendSms]
		@m_mobileno1 = N'123123',
		@m_smstext1 = N'Hello world'

Select allOpen in new window

Thanks for your valuable inputs.  I am able to configure the sp. now I am able to deploy from visual studio.  But when I run the sp , it is throwing the error "Unable to connect to the remote server"

For testing I have configured the clrsp in the local sql server 2008r2 instance. I tried to  step into sp. when I click on  step into sp on sql connection and enter values for the parameters ( mobile no , 'hello')  it exits the debug mode with the said error msg.  I have put break point on

  <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)

Auto-attach to process '[3140] sqlservr.exe' on machine 'khhp' succeeded.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'KH_CLRAPP'
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Unable to connect to the remote server
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[SendSms].
The thread 'khhp [55]' (0x132c) has exited with code 0 (0x0).
The thread '<No Name>' (0x132c) has exited with code 0 (0x0).
The program '[3140] [SQL] khhp: khhp' has exited with code 0 (0x0).
The program '[3140] sqlservr.exe: Managed (v2.0.50727)' has exited with code 259 (0x103).

when you use ClsSms, is there any additional configuration that you typically need to do?

Dim ObjTranSms As ClsSms
ObjTranSms = New ClsSms

Select allOpen in new window

Like setting IP addresses or hostnames, authentication and such? Out of interest, if you just created the stored procedure as this (below) does is show you the message when you execute it?:

   Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)

        ' Put your code here
        SqlContext.Pipe.Send(String.Format("Sending message to: {0} => {1}", m_mobileno1, m_smstext1))

    End Sub

Select allOpen in new window

Public Function SendSMS(ByVal User As String, ByVal password As String, ByVal Mobile_Number As String, ByVal Message As String, Optional ByVal SID As String = "SMSCntry", Optional ByVal MType As String = "N", Optional ByVal DR As String = "N") As String

        Dim stringpost As System.Object = "User=" & User & "&passwd=" & password & "&mobilenumber=" & _
                                           Mobile_Number & "&message=" & Message & "&SID=" & SID & _
                                           "&MTYPE=" & MType & "&DR=" & DR

        Dim functionReturnValue As String = Nothing
        functionReturnValue = ""

        Dim objWebRequest As HttpWebRequest = Nothing
        Dim objWebResponse As HttpWebResponse = Nothing
        Dim objStreamWriter As StreamWriter = Nothing
        Dim objStreamReader As StreamReader = Nothing

        Try
            Dim stringResult As String = Nothing

            objWebRequest = DirectCast(WebRequest.Create("http://www.xxx.com/SMSCwebservice_bulk.aspx?"), HttpWebRequest)
            objWebRequest.Method = "POST"

            If (objProxy1 IsNot Nothing) Then
                objWebRequest.Proxy = objProxy1
            End If

            ' Use below code if you want to SETUP PROXY.
            'Parameters to pass: 1. ProxyAddress 2. Port
            'You can find both the parameters in Connection settings of your internet explorer.

            'Dim myProxy As New WebProxy("YOUR PROXY", PROXPORT)
            'myProxy.BypassProxyOnLocal = True
            'wrGETURL.Proxy = myProxy

            objWebRequest.ContentType = "application/x-www-form-urlencoded"

            objStreamWriter = New StreamWriter(objWebRequest.GetRequestStream())
            objStreamWriter.Write(stringpost)
            objStreamWriter.Flush()
            objStreamWriter.Close()

            objWebResponse = DirectCast(objWebRequest.GetResponse(), HttpWebResponse)
            objStreamReader = New StreamReader(objWebResponse.GetResponseStream())

            stringResult = objStreamReader.ReadToEnd()


            objStreamReader.Close()
            Return (stringResult)
        Catch ex As Exception
            Return (ex.Message)
        Finally
            If (objStreamWriter IsNot Nothing) Then
                objStreamWriter.Close()
            End If
            If (objStreamReader IsNot Nothing) Then
                objStreamReader.Close()
            End If
            objWebRequest = Nothing
            objWebResponse = Nothing
            objProxy1 = Nothing
        End Try
    End Function


This was provided by the service provider which is perfectly working fine from vb.net application.

I am not able to debug the stored procedure from vs2010.  I am getting the following error

Auto-attach to process '[3140] [SQL] khhp' on machine 'khhp' succeeded.
The thread 'khhp [54]' (0xd34) has exited with code 0 (0x0).
The thread 'khhp [54]' (0xd34) has exited with code 0 (0x0).
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed (v2.0.50727)): Loaded 'KH_CLRAPP'
Auto-attach to process '[3140] sqlservr.exe' on machine 'khhp' succeeded.
'KHHP;.Net SqlClient Data Provider;6140' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll'
'KHHP;.Net SqlClient Data Provider;6140' (Managed (v2.0.50727)): Loaded 'C:\windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'
Unable to connect to the remote server
No rows affected.
(0 row(s) returned)
Finished running sp_executesql.
The thread 'khhp [55]' (0x2b60) has exited with code 0 (0x0).
The thread '<No Name>' (0x2b60) has exited with code 0 (0x0).
The program '[3140] [SQL] khhp: khhp' has exited with code 0 (0x0).
The program '[3140] sqlservr.exe: Managed (v2.0.50727)' has exited with code 259 (0x103).

Both Visual stutdio2010 and sql server instance are on the same machine

I haven't tried to debug these stored procedures directly from VS, so I cannot really say. However, coming back to this:

But when I run the sp , it is throwing the error "Unable to connect to the remote server"

, are you able to pin-point exactly where the error is happening? Is there more information about this error available?

    <Microsoft.SqlServer.Server.SqlProcedure()>
    Public Shared Sub SendSms(ByVal m_mobileno1 As String, m_smstext1 As String)

        Try

            ' Put your code here
            SqlContext.Pipe.Send(String.Format("Sending message to: {0} => {1}", m_mobileno1, m_smstext1))

            Dim wc As New System.Net.WebClient
            SqlContext.Pipe.Send(wc.DownloadString("http://labs.bible.org/api/?passage=random"))

            SqlContext.Pipe.Send(wc.DownloadString(m_smstext1)) 'This will probably throw an exception!

        Catch ex As Exception
            SqlContext.Pipe.Send(ex.ToString)
        End Try

    End Sub

Select allOpen in new window

So I modified the example I sent you yesterday, to see whether I could connect to an internet resource at all. And I get an error

Sending message to: 123 => 234534
System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
   at System.Net.WebClient.DownloadDataInternal(Uri address, WebRequest& request)
   at System.Net.WebClient.DownloadString(Uri address)
   at SQLServerDatabase1.StoredProcedures.SendSms(String m_mobileno1, String m_smstext1)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Net.WebPermission
The Zone of the assembly that failed was:
MyComputer

(1 row(s) affected)

Select allOpen in new window

Thanks a lot for your valuable inputs. It is working fine. Just one more thing. i am not able to run the solution in debug mode. I googled a bit but could not find proper answer.  can you help me out on this.

or i need to raise a separate question

Oh... The running in debug... I haven't really done that before. I rely on logging. Perhaps google,... Or another question

Can  you explain a bit on "Logging" and how it can substitute  running in debug mode

Like I did there, where I used a try...catch block, and wrote the error message back. That gave me enough information to determine where the error was occurring and led me to fixing it. That is an example of very basic logging of exceptions. You could write those messages to a text file for inspection later, or stuff like that.

There is one more issue . Yesterday i tried with datacard . it worked.  

But when I tried in my office n/w (lan) or mobile 3g ( wifi hotspot) , i am getting the same error

"Unable to connect to the remote server"

What might be the issue

Sorry for closing it late.