SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pushing Data from T-SQL to Visual Studio While Debugging


Pushing Data from T-SQL to Visual Studio While Debugging

Author
Message
matt_1ca
matt_1ca
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
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
davidandrews13
davidandrews13
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1875 Visits: 4638
what sort of text/information were you hoping to return from the Stored Procedure?

would it just be a Flag to say Pass/Fail?
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10303 Visits: 7891
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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Lowell
    Lowell
    SSC Guru
    SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

    Group: General Forum Members
    Points: 69720 Visits: 40917
    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.

    SqlHelper.vb.txt

    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
    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10303 Visits: 7891
    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.

    SqlHelper.vb.txt



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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Lowell
    Lowell
    SSC Guru
    SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

    Group: General Forum Members
    Points: 69720 Visits: 40917
    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.

    SqlHelper.vb.txt



    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
    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
    matt_1ca
    matt_1ca
    Forum Newbie
    Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

    Group: General Forum Members
    Points: 2 Visits: 2
    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
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search