SQL Server 跨库同步数据

最近有个需求是要跨库进行数据同步，两个数据库分布在两台物理计算机上，自动定期同步可以通过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.cnb […]

PowerDesigner中NAME和COMMENT的互相转换,需要执行语句

使用说明: 在【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

PowerDesigner从Sqlserver中反转为带注释的字典及快捷键操作

PowerDesigner的操作经常忘记，所以把常用的功能记录下来备忘。 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 […]

sqlserver的四种分页方式

第一种：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 […]

SQL Server 数值四舍五入，小数点后保留2位

1.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/articl […]

解决SQL Server 2008无法连接127.0.0.1的问题

电脑操作系统是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

SQL语句优化（转载）

一、操作符优化 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>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允 […]

SQLSERVER如何查看索引缺失

当大家发现数据库查询性能很慢的时候，大家都会想到加索引来优化数据库查询性能， 但是面对一个复杂的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_deta […]

SQLSERVER排查CPU占用高的情况

今天中午，有朋友叫我帮他看一下数据库，操作系统是Windows2008R2 ,数据库是SQL2008R2 64位 64G内存，16核CPU 硬件配置还是比较高的，他说服务器运行的是金蝶K3软件，数据库实例里有多个数据库   现象 他说是这几天才出现的，而且在每天的某一个时间段才会出现CPU占用高的情况 内存占用不太高，只占用了30个G CPU占用100% 排查方向   一般排查都是用下面的脚本，一般会用到三个视图sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests

1
2
3
4
5
USE master
GO
--如果要指定数据库就把注释去掉
SELECT * FROM sys.[sysprocesses] WHERE [spid]&gt;50 --AND DB_NAME([dbid])='gposdb'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]&gt;50

看一下当前的数据库用户连接有多少 然后使用下面语句看一下各项指标是否正常，是否有阻塞，这个语句选取了前10个最耗CPU时间的会话

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]&gt;50 AND DB_NAME(der.[database_id])='gposdb'  
ORDER BY [cpu_time] DESC

如果想看具体的SQL语句可以执行下面的SQL语句，记得在SSMS里选择以文本格式显示结果

1
2
3
4
5
6
7
8
--在SSMS里选择以文本格式显示结果
SELECT TOP 10
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]&gt;50  
ORDER BY [cpu_time] DESC

模拟了一些耗CPU时间的动作 &nbsp […]

实战：上亿数据如何秒查？

最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度有从半小时以上(甚至查不出)到秒查的质变。从修改SQL查询语句逻辑到决定创建存储过程实现，花了我3天多的时间，在此总结一下，希望对朋友们有帮助。   数据背景 首先，项目是西门子中国在我司实施部署的MES项目，由于项目是在产线上运作(3 years+)，数据累积很大。在项目的数据库中，大概上亿条数据的表有5个以上，千万级数据的表10个以上，百万级数据的表，很多… (历史问题，当初实施无人监管，无人监控数据库这块的性能问题。ps:我刚入职不久…) 不多说，直接贴西门子中国的开发人员在我司开发的SSRS报表中的SQL语句：

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
select distinct b.MaterialID as matl_def_id, c.Descript, case when right(b.MESOrderID, 12) < '001000000000' then right(b.MESOrderID, 9)  
 
    else right(b.MESOrderID, 12) end  as pom_order_id, a.LotName, a.SourceLotName as ComLot, 
 
    e.DefID as ComMaterials, e.Descript as ComMatDes, d.VendorID, d.DateCode,d.SNNote, b.OnPlantID,a.SNCUST
 
    from
 
    (
 
        select m.lotname, m.sourcelotname, m.opetypeid, m.OperationDate,n.SNCUST from View1 m
 
        left join co_sn_link_customer as n on n.SNMes=m.LotName
 
        where
 
        ( m.LotName in (select val from fn_String_To_Table(@sn,',',1)) or (@sn) = '') and
 
        ( m.sourcelotname in (select val from fn_String_To_Table(@BatchID,',',1)) or (@BatchID) = '')
 
        and (n.SNCust like '%'+ @SN_ext + '%' or (@SN_ext)='')
 
    ) a
 
    left join
 
    (
 
        select * from Table1 where SNType = 'IntSN'
 
        and SNRuleName = 'ProductSNRule'
 
        and OnPlantID=@OnPlant
 
    ) b on b.SN = a.LotName
 
    inner join MMdefinitions as c on c.DefID = b.MaterialID
 
    left join  Table1 as d on d.SN = a.SourceLotName 
 
    inner join MMDefinitions as e on e.DefID = d.MaterialID
 
    where not exists (
 
     select distinct LotName, SourceLotName from ELCV_ASSEMBLE_OPS 
 
    where LotName = a.SourceLotName and SourceLotName = a.LotName
 
    ) 
 
    and (d.DateCode in (select val from fn_String_To_Table(@DCode,',',1)) or (@DCode) = '')
 
    and (d.SNNote  like '%'+@SNNote+'%' or (@SNNote) = '')
 
    and ((case when right(b.MESOrderID, 12) < '001000000000' then right(b.MESOrderID, 9)  
 
    else right(b.MESOrderID, 12) end) in (select val from fn_String_To_Table(@order_id,',',1)) or (@order_id) = '')
 
    and (e.DefID in (select val from fn_String_To_Table(@comdef,',',1)) or (@comdef) = '')
 
    --View1是一个嵌套两层的视图(出于保密性，实际名称可能不同)，里面有一张上亿数据的表和几张千万级数据的表做左连接查询
 
    --Table1是一个数据记录超过1500万的表

这个查询语句，实际上通过我的检测和调查，在B/S系统前端已无法查出结果，半小时，一小时 … 。因为我直接在SQL查询分析器查，半小时都没有结果。 (原因是里面对一张上亿级数据表和3张千万级数据表做全表扫描查询) 不由感慨，西门子中国的素质(或者说责任感)就这样？ 下面说说我的分析和走的弯路(思维误区)， […]

高级T_SQL语法（2005）

排名函数： RANK() 返回结果集的分区内每行的排名，FIELD为排名依据。行的排名是相关行之前的排名数加1.例如有两个并列第一，那么后一个将排名第三，而不是第二。 语法：RANK() OVER([PARTITION BY clause]<ORDER BY clause>) 示例： select *, rank() over (partition by ClassID order by Mark DESC )as rank from dbo.Student 结果： DENSE_RANK()：返回结果集分区中行的排名。在排名中没有任何间断。行的排名等于所讨论的行之前的所有排名数加1.例如有两个并列第一，那么下一个将排名第二，而不是第三。 语法：DENSE_RANK() OVER([PARTITION BY clause]<ORDER BY clause>) 示例： select *, dense_rank() over (partition by ClassID order by MarkDESC ) as rank from dbo.Student 结果： N […]