|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
|
||||
|
||||
|
SqlConnection InfoMessage event
So I have a very simple stored procedure that uses RAISERROR() to push messages to client. I have a small program that calls the procedure, and receives the messages in the connection's InfoMessage event and writes them to the console. Unfortunately, for some reason it seems like the messages are still being buffered. I won't see any output for a while, and then all of a sudden I'll get a bunch of stuff at once. I should get the event to fire after every RAISERROR. I think it should be possible, because sql server management studio will do it.
The code below isn't my real code, just a sample I cooked up to demonstrate my problem. If you can make the sample work, I can apply the fix to the real code. Code:
stored procedure
CREATE PROCEDURE MessagePumpTest AS BEGIN -- Simplified procedure that I can use to test message pumping without putting a load on the database print '==================================================' print 'Starting test procedure: ' + convert(varchar(23), getdate(), 121) RAISERROR( '',0,1) WITH NOWAIT DECLARE @I int Set @I=1 WHILE @I <= 50 BEGIN print '------------------------------------------' Print 'Starting pass ' + cast(@I as char(2)) + ': ' + convert(varchar(23), getdate(), 121) RAISERROR( '',0,1) WITH NOWAIT WAITFOR DELAY '00:00:10' -- simulate 10 seconds of work, but don't really do anything print 'Finished pass ' + cast(@I as char(2))+ ': ' + convert(varchar(23), getdate(), 121) print '' Set @I = @I + 1 END print 'All passes finished.' print '' END Code:
.Net Console App
Imports System.Data.SqlClient
Module Module1
Const ConnectionString As String = "#################"
Const ProcedureName As String = "MessagePumpTest"
Sub Main()
Try
Using conn As New SqlConnection(ConnectionString), _
cmd As New SqlCommand(ProcedureName, conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandTimeout = 0
AddHandler conn.InfoMessage, AddressOf GetMessage
conn.FireInfoMessageEventOnUserErrors = True
conn.Open()
cmd.ExecuteNonQuery()
End Using
Catch ex As Exception
Console.WriteLine("ERROR running " & ProcedureName)
Console.WriteLine("Message: " & ex.Message)
Console.WriteLine("Source: " & ex.Source)
End Try
End Sub
Sub GetMessage(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
Console.WriteLine(e.Message)
End Sub
End Module
__________________
Primary Forums: .Net Development, MS-SQL, C Programming VB.Net: It's not your father's Visual Basic. [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers] Last edited by f'lar : May 2nd, 2008 at 01:36 PM. |
|
#2
|
||||
|
||||
|
Without reading the code, your description sounds a lot like how notifications work. I'm only familiar with notifications in PostgreSQL, but I remember that Npgsql (ADO.NET Data Provider for PostgreSQL) - under normal connection settings - will only get new notifications the next time a query is executed.
So if a notification occurs, another connection listening for that notification won't actually receive it until it executes another statement. Npgsql had a way around this by adding an option for synchronizing notifications. All they had to do to synchronize was to continuously poll PostgreSQL. Originally, they did this by repeatedly executing blank statements, but they changed that some time ago and now just poll the socket. Anyway, when you say that you won't get messages, and then you'll get several at once, that's what made me think of the above. It could be something completely different, though. Edit: Found this. It basically warns about using RPC or calling ExecuteNonQuery(). Last edited by LyonHaert : May 2nd, 2008 at 01:53 PM. |
|
#3
|
||||
|
||||
|
It's not notifications- I do get events back after several iterations through the loop in the same call to the test procedure where no query is every executed.
I'd already been through the link in your Edit. I've tried variants with .ExecuteScalar() and .ExecuteReader() instead of .ExecuteNonQuery(), though I think what he was really trying to communicate was the the function call blocks until the procedure has finished, in which case none of the suggestions were really correct and my .BeginExecuteNonQuery() was probably the best route, though it fails as well. I'll look more closely at the RPC thing, though since as far as I know I've left that alone I may already be doing that. |
|
#4
|
||||
|
||||
|
If you're connecting over TCP/IP then it's definitely not RPC.
|
|
#5
|
||||
|
||||
|
I re-worded my post a couple time, because I went back and read it again and in one place he says "Use RPC" and in another he says "Don't use RPC". I'm trying it both ways, but so far no luck.
|
|
#6
|
||||
|
||||
|
Now this is odd. When I'm using the dummy procedure the times and message values are more consistent than the real one. So I notice that the first batch of message always prints at the same time (after the 11th iteration). I varied the time it waits between each iteration (reduced from 10s to 4s) and it prints in the same place. Then I changed the amount printed (added some spaces on the end of each line) and it still outputs at the same time. Finally, I added another line to each iteration. Now we're getting somewhere- it prints one iteration earlier.
So it looks like it's the number of messages waiting that determines when the event finally fires. Except it's not a hard number- otherwise the lines that I added should have been enough to make print after the 9th pass, and this printed on the 10th. But it looks like it's holding about 50-60 messages at a go in spite of the NOWAIT directive. |
|
#7
|
||||
|
||||
|
Nailed it!
I remove this line and everything's fine:
cmd.CommandType = CommandType.StoredProcedure I also found something that sounded a little more authoritative on the RPC thing: Quote:
|
![]() |
| Viewing: Dev Shed Forums > Programming Languages - More > .Net Development > SqlConnection InfoMessage event |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|