--如果是VB做的扩展存储过程,参考:--SQL Server的存储过程调用Com组件 /*--下面的部分在VB中完成首先我们先用VB 作一个最简单的组件工程名称: testSQLCOM 类名: TestMath'函数,计算两个整数相加的结果 Public Function AddMe(a As Long, b As Long) As Long AddMe = a + b End Function编译生成后,我们就可以在 Sql Server 中对这个 Com 组件进行调用了 --*//*--下面是SQL中对上面DLL的调用--*/--定义用到的变量 declare @err int,@src varchar(255),@desc varchar(255) declare @obj int,@re int--创建调用实例 exec @err=sp_OACreate 'testSQLCOM.TestMath', @obj out if @err<>0 goto lberr --如果创建失败,则进行错误处理--调用DLL中的函数 exec @err=sp_OAMethod @obj,'AddMe',@re out,100,200 if @err<>0 goto lberr --如果调用错误,则进行错误处理print '返回的结果是:' + str(@re)--完成后释放 exec sp_OADestroy @objreturnlberr: exec sp_oageterrorinfo 0,@src out,@desc out select cast(@err as varbinary(4)) as 错误号 ,@src as 错误源,@desc as 错误描述
这里有我老师上课时的一个例子 -------------------- 首先用VB作一个最简单的两数相加的COM组件(DLL文件)。代码如下: Project Name: testSQLCOM Class Name: TestMath Public Function AddMe(a As Long, b As Long) As Long AddMe = a + b End Function 其次,注册这个COM组件。 regsvr32 testSQLCOM.dll 最后,创建一个存储过程sp_testSQLCOMCreate Procedure sp_testSQLCOM @Num1 int,@Num2 int As DECLARE @Object int,@intRet int,@intRetCode int DECLARE @strErr varchar (255),@strErr1 varchar (255) /* 首先创建Com 实例 */ EXEC @intretcode = sp_OACreate "testSQLCOM.testMath", @Object out IF @intRetCode <> 0 BEGIN /* 创建实例 失败 */ EXEC sp_OAGetErrorInfo @Object, @strErr OUT, @strErr1 OUT PRINT '创建实例失败,失败的原因是:' + @strErr + ' ' + @strErr1 RETURN END /* 创建成功,开始调用 */ EXEC @intRetCode = sp_OAMethod @Object,'AddMe',@intRet OUT,@Num1,@Num2 IF @intRetCode <> 0 BEGIN /* 调用方法出错 */ EXEC sp_OAGetErrorInfo @Object, @strErr OUT, @strErr1 OUT PRINT '调用方法失败,失败的原因是:' + @strErr + ' ' + @strErr1 EXEC sp_OADestroy @Object RETURN END PRINT '返回的结果是' + Str(@intRet) EXEC sp_OADestroy @Object
Programming Extended Stored Procedures Microsoft Open Data Services provides a server-based application programming interface (API) for extending Microsoft® SQL Server™ functionality. The API consists of C and C++ functions and macros used to build applications in the following categories: Extended stored procedures. Gateway applications. Extended Stored Procedures Packaged as dynamic-link libraries (DLLs), extended stored procedures provide a way to extend SQL Server functionality through functions developed by using C/C++, the Open Data Services API, and the Microsoft Win32® API. These functions can send result sets and output parameters back to the client from a variety of external data sources.Gateway Applications In the past, Open Data Services was used to write server applications, such as gateways to non-SQL Server database environments. With the emergence of newer and more powerful technologies, such as Windows NT Component Services and SQL Server distributed queries, the need for Open Data Services gateway applications has largely been replaced. If you have existing gateway applications, you cannot use opends60.dll and ums.dll that are shipped with SQL Server 2000 to run the applications. Gateway applications are no longer supported. You can continue to run gateway applications using the opends60.dll and ums.dll that shipped with SQL Server 7.0.
function Xabs(a1,a2:integer):integer;stdcall begin result:=a1*a2; end; exports Xabs; begin end; 请Jianli2004(健力)解释一下为什么要转换,Delphi直接写的Dll不可以吗? 如果要转换应该怎样转换呢?谢谢。
将新扩展存储过程的名称注册到 Microsoft® SQL Server™ 上。语法
sp_addextendedproc [ @functname = ] 'procedure' ,
[ @dllname = ] 'dll'参数
[ @functname = ] 'procedure'在动态链接库 (DLL) 内调用的函数名称。procedure 的数据类型为 nvarchar(517),没有默认设置。procedure 能够可选地包含 owner.function 形式的所有者名称。[ @dllname = ] 'dll'包含该函数的 DLL 名称。dll 的数据类型为 varchar(255),没有默认设置。返回代码值
0(成功)或 1(失败)结果集
无注释
使用 Microsoft Open Data Services 的程序员可以创建扩展存储过程。创建扩展存储过程后,必须使用 sp_addextendedproc 将此存储过程添加到 SQL Server 上。有关更多信息,请参见创建扩展存储过程。 只将一个扩展存储过程添加到 master 数据库中。若要从非 master 的数据库中执行扩展存储过程,请用 master 限定扩展存储过程的名称。sp_addextendedproc 将条目添加到 sysobjects 中,用 SQL Server 注册新扩展存储过程名称。同样在 syscomments 表中添加一个条目。权限
只有 sysadmin 固定服务器角色的成员才可以执行 sp_addextendedproc。示例
下例添加 xp_hello 扩展存储过程。USE master
EXEC sp_addextendedproc xp_hello, 'xp_hello.dll'
/*--下面的部分在VB中完成首先我们先用VB 作一个最简单的组件工程名称: testSQLCOM
类名: TestMath'函数,计算两个整数相加的结果
Public Function AddMe(a As Long, b As Long) As Long
AddMe = a + b
End Function编译生成后,我们就可以在 Sql Server 中对这个 Com 组件进行调用了
--*//*--下面是SQL中对上面DLL的调用--*/--定义用到的变量
declare @err int,@src varchar(255),@desc varchar(255)
declare @obj int,@re int--创建调用实例
exec @err=sp_OACreate 'testSQLCOM.TestMath', @obj out
if @err<>0 goto lberr --如果创建失败,则进行错误处理--调用DLL中的函数
exec @err=sp_OAMethod @obj,'AddMe',@re out,100,200
if @err<>0 goto lberr --如果调用错误,则进行错误处理print '返回的结果是:' + str(@re)--完成后释放
exec sp_OADestroy @objreturnlberr:
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
--------------------
首先用VB作一个最简单的两数相加的COM组件(DLL文件)。代码如下:
Project Name: testSQLCOM
Class Name: TestMath
Public Function AddMe(a As Long, b As Long) As Long
AddMe = a + b
End Function
其次,注册这个COM组件。
regsvr32 testSQLCOM.dll
最后,创建一个存储过程sp_testSQLCOMCreate Procedure sp_testSQLCOM
@Num1 int,@Num2 int
As DECLARE @Object int,@intRet int,@intRetCode int
DECLARE @strErr varchar (255),@strErr1 varchar (255) /* 首先创建Com 实例 */
EXEC @intretcode = sp_OACreate "testSQLCOM.testMath", @Object out
IF @intRetCode <> 0
BEGIN
/* 创建实例 失败 */
EXEC sp_OAGetErrorInfo @Object, @strErr OUT, @strErr1 OUT
PRINT '创建实例失败,失败的原因是:' + @strErr + ' ' + @strErr1
RETURN
END /* 创建成功,开始调用 */
EXEC @intRetCode = sp_OAMethod @Object,'AddMe',@intRet OUT,@Num1,@Num2
IF @intRetCode <> 0
BEGIN
/* 调用方法出错 */
EXEC sp_OAGetErrorInfo @Object, @strErr OUT, @strErr1 OUT
PRINT '调用方法失败,失败的原因是:' + @strErr + ' ' + @strErr1
EXEC sp_OADestroy @Object
RETURN
END
PRINT '返回的结果是' + Str(@intRet)
EXEC sp_OADestroy @Object
还有可不可以用我给的函数作个调用的例子啊。谢谢
你也可放在别处,不过加入到扩展存储过程时要指出路径
DELPHI写的DLL要转化才可用扩展存储过程对DLL的写法有一定要求的
Microsoft Open Data Services provides a server-based application programming interface (API) for extending Microsoft® SQL Server™ functionality. The API consists of C and C++ functions and macros used to build applications in the following categories: Extended stored procedures.
Gateway applications.
Extended Stored Procedures
Packaged as dynamic-link libraries (DLLs), extended stored procedures provide a way to extend SQL Server functionality through functions developed by using C/C++, the Open Data Services API, and the Microsoft Win32® API. These functions can send result sets and output parameters back to the client from a variety of external data sources.Gateway Applications
In the past, Open Data Services was used to write server applications, such as gateways to non-SQL Server database environments. With the emergence of newer and more powerful technologies, such as Windows NT Component Services and SQL Server distributed queries, the need for Open Data Services gateway applications has largely been replaced. If you have existing gateway applications, you cannot use opends60.dll and ums.dll that are shipped with SQL Server 2000 to run the applications. Gateway applications are no longer supported.
You can continue to run gateway applications using the opends60.dll and ums.dll that shipped with SQL Server 7.0.
Extended Stored Procedure Architecture
Programming Extended Stored Procedures
Extended Stored Procedure Sample: xp_hello
请Jianli2004(健力)解释一下。
begin
result:=a1*a2;
end;
exports
Xabs;
begin
end;
请Jianli2004(健力)解释一下为什么要转换,Delphi直接写的Dll不可以吗?
如果要转换应该怎样转换呢?谢谢。