Database Explorer: 31/05/2009

Friday, 5 June 2009

Create Table and Sequence

How to Create Table and the Sequence:
How to Insert Records in table:

FileZilla FTP Client Installations - Step by Step

Download the file from FileZilla Website and follow the steps in the screenshot:

http://filezilla-project.org/

Click .EXE file from above link.

Save it to Disk in your desired location Or Run without saving.

If the above Security Warning arises, Please Click the Run again.

Click "I Agree".

Click Next

Click Next - Please check the box if you need Desktop Icon.

Click Next

Click Install

Installation Proceeds.....

Once Installation Completed, Click Finish Button.

The above Interface appears after FileZilla Successfully Installed.

You can enter your IP or Host Address, Username, Password and Port Number to connect to FTP.

Renaming Objects in SQL Server

TO RENAME TABLE:
sp_rename 'T_OldTable','T_Table'
GO
TO RENAME COLUMN IN TABLE:
sp_rename 'T_Table.[OLDCOLUMNNAME]', NEWCOLUMN
go
TO RENAME DATBASE NAME:
EXEC sp_renamedb 'oldDBName', 'newDBName'
GO
ALTER DATABASE oldDBName MODIFY NAME = newDBName ---THIS OPTION AVAILABLE IN SQL SERVER 2005 AND AFTER RELEASE.
Using SQL Server Management Studio, right click on the database name and select the option "rename" and Change the New DB Name
Also you can change the database name while Detaching and Attaching:
EXEC sp_detach_db 'oldDBName', 'true'
GO
EXEC sp_attach_db @dbname = N'newDBName', @filename1 = N'D\Data\TEST_DATA.mdf', @filename2 = N'D\Data\TEST_LOG.ldf'

Primary, Foreign Keys in SQL Server - Constraint Tips

To find primary and foreign keys in table, alter, drop, you can use the below scripts
USE TEMPDB
GO
sp_pkeys T_Table --- To find the existing primary keys in table
GO
sp_fkeys T_Table --- To find the existing foreign keys in table
Primary Key
ALTER TABLE T_Table ADD CONSTRAINT PK_T_Table PRIMARY KEY CLUSTERED (ID) -- Add primary key in existing table
GO
Table Level Primary key Creation
CREATE TABLE T_TABLE
(
ID int
CONSTRAINT PK_T_Table PRIMARY KEY CLUSTERED,
COMPANY VARCHAR(10)
)
GO
Drop Primary Key Constraint
ALTER TABLE T_Table DROP CONSTRAINT PK_T_Table
GO

FOREIGN KEY
OPTION 1:
MarketingPersonID int NULL
REFERENCES MarketingPerson(MarketingPersonID)
GO
OPTION 2:
FOREIGN KEY (MarketingPersonID) REFERENCES MarketingPerson(MarketingPersonID)
GO
OPTION 3:
CONSTRAINT FK_DiscountOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, DiscountOfferID)
REFERENCES DiscountOfferProduct (ProductID, DiscountOfferID)
UNIQUE CONSTRAINTS:
Table level
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
Alter table Creation
ALTER table T_Table
ADD CONSTRAINT UN_T_Table
UNIQUE (Email,productid)
Drop unique constraint
ALTER TABLE T_Table DROP CONSTRAINT UN_T_Table
DEFAULT DEFINITIONS
DEFAULT 'Claim Not Confimed yet'
DEFAULT (getdate())
CHECK CONSTRAINTS
1. CHECK (CreditRating >= 1 and CreditRating <= 5)
2. CONSTRAINT CK_sal_id CHECK (sal_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR sal_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]') CHECK (sal_id IN ('1389', '0736', '0877', '1622', '1756') OR sal_id LIKE '99[0-9][0-9]')

Wednesday, 3 June 2009

How to limit the remote sessions on a terminal server

How to limit the remote sessions on a terminal server
1. click Start --> Go to Administrative Tools --> then click Terminal Services Configuration.
2. In the console tree --> click Connections.
3. In the right pane --> right-click RDP-Tcp --> Then click Properties.
4. On the Network Adapter tab --> Select 3 or 4 or as you wish from the Maximum connections drop-down list
5. On the Permissions tab --> click Add --> Type Everyone in the Enter the object names to select (examples) box --> click Check Names --> then click OK.
6. In the Group or user names area --> click to select the Everyone group.
7. In the Permissions for Everyone area --> click to select the Deny check box to deny permission for Guest Access --> Then click OK.

