{"id":2151,"date":"2012-05-27T07:05:15","date_gmt":"2012-05-27T07:05:15","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2151"},"modified":"2012-05-27T07:05:15","modified_gmt":"2012-05-27T07:05:15","slug":"%e5%90%91%e9%93%be%e6%8e%a5%e6%9c%8d%e5%8a%a1%e5%99%a8%e4%b8%8a%e5%90%8c%e6%ad%a5%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e3%80%81%e5%87%bd%e6%95%b0%e3%80%81%e8%a7%86%e5%9b%be","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/05\/27\/%e5%90%91%e9%93%be%e6%8e%a5%e6%9c%8d%e5%8a%a1%e5%99%a8%e4%b8%8a%e5%90%8c%e6%ad%a5%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e3%80%81%e5%87%bd%e6%95%b0%e3%80%81%e8%a7%86%e5%9b%be\/","title":{"rendered":"\u5411\u94fe\u63a5\u670d\u52a1\u5668\u4e0a\u540c\u6b65\u5b58\u50a8\u8fc7\u7a0b\u3001\u51fd\u6570\u3001\u89c6\u56fe"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\n\r\n--EXEC SyncSpToLinkServer @LinkServer=&#39;remoteserver1&#39;, @Name =&#39;perfmonFix&#39;\r\n--\u5411\u94fe\u63a5\u670d\u52a1\u5668tempdb\u4e0a\u540c\u6b65\u5b58\u50a8\u8fc7\u7a0b\u3001\u51fd\u6570\u3001\u89c6\u56fe\r\n--\u5173\u952e\u70b9\uff1a\u5728\u94fe\u63a5\u670d\u52a1\u5668\u4e0a\u6267\u884csql\uff1aEXEC(&#39;exec tempdb.dbo.sp_executesql @statement =N&#39;&#39;select 1&#39;&#39;&#39;) AT remoteserver1\r\nCREATE PROCEDURE SyncSpToLinkServer\r\n@LinkServer VARCHAR(255),\r\n@name VARCHAR(255) --\u5b58\u50a8\u8fc7\u7a0b\u3001\u51fd\u6570\u3001\u89c6\u56fe\u540d\u79f0\r\nAS\r\nBEGIN\r\n DECLARE @sql VARCHAR(max)\r\n SET @sql = &#39;\r\n DECLARE @IsDrop BIT,@IsCreate BIT,@type VARCHAR(100),@tmp VARCHAR(1000)\r\n SET @IsDrop = 0 --\u9ed8\u8ba4\u4e0d\u5220\u9664\u94fe\u63a5\u670d\u52a1\u5668\u4e0a\u7684\u5b58\u50a8\u8fc7\u7a0b\r\n SET @IsCreate = 1 --\u9ed8\u8ba4\u5728\u94fe\u63a5\u670d\u52a1\u5668\u4e0a\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b\r\n\r\n SELECT\r\n @IsCreate = CASE WHEN  rp.modify_date &lt;= lp.modify_date THEN 1 ELSE 0 END,--\u94fe\u63a5\u670d\u52a1\u5668\u5b58\u50a8\u8fc7\u7a0b\u662f\u5426\u4e0d\u662f\u6700\u65b0\r\n @IsDrop = CASE WHEN  rp.modify_date &lt;= lp.modify_date THEN 1 ELSE 0 END,\r\n @type = CASE(rp.type)\r\n  WHEN &#39;&#39;V&#39;&#39; THEN &#39;&#39;VIEW&#39;&#39;\r\n  WHEN &#39;&#39;P&#39;&#39; THEN &#39;&#39;PROCEDURE&#39;&#39;\r\n  WHEN &#39;&#39;FN&#39;&#39; THEN &#39;&#39;FUNCTION&#39;&#39;\r\n  WHEN &#39;&#39;IF&#39;&#39; THEN &#39;&#39;FUNCTION&#39;&#39;\r\n  WHEN &#39;&#39;TF&#39;&#39; THEN &#39;&#39;FUNCTION&#39;&#39; END\r\n FROM &#39; + QUOTENAME(@LinkServer) +&#39;.tempdb.sys.objects rp\r\n JOIN sys.objects  lp ON rp.name = lp.name\r\n WHERE rp.name = &#39; + QUOTENAME(@name,&#39;&#39;&#39;&#39;) +&#39;\r\n \r\n SELECT CASE(@IsDrop) WHEN 1 THEN &#39;&#39;drop&#39;&#39; ELSE &#39;&#39;nodrop&#39;&#39; END,\r\n CASE(@IsCreate) WHEN 1 THEN &#39;&#39;create&#39;&#39; ELSE &#39;&#39;nochanged&#39;&#39; END,@type\r\n\r\n IF @IsDrop = 1\r\n BEGIN\r\n  \r\n  --SET @tmp = &#39;&#39;exec tempdb.dbo.sp_executesql @statement =N&#39;&#39;&#39;&#39; DROP &#39;&#39;  + @type +   &#39; + &#39;  &#39; + QUOTENAME(@name,&#39;&#39;&#39;&#39;) +&#39;\r\n  SET @tmp = &#39;&#39;exec tempdb.dbo.sp_executesql @statement =N&#39;&#39;  + &#39;&#39;&#39;&#39;&#39;&#39;  DROP &#39;&#39;  + @type +   &#39; + &#39;&#39;&#39;  &#39;   + @name +&#39;&#39;&#39;  + &#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;\r\n  PRINT @TMP\r\n  EXEC(@tmp) AT  &#39; + QUOTENAME(@LinkServer) +&#39;\r\n END\r\n IF @IsCreate = 1\r\n BEGIN\r\n  DECLARE @CreateSql VARCHAR(max), @ExecuteSQL VARCHAR(MAX)\r\n  SELECT  @CreateSql =  REPLACE(definition,&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;,&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;)--\u5904\u7406\u5f15\u53f7\r\n  from sys.sql_modules where object_id=object_id(&#39; + QUOTENAME(@name,&#39;&#39;&#39;&#39;) +&#39; )\r\n  IF @CreateSql IS NOT NULL\r\n  BEGIN\r\n   SET @ExecuteSQL = &#39;&#39;EXEC tempdb.dbo.sp_executesql @statement =N&#39;&#39;&#39;&#39;&#39;&#39; + @CreateSql +&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;\r\n   EXEC(@ExecuteSQL)  AT &#39; + QUOTENAME(@LinkServer) +&#39;\r\n  END\r\n END &#39;\r\n\r\n --PRINT @sql\r\n EXEC (@SQL)\r\nEND\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&#8211;EXEC SyncSpToLinkServer @LinkServer=&#39;remoteserver [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2151","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2151","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2151"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2151\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2151"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}