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