May 26, 2008

DB document tool - SqlSpec

SqlSpec is from Elsasoft. It works well for SQL Server, better than SQL Doc, SQL Document Tool according to my evaluation.
However, you may not see information you are interested in Oracle, but still good in the schema level.
Here is the link. You can download an evaluation version for your trial.
just $149 for a single user, I think it is pretty worth.
http://www.elsasoft.org/


Examples of Connection String:
For SQL Server
server="serverName\instanceName"; database=dbName; user id=userName; pwd=yourPassword
For Oracle
Provider=OraOLEDB.Oracle;Data Source=tnsName;User Id=userName;Password=yourPassword

May 16, 2008

nmefwmi.exe issue in Oracle 11g on Vista

A window keeps pop-up saying "nmefwmi.exe has stopped working".

It is a known bug in Oracle, should be fixed in 11.1.0.7 release according to metalink.

Suggested Workaround prior to 11.1.0.7:

Check Compatibility in properties of setup.exe to "Run this program as a administrator".

May 15, 2008

Get User-defined Database Objects in MS SQL

I was trying to get all user-defined objects in SQL Server.
And tried to make it compatible with 2000,2005 and 2008.

Seems it wasn't easy as in Oracle, especially for stored procedures and functions. If you know a way to distinguish user-defined procedure and system procedure, please share.

select count(*) as 'UserTables'
from sysobjects
where type in ('U')

select count(*) as 'UserIndexes'
from sysindexes i
INNER JOIN sysobjects AS o ON i.id=o.id
where o.type='U'

select count(*) as 'UserTriggers'
from sysobjects o1
INNER JOIN sysobjects AS o2 ON o1.parent_obj=o2.id
where o1.type in ('TR', 'TA')
AND o2.TYPE='U'

select count(*) as 'UserViews'
from sysobjects
where type in ('V')
and name not like 'sys%'

select count(*) as 'StoredProcedures'
from sysobjects
where type in ('P','PC','X')

select count(*) as 'Functions'
from sysobjects
where type in ('FN','FS','FT','TF','AF','IF')


Reference: sysobjects.type


Common Object Types in SQL 2000, 2005 and 2008

C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
IF = SQL inline table-valued function
P = SQL stored procedure
PK = PRIMARY KEY constraint
S = System base table
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

Common Object Types in SQL 2005 and 2008

AF = Aggregate function (CLR)
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
IT = Internal table
PC = Assembly (CLR) stored procedure
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger

New Object Types in SQL 2008

PG = Plan guide

May 12, 2008

SQL Server 2000 Back Up falied to shared file system

Invalid Path when specifying BackUp location to a shared file system in SQL Server 2000.

Two things may need to be checked:

1. If SQL Server and Agent services are running under a domain service account.

I encountered this situation. After changing the services from local system to the designated domain account. The backup was successful.

2. Verify if domain account has the right permissions on the shared folder.

May 8, 2008

How Data Got Moved and Purged in MOM 2005 Database

1. Transfer data from OnePoint to SystemCenterReporting DB

1.1 Scheduled Task: SystemCenterDTSPackageTask
Schedule: 1am everyday

Check the task if successfully by looking at the field LastResult in Control Panel->Scheduled Tasks.

0x0 - successed
0xffff - failed

When failed, you can check Application Log in Control Panel->Administration Tools->Event Viewer.

If it failed with Expired Timeout, try to increase query timeout in MOM SQL Server to aovid this error.

1.2 Check the last completely run of DTS job in OnePoint database

select TimeDTSLastRan from OnePoint.dbo.reportingsettings

1.3 Check DataRetentionDays in OnePoint DB and see how many days data requires to be moved.

select * from OnePoint.dbo.groomingsettings

1.4 May need to add /latency to DTS job to speed up the large data moving if it has been failed a few days.

ex: DataRetentionDays =3 and TimeDTSLastRan= 3 days ago,
could set /latency:3

C:\Program Files\Microsoft System Center Reporting\Reporting\MOM.Datawarehousing.DTSPackageGenerator.exe" /silent /latency:3 /srcserver:mom_server_name /srcdb:OnePoint /dwserver:mom_dw_server_name /dwdb:SystemCenterReporting /product:"Microsoft Operations Manager

2. Purge the transferred data in OnePoint DB.

Run SQL Job "MOMX Partitioning And Grooming" to purge the transferred data in OnePoint DB.


3. Run SCDWGroomJob to purge Reporting DB.

3.1 Check Reporting Grooming Day

USE SystemCenterReporting
GO

SELECT CS.TableName AS TableName, WCS.GroomDays AS GroomDays
FROM SMC_Meta_WarehouseClassSchema WCS
JOIN SMC_Meta_ClassSchemas CS
ON CS.ClassID = WCS.ClassID
WHERE WCS.MustBeGroomed = 1
ORDER BY WCS.GroomDays DESC


3.2. Set Reporting Grooming day
--------------------------------------------
USE SystemCenterReporting
GO
Declare @Groomdays int
-- Retain data for 145 days
Select @Groomdays = 145
exec p_updateGroomDays 'SC_AlertFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertHistoryFact_Table', @Groomdays
exec p_updateGroomDays 'SC_AlertToEventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventFact_Table', @Groomdays
exec p_updateGroomDays 'SC_EventParameterFact_Table', @Groomdays
exec p_updateGroomDays 'SC_SampledNumericDataFact_Table', @Groomdays

3.3 Start the reporting grooming job (called SCDWGroomJob) in the SQL Agent jobs.

Or run the actual stored procedure:

EXECUTE dbo.p_GroomDatawarehouseTables

If reporting grooming job has been failed for a while, you may have to set Grooming Day to trim the data gradually out of the reporting database.

Usually this job is time-consuming, watch it out after resetting the grooming day in case of the job failure.