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