SQL Server Tip: View Schema Binding

As a database and ETL architect, I often find myself in situations where I am altering tables in SQL Server that have views that reference them. Changing the name of a column or renaming the table often breaks the view definition, resulting in an error when a database user queries the view.

Using the WITH SCHEMABINDING option when you create a view binds the view to the schema of the underlying table or tables. The result is that you will get an error message if you attempt to alter the table without first updating or dropping the view definition. Setting this option on all of the views in your database is a great way to prevent developers from altering a table schema without also updating the views that reference that table or tables.

Note: This option won't prevent you from making other alterations to your tables that might alter the data returned by a view.For example, if you change the grain of the underlying table, the WITH SCHEMABINDING option won't stop you from returning duplicate results. However, for many table alterations this little trick will save you a lot of headaches and disgruntled database users down the road.

Read more about the WITH SCHEMABINDING option on SQL Server Books Online here: http://msdn.microsoft.com/en-us/library/ms187956.aspx

Hope this helps,

Harlan

Installing Microsoft JDBC Driver for SQL Server

I have successfully configured the Microsoft JDBC Driver for SQL Server 3.0 and thought I would post my deployment steps as well as my results from some troubleshooting, in case they are helpful.

Steps to configure JDBC:
System requirements: Java Runtime Environment (JRE)

1. Download and install the Microsoft JDBC for SQL Server 3.0
2. When prompted, unzip to a directory *with no spaces*
3. To use integrated security (Windows authentication), copy the sqljdbc_auth.dll file to the C:\Windows\system32 directory. This file can be found under JDBC_install_path\sqljdbc_version\language\auth. Use the appropriate x86, x64, or IA64 sqljdbc_auth.dll file based on the JDBC help documentation.
4. Set the CLASSPATH environment variable to include the appropriate version of the JDBC driver .jar file, with class paths separated by semicolons. Only one of the .jar files should be included in CLASSPATH, either sqljdbc.jar or sqljdbc4.jar. Be sure to include any current CLASSPATH values in your set command. To see the current value, just run set with no arguments. 

set CLASSPATH=.;JDBC_install_path\sqljdbc_version\language\sqljdbc4.jar

5. You should now be able to import the java.sql package and connect to a SQL Server database!

Sample Java:

import java.sql.*;


public class ConnectJDBC {


   public static void main(String[] args) {


      // Create a variable for the connection string.
      String connectionUrl = "jdbc:sqlserver://localhost\\MyInstance;integratedSecurity=true;database=AdventureWorks";
 
 //print message
      System.out.println("Connection URL = " + connectionUrl);


      // Declare the JDBC objects.
      Connection con = null;


      try {
         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         con = DriverManager.getConnection(connectionUrl);
 
//print message
System.out.println("Successfully connected to the database!");
      }


      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
   }
}

Compile and run this code

javac ConnectJDBC.java

java ConnectJDBC 

Congratulations! You have now successfully connected to a database. Next step: Executing a query and doing something with the results!

Errors & Troubleshooting

Error 1:

java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

This one was resolved as soon as I moved the JDBC install path to a path with no spaces. This one had me scratching my head for a while.

Error 2:

com.microsoft.sqlserver.jdbc.SQLServerException: The server MyServer is not configured to listen with TCP/IP.

For this one, open SQL Server Configuration Manager and enable TCP/IP under SQL Server Network Configuration. You must restart the SQL Server service for the appropriate instance for the configuration change to take effect.

Error 3:

SEVERE: 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.

Make sure the CLASSPATH environment variable contains a reference to the appropriate .jar file, either sqljdbc.jar or sqljdbc4.jar. In my case, I am using JRE version 1.7, so I need to use sqljdbc4.jar.

Error 4:

Error: Could not find or load main class

This is a common Java runtime error when the JVM can't find the main class. In my case, I forgot to include the current directory (.) in my CLASSPATH environment variable. Adding .; to the beginning of my CLASSPATH value fixed the problem.

set CLASSPATH=.;JDBC_install_path\sqljdbc_version\language\sqljdbc4.jar

SSAS Maestro Program Site

The SSAS Maestro Program now has an official page on the Microsoft Learning site where you can find details on the program, some of the training objectives (which, by the way, make a great list for Analysis Services study concepts), and a Meet the Maestros page where you'll find info on all the current SSAS Maestro Program graduates.

Check it out: http://www.microsoft.com/learning/en/us/certification/ssas-maestros.aspx

Big thanks to Robert Davis and Microsoft Learning for putting the site together.

BISM Demystified

Just caught a great blog post from Cathy Dumas on Microsoft's Analysis Services team that helps alleviate some of the confusion around the BISM, MDX, DAX, etc. I know I personally left PASS last week a bit confused about what exactly the BISM was and how MDX & DAX play into that, especially in light of Power View (formerly Project Crescent) currently only being able to query Tabular models (multidimensional/tabular - it's all just one BISM, right?!).

PASS Summit day 1 Keynote - More Announcements

SQL Server code-name "Denali" will now be called SQL Server 2012 and will be released in H1 of calendar year 2012.
"Project Crescent" will now be named PowerView and will be released as part of SQL Server 2012.
"Project Juneau" will be named SQL Server Data Tools and will be part of the 2012 release.
SQL Server Azure Reporting Services will be released some time in 2012.
Analysis Services for Azure will be coming soon. (Yay! Cloud cubes!)
Project code-name "Data Explorer" is a new cloud service that will allow you categorize your data sets based on noun analysis, will suggest recommended Azure Marketplace data sets, and will allow you to join or "Mashup" multiple data sets from your own data or data from the cloud, then download the results to Excel, PowerPivot, etc. The product is expected to be rolled out sometime in the coming year.
Oh, and one more thing. PowerView (aka Crescent) now has export to PowerPoint. And it will be available on mobile devices - Windows Phone, iPad, Android.

PASS Summit Day 1 Keynote - SQL Server & Apache Haddop Play Nice

OK, didn't see this coming...
Microsoft's Ted Kummert just announced that the company will be making a foray into the world of Hadoop, not releasing their own MapReduce platform as I predicted yesterday.

What will this look like? Microsoft has already announced connectors for SQL Server & SQL Server Parallel Data Warehouse to get data into and out of Hadoop. In addition, the announcement at PASS today includes the following:

1. Microsoft will begin actively contributing to the Apache Hadoop project, building a Hadoop for Windows distribution to ensure that the platform runs seamlessly on clusters of both Windows and Windows Azure.
2. Microsoft will soon be releasing an ODBC driver and Excel add-in to connect to Apache Hive.
3. Microsoft is partnering with hortonworks to help accelerate the Windows/SQL Server/Hadoop/Hive integration.

Pretty exciting stuff. Not to mention my buddy Denny Lee (Principal Program Manager on the SQLCAT team) got to make the announcement. He told us a while back that he was working on Big Data but couldn't tell us much... I guess this is what he meant!

SSAS 2008 R2 Performance Guide

SQLCAT has published the latest version of the Analysis Services Performance Guide for SQL Server 2008 R2. If you are an SSAS developer or architect, do yourself a favor and spend some time studying this paper. There are all kinds of goodies in there. Keep a copy on a thumb drive and take it with you wherever you go - you'll be glad you did.