最近有个需求是要跨库进行数据同步，两个数据库分布在两台物理计算机上，自动定期同步可以通过SQL Server代理作业来实现，但是前提是需要编写一个存储过程来实现同步逻辑处理。这里的存储过程用的不是opendatasource，而是用的链接服务器来实现的。存储过程创建在IP1:192.168.0.3服务器上，需要将视图v_custom的客户信息同步到IP2:192.168.0.10服务器上的t_custom表中。逻辑是如果不存在则插入，存在则更新字段。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
create PROCEDURE [dbo].[p_pm_项目平台客户批量同步到报销平台]( @destserver nvarchar(50), @sourceserver nvarchar(50) ) AS BEGIN SET NOCOUNT ON; --不存在则添加链接服务器,外部查询必须指明IP地址,例如 select * from [IP].[database].[dbo].[table] if not exists (select * from sys.servers where server_id!=0 and data_source=@destserver) begin exec sp_addlinkedserver @server=@destserver end if not exists (select * from sys.servers where server_id!=0 and data_source=@sourceserver) begin exec sp_addlinkedserver @server=@sourceserver end begin try set xact_abort on begin transaction --http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html INSERT INTO [192.168.0.10].[dbCRM].[dbo].[t_custom] (客户ID, 客户名称, 客户简称, 输入码, 查询码, 地址, 录入登录名, 录入时间, 修改登录名, 修改时间, 审批状态ID, 审批状态名称, 是否审批结束, 审批操作时间, 项目管理客商编码, 序号) SELECT A.客户ID,A.客户名称, A.客户简称, dbo.fn_pm_GetPy(A.客户名称), A.客户编号+','+A.客户名称+','+dbo.fn_pm_GetPy(A.客户名称)+','+A.客户简称+','+dbo.fn_pm_GetPy(A.客户简称), A.地址, 'admin', getdate(), null, null, 'D65F87A8-79C8-4D1C-812D-AE4591E056A8', '已审批', 1, A.审批操作时间, A.项目管理客商编码, 0 FROM [dbPM].[dbo].[v_custom] A WHERE A.客户ID NOT IN ( SELECT 客户ID FROM [192.168.0.10].[dbCRM].[dbo].[t_custom]); ----------------------------------存在更新----------------------------------- update A set A.客户名称=B.客户名称, A.客户简称=B.客户简称, A.输入码=dbo.fn_pm_GetPy(B.客户名称), A.查询码=B.客户编号+','+B.客户名称+','+dbo.fn_pm_GetPy(B.客户名称)+','+B.客户简称+','+dbo.fn_pm_GetPy(B.客户简称), A.地址=B.地址, A.修改登录名='admin', A.修改时间=getdate(), A.项目管理客商编码 =B.项目管理客商编码 from [192.168.0.10].[dbCRM].[dbo].[t_custom] A,[dbPM].[dbo].[v_custom] B where A.客户ID=B.客户ID; commit transaction end try begin catch select ERROR_NUMBER() as errornumber,ERROR_MESSAGE() as errormsg,ERROR_LINE() as errorline rollback transaction end catch END |
如果没有正确配置，经常会出现 消息 7391，级别 16，状态 2，过程 xxxxx，第 XX 行 。无法执行该操作，因为链接服务器 "xxxxx" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务。 可以参照如下的配置： 具体可以参看：http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html from:https://www.cnblogs.com/isaboy/p/sql_server_job.html
View Details使用说明: 在【Tools】-【Execute Commands】-【Edit/Run Script】 下。输入下面你要选择的语句即可，也可以保存起来，以便下次使用，后缀为.vbs。 需要注意的问题是：运行语句时必须在Module模式下，如果是导出报表时执行会出现错误提示。 1.Name转到Comment注释字段。一般情况下只填写NAME，COMMENT可以运行语句自动生成。 将该语句保存为name2comment.vbs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
Option Explicit ValidationMode = True InteractiveMode = im_Batch Dim mdl ' the current model ' get the current active model Set mdl = ActiveModel If (mdl Is Nothing) Then MsgBox "There is no current Model " ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then MsgBox "The current model is not an Physical Data model. " Else ProcessFolder mdl End If ' This routine copy name into comment for each table, each column and each view ' of the current folder Private sub ProcessFolder(folder) Dim Tab 'running table for each Tab in folder.tables if not tab.isShortcut then if trim(tab.comment)="" then'如果有表的注释,则不改变它.如果没有表注释.则把name添加到注释里面. tab.comment = tab.name end if Dim col ' running column for each col in tab.columns if trim(col.comment)="" then '如果col的comment为空,则填入name,如果已有注释,则不添加;这样可以避免已有注释丢失. col.comment= col.name end if next end if next Dim view 'running view for each view in folder.Views if not view.isShortcut and trim(view.comment)="" then view.comment = view.name end if next ' go into the sub-packages Dim f ' running folder For Each f In folder.Packages if not f.IsShortcut then ProcessFolder f end if Next end sub |
2.将Comment内容保存到NAME中，comment2name.vbs 实习互换。语句为：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
Option Explicit ValidationMode = True InteractiveMode = im_Batch Dim mdl ' the current model ' get the current active model Set mdl = ActiveModel If (mdl Is Nothing) Then MsgBox "There is no current Model " ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then MsgBox "The current model is not an Physical Data model. " Else ProcessFolder mdl End If Private sub ProcessFolder(folder) On Error Resume Next Dim Tab 'running table for each Tab in folder.tables if not tab.isShortcut then tab.name = tab.comment Dim col ' running column for each col in tab.columns if col.comment="" then else col.name= col.comment end if next end if next Dim view 'running view for each view in folder.Views if not view.isShortcut then view.name = view.comment end if next ' go into the sub-packages Dim f ' running folder For Each f In folder.Packages if not f.IsShortcut then ProcessFolder f end if Next end sub |
from:https://www.cnblogs.com/netsql/archive/2010/05/24/1742734.html
View DetailsPowerDesigner的操作经常忘记，所以把常用的功能记录下来备忘。 1、修改反转过来的字段 PowerDesigner从数据库反转的时候，默认不带注释，需要先进行修改。 输入如下脚本： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 {OWNER, TABLE, S, COLUMN, DTTPCODE, LENGTH, SIZE, PREC, COMPUTE, NOTNULL, IDENTITY, DOMAIN, DEFAULT, COMMENT, ExtIdentityIncrement, ExtIdentitySeed} select u.name, o.name, c.colid, c.name, case when (s.usertype < 100) then s.name else x.name end, c.prec, c.length, c.scale, z.text , case (c.status & 8) when 8 then 'NULL' else 'NOTNULL' end, case (c.status & 128) when 128 then 'identity' else " end, case when (s.usertype < 100) then " else s.name end, v.text, CONVERT(varchar, ISNULL(p.[value], ")) AS text, case (c.status & 128) when 128 then ident_incr(u.name + '.' + o.name) else null end, case (c.status & 128) when 128 then ident_seed(u.name + '.' + o.name) else null end from dbo.sysusers u join dbo.sysobjects o on (o.uid = u.uid and o.type in ('U', 'S', 'V')) […]
View Details第一种：ROW_NUMBER() OVER()方式 select * from ( select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels ) as b where RowId between 10 and 20 —where RowId BETWEEN 当前页数-1*条数 and 页数*条数— 执行结果是： 第二种方式：offset fetch next方式（SQL2012以上的版本才支持：推荐使用 ） select * from ArtistModels order by ArtistId offset 4 rows fetch next 5 rows only --order by ArtistId offset 页数*条数 rows fetch next 条数 rows only —- 执行结果是： 第三种方式：--top not in方式 （适应于数据库2012以下的版本） select top 3 * from ArtistModels where ArtistId not in (select top 15 ArtistId from ArtistModels) ——where Id not in (select top 条数*页数 ArtistId from ArtistModels) […]
View Details1.round() 函数是四舍五入用，第一个参数是我们要被操作的数据，第二个参数是设置我们四舍五入之后小数点后显示几位。 2.numeric 函数的2个参数，第一个表示数据长度，第二个参数表示小数点后位数。 例如： select cast(round(12.5,2) as numeric(5,2)) 结果：12.50 select cast(round(12.555,2) as numeric(5,2)) 结果：12.56 select cast(round(122.5255,2) as numeric(5,2)) 结果：122.53 select cast(round(1222.5255,2) as numeric(5,2)) 结果：报错了！ 原因是：1222.5255，整数位是4，小数位是2，加起来4+2=6，超出了numeric设置的5位，所以为了保险，可以增减numeric的参数，例如numeric(20，2)。 此文系转载后作一调整，转载自：http://blog.csdn.net/caoyuanlang_11/article/details/5410833
View Details电脑操作系统是Win10中文版，新装的英文版SQL Server 2008，纯默认安装，没有做任何改动。 装完SQL Server 2008之后，发现只能用默认的机器名来登录： 如果用127.0.0.1登录就会报出如下的错误： 在网上查询一番之后，发现让开启SQL Server Configuration Manager中的Named Pipes就可以解决问题，然而试了并没有什么卵用。。。 于是开始自己动手尝试解决方案，经过一番尝试，功夫不负有心人啊！终于算是找到了，下面把步骤贴出来： 第一步，开启TCP/IP。因为装完SQL之后这项协议默认是不开通的： 第二步，设置TCP/IP中属性的"IP ALL"的端口为1433。众所周知，SQL Server的默认端口为1433，只是不知道为何，默认安装却没有这一项。 然后，重启SQL服务就可以用127.0.0.1登录上了： from:https://www.cnblogs.com/goldenbiu/p/4726463.html
View Details一、操作符优化 1、IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别： ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。 推荐方案：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。 2、NOT IN操作符 此操作是强列不推荐使用的，因为它不能应用表的索引。 推荐方案：用NOT EXISTS 方案代替 3、IS NULL 或IS NOT NULL操作（判断字段是否为空） 判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。 推荐方案：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。 4、> 及 < 操作符（大于或小于操作符） 大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。 5、LIKE操作符 LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。 一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。 6、UNION操作符 UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如： select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。 推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。 select * from gc_dfys union all select * from ls_jg_dfys 二、SQL书写的影响 1、同一功能同一性能不同写法SQL的影响。 如一个SQL在A程序员写的为 Select * from zl_yhjbqk B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀） C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名） D程序员写的为 Select * […]
View Details当大家发现数据库查询性能很慢的时候，大家都会想到加索引来优化数据库查询性能， 但是面对一个复杂的SQL语句，找到一个优化的索引组合对人脑来讲，真的不是一件很简单的事。 好在SQLSERVER提供了两种“自动”功能，给你建议，该怎么调整索引 第一种是使用DMV 第二种是使用DTA (database engine tuning advisor) 数据库引擎优化顾问 这篇文章主要讲第一种 从SQL2005以后，在SQLSERVER对任何一句语句做编译的时候，都会去评估一下， 这句话是不是缺少什么索引的支持，如果他认为是，他还会预估，如果有这麽一个索引 他的性能能提高多少 SQLSERVER有几个动态管理视图 sys.dm_db_missing_index_details sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_columns(index_handle) sys.dm_db_missing_index_details 这个DMV记录了当前数据库下所有的missing index的信息,他针对的是SQLSERVER从启动以来所有运行的语句， 而不是针对某一个查询。DBA可以看看，哪些表格SQLSERVER对他是最有“意见”的 以下是这个DMV的各个字段的解释： 1、index_handle:标识特定的缺失索引。该标识符在服务器中是唯一的。index_handle 是此表的密钥 2、database_id :标识带有缺失索引的表所驻留的数据库 3、object_id :标识索引缺失的表 4、equality_columns:构成相等谓词的列的逗号分隔列表 即哪个字段缺失了索引会在这里列出来（简单来讲就是where 后面的筛选字段）， 谓词的形式如下：table.column =constant_value 5、inequality_columns :构成不等谓词的列的逗号分隔列表，例如以下形式的谓词：table.column > constant_value “=”之外的任何比较运算符都表示不相等。 6、included_columns:用于查询的涵盖列的逗号分隔列表（简单来讲就是 select 后面的字段）。 7、statement:索引缺失的表的名称 比如下面这个查询结果 那么应该创建这样的索引
|
1 |
1 CREATE INDEX idx_SalesOrderDetail_test_ProductID_IncludeIndex ON SalesOrderDetail_test(ProductID) INCLUDE(SalesOrderID) |
在ProductID上创建索引，SalesOrderID作为包含性列的索引 注意事项: 由 sys.dm_db_missing_index_details 返回的信息会在查询优化器优化查询时更新，因而不是持久化的。 缺失索引信息只保留到重新启动 SQL Server 前。如果数据库管理员要在服务器回收后保留缺失索引信息， 则应定期制作缺失索引信息的备份副本 sys.dm_db_missing_index_columns(index_handle) 返回与缺少索引（不包括空间索引）的数据库表列有关的信息，sys.dm_db_missing_index_columns 是一个动态管理函数 字段解释 index_handle：唯一地标识缺失索引的整数。 sys.dm_db_missing_index_groups 返回有关特定缺失索引组中包含的缺失索引（不包括空间索引）的信息 sys.dm_db_missing_index_group_stats 返回缺失索引组的摘要信息，不包括空间索引 这个视图说白了就是预估有这麽一个索引，他的性能能提高多少 有一个字段比较重要: avg_user_impact: 实现此缺失索引组后，用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组，则查询成本将按此百分比平均下降。 就是说，增加了这个缺失索引，性能可以提高的百分比 下面是MSDN给出的示例，缺失索引组句柄为 2
|
1 2 3 4 5 6 7 8 9 10 |
1 --查询提供缺失索引的数据库、架构和表的名称。它还提供应该用于索引键的列的名称 2 USE [AdventureWorks] 3 GO 4 SELECT migs.group_handle, mid.* 5 FROM sys.dm_db_missing_index_group_stats AS migs 6 INNER JOIN sys.dm_db_missing_index_groups AS mig 7 ON (migs.group_handle = mig.index_group_handle) 8 INNER JOIN sys.dm_db_missing_index_details AS mid 9 ON (mig.index_handle = mid.index_handle) 10 WHERE migs.group_handle = 2 |
示例代码：
|
1 2 3 4 5 6 |
1 USE [AdventureWorks] --要查询索引缺失的数据库 2 GO 3 SELECT * FROM sys.[dm_db_missing_index_details] 4 SELECT * FROM sys.[dm_db_missing_index_groups] 5 SELECT * FROM sys.[dm_db_missing_index_group_stats] 6 SELECT * FROM sys.[dm_db_missing_index_columns](1) --1 ：1是根据dm_db_missing_index_details查出来的 |
[…]
View Details