SQL Prettifier (Lint) Tool

I have spent countless hours of my life staring at SQL code. While I made every effort to keep my own SQL neat, it's not always easy to decipher errors in complex queries - especially those in legacy code or written by developers who don't share my passion for code formatting.

Today I was fortunate to find an excellent SQL prettifier that analyzes code (like a lint-style tool) and formats it with proper capitalization, indentation, and so forth.

Here's the link; I hope it saves somebody time in debugging and managing SQL code as it has just done for me.

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

MSSQL Job Error: The owner () of job <Job Name> does not have server access

The other week I noticed some of the Jobs that we have scheduled in our MSSQL setup had started repeatedly failing. They were giving the message, "MSSQL: The owner () of job <Job Name> does not have server access".

I thought this was weird, especially since other jobs were running fine. I investigated a little further and found this forum that discussed this issue.

What I gathered is that if using the DOMAIN/Username (Windows Login) syntax for the Owner field when setting up a job, sometimes after a server restart it has trouble verifying the admin privileges to allow the job to actually run.

The ways to fix this are:

[More]

Help Wanted: Web Application Developer and Mobile Application Developer

These positions were filled in November 2010. Please check our Employment Openings page for the latest information about jobs at Ravenglass Technologies.


Ravenglass is seeking two application developers: one Web Application Developer and one Mobile Application Developer. Will consider contract, contract-to-hire, or full-time with salary plus full benefits. Flexible commuting arrangements considered

Web Application Developer Degree in Computer Science or related field required. Must have demonstrable experience with HTML, XML, AJAX, and SQL. Web application development experience in ColdFusion, JSP, ASP.NET, or PHP is also required. Mobile application development experience is a plus.

Mobile Application Developer Degree in Computer Science or related field required. Must have professional mobile application development experience with iOS or Android OS. Web application development (ColdFusion, PHP, ASP.NET) and HTML5 knowledge are a strong plus.

Professionalism and strong communications skills are a must. Apply with resume and cover letter to jobs@ravenglass.com.

Forcing indexes to run in SQL

Did you know you can actually force an index to run if you'd like? SQL table hints are the key here. Using these hints you can indicate that you'd like the query to give preference to once index or another. This can sometimes be beneficial if you'd think that one index may be better than one the SQL query tools automatically select.

[More]

Error: Value can not be converted to requested type

This was an interesting error that I encountered today. About two months ago, I added a bunch of cfqueryparams to the page that began returning this error today, "Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.". I assumed the value being passed to the cfqueryparam was actually invalid.

[More]

Another Simple Tip: Selecting the last inserted ID

I know that when I'm using SQL I find myself frequently having to get my ID that I just inserted into a table, so today I'm going to sharesome of the tools I use to get the last ID I inserted into a database.  Sometimes when beginning to learn SQL you may feel the need to create a transaction and do a select for the greatest value of your primary key field to get the identity.  Although this sounds great, firstly, it requires a transaction, and secondly, it doesn't take into account non-int or non-incremented keys. 

[More]

Reading an XLS file using CFQUERY

I haven't really seen a lot of documentation on how to read an XLS file into Coldfusion. Luckily, one of my colleagues was able to point me in the right direction. I am now here to write up a blog post for future reference.

Once you set up the new datasource in the CF Administrator (see comments below), it's really simple (at least more simple than parsing through the other file formats that I've had to do far). Basically, we just read it in as a query:

[More]

SELECT COUNT(*), The Basics

In SQL, COUNT() is an aggregate function that we can use to count the number of rows in a query. We can also use this function to figure out if there are duplicate records in a table.

[More]

More tips for SQL efficiency: boolean values in SQL

I'm always looking for ways to speed up my SQL Server queries.  Here's a tip that I used just today to speed up a search query, and I thought maybe it would be useful for all of you.

First up, make sure to include an execution plan by right-clicking on your query window and selecting "Include Actual Execution Plan"

Let's take a look at the following query:

view plain print about
1SELECT DISTINCT a.author_id, a.author_name, b.book_id, b.book_title
2FROM authors a
3INNER JOIN books b ON a.author_id = b.author_id
4WHERE a.author_id = 27
5AND b.book_published = 1
6AND b.best_seller = 1

As you can see, we have two tables, books and authors. Each of these tables are joined to show the author names for each book, and we filter by books that are published and are best sellers. This is where we'll be concentrating.

[More]

Getting the names of columns in a table

Good afternoon everyone, today were going to talk about meta-data.  Specifically data relating to tables in SQL Server.  Have you ever wanted to grab all of the column names of a table for some operation?  Well, here is a break down of a technique that can be used to do it.

The first table that we'll be working with is the "sysobjects" table.  This table contains all objects within a database.  Objects are things such as views, tables, and stored procedures.  If you want to see a full collection of this you can query this table and see it first hand:

view plain print about
1SELECT name
2FROM mydb.dbo.sysobjects
3ORDER BY name

To filter only tables you can add an xtype of "U"

view plain print about
1SELECT *
2FROM mydb.dbo.sysobjects
3WHERE xtype = 'U'
4ORDER BY name

 

Now on to column names, we can get the column names for a specific table by joining this on the "syscolumns" table. 

view plain print about
1SELECT mydb.dbo.syscolumns.name AS column_name
2FROM mydb.dbo.syscolumns
3&nbsp;&nbsp;&nbsp; INNER JOIN mydb.dbo.sysobjects ON mydb.dbo.syscolumns.id = mydb.dbo.sysobjects.id
4WHERE (mydb.dbo.sysobjects.name = N'table_name')

 

There you have it, working with these metadata tables is incredible useful, good luck on using it!

Previous Entries / More Entries