Microsoft SQL Studio Managements logs from stored procedure

Question

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);
                }
            }
     

   }
}

Answer 1

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)"