External stored procedures and sockets

I hope this is the right group for this question.
I am trying to deliver a UDP multicast data stream as a notification method
using an extended stored procedure. The multicast message, however, is not
making it out on the network. (At least not in a form that I can recognize.
)
- I can make the multicast work from a stand-alone console application using
the same procedure with dummy data, so I think I'm setting up the multicast
properly.
- I have verified that the extended stored procedure receives the parameters
from the trigger that calls it successfully, so the external stored procedur
e
is being called properly.
- I have verified that there are no error returns from any of the socket (or
other) API calls in the extended stored procedure, so the procedure thinks
it's doing what it ought to.
My conclusion is that there is something in the execution environment that
is keeping the extended stored procedure from working as expected (i.e., as
it does as a simple subroutine in the console application). On the other
hand, I know I've seen people write about using sockets in their extended
stored procedures.
Any ideas as to what I'm doing wrong?
Thanks.
Ed Hoch
--
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksHi
Have you checked that there is not a firewall blocking this port? Also make
sure that your account has the correct access to do this.
John
"Edward Hoch" wrote:

> I hope this is the right group for this question.
> I am trying to deliver a UDP multicast data stream as a notification metho
d
> using an extended stored procedure. The multicast message, however, is no
t
> making it out on the network. (At least not in a form that I can recogniz
e.)
> - I can make the multicast work from a stand-alone console application usi
ng
> the same procedure with dummy data, so I think I'm setting up the multicas
t
> properly.
> - I have verified that the extended stored procedure receives the paramete
rs
> from the trigger that calls it successfully, so the external stored proced
ure
> is being called properly.
> - I have verified that there are no error returns from any of the socket (
or
> other) API calls in the extended stored procedure, so the procedure thinks
> it's doing what it ought to.
> My conclusion is that there is something in the execution environment that
> is keeping the extended stored procedure from working as expected (i.e., a
s
> it does as a simple subroutine in the console application). On the other
> hand, I know I've seen people write about using sockets in their extended
> stored procedures.
> Any ideas as to what I'm doing wrong?
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks|||Hi John,
I am assuming that since I can run the same code successfully from a console
application, that it is not a firewall issue. I don't think it's possible t
o
"firewall" a specific process (like SQL Server), is it? Also, I have logged
in to the console as the user that SQL Server runs under. The console
application still works. So, unless SQL Server is somehow managinig to
affect the permissions of that account, I don't think it's an access
problem, either.
Feel free to correct me if any of my assumptions are wrong. Thanks very
much for your response.
Ed
"John Bell" wrote:
> Hi
> Have you checked that there is not a firewall blocking this port? Also mak
e
> sure that your account has the correct access to do this.
> John
> "Edward Hoch" wrote:
>

Extended stored procedures and UDP sockets

Hello,
I'm trying this question again; please excuse the repetition.
I have written an extended stored procedure using Visual C++ .Net
(unmanaged). The procedure is intended to send out a UDP multicast message
when new data arrives. The message doesn't seem to be sent.
I have verified that the ESP is getting called correctly (in response to an
INSERT trigger). I have verified that none of the Winsock API procedures
return errors when called from the ESP. I have verified the code that
actually sends the multicast message by running the exact same code from a
console application, where it works fine--including running it when logged i
n
under the account that SQL Server runs under.
There seems to be something unexpectedly different about the environment
that the ESP runs in. I'm at a loss to figure out what it is. Any clues
would be appreciated.
Thanks.
Ed Hoch
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksI'll throw out some thoughts, though I don't have anything definite:
1. Are you initializing the Windows socket library right within the proc?
(I think it's WSAInitialize()). That definitely would need to be done.
2. If you can't get a solution, I know you could set a named Windows event
inside your extended proc (I've done that) and then have another process
waiting on that event and have it send the data gram.
3. Do you have fiber mode turned on in SQL Server? If so, that may affect
threading and sockets (just guessing...)
Mike
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks|||Hi Mike,
Thanks for the reply.
1. I am initializing the socket library with WSAStartup. It seems to work
from the console version of the program, so I think the initialization is
working properly.
2. Named events seem like a possibility. What's a good reference for
those?
3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
more or less likely for the thing to work with fibers enabled?
Thanks again for the reply.
Ed Hoch
"Mike Jansen" wrote:

> I'll throw out some thoughts, though I don't have anything definite:
> 1. Are you initializing the Windows socket library right within the proc?
> (I think it's WSAInitialize()). That definitely would need to be done.
> 2. If you can't get a solution, I know you could set a named Windows event
> inside your extended proc (I've done that) and then have another process
> waiting on that event and have it send the data gram.
> 3. Do you have fiber mode turned on in SQL Server? If so, that may affect
> threading and sockets (just guessing...)
> Mike
> "Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
> news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> message
> an
> in
>
>|||> Thanks for the reply.
> 1. I am initializing the socket library with WSAStartup. It seems to
work
> from the console version of the program, so I think the initialization is
> working properly.
WSAStartup, that was it :)

> 2. Named events seem like a possibility. What's a good reference for
> those?
In Windows API look at CreateEvent(), SetEvent(), OpenEvent(). I think
there's even an overview section on synchronization objects. The thing to
be aware of for named objects is that in order for them to work across
process boundaries you need to set up the SECURITY_ATTRIBUTES properly
(unless the other process happens to be running under the same account as
the SQL Server process). The named event won't pass any other context
information.
If you need to pass context information along with triggering an event, you
may want to do something like write out a file and have the process
monitoring a folder rather than an event. There are other options besides
files but file is simple and straightforward.
If you want more information, we can take this offline or to another group
since it's off-topic and can get fairly involved.

> 3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
> more or less likely for the thing to work with fibers enabled?
Like I said, I was just guessing, but I'd guess you might have issues if
fibers were ENABLED a fiber is a "mutant" thread.
Also, perhaps you could try this (though I don't know if it's wise): In
your stored proc WinMain/on load, create a separate thread. Do your sockets
in that thread. That way it's still in the same process but on its own
thread. So your worker thread starts and waits for an item in a context
queue (something you create). When it receives an item in the queue, it
dequeues it and sends a datagram. In your exetended proc, just add items to
the queue. First, I'd do some scanning to see if secondary threads in
extended procs are OK. Regardless, the divantage of this is that its
starting to get complicated and complicated things mess up more often and
messing up inside the process space of SQL Server isn't the greatest thing.
So even if secondary threads are OK, BE VERY CAREFUL.
Mike|||What is the service account of SQL Server? Start the SQL Server service from
the command line, and execute the XP, see if that makes a difference.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
> message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
> an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
> in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks