assemblywebsocketssms

Microsoft SQL Studio Managements logs from stored procedure


Because my SSMS assembly procedure stopped working, I need to add some logs. But, because I'm quite new, I have big problem how to do it, and where I could see this logs.

As you see above in my class StoredProcedures I run class WSClient, which should send some data to websocket server. And I wanted to add here some console.log, and check if it is running. But I don't know how.

My procedure looks:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using System.Net.WebSockets;
    using System.Text;
    using System.Threading;
    using System.Threading.Tasks;
    
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void HelloWorld(String value)
        {
    
            Microsoft.SqlServer.Server.SqlMetaData columnInfo
    = new Microsoft.SqlServer.Server.SqlMetaData("VALUE", SqlDbType.NVarChar, Max);
            SqlDataRecord greetingRecord
                = new SqlDataRecord(new Microsoft.SqlServer.Server.SqlMetaData[] { columnInfo });

           //logs is working ok, displays what I need, and this is ok

            greetingRecord.SetString(0, value);
            SqlContext.Pipe.Send(greetingRecord);
    
            var ws = new WSClient();

            // how to inspect WSClient and where I could see it????
            WSClient.runWSClient(value).GetAwaiter().GetResult();
        }
    }
    
    public class WSClient
    {
        public static async Task runWSClient(String value)
        {
    
    
            using (ClientWebSocket client = new ClientWebSocket())
            {
                Uri servicesUri = new Uri("wss://websocket.kawczynski.eu");
                var cTs = new CancellationTokenSource();
                cTs.CancelAfter(TimeSpan.FromSeconds(120));
                try
                {
                    await client.ConnectAsync(servicesUri, cTs.Token);
    
                    if (client.State == WebSocketState.Open)
                    {
                        string message = value;
                        //SqlContext.Pipe.Send("ws client got message: " + message);
                        if (!string.IsNullOrEmpty(message))
                        {
                            ArraySegment<byte> byteToSend = new ArraySegment<byte>(Encoding.UTF8.GetBytes(message));
                            await client.SendAsync(byteToSend, WebSocketMessageType.Text, true, cTs.Token);
    
                        }
                    }
                }
                catch (WebSocketException e)
                {
                    //SqlContext.Pipe.Send("error from hello world assembly: " + e.Message);
                    Console.WriteLine(e.Message);
                }
            }
     

   }
}

Solution

  • There are different approaches you can have to a similar problem,

    what I would try could be, for example, to write a correct structure in the stored procedure, so you can easy catch the problem, before it arrives to your code.

    For example you can structure your stored procedure in this way:

    CREATE PROCEDURE [Report].[PpX_TestStored]
    
    AS  
    --*/  
    BEGIN
    
    SET NOCOUNT ON
    --
    --
    BEGIN TRY
    
    ---------------------------------------------------------
        --Variable Declaration
        ---------------------------------------------------------------------------- 
     --------
        DECLARE @ProcedureName              varchar(200) = "Name"
    --------------------------------------------
    
        ---------------------------------------------------------------------------- 
        --------
    
        ------------------------------------------------------------------------------------
       SELECT 1 AS returnvalue  -- PUT HERE your working code
    
    
    
    
        ------------------------------------------------------------------------------------
    
    
    
        ------------------------------------------------------------------------------------
        --
        --
    END TRY
    BEGIN CATCH
        ------------------------------------------------------------------------------------------------  
        ------------------------------------------------------------------------------------------------  
        --
        --
        ------------------------------------------------------------------------------------------------  
        --Error Handling
        ------------------------------------------------------------------------------------------------  
    
        DECLARE @ERROR_MESSAGE varchar(max)=ERROR_MESSAGE(),@ERROR_LINE int=ERROR_LINE(),@ERROR_PROCEDURE varchar(max)=ERROR_PROCEDURE(),@ERROR_DETAILS varchar(max)=ERROR_MESSAGE()
        --
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
    
        SELECT
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    
    
            RAISERROR (@ERROR_MESSAGE, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
    
    END CATCH;
    END
    

    with this code the error will be raised till the "catch block" of your own caller code.

    You can moreover put a little statement that insert the error in in a table of your database in this "catch block", so the error is saved,

    something like a simple "INSERT INTO dbo.Log values (errorMsg) (@ERROR_MESSAGE)"