Rolando's Corner

SQLCMD - Being able to run mutliple .SQL files

2007-07-04T00:34:00.000-05:00

Recently I came across a scenario that required me to generate and keep individual .SQL files. However, I also needed to run them in a certain order all the time there was a change to the database schema.

I found a way to run them at one time versus running them individually, using the new MicroSoft SQL Server Management Studio (express) for SQL 2005 db server.

Although the instructions do provide the commands allowed one thing I had to figure out on my own was the syntax for the comman :r.

The command :r is the command which you can use to run a .SQL file by specifying a path to the file. However, in my case I had spaces in the directory path. (i.e. C:\Filepath\file name.sql). For this reason I had to enclose the path with double quotes. (i.e. :r "C:\Filepath\file name.sql").

You will need to write one :r command for each .sql file you want to execute. In my case there were 4 .sql files. This reduced the amount of steps I had to perform everytime I had a schema change.

Update I have found that altough this provides for convenience it does not parse the files correctly if the files contain appostrophies/ or single quotes ( ' ) in the values of a query. For example, I had some insert statements that contained values like O''Grady. The additional bolded appostrophy is required in order for the parser to recognize the following character as part of the value, instead of termination dillimeter of the query. Or at least I thought. When I ran the query in MicroSoft SQL Server Management Studio (express) it ran fine. When I executed the file, following the instructions above in my original blog, I found the values that were being inserted were infact incomplete. In the example of the O'Grady insert statement, it inserted Orady instead. As to why this happens, well my assumption is that the parser that SQLCMD uses is not the same that MSSMS uses. I could be wrong but after this disappointment I didn't want to spend more time then I had to.

I still use the SQLCMD for .SQL files that don't have query values with appostrophies. However, I try to keep all the queries that do have appostrophies and run them in the query window. Still reduces the amount of steps but not down to one as I was hoping.

I.E. Development Toolbar

2007-03-24T22:32:00.000-05:00

I came across a new free Microsoft tool for assisting me in developing websites. Internet Explorer Developer Toolbar beta 3 can allow developers to easily understand and troubleshoot complex web pages.

The core function of this tool exploits the document object model (DOM) in a simply done user interface. It can be pinned to you're browser while exploring a web page. You can read about more of the other features on the Microsoft Download Center where this tool is available.

OOP Object Oriented Thinking?

2007-02-03T00:01:00.000-06:00

I was first motivated to write this for a buddy of mine who's just starting out in the software development industry, when I quickly realized others can benefit from this as well.

The purpose of this article, is to educate (in not so technical terms) those interested in object oriented design / programming, also known as (OOD OR OOP). This is the fundamental basics to modern software development.

My goal is to share my point of view, including those that contributed to the motive. I will not, however, explain how to use a specific programming language nor tool(s) , but I do intend to help you better understand the general purpose for using them.

The objective to modern software development is to understand the business model of your clients first. This means understanding their terminology, business rules and the entities of their business. Trying to get a the big picture of this model through many meetings with the client can be quite challenging, especially when you don't have the luxury of starting on a project from the beginning.

What I like to do first when I join a project is try to identify the purpose of the application, which they hired me to help develop. So the first question I ask is, "What is the application supposed to do?". Although this may seem like common sense, you would be surprised how easy it is to lose focus on the intent of the application, once you begin to get very involved with the specific requirements. Especially, when "nice" features tend to creap up. After I get my answer, then I have a better understanding of the goal in mind. In order to help get my point across as simple as possible I will use the following scenario.

A client needs an application that can add, edit and search for their customers.

Okay, simple enough. Now I have identified the players in the game. In this case Customer can be identified as an entity. Now lets take a closer look at the requirements.

Add: The application has to be able to add a Customer into the system. Okay. So what type of information should be added into the system? This information is typically represented as properties of a Customer. (ex. Customer Name, Customer Address, Customer Phone number, etc...). There may even be some rules which the application should apply in order for someone to add a Customer. For example, a Customer can't be entered into the system if no name is provided or the Customer must be over 18 years old or has to have a bank account number. You get the point.

Edit: The application has to allow for someone with access and authority to edit Customer information. Although the specifics to this requirement was not so obvious, a company usually doesn't want everyone in the company to have the ability to edit the information. They may require that the application have some security built into it. Which will require a security model to be designed and implemented. A good resource can provide the essential basics to complete this task.

Search: The application has to allow for people to search for Customers. So what should they be able to search on? This could be any property on the Customer right? Sure why not. Although there may be other things like computed values to search on. For example, being able to search on a customer who bought 10 items. This may not be a Customer property. In order to keep track of this you have to know what items the Customer bought. Hmmm... sounds like we have other information you need to store like Products and inventory and prices and more.

The point is that we are beginning to identify the entities of the business.

A modern concept called "Domain" or "Object Model" is used to represent such entities. This should not be confused with any other acronymns out there such as the DOM (Document Object Model for Javascript).

In most of my projects, a domain model was defined with the same names as the names my clients used. Most, if not all my business domain models where stored in a databases, although some have been represented by xml schema(s).

In this case I used a Customer, but there are other examples such as an Employee, Supplier etc... I also defined the properties which were associated to each domain. In the example of the Customer, I had identified properties such as the Customer's Name, Address, Account Number, etc... which were stored as columns in the Customer database table. I defined the interactions between the models with behaviors or so called Events. For example, in order to become a Customer, the business required that the Customer register and create an account. The behavior or event in this example was that once the Customer filled out the proper registration forms, a unique Account Number was created. Now I can validate a Customer with an Account number at any time.

Although this is at a very high level, I hope that you begin to understand the concepts behind Object Orientation Thinking or better known as OOD / OOP.

As you develope using this Object Orientation thinking, you will not be successful until you also understand the foundation that makes this concept work. This includes Inheritence, Encapsulation and Polymorphism the pillars of Object Orientation.

As hard as this may seem to understand at first, once you begin to grasp and nurture this concept, you will begin to realize the potential. However, this doesn't go without saying that you MUST also know how to use modern software developement tool(s) in order to produce mainstream applications. Most systems have proprietary tools and languanges, which usually have a cost associated with it, while others are open source and configurable without any cost. Except effort and time of course.

One final note:
Although, objects and their relationships provide a majority of the characteristics of a DOM, domain object models influenced by events that occur between objects.

Generating SQL Insert Statements From A Data Table

2007-01-10T22:53:00.000-06:00

Every now and then one is faced with a situation where they have to write and execute insert statements with existing tables and data. Having to personally deal with this before, a friend of mine shed some light as to how to generate SQL Insert statements. This comes in handy when you may not have access to tools, such as Microsoft's Enterprise Manager for SQL server or of the sort. Below is an example of such a solution.

The following is a simple example, but it can be used as a guide to generate various queries.

select('Insert into TableName + '(' + TableColumnName1,TableColumnName2,TableColumnName3 + ')' + values(''' + TableColumnName1 + ''',''' + TableColumnName2 + ''',''' + TableColumnName3 + ''')') from TableName

The basic principle is concatenating strings. The text in italics serve as parameters, which represent your table's name and columns.

In my case, I used this to generate an insert statment for each row in a static table. The output to this looked something like the following.

Insert into TableName (TableColumnName1, TableColumnName2, TableColumnName3) values(' TableColumnName1','TableColumnName2','TableColumnName3') from TableName

As simple as this may seem, there's a few things to consider.

After you run this script, if you have data that contains appostrophies, you will need to manually add the proper set of single quotes to the output in order to escape those characters.

The opposite is true when dealing with Integer types. You don't need to add the single quotes within the script. Update The example as is will not work for Integers or any other type other then varchars. You will encounter an invalid casting message. I have yet to solve this one as well.

This will not work with tables that contain nulls. The script will abend once it encounters nulls.

If anyone can post a solution to this, I would highly appreciate it.

Issue with opening VS.Net web projects.

2006-12-27T18:21:00.000-06:00

Recently, I encountered an error message when attempting to open a web project in Visual Studio 2003. The message stated "Refreshing the project failed. Unable to retrieve folder information from the server." This is a generic message.

In my situation, this was caused by a combination of using Subversion and TortoiseSVN as my source control.

Within the help files I found the following solution to this issue.

"Use "_svn" instead of ".svn" directories
VS.NET when used with web projects can't handle the .svn folders that Subversion uses to store its internal information. This is not a bug in Subversion. The bug is in VS.NET and the frontpage extensions it uses. Read Section 5.26.7, “Subversion Working Folders” to find out more about this issue.
If you want to change the behaviour of Subversion and TortoiseSVN, you can use this checkbox to set the environment variable which controls this.
You should note that changing this option will not automatically convert existing working copies to use the new admin directory. You will have to do that yourself using a script (See our FAQ) or simply check out a fresh working copy."

The checkbox they are referring to is within the "Settings" dialog. This can be located from the Tortoise's context menu (usually accessible by right clicking on the directory of your project). You can find the checkbox within the General section.

In section 5.26.7 it goes on to say...

"5.26.7. Subversion Working Folders
VS.NET when used with web projects can't handle the .svn folders that Subversion uses to store its internal information. This is not a bug in Subversion. The bug is in VS.NET and the frontpage extensions it uses.
As of Version 1.3.0 of Subversion and TortoiseSVN, you can set the environment variable SVN_ASP_DOT_NET_HACK. If that variable is set, then Subversion will use _svn folders instead of .svn folders. You must restart your shell for that env variable to take effect. Normally that means rebooting your PC. To make this easier, you can now do this from the general settings page using a simple checkbox - refer to Section 5.26.1, “General Settings”.

One thing to keep in mind is that making this setting change is a general (global) change and will impact other existing working folders. You may find that when you try to perform an "Update", Tortoise will give you a "Skipped" message. What this means is that you will have to perform the steps of this solution for every working directory, eventhough those directories don't contain any web projects.

I haven't tried to duplicate this on VS.Net 2005 yet, but will post my results of that as soon as I do.

Resharper Holiday Special

2006-12-24T00:14:00.000-06:00

I'm a bit excited to now own my very own personal license of Resharper. This is a great addition to the Visual Studio IDE for developers, which provides nice features to increase productivity.

Enough with the hype. The main reason for this post is to highlight the special Resharper has for this year. Its not that easy to find this page through they're site so I thought I'd share this. This is a limited offer and for just under $100 it's worth it. It currently runs for about $149.

Cropper anyone?

2006-12-17T00:55:00.000-06:00

If you haven't heard by now, Cropper (by Brian Scott) is one of the best (free) screen capturing tools out there written in C#. Simply download it and use it to replace the old ALT+Print Screen function. Yes, there are other tools that do this, but for those developers out there, you can even download the src code to work on adding your own plug ins (i.e. Jon Galloway's animated gif pluggin).