To make a Remote Desktop connection to the server, use the mstsc.exe /console (windows xp or 2003) and/or mstsc.exe /admin (windows Vista) command to connect to the console session of a terminal server.

Screen shot is below:

Tuesday, 2 June 2009

Oracle Products - Their Short Details

1. Oracle Database
2. Oracle Application Server
3. Oracle Applications
4. Oracle Colloboration Suite
5. Oracle Development Suite
6. Oracle Services.

1. Oracle Database
It is the most flexible and cost effective way to manage information and applications. It is available in 3 editions namely a) Enterprise 2) Standard and 3) Personal
2. Oracle Application Server
Oracle Java 2 Enterprise Edition certified server - Purpose of this server is to integrate all needed to develop and deploy web based applications
3. Oracle Applications
Oracle E-Business suite is a complete set of business applications for managing and automating process across any organisation.
4. Oracle Colloboration Suite
Integrated system for the organisation communication data : voice, email,fax,wireless, calendar infomation and files.
5. Oracle Development Suite
Integrated environment that combines application development and business intelligence tools.
6. Oracle Services.
Services like Oracle university and oracle consulting provide expertise in that area.

Web Service Extensions Permissions - IIS

To enable ASP or ASP.Net Framework in IIS, you have to go to IIS Manager (Go to Start --> Run Prompt --> Type inetmgr to see the Internt Information Services Manager.

Then as per below screen shot, you may Allow or Prohibit, that is enable or disable the extension.

Monday, 1 June 2009

Step by Step - FileZilla FTP Server Installation

To set up FileZilla FTP server. Follow the below steps:

Click Download Filezilla Server

Click .exe file

Save it in hard disk in the appropriate location or Run

Click Next

The port we configure for this demo purpose is 14147. Click Next

Click Install

Click Close to Quit the Installation.

Connect to Filezilla Ftp Server

Create user as you like and share the folder whatever folder you want.

And Give permissions for that folder accordingly and Click OK.

The above diagram shows the account successfuly created in the server.

To access the ftp. Go to windows Programs --> Run --> Type Explorer and Enter. Once you see the username and login prompt like below screen shot. Type the username and password to get into the FTP server.

Hope you enjoy the above article.

Keyboard Shortcuts in SQL Management Studio

Keyboard Shortcuts in SQL Server Management Studio:
Select the word and then search the same word in the current document or whole project:
Basically, to view the available options in the SQL Management Studio
1. Go to Edit Menu --> Then Advanced Options to see the ShortcutKeys
2. Select the word or sentence using - Ctrl + F3
3. Then continue to search by - F3
4. Execution Plan - Ctrl + L
5. Go to Line - Ctrl+G
6. Comment Out - Ctrl + K + C
7. Uncomment - Ctrl + K + U

This will be updated often with shortcut keys - Therefore advised to check periodically.

Sunday, 31 May 2009

Step by Step - Uninstall Oracle 9i

Go to Oracle Installation Products from Programs - Then Universal Installer - Then follow the screen shot.

Click Deinstall Products

Select All products

Select All products by clicking all the above. Then Next to uninstall.
Click on "Remove" and click "Yes"
once it finished click "Close" and then "Exit"
You may manually interrupt to remove other directories and services related to oracle.
Step I to check:
Stop any Oracle services that have been left running. Go to Start > Settings > Control Panel > ServicesOR Go to Run Then type services.mscServices may be 'OracleOraHome90TNSListener' and 'OracleServiceORACLE'. Generally service starting with 'Oracle'.
Step II to check:
Run regedit - Start Then Run Then type "regedit", click "Ok"Delete the following keys:HKEY_LOCAL_MACHINE \SOFTWARE\ORACLEHKEY_LOCAL_MACHINE \SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracleHKEY_CURRENT_USER\SOFTWARE\ORACLE
Step III:
Delete the Oracle home directory:"C:\Oracle" or "D:\oracle"
Above will remove database files:
Step IV:
Delete the Oracle program Files directory:"C:\Program Files\Oracle"
Step V:
Delete the Oracle programs profile directory:"C:\Documents and Settings\All Users\Start Menu\Programs\Oracle - OraHome90"
Step VI:
You may also need to check the below:
To remove these: Start > Settings > Control Panel > System > Advanced > Environment Variables, See the system variable 'PATH' and edit them to remove any references to Oracle.

Step by Step - Oracle 9i Installation

Step by Step Installation (Oracle 9i) - Demonstration done in Windows.
You may find oracle 9i from the oracle website.
http://www.oracle.com/technology/software/products/oracle9i/index.html