享受代码

1、
问题描述：
分发代理无法在“C:Program FilesMicrosoft SQL Server100COM”目录中创建临时文件。系统返回的错误代码为 5。 (源: MSSQL_REPL，错误号: MSSQL_REPL21100)
 
解决办法：
在订阅服务器上的SQLSERVER安装根文件夹“C:Program FilesMicrosoft SQL Server100COM”，为分发代理账户（xxxdistagentadmin)添加修改的权限。
 
 
2、
问题描述：
正在连接到分发服务器，代理消息代码 14080。 远程服务器不存在，未被指定为有效的发布服务器，或您无权查看可用的发布服务器。
 
解决办法：
在分发服务器上创建分发代理登录名和账户；在发布属性下的访问列表中，添加分发代理账户。
 
 
3、
问题描述：
所有者 xxxsql_admin_cc (拥有作业 )没有服务器访问权限。
 
解决办法：
将该作业的所有者更改为sa，重启分发代理；在发布属性下的访问列表中，添加分发代理账户。
 
 
4、
问题描述：
由于出现操作系统错误 3，进程无法读取文件。 (源: MSSQL_REPL，错误号: MSSQL_REPL20024)
 
解决办法：
将复制模式设置为推送订阅；在FTP共享文件夹上添加分发代理启动账户和FTP用户读取权限，并共享该文件夹。
 
 
5、
问题描述：
对带有“$”符号的共享文件夹的访问被拒绝。
 
解决办法：
更改分发服务器属性，重新设置网络共享文件夹路径为不带“$”；在发布属性中，不使用默认快照文件夹，将快照生成到其他有访问权限的文件夹中； 修改ftp站点属性，允许任何人访问，重启IIS服务。
 
6、
问题描述：
用服务器名连接服务器的时候报无法生成SSPI上下文，但是用IP地址可以用客户端登陆。
 
解决办法：
在hosts文件中增加服务器的IP地址和服务器名。
 
7、
问题描述：
应用复制的命令时在订阅服务器上找不到该行(源: MSSQLServer，错误号: 20598)
 
解决办法：
　　（1）修改配置文件，然后新建一个新的配置文件，新建后修改-SkipErrors这一项，在“值”栏后输入20598，确定，然后使用新建的这个配置文件。重启代理。 
（2） 使用sp_setsubscriptionxactseqno 存储过程，跳过一个或多个导致错误的事务。
（3）在发布中取消该表的复制，然后再添加复制。
 
 
8、
问题描述：
快照代理无法启动，对路径“”的访问被拒绝。
 
解决办法：
更改快照代理启动账户为有权限的账户，或更改快照代理作业为sa ，主要是发布包中有推送订阅，也有请求订阅。
 
 
9、
问题描述：
代理消息代码 20033。 进程无法从 FTP 站点检索文件。
 
解决办法：
主要是FTP问题，发布和订阅要通过公网连接，不采用请求订阅，改为推送订阅模式。
 
 
10、
问题描述：
消息 21618，级别 16，状态 1，过程 sp_MSreplagentjobexists，第 116 行
发布服务器不存在。若要查看发布服务器的列表，请使用存储过程 sp_helpdistpublisher。
解决办法：
发布的数据库是拷贝其他数据库的，包括复制的一些系统表也复制过来了，在发布库上执行如下代码：
Exec sp_removedbreplication @dbname =<dbname>
 
 
11、
问题描述：
尝试的命令:if @@trancount > 0 rollback tran(事务序列号: 0x000019AB0001E7A7019200000000，命令 ID: 7275)，进程无法从 FTP 站点“DB-DISTBACK01”检索文件“”。 (源: MSSQL_REPL，错误号: MSSQL_REPL20033)
 
解决办法：
使用sp_change_subscription_properties更改订阅属性，不使用ftp。
 
 
12、
问题描述：
进程无法在“”上执行“sp_repldone/sp_replcounters”。 (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
 
解决办法：
在发布数据库上执行 sp_replflush存储过程。
 
 
13、
问题描述：
进程无法在“”上执行“sp_replcmds”。
 
 
解决方法：
权限问题：将数据库所有者改为sa或其它配置分发时指定的账号。即账号要在数据库上有dbo_owner权限
ALTER AUTHORIZATION ON DATABASE::[OA] TO [sa]
 

--查询指定对象引用的对象或列，如存储过程引用的表
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'bdorder_sel');

