How to build an extended stored procedure that broadcast a message

[frankiecifarra]

Hi,
I built an extended stored procedure which function is to broadcast a message to an application.
The broadcasting is achieved via the API functions, but the problem is that even every thing works fine the broadcasting process from inside SQL Server doesn't work.
What should I do ?
Thanks in advance [sig][/sig]

 

[TomSark]

Why not use net send in an xp_cmdshell instead of building an extended stored proc? Extended procs can be dangerous if you don't code them properly.. is there a reason why you can't use net send?

Tom [sig][/sig]

 

[frankiecifarra]

Answer to Tom Sark.
Dear Tom:
Thanks for your reply.
What you told me is not what I need.
I would invoke xp_cmdshell from inside a stored procedure or a trigger, that costs a lot of time and resource.
What I really have is a Service waiting for a notification from inside SQL Server in order to unchain a heavy task.
Is that imposible ?
Regards
Francisco

 

[TomSark]

I see.. sorry, I misunderstood the question. From what I know about SQL Server, I can only say that it takes over some of the kernal functions that NT normally performs (especially memory stuff). I do not know how it affects messaging.

But... you could make this data driven such that the application polls for info in say, a control table, and does it's thing when it finds the data it wants...

That would most likely work...


Tom

 

[foxdev]

I'm thinking along the lines of Tom...could this other application, the one that is the recipient of the message, look for a flag file with a predetermined name and in a predetermined directory? Now that I've said that, I don't know how you'd create a simple text file from within SQL Server in a quick fashion (i.e., not xp_cmdshell or similar).

Could your target app read the NT log? If so, you could write a message there that would trigger the other app.

Tom's solution would be the cleanest and quickest, though, assuming the target app could access the SQL Server table (or even some other table, if you want to get fancier).

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

How to create a text file in SQL Server (let vb do all the file processing work )

You can actually create a text file from within SQL Server via a vb dll that opens, writes, closes the file, etc. then use sp_oacreate, sp_oamethod...etc. inside a stored proc that calls the file manipulation methods in the vb dll via the sp_oamethod calls. You cannot use xp_cmdshell to cleanly write to a text file. I also must warn anyone to make darn sure they clean up their objects when they've called sp_oacreate (etc). If not, you run the risk of corrupting SQL Server memory. That's bayad... mmmkay? LOL

If can of course, manipulate the text files in DTS as we all already know...

Tom

 

[foxdev]

I assume, Tom, that even though you specify VB (you Microsofty, you) I can use Delphi or VFP or any other COM DLL builder, eh?

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Yup

Tom