Test for MsgBox available

Question

Hi there,

I want to check if I can use MsgBox during testing in a sqlproject in VS2008R2.

I know I can test for "Microsoft.SqlServer.Server.SqlContext.IsAvailable = False"  

But is there something like "Microsoft.Windows.Gui.IsAvailable = True" ?

so I can use MsgBox ?

Suggestions are appreciated

Jos 

---

I'll be back

Answer 1

Jos,

Could you tell us in which context do you want to use MsgBox? Is it a Unit Test in database project?

MsgBox is available in VB testing project, database Unit Test code behind.

Please let me know if you get progress, I'll try my best to assistant.

---

Forrest Guo | MSDN Community Support | Feedback to us

Answer 2

Hi Forrest,

I have an sqlserver project that contains coding for sql clr functions and procedures. If a function or procedure wants to raise an error condition I use a central error routine FoutMsg that logs the error and here is the crux displays the error using MsgBox. As MsgBox cannot be used in a clr, this part now is suppressed by using the directive #If CONFIG = "CLR_DEBUG" Then ... #Else MsgBox(MessageString) #End If, where "CLR_Debug" is a configuration setting. However all other error handling is also suppressed, which is not desired.

	<Server.SqlFunction(DataAccess:=Server.DataAccessKind.Read, SystemDataAccess:=Server.SystemDataAccessKind.Read)> _
	 Public Shared Function FoutMsg(ByVal ProcId As Integer, ByVal MsgText As String, Optional ByVal Keys As Integer = 0, Optional ByVal Batch As Boolean = False) As Integer
		'----------------------------------------
		'	Tonen en/of schrijven FoutMelding	'
		'----------------------------------------
		Dim Mb As System.Reflection.MethodBase = Nothing
		Dim ErrText As String = ""
		Dim TltText As String = ""
		Dim BoxText As String = ""
		Dim Caller As String = ""
		Dim UserId As String = ""
		Dim CnnStr As String = ""
		Dim RetVal As Integer = 0
		Dim DateTimeStr As String = ""

		'   Als Err gevuld: voor MsgText zetten
		If Err.Description = "" And Err.Number = 0 And Err.Erl = 0 Then
		Else
			ErrText = Err.Description
			If Err.Number <> 0 Then
				ErrText += vbCrLf & "Error: " & CStr(Err.Number)
			End If
			If Err.Source <> "" Then
				ErrText += " Uit: " & Err.Source
			End If
			If Err.Erl <> 0 Then
				ErrText += " Op: " & CStr(Err.Erl)
			End If
			If Not IsNothing(Err.GetException) Then
				ErrText += vbCrLf & "Stack: " & Err.GetException().StackTrace
				If Not IsNothing(Err.GetException().InnerException) Then
					ErrText += vbCrLf & "InnerExeption: " & Err.GetException().InnerException.Message
					ErrText += vbCrLf & "InnerStack: " & Err.GetException().InnerException.StackTrace
				End If
			End If
		End If

		'	Opmaken Header Melding
		Caller = Baanstede.GetCaller()
		UserId = Baanstede.GetUserId()
		CnnStr = Baanstede.GetCnnStr()
		DateTimeStr = Baanstede.GetDateTime()
		TltText = "Melding uit: " & Baanstede.AP(Caller) & " (" & UserId & ", " & DateTimeStr & ") "
#If CONFIG = "Debug" Then
		MsgText = TltText & vbCrLf & "ConnectString=" & CnnStr & vbCrLf & vbCrLf & MsgText
