Search This Blog

Monday, May 14, 2012

SQL DBA : SQL 2005 - Move C2 Trace file from one Location to another Location


Move C2 Trace file from one Location to another Location :-


Create a Job With the Option of “ActiveX Script” (Select VB Script)


dim TracePath         ‘Declaring Variable for Assigning the Source path
dim FName              ‘Declaring Variable for Assigning the File Name

TracePath="G:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" ‘Source path of Trace File

   Set fso = CreateObject("Scripting.FileSystemObject")  ‘Object Creation for File system 
   Set folder = fso.GetFolder(Left(TracePath,len(TracePath)-1)) ‘Object Creation for Folder Access 
   Set files = folder.Files                                     ‘Object Creation For File Access

i=0

 For each fileIdx In files                     ‘Loop For Accessing the Files in the Source Folder
 FName = fileIdx.Name
              if right(FName ,4) = ".trc" then  ‘Checking the file Extension of the Trace file
                 Redim Preserve allFiles(i) 
                 Redim Preserve allTimeStamps(i)
                 allFiles(i)=fileIdx.Name       ‘Storing a Filename in a Array
                 allTimeStamps(i)=fileIdx.DateLastModified ‘Storing the Datemodified value of file
                      i=i+1
              end if
 Next

               'Now sort as per Modified TimeStamp  
 for i =  lbound(allTimeStamps) to ubound(allTimeStamps)
       for j =  lbound(allTimeStamps) to ubound(allTimeStamps)
              if allTimeStamps(i)<allTimeStamps(j) then
                     tt=allTimeStamps(i)
                     allTimeStamps(i)=allTimeStamps(j)
                      allTimeStamps(j)=tt
                      tt=allFiles(i)
                     allFiles(i)=allFiles(j)
                     allFiles(j)=tt
              end if
       next
 next

for i =  lbound(allFiles) to ubound(allFiles) -1        ' -1 so that we leave the latest file
       fso.movefile TracePath&allFiles(i) ,"P:\Audit\"  ‘Move the trace to Destination Path
Next

set fso = Nothing     ‘Clearing the Created Objects
set folder = Nothing
Set files = Nothing


Finally you can create a Step for Success and failure status mail for Mail Alert

Thursday, May 10, 2012

SQL 2005 - Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005

Below is the most basic coding to find out ALL of the Stored Procedures that exist within SQL Server 2005.

USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'FN' OR [type] = 'V')
ORDER BY [name];
GO

This coding is based on filter by the object "type". Use the following table to determine the type of object you want returned, and then adjust the WHERE clause to only return those type(s) you wish to be returned. This listing only lists views, functions, and procedures. There are other types which you can also filter, be sure to review BOL for further information on the types.

Type
 Type Of Object Returned
FN
SQL Scalar Function
IF
Inline Table Valued Function
P
SQL Stored Procedure
PC
CLR Stored Procedure
TF
SQL Table Valued Function
V
View
X
Extended Stored Procedure

User-Defined Stored Procedures

Now, one other field that is worthwhile to filter by is the "is_ms_shipped" field. This, obviously, determines if the SP was originally shipped by Microsoft. If the value of the field is 0 then it was NOT shipped by MS; if the value is 1 then it WAS shipped by MS. Simple enough. If you want to use it in conjunction with the above TSQL statement you will put in "AND [is_ms_shipped] = 0" to find SPs that were NOT created by MS, and most likely created by yourself or someone else with access to your database. If you ONLY want MS provided SPs then change the value from 0 to 1.
Here is a TSQL coding example of how to obtain only User-Defined SPs (using the previously mentioned "is_ms_shipped" field to obtain the listing). Note: This listing will definitely vary depending on what SPs you have installed. If you received no results then this indicates the database you are running your query on does not contain any SPs that were not created by MS...Try another database that you know you have created an SP for, or create an SP for your database and re-run the query.

USE [your_database_name_here];
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND [is_ms_shipped] = 0
ORDER BY [name];
GO