This can be a handy piece of code to have in your toolbox if you have a ColdFuson host that will not allow you to create DSN's. Using this method, you can wrap this in a nice CFC that allows you to query a Microsoft SQL database. The object code example is as follows ...
<cfscript>
classLoad = createObject(“java”, “java.lang.Class”);
classLoad.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
dm = createObject(“java”,”java.sql.DriverManager”);
conn = dm.getConnection(“jdbc:odbc:DRIVER={SQL Server};Database=”DBNAME”;Server=SERVER NAME OR IP;”, “LOGIN”, “PASSWORD”);
st = conn.createStatement();
rs = st.ExecuteQuery(“SELECT * FROM myTable”);
qry = createObject(“java”, “coldfusion.sql.QueryTable”).init(rs);
</cfscript>
Once you have the result simply loop over the QueryTable Object (qry) as you would usually in a cfoutput or cfloop block. As explained above this solution is a DSNless connection solution. For security and speed, if you have the option to create a DSN connection to your database, then use that option.