SQL CLR ¼¯³É
Ò»¡¢ÅäÖà SQL Server£¬Ê¹Ö®ÔÊÐí CLR ¼¯³É:
¡¡¡¡1.µ¥»÷“¿ªÊ¼”°´Å¥£¬ÒÀ´ÎÖ¸Ïò“ËùÓгÌÐò”¡¢Microsoft SQL Server 2005 ºÍ“ÅäÖù¤¾ß”£¬È»ºóµ¥»÷“ÍâΧӦÓÃÅäÖÃÆ÷”¡£
¡¡¡¡2.ÔÚ SQL Server 2005 ÍâΧӦÓÃÅäÖÃÆ÷¹¤¾ßÖУ¬µ¥»÷“¹¦ÄܵÄÍâΧӦÓÃÅäÖÃÆ÷”¡£
¡¡¡¡3.Ñ¡ÔñÄúµÄ·þÎñÆ÷ʵÀý£¬Õ¹¿ª“Êý¾Ý¿âÒýÇæ”Ñ¡ÏȻºóµ¥»÷“CLR ¼¯³É”¡£
¡¡¡¡4.Ñ¡Ôñ“ÆôÓà CLR ¼¯³É”¡£
´ËÍ⣬Äú¿ÉÒÔÔÚ SQL Server ÖÐÔËÐÐÒÔϲéѯ£¨´Ë²éѯÐèÒª ALTER SETTINGS ȨÏÞ£©:
USE [database]
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
¶þ¡¢´´½¨ SQL Server Project£¬ÅäÖÃÊý¾Ý¿âÁ¬½ÓÐÅÏ¢¡£ÓÒ¼ü´ò¿ª¸ÃÏîÄ¿ÊôÐÔ£¬Ñ¡Ôñ“database ”£¬½«Permission LevelÉèΪ“unsafe”
Èý¡¢
ÏîÄ¿´´½¨ºóÌí¼ÓÒ»¸ö.cs Îļþ£¬ÈçÏÂÑÝʾÈçºÎ½øÐбíÖµº¯ÊýÀ©Õ¹
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
using System.Data.SqlTypes;
using System.Collections;
public partial class FuncTest
{
//±íÖµº¯Êý¶¨Òå.µÚÒ»¸ö·½·¨ (InitMethod) ¸³Óè SqlFunction ÊôÐÔ£¬ÓÃÓÚ½«ËüÖ¸¶¨Îª¸Ã±íÖµº¯ÊýµÄÈë¿Úµã
//´Ë·½·¨±ØÐë·µ»Ø IEnumerable »ò IEnumerator ¶ÔÏ󡣸öÔÏó°üº¬½«ÓÃÓÚÌî³ä·µ»Ø±íµÄÊý¾Ý¡£
//Ö´Ðиú¯Êýʱ£¬SQL Server ½«Ñ»··ÃÎÊ IEnumerator ¶ÔÏóÖеÄÿ¸ö¶ÔÏ󣬲¢Ê¹ÓÃËüÀ´Ìî³äÊý¾ÝÐС£
//Ϊ´Ë£¬ËüÒª½«¸Ã¶ÔÏ󴫵ݵ½¸ÃÀàÖеĵڶþ¸ö·½·¨ FillRow¡£´Ë·½·¨»á½«¸Ã¶ÔÏóת»»³É·µ»Ø±íÖеÄijһÐС£
//´Ë·½·¨ÔÚ SqlFunction ÊôÐ﵀ FillRowMethodName ²ÎÊýÖÐÖ¸¶¨
//²¿ÊðÏîÄ¿ºóÔÚÊý¾Ý¿âÖлáÉú³ÉÏàÓ¦µÄ±íÖµº¯Êýdbo.ReadEventLog
[SqlFunction(TableDefinition = "logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint",
Name = "ReadEventLog", FillRowMethodName = "F
Ïà¹ØÎĵµ£º
1.sp_helpsort
SELECT SERVERPROPERTY ('Collation')
²é¿´ÄãµÄÅÅÐò¹æÔò.
²»¹ýÄãµÄÕâ¸öÓ¦¸ÃºÍ×Ö·û¼¯ÓйØ.
2.¸ü¸Ä·þÎñÆ÷ÅÅÐò¹æÔò
¸ü¸Ä SQL Server 2005 ʵÀýµÄĬÈÏÅÅÐò¹æÔòµÄ²Ù×÷¿ÉÄÜ»á±È½Ï¸´ÔÓ£¬°üÀ¨ÒÔϲ½Ö裺
È·±£¾ßÓÐÖØд´½¨Óû§Êý¾Ý¿â¼°ÕâЩÊý¾Ý¿âÖеÄËùÓжÔÏóËùÐèµÄÈ«²¿ÐÅÏ¢»ò½Å±¾¡£
ʹÓù¤¾ß£¨ÀýÈç´óÈÝÁ ......
SQL ServerÀïµÄ ISNULL Óë Oracle ÖÐµÄ NULLIF²»Í¬:
SQL Server ÖÐÓÐÁ½¸ö²ÎÊý£¬Óï·¨£º
ISNULL(check_expression, replacement_value)
check_expression Óë replacement_value Êý¾ÝÀàÐͱØÐëÒ»ÖÂ
Èç¹û check_expression Ϊ NULL£¬Ôò·µ»Ø replacement_v ......
Sql ServerÖеÄÈÕÆÚÓëʱ¼äº¯Êý
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
......
ʹÓÃSQL Serverµ¼Èë/µ¼³öExcel,°üº¬²¿·Ö´íÎóÐÅÏ¢´¦Àí·½·¨;
²Ù×÷ÊÖ¼Ç,Áô´Ë±¸²é
/*
µ¼Èë
*/
--´íÎóÐÅÏ¢ÈçÏÂʱ£º
--Msg 15281, Level 16, State 1, Line 2
--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as ......
×òÌì½âÎöÁËdblp.xml£¬´æÈëÊý¾Ý¿â£¬Éú³ÉÁËÈô¸ÉÕÅÁÙʱ±í¡£½ñÌìÉÏÎ磬¶ÔÕâЩÁÙʱ±í½øÐд¦Àí£¬È»ºó´æÈëʵÑéÉè¼ÆµÄ±íÖС£Êý¾Ý¿âµÄÊý¾ÝÁ¿±È½Ï´ó£¬50¶àM£¬80¶àÍòÌõ¼Ç¼¡£Òò¶øÖ´ÐÐsqlʱ£¬¾ÍÓöµ½Á˺ܶàÎÊÌâ¡£
1¡¢È¥³ýÖظ´tuple
Ôʼdblp.xmlÖУ¬Í¬Ò»ÂÛÎĵĴæÔÚ¼¸¸öÍêÈ«ÏàͬµÄ&l ......