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:
>

External notification of database updates

MS-SQL 2005, C# 2.0

I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.

Transfering the data (Web Services, Remoting) is not a problem.

What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).

Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?

Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...

There are some BLOB's involved (1-2 page PDF's), if that makes a difference.

I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.

I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)

Thanks

Rob.

One interesting technique to consider would be using triggers and service broker. When an insert, update or delete occurs, send a message via service broker to your central server informing it of the change along with whatever data is necessary to identify it. The central server can then asynchronously receive and process the message.

Dan

External Activation and Notification Services

I have been going over the samples from PDC 2005 and trying to run the External Activator program in section 2 'CLR Services'. After sending a message to the Inventory Queue, I see that 'dm_broker_queue_monitors' table has a row in the table, with the state column shown as 'NOTIFIED', but the ExternalActivator program never gets notified of the event and therefore never launches the 'InventoryServer' program.

Querying the [ExternalActivatorQueue] shows no rows.

Querying the [Inventory Queue] shows messages are waiting to be received.

Executing the 'activator' command in the ExternalActviator program shows

Notification service 'ExternalActivator' on SQL Server 'DBSERVER\' and Database 'In
ventory' is connected to the database and working.

Any Ideas?

Thanks,

-KuoAfter some browsing through code in the samples, I figured out the problem. The queue thats suppose to contain all the notification messages for the ExternalActivator program was checking the correct queue for messages, however no messages were being created in the [ExternalActivatorQueue] whenever a new message was sent to the [Inventory Queue].
So I, dDropped the event, 'en', and reran the create event notifcation:

drop event notification en on queue [Inventory Queue]

create event notification en
on queue [Inventory Queue]
for queue_activation
to service 'ExternalActivator', 'current database';

sent a few more messages and the ExternalActivatorQueue started getting messages in the queue whenever a new message was sent to the [Inventory Queue].

This is really strange. I know I must of ran the create event notification command a few times. Oh well, it works now.

-Mr.Kuo

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...
> > In my extended stored procedure, I'm trying to return rows as data is
> > available. The client is expecting to use this data to show progress
> > notification (10%, 20% etc etc).
> >
> > Even with variations of the srv_describe, srv_sendrow & srv_senddone
> > I'm not able to get the client to display the data as it becomes
> > available. Instead, the rows come all together at the end of the call.
> >
> > Has anyone succeeded in acheiving this? What am I doing wrong?
> >
> > Any help appreciated,
> > Kartik
> >
> > ----
> > Here is a snippet of the code..
> >
> > ...
> > srv_describe ( This.srvproc_,
> > 1,
> > "messages",
> > SRV_NULLTERM,
> > SRV_TDS_NVARCHAR, // Dest data type.
> > (DBINT) 512, // Dest data length.
> > SRV_TDS_NVARCHAR, // Source data type.
> > (DBINT) 512, // Source data length.
> > NULL );
> > ...
> >
> > ...
> > srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> > srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> > );
> > if ( !srv_sendrow( This.srvproc_ ) )
> > {
> > ASSERT ( 0 );
> > }
> > ...
> >
> > ...
> > srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> > rowcount );
> > ...

Extended Stored Proc & Progress notification

In my extended stored procedure, I'm trying to return rows as data is
available. The client is expecting to use this data to show progress
notification (10%, 20% etc etc).
Even with variations of the srv_describe, srv_sendrow & srv_senddone
I'm not able to get the client to display the data as it becomes
available. Instead, the rows come all together at the end of the call.
Has anyone succeeded in acheiving this? What am I doing wrong?
Any help appreciated,
Kartik
----
Here is a snippet of the code..
...
srv_describe ( This.srvproc_,
1,
"messages",
SRV_NULLTERM,
SRV_TDS_NVARCHAR, // Dest data type.
(DBINT) 512, // Dest data length.
SRV_TDS_NVARCHAR, // Source data type.
(DBINT) 512, // Source data length.
NULL );
...
...
srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
);
if ( !srv_sendrow( This.srvproc_ ) )
{
ASSERT ( 0 );
}
...
...
srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
rowcount );
...Kartik,
I doubt that this is possible. what you are describing is callbacks or
events and SQL Server 2000 doesn't support this.
regards
Greg O
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403041544.df23472@.posting.google.com...
> In my extended stored procedure, I'm trying to return rows as data is
> available. The client is expecting to use this data to show progress
> notification (10%, 20% etc etc).
> Even with variations of the srv_describe, srv_sendrow & srv_senddone
> I'm not able to get the client to display the data as it becomes
> available. Instead, the rows come all together at the end of the call.
> Has anyone succeeded in acheiving this? What am I doing wrong?
> Any help appreciated,
> Kartik
> ----
> Here is a snippet of the code..
> ...
> srv_describe ( This.srvproc_,
> 1,
> "messages",
> SRV_NULLTERM,
> SRV_TDS_NVARCHAR, // Dest data type.
> (DBINT) 512, // Dest data length.
> SRV_TDS_NVARCHAR, // Source data type.
> (DBINT) 512, // Source data length.
> NULL );
> ...
> ...
> srv_setcoldata ( This.srvproc_, 1, (BYTE*)bstrMessage.m_str );
> srv_setcollen ( This.srvproc_, 1, bstrMessage.Length()*sizeof(WCHAR)
> );
> if ( !srv_sendrow( This.srvproc_ ) )
> {
> ASSERT ( 0 );
> }
> ...
> ...
> srv_senddone ( This.srvproc_, SRV_DONE_FINAL | SRV_DONE_COUNT, 0,
> rowcount );
> ...|||Greg,
I can receive the notification in the extended stored proc. Now to
inform the client about the notification, I had intended to send a
single row with the message. But somehow, the client seems to be
getting all the rows all at once at the end of the extended stored
procedure.
The documentation seems to indicate that the API does provide a way to
send data as it is produced.
Thanks,
Kartik
"Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in message news:<#OymsAm
AEHA.2292@.TK2MSFTNGP12.phx.gbl>...
> Kartik,
> I doubt that this is possible. what you are describing is callbacks or
> events and SQL Server 2000 doesn't support this.
> regards
> Greg O
>
> "Kartik Shah" <k2shah10@.hotmail.com> wrote in message
> news:7f618d19.0403041544.df23472@.posting.google.com...|||Hi,
In my experience that isn't the case. The process is internal to SQL Server
(within the memory space) so you would have to jump out of your process and
return to the calling process. I see no facility that does this.
You might want to get in touch with a guy names Gert ER Drapers at
www.sqldev.net. Gert was the architect of the SQL Server Tools in SQL
Server. Gert's a good bloke and will be able to tell you if it can or can't
be done. If it can he can tell you the correct way to do it.
regards
Greg O
http://www.ag-software.com
"Kartik Shah" <k2shah10@.hotmail.com> wrote in message
news:7f618d19.0403050653.2505cc9c@.posting.google.com...
> Greg,
> I can receive the notification in the extended stored proc. Now to
> inform the client about the notification, I had intended to send a
> single row with the message. But somehow, the client seems to be
> getting all the rows all at once at the end of the extended stored
> procedure.
> The documentation seems to indicate that the API does provide a way to
> send data as it is produced.
> Thanks,
> Kartik
>
> "Greg Obleshchuk" <greg-n-o-s-p-a-m-@.ag-s-o-f-t-w-a-r-e.com> wrote in
message news:<#OymsAmAEHA.2292@.TK2MSFTNGP12.phx.gbl>...