2012年9月 的存档
2012九月5

SqlServer连接mysql

sql server 评论关闭

方案有二种:
1,OPENROWSET
SELECT *
FROM
OPENROWSET('MSDASQL','DRIVER={MySQL ODBC 3.51 Driver};
SERVER=192.168.16.210;PORT=3306;DATABASE=mydb;USER=mas;PASSWORD=123456;
STMT=set names gb2312;OPTION=2049',
'select bill_time,mobile,sms_content from tbl_sm_bill_120809;')

2,建立链接服务器。
  1)安装mysql ODBC
  2) 配置系统DSN,控制面板 – 管理工具-数据源 (ODBC)
  3)sqlserver中增加链接服务器。
参考:How To Setup SQL Server Linked Server to MySQL

  4)使用:SELECT * FROM mysqlsms…tbl_sm_bill_120809
     或者:SELECT * FROM OPENQUERY(mysqlsms, 'SELECT * FROM tbl_sm_bill_120809;')

  对于mysql中列是char型的查询会报错,如:

链接服务器 'cti' 的 OLE DB 访问接口 'MSDASQL' 返回的数据与列 '[MSDASQL].V_Caller' 所需的数据长度不匹配。所需的(最大)数据长度为 60,但返回的数据长度为 24。
  处理方法,在查询时转换一下
  

    SELECT TOP 10 * 
    FROM OPENQUERY(cti, 'SELECT cast(tel as char(30)) FROM mytable;')

INSERT INTO OPENQUERY(Servername,'select * from DBName.tablename WHERE 1=2') --1=2防止查询整个表
SELECT '1'

SELECT *
FROM OPENQUERY(Servername,'select * from DBName.tablename')

UPDATE OPENQUERY(Servername,'select * from DBName.tablename')
SET colname='2'
WHERE colname='1'

SELECT *
FROM OPENQUERY(Servername,'select * from DBName.tablename')

 
2012九月1

sqlserver与CLR

--1,VS新建数据库项目,,添加新项-sqlserverclr #,添加自定义函数,
--函数前标记 [Microsoft.SqlServer.Server.SqlFunction]
--可直接右建部署。生成后在debug文件夹下会有sql执行的部署文件
--选择一数据库,便于调试与部署测试
--注:--vs并不运行直接添加第三方引用,可参考此种方式:http://updates.sqlservervideos.com/2010/07/adding-references-to-sql-clr-projects.html
--注意,64位的sqlserver不支持32位的dll 

--开启数据库支持
sp_configure 'clr enabled', 1;
RECONFIGURE;

--在部署时若程序集引用了其它的dll,应将这些dll先注册进sqlserver再创建自己的程序集
--注册引用
ALTER DATABASE [master] SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [System.Net.Http]
AUTHORIZATION [dbo]
FROM
'C:Program FilesReference AssembliesMicrosoftFrameworkv3.5System.Net.Http.dll' –没有的copy放置上
WITH PERMISSION_SET = UNSAFE

–-部署
–-1,建立程序集
–-增加
CREATE ASSEMBLY [SqlServerProject1]
from N'I:ITshareSqlServerProject1.dll'
–或者直接增加二进制文件,二进制文件见部署sql

CREATE ASSEMBLY [MySafe]
    AUTHORIZATION [dbo]
 FROM 0x4D5A900003000000040000....
WITH PERMISSION_SET = UNSAFE;

–-2,映射
CREATE FUNCTION [dbo].[Csharp]
(@code NVARCHAR (4000))
RETURNS NVARCHAR (4000)
AS
 EXTERNAL NAME [CLR].[UserDefinedFunctions].[Csharp]
–删除
DROP FUNCTION Decrypto
DROP ASSEMBLY MySafe

–-查询
/*
TA = 程序集(CLR 集成)触发器 CLR_TRIGGER
FS = 程序集 (CLR) 标量函数 CLR_SCALAR_FUNCTION
FT = 程序集 (CLR) 表值函数 CLR_TABLE_VALUED_FUNCTION
PC = 程序集 (CLR) 存储过程 CLR_STORED_PROCEDURE
*/

–-取调用的函数
SELECT s.type_desc 类型,s.name 名称,f.name 程序集,m.assembly_class 类名,m.assembly_method 方法名
FROM sys.objects s
JOIN sys.assembly_modules m ON s.object_id = m.object_id
JOIN sys.assemblies f ON m.assembly_id = f.assembly_id
WHERE type_desc LIKE 'clr%'
ORDER BY s.type_desc

–-程序集
SELECT TOP 10 *
FROM sys.assemblies

–-程序集对应文件
SELECT TOP 10 *
FROM sys.assembly_files
WHERE assembly_id = 65537
SELECT TOP 10 *
FROM sys.assembly_references

-–程序集对应函数
SELECT TOP 10 *
FROM sys.assembly_modules
SELECT dbo.Decrypto('UW7EcyjrWA2Qanp9nXmjJw==')
SELECT dbo.CLR_Decrypto('F0+zdAqmYv51nUL4fY5Z/w==')
select dbo.CLR_Encrypto('aaa')