Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pushing Data from T-SQL to Visual Studio While Debugging Expand / Collapse
Author
Message
Posted Sunday, April 7, 2013 2:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 10:36 PM
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
Post #1439629
Posted Monday, April 8, 2013 5:12 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:41 AM
Points: 616, Visits: 3,581
what sort of text/information were you hoping to return from the Stored Procedure?

would it just be a Flag to say Pass/Fail?
Post #1439751
Posted Monday, April 8, 2013 7:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:38 PM
Points: 1,816, Visits: 5,914
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1439828
    Posted Monday, April 8, 2013 10:25 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 9:31 AM
    Points: 12,962, Visits: 32,501
    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

    --There is no spoon, and there's no default ORDER BY in sql server either.
    Actually, Common Sense is so rare, it should be considered a Superpower. --my son
    Post #1439935
    Posted Monday, April 8, 2013 11:26 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:38 PM
    Points: 1,816, Visits: 5,914
    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1439954
    Posted Monday, April 8, 2013 11:36 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 9:31 AM
    Points: 12,962, Visits: 32,501
    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

    --There is no spoon, and there's no default ORDER BY in sql server either.
    Actually, Common Sense is so rare, it should be considered a Superpower. --my son
    Post #1439957
    Posted Thursday, April 11, 2013 10:43 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, April 11, 2013 10:36 PM
    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!

    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
    Post #1441570
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse