If my stored procedure has a print statement inside it:
print 'message'
Is there a way to fetch the output in java program that connects to SQL Server 2008 through JDBC?
Also, is there a danger that print messages left for debugging would shutdown connection when called from JDBC application?
You can't do it. Your options are to use an output parameter from your stored procedure or to do
SELECT 'message'
which will give you a ResultSet you can read in the Java.
this article shows how to do that in VB.NET
I am sure you can do the same in your java code.
All you need to do is attach a handler function to SqlInfoMessageEvent
VB.NET code specified in the above article does not work properly some how on my studio 2005 environment.
So I re wrote it as below
Imports System.Data.SqlClient
Module Module1
Public Sub Main()
'change your database name in following line.
Dim conn As New SqlConnection("server=(local);Integrated Security=SSPI;database=Test")
AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
conn.Open()
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[SPWithPrint]"
cmd.ExecuteNonQuery()
conn.Close()
' Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
Console.WriteLine("The {0} has received a severity {1}, state {2} error number {3}\n" & _
"on line {4} of procedure {5} on server {6}:\n{7}", _
err.Source, err.Class, err.State, err.Number, err.LineNumber, _
err.Procedure, err.Server, err.Message)
Next
End Sub
End Module
Other usefull links are as below.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.infomessage.aspx