To Work With

Registration SYSTEM DSN via command prompt
"C:\WINDOWS\regedit.exe" /s "\\Dfcgsts22\d$\temp\DFCGSTS_CBMSProd.reg"
"C:\WINDOWS\system32\odbcad32.exe" configsysdsn "SQL Server" "DSN=CBMSProd"
http://www.itechtalk.com/thread3075.html

http://rssexplorer.codeplex.com/
---------
SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName FROM sys.master_files AS mf
------------
use master;
select name, suser_sname(owner_sid) from sys.databases;
---

USE msdb;
SELECT
 A.Name AS JobName,
 B.name AS JobOwner
 FROM dbo.sysjobs A
 inner join master.sys.syslogins B on
 A.owner_sid = B.sid
where a.enabled = 1;


Oracle 9i imported database shows error ORA 600 on dropping user

http://www.dbasupport.com/forums/showthread.php?52489-Error-while-drop-user

DECLARE @name VARCHAR(50)
DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('model','msdb','tempdb','ReportServer','ReportServerTempDB') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
While @@FETCH_STATUS = 0
Begin
 Print 'Use ' + @name
 Print 'Go'
 Print 'select DB_NAME() as database_name '
 Print 'select name as TableName from ' +@name+'.'+ 'sys.tables where name IN ' + '(''a_GovernmentBuildings'',''a_Occupancy'',''a_TransHistory'',''a_UserSecurity'',''Agencies'',''Area'',''ARS_CASUAL_CLIENTS_STAFF'',''Consumption'')'
 FETCH NEXT FROM db_cursor INTO @name  
End
CLOSE db_cursor
DEALLOCATE db_cursor
-----

DECLARE @name VARCHAR(50)

 

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') 

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

 

While @@FETCH_STATUS = 0

Begin

-- Print 'Use ' + @name

-- Print 'Go'

-- Print 'select DB_NAME() as database_name '

 Print 'select name COLLATE DATABASE_DEFAULT as Procedure_Name from '+ @name +'.'+ 'sys.procedures where name IN (select name COLLATE DATABASE_DEFAULT from master.sys.procedures where type=''P'')'

 FETCH NEXT FROM db_cursor INTO @name  

End

CLOSE db_cursor

DEALLOCATE db_cursor

 

 

 

Reading for this week (1 and 2 Sept)
Redgate
http://stackoverflow.com/questions/861334/how-to-restore-database-using-sqb-files-in-sql-server

Reading for this week (25 and 26 August)
http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
http://msdn.microsoft.com/en-us/library/cc280402(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms189625(v=sql.105).aspx
http://stackoverflow.com/questions/10441336/list-which-columns-have-a-full-text-index-in-sql-server-2000
AD, DC concepts
sql restore all dbs:
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp
----- For Collation: automated script will include following [sample db code is below]
Schema-Binding-View
check constraint
default constraint
SQL reserve word



http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/

- Set the DB in Simple recovery model if it is in Full.

-  use databasename

   dbcc shrinkfile (2, 1)

- Revert back to full recovery model

http://www.codeproject.com/Articles/193855/An-indespensible-SSIS-transformation-component-Scr


Failed job identification

USE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[zz_check_failed_jobs]    Script Date: 08/10/2012 16:38:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[zz_check_failed_jobs] @NumDays int

AS

SET NOCOUNT ON

PRINT       'Checking for all jobs that have failed in the last ' + CAST(@NumDays AS char(2)) +' days.......'

PRINT ' '

SELECT     

      CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',

      SUBSTRING(T2.name,1,40) AS 'Job Name',

      T1.server as 'Server',

      T3.database_name,

      T1.step_name AS 'Step Name',

      T1.step_id AS 'Step #',

      T1.message AS 'Message'

FROM msdb..sysjobhistory T1, msdb..sysjobs T2, msdb..sysjobsteps T3

WHERE T1.run_status != 1

      AND   T1.step_id != 0

      AND T1.job_id = T2.job_id

      AND T2.job_id = T3.job_id

      AND T3.database_name not in ('master')

      AND   run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)

----------------------------------------------- Another One ------------------------------

select sjh.server, sjst.database_name, sj.name, sjst.database_user_name,
CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12),
case sjh.run_status
when 1 then 'SUCCEEDED'
when 0 then 'FAILED'
when 2 then 'RETRY'
when 3 then 'CANCELED'
end
from sysjobservers sjs, sysjobhistory sjh, sysjobs sj, sysjobsteps sjst
where sjs.job_id = sjh.job_id
and sjs.job_id = sj.job_id
and sjs.job_id = sjst.job_id
and sjs.last_run_outcome = 0
and DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 720; -- 1 month

Linked Server with Oracle

http://stackoverflow.com/questions/307636/how-do-you-setup-a-linked-server-to-an-oracle-database-on-sql-2000-2005
http://support.microsoft.com/kb/280106/en-us
http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx
worked for me !!!!
http://gleew.wordpress.com/2008/03/19/sql-server-linked-server-to-oracle/




Windows batch script

http://stackoverflow.com/questions/4340350/how-to-check-if-a-file-exists-from-inside-a-batch-file




Sample Database: