External SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if SQLServer/MSDE
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in SQLServer/MSDE
Best regards
Peter Sullvan
hi Peter,
Peter Sullvan wrote:
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if SQLServer/MSDE
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in SQLServer/MSDE
> Best regards
> Peter Sullvan
you can write so called "extended stored procedures" externded by dll... at
http://www.sqldev.net/xp.htm you can find some examples as long as usefull
links..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

External SQL functions exported by DLLs

We have a few rather advanced calculation routines
developed in C++. Currently we are using a Sybase ASA
database for our app. and these routines are accessible
by so called external functions exported by a DLL
(dynamic link library) that we wrote in C++. Sybase
supports this quite nicely.
The client app can call such routines by:
"select get_roof_angle() as angle from dummy;"
or
":angle = call get_roof_angle();"
Now we are checking if other databases can provide us
with similar abilities. So I just wonder if MS-SQLServer
supports the ability to call a routine exported by a
DLL? If yes, does anyone know the SQL syntax to declare
it in MS-SQLServer.
Best regards
Peter SullvanCurrently only as (extended) stored procedures.
EXEC xp_procname -- which really is a function in a DLL file.
SQL Server 2005 will implement CLR inside SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||In SQL Server 2000, you can create an extended stored procedure. See the
Books Online for details. For scalar functions, you can encapsulate an
extended stored procedure in a Transact-SQL scalar function like the example
below.
CREATE FUNCTION get_roof_angle()
RETURNS int
AS
BEGIN
DECLARE @.RoofAngle int
EXEC master..xp_get_roof_angle @.RoofAngle OUT
RETURN @.RoofAngle
END
GO
SELECT dbo.get_roof_angle()
GO
The planned SQL Server 2005 version will allow you to develop functions
using CLR managed code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>|||To clarify my response, you can create extended stored procedures in all
versions of SQL Server. However, Transact-SQL functions are available only
in SQL 2000+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23YTFuOkJFHA.1280@.TK2MSFTNGP09.phx.gbl...
> In SQL Server 2000, you can create an extended stored procedure. See the
> Books Online for details. For scalar functions, you can encapsulate an
> extended stored procedure in a Transact-SQL scalar function like the
> example below.
> CREATE FUNCTION get_roof_angle()
> RETURNS int
> AS
> BEGIN
> DECLARE @.RoofAngle int
> EXEC master..xp_get_roof_angle @.RoofAngle OUT
> RETURN @.RoofAngle
> END
> GO
> SELECT dbo.get_roof_angle()
> GO
> The planned SQL Server 2005 version will allow you to develop functions
> using CLR managed code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
> news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server only supports Extended Stored Procedures, no Extended Functions,
but you could wrap an extended stored procedure inside a user defined T-SQL
function and get the same behavior, but you need to use output parameters in
your extended stored procedure to return values, you can not return a result
set.
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.
"Peter Sullvan" <ps@.NOSPAMonline.de> wrote in message
news:%23Usr70jJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> We have a few rather advanced calculation routines
> developed in C++. Currently we are using a Sybase ASA
> database for our app. and these routines are accessible
> by so called external functions exported by a DLL
> (dynamic link library) that we wrote in C++. Sybase
> supports this quite nicely.
> The client app can call such routines by:
> "select get_roof_angle() as angle from dummy;"
> or
> ":angle = call get_roof_angle();"
> Now we are checking if other databases can provide us
> with similar abilities. So I just wonder if MS-SQLServer
> supports the ability to call a routine exported by a
> DLL? If yes, does anyone know the SQL syntax to declare
> it in MS-SQLServer.
> Best regards
> Peter Sullvan
>