DBA-Philiac

From 2000 --Drop All Constraints select + 'alter table ' + table_schema + '.' + table_name + ' drop constraint ' + constraint_name  from INFORMATION_SCHEMA . TABLE_CONSTRAINTS where table_name in ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA . TABLES where table_name like 'tbl%'   );                                   --Drop All Views Select 'drop view ' + name From sysobjects Where type = 'V' and name like '%vw%' ;   --Drop All Tables select + 'drop table ' + table_schema + '.' + table_name from INFORMATION_SCHEMA . TABLES where table_name like 'tbl%' ;     http://www.daniweb.com/web-development/databases/ms-sql/threads/51556/get-table-list-from-sql-2k-server http://sujithcjose.blogspot.com.au/2009/01/list-all-stored-procedurestables-or....

So far I have used two ways to create linked server with Oracle. By 1. MS OLE DB Provider for ODBC Driver 2. OraOLEDB.Oracle Provider For first one steps are like [this actions will be done where the sql server resides. assume oracle db is in remote machine] 1. Install Oracle Client with all options. 2. Reboot the machine (not sure mandatroy or not) 3. Go to tnsnames.ora and note the oracle database info you want to connect 4. Create a system DSN name through adminsitrative tool --> Data Source --> System Driver --> Add new [configure and test it oracle user name and password] 5. Right Click of Provider MSDASQL in SSMS->Server Objects->Linked Server and check the box "Allow In Process" 6. Now open SSMS and put the below queries exec master . dbo . sp_addlinkedserver @server = N'TEST2ORACLE' , @srvproduct = N'DSN_NAME' , @provider = N'MSDASQL' , @datasrc = N'DSN_NAME' exec master . dbo . sp_addlinkedsrvlogin...

| Technical Integration and Support | Business Technology Department for Communities and Social Inclusion Level 3 West, Riverside Centre, North Terrace, Adelaide SA 5000 | GPO Box 292, Adelaide SA 5001 Tel. 08 8207 0362 | Fax. 08 8207 0305 | E-mail: paul.copley@dcsi.sa.gov.au

Hi All, Here I try to collect a list of failed jobs from SQL Server agent which are getting failed for a specific time being. ------------------------------------------------------------------------------ ---- First create a temp table to Populate data for all failed jobs in last  o ne month (720 hours) ------------------------------------------------------------------------------- USE msdb GO select sjh . server as instance , sjst . database_name as database_name , sj . name as job_name , CONVERT ( datetime , CONVERT ( VARCHAR ( 8 ), sjh . run_date ), 12 ) as run_date , case sjh . run_status          when 1 then 'SUCCEEDED'        when 0 then 'FAILED'        when 2 then 'RETRY'        when 3 then 'CANCELED' end as run_status into #temp_job_status    from sysjobservers sjs , sysjobhistory sjh...

Hi All, As a novice windows batch script writer I appoligize you first for posting such a silly post. Here I have learnt some thing like 1. How to copy file over the network with file name containing spaces (with double quote stuff). 2. How to create a logfile against a batch job. 3. How to remove n days older files (with forfiles command). 4. How to format date parameter for filename suffix. 5. How to compact and repair access databases. 6. How to use if condition and Label. ::::::::::::: Create a log file ::::::::::::::: setlocal set "LogPath=D:\temp\accessdb_backup\logs\" set LogFileExt=.log set LogFileName=DailyAccessDBBackup%LogFileExt% ::use set MyLogFile=%date:~4% instead to remove the day of the week set MyLogFile=%date:~10,4%%date:~4,2%%date:~7,2% ::set MyLogFile=%MyLogFile:/=-% set MyLogFile=%LogPath%%MyLogFile%_%LogFileName% ::Note that the quotes are REQUIRED around %MyLogFIle% in case it contains a space If NOT Exist "%MyLogFile%" goto:nose...

DECLARE @DatabaseName nvarchar(50) SET @DatabaseName = N'Works' --SET @DatabaseName = DB_NAME() DECLARE @SQL varchar(max) SET @SQL = '' SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId -- SELECT @SQL EXEC(@SQL) Source: http://www.kodyaz.com/articles/kill-all-processes-of-a-database.aspx

- Detach all user databases. - Put the below command setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=[either named instance without servername defaultinstance] /SQLSYSADMINACCOUNTS="Admin_User" /SAPWD=****** /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS Here few things you need to keep in mind 1. setup.exe should be the source where you copied all your installer file from dvd to local drive. 2. InstanceName will not include the server name only the instance name. 3. If SQLServer authentication is enabled put the sa password like /SAPWD=***** after /SQLSYSADMINACCOUN TS=***** . 4. You can see the log files in below locations      a. C:\Users\Saifur Shaon\AppData\Local\Temp\SQLSetup.log      b. C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120805_132518 Source: http://msdn.microsoft.com/en-us/library/ms179254.aspx