--查询引用指定对象的对象，如引用某表的存储过程
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    o.type_desc AS referencing_desciption, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referenced_id = OBJECT_ID(N'dbo.bdorder');


--查询引用其它数据库的对象,连接服务器

SELECT OBJECT_NAME (referencing_id),referenced_server_name,referenced_database_name,

sys.sql_expression_dependencies

sqlserver的sa密码记得，但windows的忘记了，通过如下方法增加windows权限账号。

EXEC [sys].[sp_configure] @configname = 'show advanced options', -- varchar(35)
@configvalue = 1 -- int
RECONFIGURE WITH override
GO
--如果没有就开启
EXEC [sys].[sp_configure] @configname = 'Ole Automation Procedures', -- varchar(35)
@configvalue = 1 -- int
RECONFIGURE WITH override
GO

--添加一个hack用户，所属用户组为Administrators
DECLARE @shell INT
EXEC SP_OAcreate 'wscript.shell',@shell out
EXEC SP_OAMETHOD @shell,'run',null, 'net user hack2 123 /add'
EXEC SP_OAMETHOD @shell,'run',null, 'net localgroup Administrators hack2 /add'

指定了默认值
若增加了 not null限制，则自动将所有的新列值赋予默认值。
或者不加 not null限制，但指定 with values修饰，也会自动将所有的新列值赋予默认值。
只有指定了默认值，既没有not null,有没有with values时，才只将新insert的列赋值。
参考：column_definition

USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.test','U') IS NOT NULL
    DROP TABLE dbo.test;
GO

--Create the test table
CREATE TABLE dbo.test ( Col1 INT );
GO

--Insert some test data
INSERT INTO dbo.test ( Col1 ) 
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5;
GO

--Add a new, NOT NULL column with default constraint
--Use with WITH VALUES clause to also populate the columns
ALTER TABLE dbo.test 
    ADD col2 INT NOT NULL DEFAULT(0),
    col3 INT  DEFAULT(0) WITH VALUES,
    col4 INT DEFAULT(0)
    ;
GO

SELECT *
FROM test

需求：数据库服务器经常变动，每次管理的时候都要更改IP，特别在管理人员比较多的时候，大家都在改IP，比较复杂一些。
解决：做一个域名，解析到服务器IP，大家访问数据库时用域名而不是IP访问。这样，在服务器IP更改时，只需将域名解析更改一下，就全部都更改了。
注：sqlserver会获取域名解析后的IP，当作IP访问，只是多增加了一道解析操作。

查看job日志，发现只有当天的部分，其它的没有了。
原因是默认job日志记录是1000条，修改下即可。
SQLServer代理-属性-历史记录中调整，或者使用如下sql

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000
GO

SELECT  
dbs.[name] AS DatabaseName 
FROM    master.sys.databases dbs
where dbs.database_id <> 2  
AND dbs.[name] NOT IN		
	(	SELECT 
		bus.database_name as 'name'
		FROM msdb.dbo.backupset bus
		INNER JOIN msdb.dbo.backupmediafamily bume ON bus.media_set_id = bume.media_set_id
		WHERE 
		dbs.database_id <> 2 
		AND dbs.is_in_standby = 0 
		AND dbs.source_database_id IS NULL 
		AND dbs.[state] <> 1 
		AND bus.backup_finish_date >= DATEADD(d, -7, GETDATE())
	
	)

--主键表
SELECT TOP 50 * 
FROM sys.key_constraints

--没有主键的表
SELECT TOP 50 * 
FROM sys.tables a
WHERE a.object_id NOT IN(
	SELECT parent_object_id
	FROM sys.key_constraints
)
    
    
	SELECT TOP 50 * 
	FROM sys.parameters 

--查询计算列
SELECT TOP 50 * 
FROM sys.computed_columns 

Select  Object_Name(id.object_id) As [table_name]
    , id.name As [column_name]
    , t.name As [data_type]
	,seed_value
	,increment_value
    , Cast(id.last_value As bigint) As [last_value]
    , Case 
        When t.name = 'tinyint'   Then 255 
        When t.name = 'smallint'  Then 32767 
        When t.name = 'int'       Then 2147483647 
        When t.name = 'bigint'    Then 9223372036854775807
        End As [max_value]
