MSSQL DBA 精华总结[处理对象名称中特殊字符]

Cherish_j_wang · 发表于 2012-3-13 21:29

今天分享至此结束，希望大家多多提意见

hwtong · 发表于 2012-3-14 17:27

Cherish_j_wang 发表于 2012-3-13 21:29
今天分享至此结束，希望大家多多提意见

加油这周末结束再给你发个红包

Cherish_j_wang · 发表于 2012-3-14 20:08

hwtong 发表于 2012-3-14 17:27
加油这周末结束再给你发个红包

发啥子红包嘛~~

Cherish_j_wang · 发表于 2012-3-14 21:09

本帖最后由 Cherish_j_wang 于 2012-3-14 21:17 编辑

MSSQL DBA 精华总结

之利用tablediff生成Insert语句

背景

MSSQL DBA有时候需要将某个表的数据来生成Insert语句。在MSSQL SSMS中没有提供这个直接生成INSERT脚本的方法(当然我们可以使用SMO来自己写)，本文是使用Replication修复工具tablediff来完成这个工作。

PS：其实论坛中也有人问到这个问题，本次就在这里将其解决吧。帖子地址如下：

http://www.itpub.net/forum.php?mod=viewthread&tid=1586574&page=1#pid19133473

方法

Step1.设置环境变量：如果你已经设置好了，请跳过这个步骤

在Windows的Path路径中添加下面的地址(针对MSSQL 2008)：C:\Program Files\Microsoft SQL Server\100\COM。

如果没有设置path环境变量或者设置错误，在使用tablediff的时候，会报告下面的错误：

'tablediff'is not recognized as an internal or external command,operable program or batchfile.

Step2.创建测试表并插入数据

use test
go
--creating test table
if OBJECT_ID('dbo.test_tablediff1','u') is not null
drop table dbo.test_tablediff1
go
create table dbo.test_tablediff1
(
id int not null primary key
,name varchar(20) not null
);
--data init.
insert into dbo.test_tablediff1
select 1,'AA'
UNION ALL
select 2,'BB'
;
go

复制代码

Step3.创建与测试表结构相同的空表

select top 1 *
into dbo.test_tablediff1_bak
from dbo.test_tablediff1
where 1 = 2

复制代码

Step4.构造tablediff批处理文件

例如将bat文件放到D:\temp\ tablediff_test.bat，内容如下：

tablediff /sourceserver "LCMIS005" /sourcedatabase "test" /sourceschema "dbo" /sourcetable "test_tablediff1" /sourceuser "sa" /sourcepassword "1qazxsw2!@#" /destinationserver "LCMIS005" /destinationdatabase "test" /destinationschema "dbo" /destinationtable "test_tablediff1_bak" /destinationuser "sa" /destinationpassword "1qazxsw2!@#" /bf "3000" /f "LCMIS005_test_dbo.test_tablediff1.sql" /t "6000"

复制代码

Step5.执行批处理文件

双击该批处理文件，直接执行就行了

批处理执行完毕，会有类似如下的提示：

Table[test].[dbo].[test_tablediff1] on LCMIS005 and Table [test].[dbo].[test_tablediff1_bak]on LCMIS005 have 2 differences.

Fix SQL written toLCMIS005_test_dbo.test_tablediff1.0.sql.

Err id Col

Src. Only 1

Src. Only 2

The requested operation took0.1200069 seconds.

Step6.查看生成的.sql文件

执行批处理后，生成的文件为D:\temp\ LCMIS005_test_dbo.test_tablediff1.0.sql。内容如下：

-- Host: LCMIS005
-- Database: [test]
-- Table: [dbo].[test_tablediff1_bak]
INSERT INTO [dbo].[test_tablediff1_bak] ([id],[name]) VALUES (1,N'AA')
INSERT INTO [dbo].[test_tablediff1_bak] ([id],[name]) VALUES (2,N'BB')

复制代码

Step7.替换生成的sql文件中的表名称

这个使用SSMS的查找替换功能(ctrl+H)，将[test_tablediff1_bak]替换为[test_tablediff1]，保存即可：

-- Host: LCMIS005
-- Database: [test]
-- Table: [dbo].[test_tablediff1]
INSERT INTO [dbo].[test_tablediff1] ([id],[name]) VALUES (1,N'AA')
INSERT INTO [dbo].[test_tablediff1] ([id],[name]) VALUES (2,N'BB')

复制代码

说明