#End If

		'	Opmaken Body Melding
		If ErrText <> "" Then
			BoxText += vbCrLf & ErrText
		End If
		If MsgText <> "" Then
			BoxText += vbCrLf & MsgText
		End If
		If Keys = 0 Then
			Keys = MsgBoxStyle.Critical
		End If

		'#If CONFIG = "CLR_Debug" Or CONFIG = "CLR_Release" Then
		'		If SqlContext.IsAvailable Then
		'			SqlContext.Pipe.Send(MsgText & " " & ErrText & " " & TltText)
		'		End If
		'#End If
		'	Schrijf naar tblFoutMsg
		Dim Cnn As SqlClient.SqlConnection = New SqlClient.SqlConnection(CnnStr)
		Cnn.Open()
		Dim Sql As String = "EXEC	dbo.WriteFoutMsg	@Msg, @Err, @Mld, @Keys, @User, @Date, @ProcId"
		Dim Cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(Sql, Cnn)
		Cmd.Parameters.AddWithValue("@Msg", MsgText)
		Cmd.Parameters.AddWithValue("@Err", ErrText)
		Cmd.Parameters.AddWithValue("@Mld", TltText)
		Cmd.Parameters.AddWithValue("@Keys", Keys)
		Cmd.Parameters.AddWithValue("@User", UserId)
		Cmd.Parameters.AddWithValue("@Date", Now)
		Cmd.Parameters.AddWithValue("@ProcId", ProcId)
		Dim Rc As Object = Cmd.ExecuteNonQuery
		Cmd = Nothing
		Cnn.Close()
		Cnn = Nothing
		'#End If

		'	Waarschuwing (Retval=0) of Error (RetVal=1)
		If (Keys And MsgBoxStyle.Critical) = MsgBoxStyle.Critical Then
			RetVal = 1
		Else
			RetVal = 0
		End If

		'	Bij Batch: Exit
		If Batch Then
			Return RetVal
		End If

#If CONFIG = "CLR_Debug" Or CONFIG = "CLR_Release" Then
		'		If WindowsIdentity.GetCurrent. Then
#Else
		If Microsoft.SqlServer.Server.SqlContext.IsAvailable = False Then
			'	Force to ForeGround
			Keys = CInt(Keys) + MsgBoxStyle.MsgBoxSetForeground
			'-----------------------------------'
			'	Display Message mbv MsgBox		'
			'-----------------------------------'
			<strong>RetVal = MsgBox(BoxText, CType(Keys, MsgBoxStyle), TltText)
</strong>		End If
#End If

		'	RetVal = Response op MsgBox of anders 0=Goed, 1=Fout
		Return RetVal

	End Function

 

---

I'll be back

Answer 3

Hi Forrest,

So I want to display the MsgBox whenever possible. How can I determine when I can use MsgBox?

Suggestions are greatly appreciated.

Regards, Jos

---

I'll be back

Answer 4

Hi Jos,

I try to get other experienced people on this question earlier today, so my collegue will help you.  Sorry for inconvenience.

Best Regards,

---

Forrest Guo | MSDN Community Support | Feedback to us

Answer 5

If you mean in a code implementation like a .Net trigger / procedure etc. then this is not possible as the System.Windows namespace is by default not included and not recommended (as not being trusted) to be included as a reference in SQLCLR. Maybe the you are can use System.Diagnostics using a Trace output which you would be able to capture with the diagnostics tools like sysinternals...

---

Cheeeeerrzzzz!
S@kthi
http://blogs.msdn.com/sqlsakthi | http://twitter.com/PowerSakthi

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.

Answer 6

Oké, let me put the question different.

I need to debug my SqlServer vb Project that contains .dll's to be used in a Report Manager Report. If an error condition occurs, both from within a TRY CATCH block or if some other programmatic error occurs,
How do I get these messages displayed whilest debugging and whilest running the Report?

This is what I have done so far:

	<Server.SqlProcedure()> _
	 Public Shared Function DtsPrsVariant2Table(ByVal ProcId As Integer, ByVal VarName As String, ByVal VarParms As String) As System.Int32
		Dim RetVal As Boolean = False
		'	Indien mogelijk: ImpersonateUser
		Dim CurrentIdentity As System.Security.Principal.WindowsIdentity = Nothing
		Dim ImpersonatedUser As System.Security.Principal.WindowsImpersonationContext = Nothing
		If Microsoft.SqlServer.Server.SqlContext.IsAvailable Then
			If Not IsNothing(Microsoft.SqlServer.Server.SqlContext.WindowsIdentity) Then
				CurrentIdentity = Microsoft.SqlServer.Server.SqlContext.WindowsIdentity
				ImpersonatedUser = CurrentIdentity.Impersonate
			End If
		End If

		Try
			'	Haal Variant Velden op en zet in LsTblPrs en Zet VarParms in VarSet
			If Not GetVariantVelden(ProcId, VarName, VarParms, LsTblPrs, VarSet) Then
				RetVal = False
				GoTo End_Function
			End If
....
....

			RetVal = True
End_Function:
		Catch ex As Exception
			Bst.FoutMsg(ProcId, "Fout")
		Finally
			If Microsoft.SqlServer.Server.SqlContext.IsAvailable Then
				If Not IsNothing(ImpersonatedUser) Then
					ImpersonatedUser.Undo()
				End If
			End If
		End Try
		Return CType(RetVal, System.Int32)
	End Function

