.Net Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsProgramming Languages - More.Net Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
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  
Old May 2nd, 2008, 01:29 PM
f'lar's Avatar
f'lar f'lar is offline
Senior WeyrLeader
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Aug 2003
Location: WI
Posts: 3,782 f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 1 Month 1 Week 3 Days 22 h 12 m 27 sec
Reputation Power: 675
Send a message via Google Talk to f'lar
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
Any thoughts on how to make this get the InfoMessage event right away, or an explaination of why it's not possible, are appreciated. I've tried a number of things, including re-writing this using .BeginExecuteNonQuery()/.EndExecuteNonQuery() and upping the severity in RAISERROR (though I can't go too high).
__________________
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.

Reply With Quote
  #2  
Old May 2nd, 2008, 01:42 PM
LyonHaert's Avatar
LyonHaert LyonHaert is offline
Arcane Scribbler
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Jun 2005
Location: Indianapolis, IN
Posts: 1,610 LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 1 Day 18 h 39 m 36 sec
Reputation Power: 378
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().
__________________
Joel B Fant - LyonHaert.net

2 + 2 is... 10... in base 4

Last edited by LyonHaert : May 2nd, 2008 at 01:53 PM.

Reply With Quote
  #3  
Old May 2nd, 2008, 02:16 PM
f'lar's Avatar
f'lar f'lar is offline
Senior WeyrLeader
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Aug 2003
Location: WI
Posts: 3,782 f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 1 Month 1 Week 3 Days 22 h 12 m 27 sec
Reputation Power: 675
Send a message via Google Talk to f'lar
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.

Reply With Quote
  #4  
Old May 2nd, 2008, 02:45 PM
LyonHaert's Avatar
LyonHaert LyonHaert is offline
Arcane Scribbler
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Jun 2005
Location: Indianapolis, IN
Posts: 1,610 LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level)LyonHaert User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 1 Day 18 h 39 m 36 sec
Reputation Power: 378
If you're connecting over TCP/IP then it's definitely not RPC.

Reply With Quote
  #5  
Old May 2nd, 2008, 03:23 PM
f'lar's Avatar
f'lar f'lar is offline
Senior WeyrLeader
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Aug 2003
Location: WI
Posts: 3,782 f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 1 Month 1 Week 3 Days 22 h 12 m 27 sec
Reputation Power: 675
Send a message via Google Talk to f'lar
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.

Reply With Quote
  #6  
Old May 2nd, 2008, 04:50 PM
f'lar's Avatar
f'lar f'lar is offline
Senior WeyrLeader
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Aug 2003
Location: WI
Posts: 3,782 f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 1 Month 1 Week 3 Days 22 h 12 m 27 sec
Reputation Power: 675
Send a message via Google Talk to f'lar
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.

Reply With Quote
  #7  
Old May 2nd, 2008, 05:01 PM
f'lar's Avatar
f'lar f'lar is offline
Senior WeyrLeader
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Aug 2003
Location: WI
Posts: 3,782 f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level)f'lar User rank is Brigadier General (60000 - 70000 Reputation Level) 
Time spent in forums: 1 Month 1 Week 3 Days 22 h 12 m 27 sec
Reputation Power: 675
Send a message via Google Talk to f'lar
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:
Originally Posted by http://www.cnblogs.com/aspxphpjsprb/archive/2008/01/08/1030144.html
Unfortunately, there is a bug in SQL Server with NOWAIT, which affects you only if you are calling a procedure through RPC (remote procedure call), so that it this case, SQL Server buffers the messages nevertheless. RPC is the normal way to call a procedure from an application (at least it should be), but if you are running a script from OSQL or Query Analyzer, this bug does not affect you.

Reply With Quote
Reply

Viewing: Dev Shed ForumsProgramming Languages - More.Net Development > SqlConnection InfoMessage event


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

IBM developerWorks




© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway