当前位置:Gxlcms > mysql > SQLSERVER的CLR表值函数

SQLSERVER的CLR表值函数

时间:2021-07-01 10:21:17 帮助过:169人阅读

一、使用CLR表值 函数 的背景 在SQL SERVER里面,直接读取远程 数据库 的表,似乎会占用大量的内存,出现类似错误: 链接服务器 192.168.0.1 的 OLE DB 访问接口 SQLNCLI10 报错。提供程序内存不足。 [SQLSTATE 42000] (错误 7399) 。 我不知道读取远程 数据

一、使用CLR表值函数的背景
在SQL SERVER里面,直接读取远程数据库的表,似乎会占用大量的内存,出现类似错误:


链接服务器 "192.168.0.1" 的 OLE DB 访问接口 "SQLNCLI10" 报错。提供程序内存不足。 [SQLSTATE 42000] (错误 7399) 。


我不知道读取远程数据库的表是一个什么样的原理,是首先从远程将该表一古脑全部加载到本地内存,然后再根据过滤条件来获得相应的记录,还是这个过滤在远程服务器完成,然后返回找到的记录?


从占用大量内存来看,我觉得应该是前者。


如果在远程数据库里面提供一个函数,获取什么记录,都通过这个函数,这样返回来的就是少量的记录,绝大部分的数据处理都已在远端完成,御敌于国门之外。

但是远程数据库函数是不能直接使用的,会报“不允许使用远程表值函数调用。”的错误。存储过程可以,函数不行。只能通过OPENQUERY的方式来完成调用。但是,OPENQUERY又不能传递参数。(详见如何给OPENQUERY传递参数


变通的办法是,在本地写一个CLR的表值函数,由它负责去访问远程函数,而本地访问它,等于间接访问远程函数


之所以用CLR表值函数,是比较好传递参数给OPENQUERY,并且获得返回记录后,又能够装配好返回给本地调用方。假如不是函数而是存储过程,我都不知道调用方怎么去获取到这些记录。


二、编写CLR表值函数


三、部署CLR表值函数

0、开启CLR

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


1、 代码编写完后,编译成DLL。然后打开SQL SERVER的目标数据库 --》程序集 --》添加程序集,将DLL导入。给该程序集起个名字,比如叫myClrDll


2、如果代码更改,重新编译成DLL,可以用以下语句重新导入
ALTER ASSEMBLY [myClrDll]
FROM 'd:/code/temp/myClrDll.dll'
注意'd:/code/temp/myClrDll.dll'一定是数据库所在机器的路径。通常我们程序和数据库不同机器,所以这个路径常常搞错。


3、创建数据库表值函数,以使用这个CLR表值函数
USE [mydb]
GO
CREATE FUNCTION [dbo].[cr_func_getdatabyId](@Id [int])
RETURNS TABLE (
[Id] [int] NULL,
[Name] [nvarchar](300) NULL,--VARCHAR不行,要用NVARCHAR
[CreateDate] [smalldatetime] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [myClrDll].[myFunc].[GetDataById]
完成,从此您可以幸福地使用函数cr_func_getdatabyId鸟。

人气教程排行