In the dll in every function or procedure there is a TRY/CATCH block with wihtin the CATCH:

Use							Baanstede_Test
GO
ALTER						Procedure						DtsPeriodiekeVerhoging
(
							@ProcId						As	Int
		,					@VarParms					As	NVarChar(Max)		=	''
)
AS
BEGIN
	Declare					@VarName					As	NVarChar(Max)
	Set						@VarName					=	'PeriodiekeVerhoging'
	Declare					@Return						As	Int
	Set						@Return						=	0
	
	--	Maak tblDtsPeriodiekeVerhoging adv @VarName mbv DtsPrsVariant2Table
	Exec					@Return						=	dbo.DtsPrsVariant2Table			
															@ProcId
														,	@VarName
														,	@VarParms
	
	--	Check of er fouten waren
	Exec					@Return						=	dbo.GetFoutMsg
															@ProcId
	If						@Return						<>	0
		Return				@Return
		
	--	Pass tblDtsPeriodiekeVerhoging
	Select					*
	From					tblDtsPeriodiekeVerhoging
	
	Return					@Return
END
GO
--	Test
Declare						@ProcId						As	Int
Set							@ProcId						=	dbo.GetProcId()
Declare						@Return						As	Int
Set							@Return						=	0
Declare						@VarParms					As	VarChar(Max)
Exec						@Return						=	dbo.DtsPeriodiekeVerhoging
															@ProcId
														,	@VarParms

					

USE						[Baanstede_Test]
GO
ALTER					PROCEDURE								[dbo].[GetFoutMsg]
--=============================================================================
--			Datum		30 mrt 2011
--			Auteur		Jos vd Vlis
--			Doel		Ophalen FoutMelding uit tblFoutMsg adv @ProcId
--=============================================================================
(
						@ProcId								As	Int					=	0
)
AS
BEGIN

	Declare				@UserId								As	VarChar(Max)
			,			@Msg								As	VarChar(Max)
			,			@Err								As	VarChar(Max)
			,			@Mld								As	VarChar(Max)
			,			@Keys								As	Int
			,			@Proc								As	Int
			,			@Text								As	VarChar(Max)
			,			@DateTime							As	DateTime
			,			@Severity							As	Int
			,			@State								As	Int
			,			@MsgBoxStyle_Critical				As	Int
			,			@MsgTot								As	VarChar(Max)
			,			@ErrTot								As	VarChar(Max)
			,			@MldTot								As	VarChar(Max)
			,			@LinDel								As	VarChar(Max)
			
	Set					@MsgBoxStyle_Critical				=	16
	Set					@MsgTot								=	''
	Set					@ErrTot								=	''
	Set					@MldTot								=	''
	Set					@LinDel								=	CHAR(10)
	
	--	tblFoutMsg
	Declare				FoutMsg			Cursor				For
	Select				[DateTime]
				,		UserId
				,		Msg	
				,		Err
				,		Mld
				,		Keys
				,		ProcId
	From				dbo.tblFoutMsg
	Where				ProcId								=	@ProcId
		
	Open				FoutMsg
	Fetch				Next	
	From				FoutMsg
	Into				@DateTime							
				,		@UserId								
				,		@Msg								
				,		@Err								
				,		@Mld								
				,		@Keys								
				,		@Proc								
	
	While				@@FETCH_STATUS						=	0
	Begin
		Set				@MsgTot								=	dbo.ApStr(@MsgTot,	@LinDel,	@Msg)
		Set				@ErrTot								=	dbo.ApStr(@ErrTot,	@LinDel,	@Err)
		Set				@MldTot								=	dbo.ApStr(@MldTot,	@LinDel,	@Mld)
		Fetch			Next	
		From			FoutMsg
		Into			@DateTime							
				,		@UserId								
				,		@Msg								
				,		@Err								
				,		@Mld								
				,		@Keys								
				,		@Proc								
	End
	Close				FoutMsg
	Deallocate			FoutMsg
	
	If					IsNull(@Proc, 0)					=	@ProcId
	Begin
		If				ISNULL(@Mld, '')					=	''
			Set			@Mld								=	'Melding: '		
		Set				@Text								=	@Mld
															+	'  ('
															+	dbo.EditDagDatum(@DateTime)
															+	' '
															+	dbo.EditTijd(@DateTime)
															+	', '
															+	@UserId
															+	')'
		If				IsNull(@ErrTot, '')					<>	''
			Set			@Text								=	@Text	
						+		@LinDel						+	@ErrTot
		If				IsNull(@MsgTot, '')					<>	''
			Set			@Text								=	@Text	
						+		@LinDel						+	@MsgTot

		Set				@State								=	10

		If				@Keys								>	0
			And			(@Keys	%  @MsgBoxStyle_Critical)	=	0
			Set			@Severity							=	16
		Else
			Set			@Severity							=	1

		RaisError(		@Text, @Severity, @State	)
		
		If				@Severity							>	10
			Return		1
	End
	Return				0
