April 7, 2013 at 2:52 am
I am debugging my T-SQL code in Visual Studio ... is it possible for me to write something in my Transact-SQL code and have it pushed in either the Output Window or Immediate Window of Visual Studio... if yes how do I do it?
If I were writing a VB code for example it is simple .. I can just write debug.print "text write" ... and boom the text I want to end up in the Immediate Window gets there ... so how about in T-SQL being debugged in VS, how is it done?
I have spent hours in books and google without getting any closer to the answer I am seeking and I am hoping people out there who have more experience would shed light and give me some enlightenment on this.
This technique if possible would help me a lot in my debugging work. Thanks so much for all the help.
Matt
April 8, 2013 at 5:12 am
what sort of text/information were you hoping to return from the Stored Procedure?
would it just be a Flag to say Pass/Fail?
April 8, 2013 at 7:39 am
You need to hook up the InfoMessage Event on the SqlConnection, and set the property FireInfoMessageEventOnUserErrors on the SqlConnection to True.
Now you can use PRINT and RAISERROR in your SQL and it will be fired into the InfoMessage event handler.
You should note that just like in SSMS, PRINT messages are queued up until the batch completes OR the next error occurs, so it is best to use RAISERROR ... WITH NOWAIT if you want instant notifications to your event handler.
If all you want is a log of PRINT messages and are happy to get them at completion of the batch, then PRINT is fine.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 8, 2013 at 10:25 am
I've done this a few times;
here's a link to a slightly modified version of Microsofts Microsoft.ApplicationBlocks.Data SQLHelper.cs class, which i ran thru a converter, and then enhanced.
the usage is something like the below code example: note there are some undeclared variables here that are not really needed for you to modify the example.
also take a peek at this region for my modifications:
#Region "Non-MS Additions For Ease Of Use"
and finally note it has two collections, one for ErrorMessages and Another for InfoMessages (which are your print statements, but also 4 row(s) affected and other dbcc messages.
let me know if this helps you at all!
Dim myConnectionString As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Application Name=SSCExample.YourApplicationName.exe;"
myConnectionString = String.Format(SqlConnectionFormat, "YourServer", "databasename", "SomeUserName", "NotARealPassword")
Dim sql As String = ""
sql = "INSERT INTO EDIHistory(AgencyAlias,HDSVersion,ReferenceNbr,RecordType,RecordMode,HUDSTATUS,RecordDate,CPSNBR,CPSYEAR,PRJNBR,PRJNAME,IDISNUMBER,ACTNBR,ACTNAME,EFILENAME)" & vbCrLf
sql = sql & "SELECT @AgencyAlias,@HDSVersion,@ReferenceNbr,@RecordType,@RecordMode,@HUDSTATUS,@RecordDate,@CPSNBR,@CPSYEAR,@PRJNBR,@PRJNAME,@IDISNUMBER,@ACTNBR,@ACTNAME,@EFILENAME " & vbCrLf
SqlHelper.InfoMessages.Clear()
SqlHelper.ExecuteNonQuery(myConnectionString, CommandType.Text, sql, pAgencyAlias, pHDSVersion, pReferenceNbr, pRecordType, pRecordMode, pHUDSTATUS, pRecordDate, pCPSNBR, pCPSYEAR, pPRJNBR, pPRJNAME, pIDISNUMBER, pACTNBR, pACTNAME, pEFILENAME)
If SqlHelper.InfoMessages.Count > 0 Then
Dim s As String = String.Empty
For x As Integer = 0 To SqlHelper.InfoMessages.Count - 1
s = s & SqlHelper.InfoMessages(x) & vbCrLf
Next
MsgBox(s)
End If
Lowell
April 8, 2013 at 11:26 am
Lowell (4/8/2013)
I've done this a few times;here's a link to a slightly modified version of Microsofts Microsoft.ApplicationBlocks.Data SQLHelper.cs class, which i ran thru a converter, and then enhanced.
Lowell, that code references a "frmErrors" object, which might be a bit of a hiccup for people - do you have a version without it?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 8, 2013 at 11:36 am
mister.magoo (4/8/2013)
Lowell (4/8/2013)
I've done this a few times;here's a link to a slightly modified version of Microsofts Microsoft.ApplicationBlocks.Data SQLHelper.cs class, which i ran thru a converter, and then enhanced.
Lowell, that code references a "frmErrors" object, which might be a bit of a hiccup for people - do you have a version without it?
ahh darn i forgot about that; thank you for finding that, MM!
the code as posted would be handy for me, not so handy for folks without my whole source code.
I've modified the code i posted to just throw up a messagebox instead.
the code snippet was changed to this instead:
Public Shared Function ShowErrors()
If SqlHelper.ErrorMessages.Count > 0 Then
'This example below passed both error collectiosn to a form i had made; code below modified to simply throw up a MessageBox:
'frmErrors.ShowMe(Nothing, SqlHelper.ErrorMessages, SqlHelper.InfoMessages)
Dim results As String = "ErrorMessages: " & vbCrLf
For x As Integer = 0 To SqlHelper.ErrorMessages.Count - 1
results = results & SqlHelper.ErrorMessages(x) & vbCrLf
Next
results = results & "InfoMessages: " & vbCrLf
For x As Integer = 0 To SqlHelper.InfoMessages.Count - 1
results = results & SqlHelper.InfoMessages(x) & vbCrLf
Next
MsgBox(results)
SqlHelper.ErrorMessages.Clear()
SqlHelper.InfoMessages.Clear()
End If
End Function
Public Shared Function ShowErrors(ByVal KeepMessages As Boolean)
If SqlHelper.ErrorMessages.Count > 0 Then
'This example below passed both error collectiosn to a form i had made; code below modified to simply throw up a MessageBox:
'frmErrors.ShowMe(Nothing, SqlHelper.ErrorMessages, SqlHelper.InfoMessages)
Dim results As String = "ErrorMessages: " & vbCrLf
For x As Integer = 0 To SqlHelper.ErrorMessages.Count - 1
results = results & SqlHelper.ErrorMessages(x) & vbCrLf
Next
results = results & "InfoMessages: " & vbCrLf
For x As Integer = 0 To SqlHelper.InfoMessages.Count - 1
results = results & SqlHelper.InfoMessages(x) & vbCrLf
Next
MsgBox(results)
If Not KeepMessages Then
SqlHelper.ErrorMessages.Clear()
SqlHelper.InfoMessages.Clear()
End If
End If
End Function
Lowell
April 11, 2013 at 10:43 pm
Thank you all for the great response..
It's my first time to post a thread here and I can already see ... this place rocks!:cool:
I think I have discovered a way to do it: By simply doing something like (Select 'My text here') at
critical points of my code (including inside event handler catch) then it ends up automatically
where I want to see it so that I can examine the program flow..
Thanks again for all the help I really appreciate it.
Matt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply