Team Ravenglass

While searching for SQL Joins I found an article that I believe explains the different types of joins pretty well - A Visual Representation of SQL Joins

I don't know about you, but remembering what is returned with each different type of join is not my strong suit.

Do you have any tricks or sayings that help you?

This entry was posted on June 17, 2013 at 12:56 PM and has received 194 views. There are currently 0 comments. Print this entry.

While looking into an error with a SSIS task that executed a stored procedure, I found that SQL Server was truncating a string being used in a varchar variable.

This stored procedure needed to dynamically build a SQL statment, then execute it, based on a generated SQL string that was stored in a database table. The generated SQL string was much longer than anticipated when the stored procedure was set up originally, and the result was the generated SQL string being cut off in the middle and the final SQL to execute being invalid SQL.

To fix this, I modified the variable declaration for the searchQuery to be a varchar(8000) value and then modified the final executable SQL variable to be nvarchar(max).

Without the nvarchar(max) setting, the final SQL string just kept getting cut off in the middle and executing invalid sql statements.

Luckily, the generated SQL was invalid and just failed and gave me an error in the SSIS package. However, I can see how it would be possible for an unexpected SQL statement to be generated in the variable which could have very bad results if it was a valid statement that didn't do what you expected (for example a DELETE statement that didn't have the entire WHERE clause).

Fixed stored procedure example:

The lesson here is to make sure that your variable definitions will allow the full SQL statement to be generated before you dynamically create and execute.

This entry was posted on June 3, 2013 at 10:32 AM and has received 266 views. There are currently 0 comments. Print this entry.

As you may or may not know, I am an avid fan of database design. This morning I read an excellent editorial I just had to share:

Is the OLTP Programming DBA a Dying Breed?

Here's an excerpt: "...So, just as we no longer code in Assembler, then C, followed by C++, now we program in bloated languages like Java and C#. Not because either model is superior...because of Cost. At the end of the day, How Much Working Code can you produce and how much did it cost you? If you can purchase hardware for half the cost of writing smaller, faster, more efficient software, then maybe the best solution is to throw hardware at the problem."

I thought the article made an excellent point on the fact that we really do just throw hardware at problems more-so now than any time in the past, and many of the conventional old school DBA tasks can now be handled by web developers themselves. What do you think?

This entry was posted on September 26, 2012 at 12:52 PM and has received 441 views. There are currently 0 comments. Print this entry.

Sometimes you'll want to sort rows in your SQL queries in a way other than the default available sorting methods. That's just the situation I found myself in the other day. I didn't want to create a new column. Take the following SQL:

[More]

This entry was posted on June 27, 2012 at 12:17 PM and has received 1033 views. There are currently 2 comments. Print this entry.

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

This entry was posted on August 8, 2011 at 9:04 PM and has received 9039 views. There are currently 2 comments. Print this entry.

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]

This entry was posted on March 11, 2011 at 2:09 PM and has received 2162 views. There are currently 0 comments. Print this entry.

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

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.

This entry was posted on September 3, 2010 at 3:57 PM and has received 1462 views. There are currently 0 comments. Print this entry.

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]

This entry was posted on March 19, 2010 at 4:14 PM and has received 690 views. There are currently 0 comments. Print this entry.

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]

This entry was posted on February 19, 2010 at 6:13 PM and has received 1351 views. There are currently 1 comments. Print this entry.

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]

This entry was posted on October 13, 2009 at 3:35 PM and has received 834 views. There are currently 1 comments. Print this entry.