END

---

I'll be back

Answer 7

For debugging purposes you can consider attaching your Visual Studio process to the sqlservr.exe process and set the code to “Break on exceptions” in VS. If you do not have access to the sqlserver process from the dev machine you can also trace the error message using System.Diagnostics.Trace and display the output in dbgview which is available from systinternals.

---

Cheeeeerrzzzz!
S@kthi
http://blogs.msdn.com/sqlsakthi | http://twitter.com/PowerSakthi

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.

Answer 8

Hi Sakthivel,

What I am asking is simple in nature, I want a general routine for handling messages. I forgot to mention the MsgBox function can also be used for user replies like YES/NO/CANCEL, ABORT/RETRY etc. Sometimes I use this functionally to ask if proccessing should be continued or not.

The sqlserver project is part of a solution that also contains a Windows Form project that I use to edit parameters for the sqlserver project routines, for example I want sometimes to select one or more departments to be used within the CLR routine.

Also the solution contains logic for taking the test version of the reports and CLR's etc to production. In all these cases I use the same FoutMsg routine to report errors or ask whether or not to continue proccessing. If this routine does  not know if it can use MsgBox in the currrent environment than the problem is:

- It assumes MsgBox can be used: a runtime error occurs when invoked from the database assembly.

- It assumes MsgBox cannot be used: no MsgBox appaeres when a question must be answered, for example in the test -> production part of the solution.

Hope I made it a little clearer why I like to know when the MsbBox can be used or not.

---

I'll be back

Answer 9

From your description you have a class which does the checks and another one which will display the errors. In those cases I would do  the following. Implement the class for checking. Implement a delegate and event to have people subscribe to the event from outside. Having this, you can through the message at the delegate no matter if someone is subscribed to it or not. Depending on the implementation SQLCLR /WIN / CONS you can then handle the event as you want. A simple implementation of this would be the following

Logic class:

public delegate void TraceMessage(string Message, MessagePriority priority);

public event TraceMessage TraceMessageEvent;

Calls would be made with:

TraceMessageEvent(string.Format("SQLCmdVar '{0}' found with value '{1}' in variable assignment which was already found with value '{2}' and will be skipped.", SqlCmdVar, SqlCmdVarValue,CommandVars[SqlCmdVar]), MessagePriority.Informational);

And the subscription would be done from the outside class with:

worker.TraceMessageEvent +=new SQLCMDScriptExpanderWorker.TraceMessage(WriteMessage);

and the implementation to write the messages could be something like:

    staticvoid WriteMessage(stringMessage, SQLCMDScriptExpanderWorker.MessagePrioritypriority)

       {

switch(priority)

{

    caseSQLCMDScriptExpanderWorker.MessagePriority.Error:

    caseSQLCMDScriptExpanderWorker.MessagePriority.Critical:

    ConsoleColour.SetForeGroundColour(ConsoleColour.ForeGroundColour.Red);                  
   

    Console.WriteLine(Message);

break;

caseSQLCMDScriptExpanderWorker.MessagePriority.Informational:

caseSQLCMDScriptExpanderWorker.MessagePriority.Verbose:

ConsoleColour.SetForeGroundColour(ConsoleColour.ForeGroundColour.Grey);

Console.WriteLine(Message);

break;

caseSQLCMDScriptExpanderWorker.MessagePriority.Success:

ConsoleColour.SetForeGroundColour(ConsoleColour.ForeGroundColour.Green);

Console.WriteLine(Message);

break;

default:

break;

}

}

(in that case for a Console Application)

---

Cheeeeerrzzzz!
S@kthi
http://blogs.msdn.com/sqlsakthi | http://twitter.com/PowerSakthi

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.