健忘症補救株式會社

展開linked server時,出現下列錯誤訊息：

The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'.

Solution：

USE [master]

有一台沒上去看過的db server,因為空間不足,需要去壓縮log file,

但是查看db的Properites時,出現

Property Owner is not available for Database '[db_name]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

原因應該是這個db之前設置的owner可能是某人的AD帳號,但是那個人離職了.

在SQL SERVER 2008 R2要啟動Activity Monitor,

需在Server Name上Right click mouse -> Activity Monitor.

若是要給予某個帳號有執行Activity Monitor的權限,

SSMS UI：

建立db mirror時,出現

Error 1418

The server network address "tcp://[server name]:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

已經確定principal & mirror兩方都是相同版本的SQL SERVER,

CLR(Common Language Runtime)在SQL SERVER上預設沒開啟,

啟用clr enabled選項：

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;    --立即變更,不需等到重啟服務

Event Viewer一直持續出現：

The log shipping secondary database XXX.YYY has restore threshold of 45 minutes and is out of sync. No restore was performed for 106 minutes. Restored latency is 15 minutes. Check agent log and logshipping monitor information.

Solution：

將secondary database主機上的Windows System DateTime & TimeZone更改成跟primary database主機上的相同,

設置Link Server後,出現了data access error

開啟SSMS,

Server Objects->Linked Servers->{link server name}->Properties->Server Options

將Data Access的值改為True.

想要讓帳號有執行stored procedure的權限,

但是又不想讓這個帳號可以 read/write,

USE [db_name]

GO

在做資料庫轉移時,

要重建db server上的所有登入帳號事件頭痛的事,

找到Microsoft上的解決方式,

前提是,A & B這兩台主機是在同一個domain中,這樣他們的SID才會是相同的,

1.在A Server上的SSMS上輸入:

建立Link Server,透過這個Link Server執行Stored Procedure時,

出現Server 'XXXXXX' is not configured for RPC.

解決方式：

exec sp_serveroption @server='XXXXXX', @optname='RPC', @optvalue='True'
exec sp_serveroption @server='XXXXXX', @optname='RPC Out', @optvalue='True'

[Start the instance of SQL Server in master-only recovery mode]

Default instance:

    NET START MSSQLSERVER /f /T3608

Named instance:

    NET START MSSQL$instancename /f /T3608

[use sqlcmd commands]

ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

參考資料

在SQL SERVER 2008之前,都有Sample Database(AdventureWorks),

到了2008 R2之後,就沒有了,可以另外安裝,

微軟提供下載點

安裝教學

修改設置時,出現database lock 或是 database is in use

解決方式：

在database上滑鼠右鍵 - > properties

Server中的Event Log持續出現:

Replication-Replication Distribution Subsystem agent (null) failed. The publication 'xxxxxx' does not exist.

把Job中相關的Replication Job刪除,就解決了.

安裝完SQL SERVER 2008 R2之後,

由舊的db server (SQL SERVER 2008)上將 Job 匯入到2008 R2,

要開啟Job的Edit時,出現了

Creating an instance of the COM component with CLSID {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX} from the IClassFactory failed due to the following error: c001f011

試了三台機器都出現,真冏...

解決方式：

Linked Servers->Providers->OraOLEDB.Oracle->右鍵->Properties->Provider options

將"Allow inprocess"勾選,儲存.

解決收工...

有某個大系統,如果User有帳號,又有SSMS,

如果User不想從系統登入,直接用SSMS給你溜進來,

這樣竟然也是可以,這是世界知名大廠的設定嗎??

阿知,那個系統我又不熟,也沒安裝過,

不過既然有人想先把這個洞補起來,那就只好想想辦法.....

--查Table使用多少空間

use [db_name]

GO

exec sp_spaceused 'table_name'

SSMS中連線畫面的服務器清單越來越多,覺得不順眼想清除的話,

在2008 or 2008 R2中,把SqlStudio.bin這個檔刪除,

位置在：

 C:\Users\{%UserAccount%}\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

SELECT name FROM msdb.dbo.sysjobs ORDER BY name