[IT] Connecting JSP with SQL Server 2008 R2 Express (JDBC)

Here are the problems that I got when I tried to connect JSP (JavaServer Pages) with SQL Server 2008 R2 Express through JDBC (Java Database Connectivity):

1. a. java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
   b. java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
2. Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
3. Login failed for user ...

The software and file that I used:

1. Tomcat 7.0 (Apache Software Foundation)
2. Java Runtime Environment (JRE) 1.7
3. JDBC Driver 4.0 for SQL Server (sqljdbc.jar and sqljdbc4.jar)

I will explain the problems in the 'REFER TO PROBLEMS' section in the steps I'll explain.
Pre-requirement:

1. Tomcat
2. Java Runtime Environment (JRE)
3. Microsoft SQL Server
4. Any web-editing softwares

THE STEPS

1. Download the Microsoft JDBC Driver for SQL Server to connect any Java programming-based applications with SQL Server. We can download it officially here (if the link is dead, search "Download Microsoft JDBC Driver for SQL Server").
2. Extract sqljdbc_4.0.2206.100_enu.exe or sqljdbc_4.0.2206.100_enu.tar.gz. We'll get two core files, sqljdbc.jar and sqljdbc4.jar, that we need in folder sqljdbc_4.0\enu.
3. Make sure that Tomcat's service status is 'stopped'.
4. Copy both sqljdbc.jar and sqljdbc4.jar to Tomcat's lib folder (commonly: C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib) 
5. Copy either sqljdbc.jar or sqljdbc4.jar to JRE's ext folder (commonly: C:\Program Files\Java\jre7\lib\ext). If we use JRE 1.5 below, copy just sqljdbc.jar. If we use JRE 1.6 above, copy just sqljdbc4.jar.
6. Start the Tomcat's service. We can officially connect to SQL Server 2008.

REFER TO PROBLEMS

• java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
• java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
  
  These problems could happen because:
  First, the Tomcat fails to 'recognize' the needed classes that are 'included' in sqljdbc.jar or sqljdbc4.jar. These problems can be solved if we accomplish step 3 and 4 above.
  
  Second, the wrong usage of Class.forName(). Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); is for SQL Server 2000 and Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); is for SQL Server 2005 and above. Don't interchange them, pay attention to their differences. We must pay attention to the differences of the connection string in DriverManager.getConnection().
  
  Before we start to focus on the last problem, please type this following syntax and save it as JSP (.jsp) and display the result to browser.
  
  <%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
  <%
  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;");
  Statement st = con.createStatement();
  %>
• Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
  
  This error can be solved through step 5.
• Login failed for user ...
  
  If you encounter the same problem, do these following steps:

1. Open SQL Server Configuration Manager.
2. Make these following changes:

3. Open Server SQL Management Studio and connect to database engine.
4. Make new login.

5. Type the login name.

6. Select SQL Server authentication (you can type password--and remember it).
7. Select Server Roles.

8. Click OK.
9. Set the server properties:

10. Change server authentication:

11. Click OK.

Copy and paste these codes:

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=evo");
Statement st = con.createStatement();
%> 

 Note: user=evo, evo is the login name you made.

If the result on the browser is blank page without error, you made it!

Read More

[IT] Microsoft® SQL Server® 2008 Management Studio Express Not Appearing After Installing SQL Server 2008 R2 Express

[Article in English]

Microsoft® SQL Server® 2008 Management Studio Express Not Appearing After Installing SQL Server 2008 R2 Express

This problem is quite confusing. The source of the problem is the other Microsoft's product, such as Visual Studio, that has the version below R1.

To overcome it, you should update Visual Studio with a new version or remove the program from your PC. Then, you should download Microsoft® SQL Server® 2008 Management Studio Express and start re-installing (like the installation of SQL Server 2008 R2 Express beforehand).


[Artikel dalam Bahasa Indonesia]

Microsoft® SQL Server® 2008 Management Studio Express Tidak Menampakkan Diri Saat Selesai Instalasi SQL Server 2008 R2 Express

Masalah Microsoft® SQL Server® 2008 Management Studio Express tidak menampakkan diri saat setelah instalasi SQL Server 2008 R2 Express membuat masalah ini cukup membinggungkan. Ternyata masalah ini bermuara pada eksistensi produk Microsoft seperti Visual Studio yang memiliki versi di bawah R1.

Untuk mengatasinya, cukup menginstal ulang versi Visual Studio yang ada dengan versi yang lebih baru ataupun me-remove program tersebut dari PC. Setelah itu mengunduh Microsoft® SQL Server® 2008 Management Studio Express dan memulai instalasi ulang (seperti instalasi SQL Server 2008 R2 Express sebelumnya).

Read More