From sys.identity_columns As id
Join sys.types As t
    On id.system_type_id = t.system_type_id
Where id.last_value Is Not NULL
ORDER BY last_value DESC

In this blog I would like to demonstrate a scenario where users want to move the changes between the tables in two different databases.
Let’s say we would like to compare and move the changes between the databases for some tables using T-SQL
The below example talks about moving the changes between the two databases in the same instance However the same can be extended across instances if you use linked server or SSIS packages.
Also we can write queries to move the DML changes from source to destination and vice versa. Let’s look at the below example
 
 
--creating a source database
create database source
 
--create source  table
use source
 
create table Product(
 
Pid int  primary key ,
Pname varchar (10),
Pcost float,
source int ,
location varchar(10))
 
--create destination database
 
create database Destination
 
--create destination table
 
use Destination
 
create table Product(
 
Pid int  primary key ,
Pname varchar (10),
Pcost float,
source int,
location varchar(10) )
 
--Insert data into source table
use source
 
insert into product values  ( 1,'rdbms',100,200,'ind')
insert into product values  ( 2,'dbm',20,100,'US')
insert into product values  ( 3,'arp',30,250,'UK')
insert into product values  ( 4,'mqr',40,100,'ind')
insert into product values  ( 5,'ttp',50,200,'us')
 
-- EXCEPT returns any distinct values from the left query that are not also  found on the right query.
--The below query gives us difference between sourec and destination
-- we can use except ket word to look at selected columns or entire table
 
select * from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
--updating destination table with the changes from source
 
insert into [Destination].dbo.product
select * from source.dbo.product
except
select * from [Destination].dbo.product
 
-- We see that the destination is populated with all the rows from source
 
select * from [Destination].dbo.product
 
--Now lets update the row in the source and see how it works
 
update source.dbo.product
set pname='sql'
where pid =1
--run the below query
select * from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
-- the result gives us the only row which was changed in source
 
-- loading the deiffrences to a temp table
select * into #temp from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
--updating the destination with changes
 
update [Destination].dbo.product
set [Destination].dbo.product.pname= #temp.pname
from #temp where #temp.pid= [Destination].dbo.product.pid
 
--lets run the statement to see the difference between these tables
 
select * from source.dbo.product
 
except
 
select * from [Destination].dbo.product
 
--lets see how the delete works
 
delete from source.dbo.product where pid= 2
 
-- to see the rows which were deleted at source or inserted at destination only
select * from [Destination].dbo.product
except
select * from source.dbo.product
--based on the application logic either we will insert it back in the source or delete from dest
 
--lets say we want to delete from dest as well ,
 
select * into  #temp from [Destination].dbo.product
except
select * from source.dbo.product
 
delete from [Destination].dbo.product where pid in ( select pid from #temp)
 
-- Now lets see that difference between the tables
select * from [Destination].dbo.product
except
select * from source.dbo.product
 

来自:How to compare the rows of two tables and fetch the differential data.

场景：从发布库上将一数据库移到另一服务器，在对表改名时提示“该表已为了复制而被发布，所以无法重命名。”
原因：移的数据库原来参与了复制分发，需要在新服务器上去掉。
方法：sp_removedbreplication 'mydb'

sp_removedbreplication
该存储过程在发布服务器的发布数据库中或在订阅服务器的订阅数据库中执行。 该过程将从执行它的数据库中删除所有复制对象，但它不会从其他数据库（例如，分发数据库）中删除对象
只有当其他删除复制对象的方法都失败后，才应当使用此过程。

在设置数据库状态时
with子句忽略，则当数据库中存在任何锁时，ALTER DATABASE 语句将无限期等待。
with NO_WAIT ，指定若有事务还没有提交，则立即失败，不再进行下面的操作
with ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE:指定是在指定秒数之后回滚还是立即回滚。

注意：并非所有数据库选项都使用 WITH <termination> 子句，也不是所有数据库选项都能结合其他选项指定。

ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

ALTER DATABASE SET 选项 (Transact-SQL)