Wednesday, August 21, 2013

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

Errorinfo is "System.Data.OracleClient requires Oracle client software version 8.1.7 or greater."

I have installed Oracle Client 11g (11.2.0.1.0) on Server is greater than 8.1.7 , still the error comes. On serching out I find out following solution:

It is a security issue, so to fix it simply do the following:
1- Go to the Oracle Client folder.
2- Right Click on the folder.
3- On security Tab, Add "Authenticated Users" and give this account Read & Execute permission.
4- Apply this security for all folders, Subfolders and Files (IMPORTANT).
5- Don't Forget to REBOOT your Machine; if you forgot to do this you will still face the same problem unless you restart your machine.

Friday, August 16, 2013

SQL Optimization



1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'. 
If we use '*' , then sql takes time to expand it. Though time taken is very less but in large and complex queries, it can be a considerable factor.
For Example: Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as
SELECT subject, count(subject) FROM student_details WHERE subject != 'Science' AND subject != 'Maths' GROUP BY subject;
Instead of:
SELECT subject, count(subject) FROM student_details GROUP BY subject HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name FROM employee WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) FROM employee_details) AND dept = 'Electronics';
Instead of:
SELECT name FROM employeeWHERE salary = (SELECT MAX(salary) FROM employee_details) AND age = (SELECT MAX(age) FROM employee_details) AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p where EXISTS (select * from order_items o where o.product_id = p.product_id)
Instead of:
Select * from product p where product_id IN (select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
SELECT d.dept_id, d.dept FROM dept d WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of:
SELECT DISTINCT d.dept_id, d.dept FROM dept d,employee e WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name FROM student_details_class10 SELECT id, first_name FROM sports_team;
Instead of:
SELECT id, first_name, subject FROM student_details_class10 UNION SELECT id, first_name FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as
SELECT id, first_name, age FROM student_details WHERE first_name LIKE 'Chan%';
Instead of:
SELECT id, first_name, age FROM student_details WHERE SUBSTR(first_name,1,3) = 'Cha';
Write the query as
SELECT product_id, product_name FROM product WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name FROM product WHERE unit_price >= MAX(unit_price) and unit_price <= MIN(unit_price)
Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age NOT = 10;
8)  To store large binary objects, first place them in the file system and add the file path in the database.
9) To write queries which provide efficient performance follow the general SQL standard rules.
a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

Install IIS 7.5 on Windows Server 2008 R2

Install IIS 7.5 on Windows Server 2008 R2

IIS is one of the Windows Server® server roles. IIS can be installed through the graphical user interface (GUI) by using the new Server Manager interface after the Windows Server operating system is installed.
Server Manager provides a single dashboard to install or uninstall server roles and features. Server Manager also gives an overview of all currently installed roles and features. When IIS is chosen from the Server Manager, the basic components and services needed for IIS are automatically selected.
1. Click Start -> All Programs -> Administrative Tools -> Server Manager.
Figure 2: Server Manager
2. In the Server Manager window, scroll down to Roles Summary, and then click Add Roles. The Add Roles Wizard will start with aBefore You Begin page. The wizard asks for verification of the following:
a. The administrator account has a strong password.
b. The network settings, such as IP addresses, are configured.
c. The latest security updates from Windows® Update are installed.
3. Select Web Server (IIS) on the Select Server Roles page. An introductory page will open with links for further information.
Note: When you use the Add Roles Wizard to install IIS, you get the default installation, which has a minimum set of role services. If you need additional IIS role services, such as Application Development or Health and Diagnostics, make sure to select the check boxes associated with those features in the Select Role Services page of the wizard.
Figure 3: Select Server Roles
4. Select the IIS services to be installed on the Select Role Services page. Add only the modules necessary. In this case, ASP.NET is selected, and a description of ASP.NET appears in the right pane. Once desired modules are added, click Next.
Figure 4: Select Role Services
5. Add any required role services.
Figure 5: Wizard warning page
6. IIS is now installed with a default configuration for hosting ASP.NET on Windows Server. Click Close to complete the process.
Figure 6: Installation Results page
7. Confirm that the Web server works by using http://localhost.
Figure 7: Default Web site
Note: Install only the absolutely necessary IIS services to minimize the IIS installation footprint. This also minimizes the attack surface, which is one of the benefits of IIS 7 and above.

Use a Script to Install IIS 7.5 on Windows Server 2008 R2

You can also use a script to install IIS 7.5. Note that if you use this script, you get the full IIS installation, which installs all available feature packages. If there are feature packages you do not need, you should edit the script to install only the packages you require. 
To install IIS 7.5 with a script, type the following at a command prompt:
CMD /C START /w PKGMGR.EXE /l:log.etw /iu:IIS-WebServerRole;IIS-WebServer;IIS-CommonHttpFeatures;IIS-StaticContent;IIS-DefaultDocument;IIS-DirectoryBrowsing;IIS-HttpErrors;IIS-HttpRedirect;IIS-ApplicationDevelopment;IIS-ASP;IIS-CGI;IIS-ISAPIExtensions;IIS-ISAPIFilter;IIS-ServerSideIncludes;IIS-HealthAndDiagnostics;IIS-HttpLogging;IIS-LoggingLibraries;IIS-RequestMonitor;IIS-HttpTracing;IIS-CustomLogging;IIS-ODBCLogging;IIS-Security;IIS-BasicAuthentication;IIS-WindowsAuthentication;IIS-DigestAuthentication;IIS-ClientCertificateMappingAuthentication;IIS-IISCertificateMappingAuthentication;IIS-URLAuthorization;IIS-RequestFiltering;IIS-IPSecurity;IIS-Performance;IIS-HttpCompressionStatic;IIS-HttpCompressionDynamic;IIS-WebServerManagementTools;IIS-ManagementScriptingTools;IIS-IIS6ManagementCompatibility;IIS-Metabase;IIS-WMICompatibility;IIS-LegacyScripts;WAS-WindowsActivationService;WAS-ProcessModel;IIS-FTPServer;IIS-FTPSvc;IIS-FTPExtensibility;IIS-WebDAV;IIS-ASPNET;IIS-NetFxExtensibility;WAS-NetFxEnvironment;WAS-ConfigurationAPI;IIS-ManagementService;MicrosoftWindowsPowerShell