Software Applications Programming Errors and solutions.

Workbook Data Into One Single Sheet using MS Excel  If you have 10 workbook each workbook have single sheet of data (tab name should be anything) if you need consolidation sheet into all 10 workbook into one then you can do this with code use below mention code and your work will done . Sub test() Dim FS, Fle, FLDR, fles Dim Fletype As Variant Set FS = CreateObject("scripting.filesystemobject&quot ;) Dim intLstrow As Integer Dim intLstcol As Integer Dim dlgDialoge As FileDialog Dim srcsheet As Worksheet Dim wk As Workbook 'Set dlgDialoge = Application.FileDialog(msoFileDialogFolderPicker) Set wk = ThisWorkbook Set FLDR = FS.getfolder(BrowseFolder) Set fles = FLDR.Files For Each Fle In fles Fletype = Split(Fle.Name, ".") If (Fletype(UBound(Fletype)) = "xls" Or Fletype(UBound(Fletype)) = "xlsx") Then Set srcsheet = Workbooks.Open(Fle.path).Worksheets(1) intLstrow = srcsheet.Range("

1.Create  or replace trigger before insert on field in sql Create or replace trigger t1 Before insert on Employee For each row declare v_firstname Employee.firstname%type; v_lastname Employee.lastname%type; Begin DBMS_OUTPUT.PUT_LINE('You inserted the first Name:'|| :NEW.FIRSTNAME); DBMS_OUTPUT.PUT_LINE('You inserted the first Name:'|| :NEW.LASTNAME); Begin select distinct firstname into v_firstname from Employee where firstname=:NEW.FIRSTNAME; IF(:NEW.FIRSTNAME = v_firstname) THEN DBMS_OUTPUT.PUT_LINE('This first name exists already in the table Employee.'); end if; exception when no_data_found then DBMS_OUTPUT.PUT_LINE('This first name does not exist in the table Employee.'); End; Begin select distinct lastname into v_lastname from Employee where lastname=:NEW.lastNAME; IF(:NEW.LASTNAME = v_lastname) THEN DBMS_OUTPUT.PUT_LINE('This last name exists already in the table Employee.

To get owner of all database in sql you can run following query in sql server query editor. select suser_sname(owner_sid) from sys.databases To get owner of particular database like Testdb is database name in sql you can run following query in sql server query editor. select suser_sname(owner_sid) from sys.databases where name = 'Testdb'  Get package name from the database query level If you  have a front end application.When a button on that page is clicked, a package is called.If you  want to find that package name from from the db level. Db level means by writing a query. How to find it? use who called me function for example OWA_UTIL.WHO_CALLED_ME(owner ,name, lineno ,caller_t ); 

Difference between primary key and unique key A table can have only one primary key but multiple unique keys. unique key may have null value which is not allowed in primary. simply primary key is unique + not null where as unique key Field contains any number of NULL values. Find primary key, index_name ,index key  using sp_Help command in sql How to find primary key, index_name and index_key in the same query For a particular table and also want to group by index_name use sysindexes table to get the output you want use the stored procedure sp_helpindex [object name] sp_help 'table name' Null and Not Null value in Primary and unique keys in sql Primary key should contain unique value and not null because if attribute value is null than how should maintain the uniqueness.. one more think i want to add that Primary key is a type of constraint and foreign key is also a type of constraint and pk is null then u can't use the null value as a foreign key. S

Simple command to shrink all database at once to reduce database size in SQL server This is very simple command to shrink all database at once to reduce database size in SQL server but one thing you need to remember do not forget to make recovery mode simple before executing this command. EXEC sp_MSForEachDB 'select ''?'' as [Database]; ALTER DATABASE [?] SET RECOVERY SIMPLE; DBCC SHRINKDATABASE (''?'' , 0)' After execution of command it will display all database name with details of size. I hope this command will help you a lot on troubleshooting of disk size. Shrinking and reducing database size in sql Summary: If there is big difference in ldf file and .bak file then you can shrink database to reduce size of database. you can use following method at Details use <dbname> checkpoint go backup log <dbname> with truncate_only, go dbccshrinkfile(<logfilename>,1000)