extended stored procs

  • Thread starter Thread starter ToddT
  • Start date Start date
T

ToddT

looking at the help on msdn in regards to implementing an extended
stored proc, i am learly of doing this for the following reasons:

1. straight c++ code. no vb or .net code.
2. can't use mfc or atl
3. must be thread safe
4. memory leaks would affect sql server 2000
5. the goal is to invoke another app with primary key info - kind of
difficult using only straight c++ code.

my goal is to be notified when a certain table is updated. it can be
updated via multiple programs or a batch update via manually entered
sql code.

my questions are:

1. are extended stored procs difficult to create
2. is there any other way i can invoke a .net-based app when a row is
inserted into a specific table.

any help or guidelines would be greatly appreciated.
 
Ah you're reinventing the wheel. Check out notification services.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Todd,

ToddT said:
looking at the help on msdn in regards to implementing an extended
stored proc, i am learly of doing this for the following reasons:

1. straight c++ code. no vb or .net code.
2. can't use mfc or atl
3. must be thread safe
4. memory leaks would affect sql server 2000
5. the goal is to invoke another app with primary key info - kind of
difficult using only straight c++ code.

my goal is to be notified when a certain table is updated. it can be
updated via multiple programs or a batch update via manually entered
sql code.

my questions are:

1. are extended stored procs difficult to create
Click to expand...

No. Well, it depends on the complexity, but simple procs are easy enough.
There are also samples that come with Sql server.
2. is there any other way i can invoke a .net-based app when a row is
inserted into a specific table.
Click to expand...

You might check out sql server notification services.
http://www.microsoft.com/sql/NS/default.asp
 
ToddT said:
looking at the help on msdn in regards to implementing an extended
stored proc, i am learly of doing this for the following reasons:

1. straight c++ code. no vb or .net code.
2. can't use mfc or atl
3. must be thread safe
4. memory leaks would affect sql server 2000
5. the goal is to invoke another app with primary key info - kind of
difficult using only straight c++ code.

my goal is to be notified when a certain table is updated. it can be
updated via multiple programs or a batch update via manually entered
sql code.

my questions are:

1. are extended stored procs difficult to create
Click to expand...

Yes. Or rather because of 3 and 4 above, DBA's shouldn't let you use them
unless you can somehow guarantee their thread safeness and memory leak
freeness. Which means that for all practical purposes you should leave them
alone.
2. is there any other way i can invoke a .net-based app when a row is
inserted into a specific table.
Click to expand...

Yes. Look at the sp_oaxxx stored procedures. These do COM interop from
within TSQL and can get you out to code written in C++ ATL or VB6. Because
of the memory and threading issues, you can't directly invoke .NET
assemblies through COM interop from TSQL
(http://support.microsoft.com/default.aspx?scid=kb;en-us;322884 )But you can
invoke an unmanaged COM proxy.

It's actually quite easy to safely run .NET code from TSQL. Just build any
old .NET class and regester it for COM interop. Then take the type library
and install it on the Database server as a COM+ server application. Then
you can invoke it using the sp_oaxxx stored procedures and the .NET assembly
will run in a seperate COM+ process, isolating its memory from Sql Server.

Here's a short example:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=u$dR#[email protected]

The best thing is, when SqlServer 2005 is released, you can just get rid of
the COM interop and the COM+ server application and run your .NET code
directly. It's a way to have the future, today.

David
 
Back
Top