1. 请大家思考，如果我们需要生成满足某个条件的INSERT语句，怎么采用类似的方法处理呢？比如：请生成满足下面语句条件的INSERT语句

select *
from dbo.test_tablediff1
where id = 1;

复制代码

2. 如果测试表结构稍微做如下修改，我们的tablediff方法是否凑效？

if OBJECT_ID('dbo.test_tablediff1','u') is not null
drop table dbo.test_tablediff1
go
create table dbo.test_tablediff1
(
id int not null primary key
,[newid] uniqueidentifier not null
constraint df_newid default(newid())
,name varchar(20) not null
,[desc] nvarchar(max) null
);
insert into dbo.test_tablediff1
select 1,NEWID(),'AA','Just for testing'
union all
select 2,NEWID(),'BB','Just for testing'
;
go

复制代码

Cherish_j_wang · 发表于 2012-3-16 21:11

今天的分享就解决上次提出来的两个问题：
1.如果需要生成带条件的Insert语句的话，我们只需要和目的端的一个View进行tablediff即可，那么我们建立如下视图

create view dbo.V_testTableDiff
as
select *
from dbo.test_tablediff
where id = 1;

复制代码

2.如果表中含有时间戳或者大字段数据类型，我们使用tablediff是无法比出来的。其实很好理解，MSSQL是不会去比较一个2GB大小的数据的。所以，MSSQL直接就不支持大数据类型。

itfy · 发表于 2012-3-16 23:03

谢谢感觉不多啊多分享下啊！！！！

Cherish_j_wang · 发表于 2012-3-17 20:15

itfy 发表于 2012-3-16 23:03
谢谢感觉不多啊多分享下啊！！！！

你有点贪心哦~~虽然我没有去数到底有多少个主题，但也应该也不少了哦

Cherish_j_wang · 发表于 2012-3-17 20:24

本帖最后由 Cherish_j_wang 于 2012-3-17 20:25 编辑

MSSQL DBA 精华总结

之MSSQL查看IP和端口号

使用MSSQLServer查看主机名和MSSQL Server的端口号方法的脚本如下：

DECLARE
@tcpEndport VARCHAR(20)
,@regeditKey VARCHAR(100)
;
--------============open xp_cmdshell
IF EXISTS (
SELECT TOP 1 *
FROM sys.configurations WITH (NOLOCK)
WHERE name='xp_cmdshell' and value=0)
BEGIN
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
END
--------============end open xp_cmdshell
declare @tb_output TABLE
(
OutPut NVARCHAR(max)
)
INSERT INTO @tb_output
exec sys.xp_cmdshell 'ipconfig'
IF @@VERSION LIKE 'Microsoft SQL Server 2000%'--SQLServer 2000
BEGIN
SET @regeditKey= N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'
EXEC master.dbo.xp_regread
@rootkey='HKEY_LOCAL_MACHINE'
,@key= @regeditKey
,@value_name='TcpPort'
,@value=@tcpEndport OUTPUT
SELECT @tcpEndport
END
ELSE IF (@@VERSION LIKE 'Microsoft SQL Server 2005%' OR @@VERSION LIKE 'Microsoft SQL Server 2008%')
BEGIN
IF @@Servicename='MSSQLSERVER'--Default instance
BEGIN
SET @regeditKey='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'
END
ELSE
BEGIN
SET @regeditKey= 'SOFTWARE\Microsoft\Microsoft SQL Server\'+@@Servicename+'\MSSQLServer\SuperSocketNetLib\Tcp'
END
EXEC master.dbo.xp_regread
@rootkey='HKEY_LOCAL_MACHINE'
,@key= @regeditKey
,@value_name='TcpPort'
,@value=@tcpEndport OUTPUT
SELECT
ServerName = @@SERVERNAME
,Ipadd = OutPut
,@tcpEndport AS tcp_Endpoint
FROM @tb_output
WHERE OutPut LIKE '%IP%Address%'
END
ELSE
SELECT 'Unknown'

复制代码

我的结果集：

ServerName Ipadd tcp_Endpoint

LCMIS005 IPv4 Address. . . . . . . . . . . : 192.168.1.100 1433

hwtong · 发表于 2012-3-19 08:32

Cherish_j_wang 发表于 2012-3-14 20:08
发啥子红包嘛~~

转账转出 pub币 -1000 向 Cherish_j_wang 进行积分转帐 2012-03-19 08:31

crystal123789 · 发表于 2012-3-19 11:03

支持！好贴

[精华] MSSQL DBA 精华总结[处理对象名称中特殊字符]

浏览过的版块