Extended Stored Procedures

Dear all,
Does anyone knows how to write extended SPs in SQL Server 2000?
Can I get sample projects for the purpose?
Thanks in advance
VijayakumarCheck http://www.codeproject.com/database/mssqltutorial.asp.
There is also a lot of info in the Ken Hendersons book "Guru's Guide to SQL
Server Stored Procedures, XML, and HTML, The".
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Vijayakumar" <yvijayakumar@.hotmail.com.NO_SPAM> wrote in message
news:O2jUKoohEHA.3320@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Does anyone knows how to write extended SPs in SQL Server 2000?
> Can I get sample projects for the purpose?
> Thanks in advance
> Vijayakumar
>

Extended stored procedure?

I noticed that there is something in master database called
Extended stored procedure which can be dlls? what's this? How can we make
our own sps a dll and put it somewhere like this?
Thankshttp://www.codeproject.com/database/extended_sp.asp
"Ray5531" <RayAll@.microsft.com> wrote in message
news:%23984aPOTFHA.612@.TK2MSFTNGP12.phx.gbl...
>I noticed that there is something in master database called
> Extended stored procedure which can be dlls? what's this? How can we make
> our own sps a dll and put it somewhere like this?
>
> Thanks
>|||Whoa, you really don't want to venture there. Xp runs as in-proc thus a
simple mistake in your custom xp can take the entire sqlserver down.
Anyway, here is some read.
http://msdn.microsoft.com/library/e...des_07_9rxv.asp
http://msdn.microsoft.com/library/e...con_01_22sz.asp
-oj
"Ray5531" <RayAll@.microsft.com> wrote in message
news:%23984aPOTFHA.612@.TK2MSFTNGP12.phx.gbl...
>I noticed that there is something in master database called
> Extended stored procedure which can be dlls? what's this? How can we make
> our own sps a dll and put it somewhere like this?
>
> Thanks
>|||Do they really have to be in Master Database only?
I don't know C++,is there another way of making an extended sp? like using
C#?
Thanks
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23Oc2HSOTFHA.2304@.tk2msftngp13.phx.gbl...
> http://www.codeproject.com/database/extended_sp.asp
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:%23984aPOTFHA.612@.TK2MSFTNGP12.phx.gbl...
>|||Not in managed code, no. AFAIK, SQL 2K5 will allow hosting of managed code.
I would either: 1) Write whatever you're trying to do as an external app and
run it separately from SQL Server, or 2) Wait for SQL 2K5.
"Ray5531" <RayAll@.microsft.com> wrote in message
news:%23ECPaUOTFHA.584@.TK2MSFTNGP15.phx.gbl...
> Do they really have to be in Master Database only?
> I don't know C++,is there another way of making an extended sp? like using
> C#?
> Thanks
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23Oc2HSOTFHA.2304@.tk2msftngp13.phx.gbl...
>|||Can I write my own sps as dlls in 2005 with managed code ?
Thanks
"Michael C#" <howsa@.boutdat.com> wrote in message
news:u1iDgeOTFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Not in managed code, no. AFAIK, SQL 2K5 will allow hosting of managed
> code. I would either: 1) Write whatever you're trying to do as an external
> app and run it separately from SQL Server, or 2) Wait for SQL 2K5.
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:%23ECPaUOTFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||> Do they really have to be in Master Database only?
Yes.

> I don't know C++,is there another way of making an extended sp? like using C#?[/co
lor]
Not managed code. Not classic VB (as it creates COM DLLs, not classic DLLs).
Only C, C++ or Delphi.
Another option is to write a COM object and use SP_OACreate etc. to use that
COM object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ray5531" <RayAll@.microsft.com> wrote in message news:%23ECPaUOTFHA.584@.TK2MSFTNGP15.phx.gb
l...
> Do they really have to be in Master Database only?
> I don't know C++,is there another way of making an extended sp? like using
C#?
> Thanks
> "Michael C#" <howsa@.boutdat.com> wrote in message news:%23Oc2HSOTFHA.2304@.
tk2msftngp13.phx.gbl...
>|||I can't speak to extended sp's on 2K5, but my understanding is that you'll
be able to create SP's, functions, triggers and data types in managed code
that runs on SQL 2K5.
Here's a link to some MS Marketing stuff:
http://msdn.microsoft.com/msdntv/ep...>
manifest.xml
"Ray5531" <RayAll@.microsft.com> wrote in message
news:uIQ%23wxOTFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Can I write my own sps as dlls in 2005 with managed code ?
> Thanks
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:u1iDgeOTFHA.3216@.TK2MSFTNGP10.phx.gbl...
>|||The official word regarding calling a .NET component with sp_OA* is "not
supported".
http://support.microsoft.com/kb/322884
However, David Browne has some work around...
http://tinyurl.com/4ur72
-oj
"Ray5531" <RayAll@.microsft.com> wrote in message
news:uIQ%23wxOTFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Can I write my own sps as dlls in 2005 with managed code ?
> Thanks
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:u1iDgeOTFHA.3216@.TK2MSFTNGP10.phx.gbl...
>

Ext. SPs

I need to be able to create my own MSSQ UDF to return the current system
date, offset by a configurable number of minutes, depending on a value in a
table. The reason I am trying to do this is, I want to be able to, for
testing purposes, fake the system into thinking that time has elapsed.
Changing the time on the computer is not an option.

I would like to call the UDF MyGetDate and to replace all code occurrence of
getdate() in the database with this call. This includes column default value
constraints and stored procedures.

The problem is that MSSQL does not allow the function GETDATE with a UDF. I
thought to try an fake it out by having the UDF call a SP, which in turn
called GETDATE. When I did this, I got the error 'Only functions and
extended stored procedures can be executed from within a function.'

I guess I can go down the road to try and learn how to write an extended
stored procedure to return the current time, but I imagine that there is a
learning curve here.

I realize that all COLUMN default CONSTRAINT with GETDATE could be handled
by create ADD AND UPDATE TRIGGERS that populate thisIt's a kludge but you can create a UDF that reads a value from a table.
Then schedule a Sql Agent Job to run every minute, updating that
table's value.|||Chad (chad.dokmanovich@.unisys.com) writes:
> I need to be able to create my own MSSQ UDF to return the current system
> date, offset by a configurable number of minutes, depending on a value
> in a table. The reason I am trying to do this is, I want to be able to,
> for testing purposes, fake the system into thinking that time has
> elapsed. Changing the time on the computer is not an option.
> I would like to call the UDF MyGetDate and to replace all code
> occurrence of getdate() in the database with this call. This includes
> column default value constraints and stored procedures.
> The problem is that MSSQL does not allow the function GETDATE with a
> UDF. I thought to try an fake it out by having the UDF call a SP, which
> in turn called GETDATE. When I did this, I got the error 'Only functions
> and extended stored procedures can be executed from within a function.'
> I guess I can go down the road to try and learn how to write an extended
> stored procedure to return the current time, but I imagine that there is a
> learning curve here.

Using a UDF in all sorts of constraints, could have performance issues,
and if that UDF calls an extended procedure that does not make things
better.

You could save the show with:

CREATE FUNCTION kalle(@.d datetime) RETURNS datetime AS
BEGIN
RETURN dateadd(DAY, 12, @.d)
END
go
select dbo.kalle(getdate())

Yes, that will be somewhat bulkier, but it should get the job one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp