SQL Management Studio Error: An error occurred while executing batch. Error message is: The directory name is invalid.

While trying to execute a query inside Management Studio, I recieved this gem of an error. The solution is quite simple, just log off and then log back in.

My many hats

I wear a lot of hats where I work. People often ask me what hats I wear when I perform the many tasks at hand. Here is the list:

Network administration: a Sherlock Holmes hat

Database administration: wizard hat (and robe)

Website Administration: Baseball cap

Technical Support: Yamika

ColdFusion Programming: Sombrero

.Net Programming: a jimmy hat

Rebulding MSDB in SQL Server 2005

Again I spent a gorgeous day in front of the computer trying to solve a major problem.

Seems that because of some diskspace issures we had in April, my MSSQL configure got hosed and the server hasn’t been able to perform any backups nor has the sql server agent been running. This was due to the fact that the MSDB database has been corrupted.

The following are the steps that I took to get everything back up and running. I will warn you that by doing these steps, you will lose all you Maintenance Plans that you had and you will have to recreate them. Figure that this is a small price to pay because let’s face it, you got to have backups.

I would strongly suggest that you don’t be an idiot like me and make sure that you backup your msdb database. However if you are like me and didn’t do this, these steps should get everything back on track.

1. Put MSSQL into single user mode
   1. Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools - > SQL Server Configuration Manager
      
   2. Right click on SQL Server and choose Properties
   3. Click on the Advanced tab. Under Startup Parameters you will be adding the following parameters to the beginning of the string: -m;-c;-T3608
2. Restart SQL Server
3. Connect to SQL server through the Management Console. From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.
4. Detach the MSDB database using the following commands:
   use master
   go
   sp_detach_db ‘msdb’
   go

   and click Execute

5. We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.
   1. Usually these files are located in the following directory:
      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
      Your’s might differ.
   2. Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.
6. Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:
   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install
7. Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.
8. At this point you should have your MSDB database restored. The only thing left is cleanup.
9. Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:
   EXEC sp_configure ’show advanced options’, 1
   GO
   RECONFIGURE WITH OVERRIDE
   GO
   EXEC sp_configure ‘xp_cmdshell’, 0
   GO
   RECONFIGURE WITH OVERRIDE
   GO
10. Shutdown SQL Server
11. Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.
12. Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.

Let me know if you have a better way or a way to restore the configuration information from the old msdb database.

Other SQL optimization tips

Ben started a whirl wind of ideas with his recent post about optimizing SQL queries for better performance. I was getting too carried away with commenting and decided that further tips that I had should be put down in my own post. Below are the ideas that I commented on, along with some new ones.

1) Create indexes on foreign key(s)

I think, no wait, I know that this is the most overlooked design aspect on almost every database. Far too often people forget to create an index for the foreign key(s) that are on a table. I guess the reason is because since all database servers automatically create an index on the columns that you specify to make up the primary key, why shouldn’t it do the same for foreign keys? Well it doesn’t and this is why it’s the most overlooked and biggest culprit of performance lost in a database.

2) Normalization

How many times have you seen a statement that joins 10 different tables all to grab a single value from each table? I’m so guilty of this, I should be thrown to the wolves. Again this is a HUGE performance hog and can be avoided by sitting down and taking the time to normalize your database. What is normalization you ask? It’s the idea that some data that is in one table can be copied into another table to prevent a join from occurring. I guess an example I can give is:

You have 2 tables named employees and companies. Companies has an one to many relation to Employees. When you write a query to retrieve an employee’s company name, you create a join between the employees table and the companies table and retrieve the company name.

It’s simple and we all do it and it’s probably not the greatest example, but I wanted something simple. Now if you wanted to normalize this, you would create a column on the employees table called company_name and copy the value from the companies table into that column, thus preventing you from have to create a join when retrieving the company name in a query which will improve the performance of the query. This can be accomplished at the application level or by using triggers within the database.
Now I wouldn’t use normalization in this situation, again I wanted something simple to explain the concept. With that said, when should you start and how do you determine what to normalize all depends on the data and the time the queries are taking to retrieve it. Most of time though I only normalize simple data that isn’t changed too often. For that stuff I use views.

3) Views and Indexed Views

Yes they’re different and indexed views aren’t supported on all database platforms so this doesn’t pertain to everyone.

Normalization is tough to deal with and maintain. So how do we go about increasing the performance of our database with copying columns and data everywhere, we use views!

Views are basically queries that you can save and then use like normal table when writing other queries. The idea is that instead of writing the same sub-queries or derived tables all the time and throughout your application, you can move those statements into views and then interact with them as you would with any other table.

Views are a great place to house the business logic of your application when you don’t want to or can’t use stored procedures. I use them a lot in applications to calculate expiration dates of memberships, totaling up line items in a shopping cart or just about any other calculations that the application needs.

Imagine trying to maintain a set of complicated calculations and business logic in an application that is copied over and over in queries scattered everywhere? You can’t!

Now I understand that most people argue that calculations like these should be moved into classes with the application, but I’ve found that that’s not always a smart thing to do and can sometimes cause bigger performance lost then realized. Imagine if you would, you have a method in your application that calculates the expiration date of a member and you need to access to this information at the database level. Well if the calculation is performed at the application level, that means you will need to copy and translate the logic to your database. By moving this calculation to a view you now have access to the information from both your database and the application and it’s all in one place! Also now since the calculation is being performed at the database level it makes you write less code in your application and not have to calculation for each record you return.

Ok great… so what is an indexed view? An index view is the basically the same thing as a regular view only you have the option of creating indexes on it where as a regular view you can’t. This can greatly increase the performance of queries that use views or span across multiple views. BE FOREWARNED though there are STRICT guidelines that you must follow in the creation and use of these views and they differ between database servers. You must consult your database servers documentation when attempting to use them.

Examples on using and creating indexed views in MSSQL 2000 can be found here.

4) Clustered Indexes

At the beginning of this article I mentioned that placing indexes on foreign key can be a big help and that almost all database server automatically create indexes for primary keys. Well expanding on that is the use of clustered indexes versus regular indexes.

When you create a regular old index on a table your database server basically creates a file on the server with information about where everything is within that table. Nothing happens to the data within the original table. Not so when you create a clustered index. A clustered index tells the database server how to physically write the data for this table onto the server’s disk. This is the reason why you can only have one clustered index on a table and it’s THE MOST important decision you can make when talking about performance.

Remember the advice at the beginning about placing index on foreign keys? Well to expand on that, you should also determine if your foreign key should be used as or be part of the clustered index on the table.

Let’s look back at the scenario I gave between the employees and the companies. After we’ve written our application, we noticed that throughout our application, there are many times when we query to list or find employees that are part of a particular company. Further investigation reveals that the only time we’re ever querying the employees table directly is when We’re authenticating a login or retrieving their profile.

By looking at this scenario it would probably make sense to include the company foreign key within the clustered index on the employees table and make it the first column of the index. Reason is that this will dramatically speed up the seek time of getting all the employees for a company since they will all be located around each other.

Now let me pull the reigns back a little. I’m not saying that you should go about doing this on every table in your database. There are very specific situations where this action makes sense and it doesn’t occur often. The only way to be completely sure is to load test your database in a testing environment with the change. Another HUGE WARNING! Doing this will cause the physical restructuring of data on the disk and as such it can take an incredibly long time to complete this change on large tables. Again, only testing can determine if a change like this is worth making.

5) Dropping, Rebuilding and Defragging Indexes and General Maintenance

When was the last time you rebuild or defragged your indexes?

Have you updated the statistics on the database lately?

How about checked the integrity of your data?

When was the last time you backed up your data?

Do you have any idea what I’m saying?

If your aren’t continually performing proper maintenance on your database, none of the ideas I talked about make any sense doing. Without proper maintenance your database will continue to degrade in performance no matter what you do. Almost all aspects of maintenance is automated and it’s so simple there’s no reason not to do it. MSSQL is especially easy since it has the Database Maintenance Plan wizard to guide you though it all. Check out the documentation the came with your database server to see what maintenance options or wizards it comes with.

Linked Servers: Performing Select, Insert, Update and Delete (CRUD) actions

You could literally spend hours combing the internet trying to figure out how to do something.

In my last post, I spend 4 hours trying to figure out exactly how to get a linked server working with SQL Server. After I finally getting the server linked so I could perform simple queries against it, I needed to now figure out how I could perform inserts, updates and deletes to it.

Scouring the internet again proved totally a waste of time and I decide to just try to figure it out on my own. Well after another 2 hours, I have figured it out. Here is the syntax to do select, insert, update, and delete (CRUD) actions against a linked server. Remember that your syntax might vary slightly. All queries use the OPENQUERY command.

SELECT:

select * from openquery(’my_linked_server’, ’select * from table_schema.table_name’)

INSERT:

insert openquery(’my_linked_server, ’select column_1, column2 from table_schema.table_name’)
values (’my_value1′, ‘my_value2′)

UPDATE:

update openquery(’my_linked_server, ’select column_1, column_2 from table_schema.table_name where pk = pk_value’)
set column_1 = ‘my_value1′, column_2 = ‘my_value2′
DELETE:

delete from openqueryopenquery(’my_linked_server, ’select * from table_schema.table_name where pk = pk_value’)

There you have it, I just saved you 2 hours (can I get mine back).

SQL Server 2000: Linked Oracle Server Errors

I HATE ORACLE!!! Why does this always happen to me?

Again, I spent the last 4 hours of my day trying to get something as simple as a linked server in MSSQL 2000 to work. What I was doing was setting up a linked server between MSSQL 2000 and Oracle 9i and ran into a lot of problem. Below is ranting and how I finally solved my problem.

The one thing I can’t stand about setting up a linked server in MSSQL with Oracle is that it’s never the same. Sometime you can get away with using the “Microsoft OLE DB Provider for Oracle”, other times you need to use the “Microsoft OLE DB Provider for ODBC Drivers” and still sometimes you need to use the “Oracle Provider for OLE DB”. In summary you have 3 different providers to choose from depending on nothing but the toss of a hat.

Just to prove my point, I already have a linked server setup on another MSSQL 2000 server using the “Microsoft OLE DB Provider for Oracle” provider. On the server I’m setting up, I following the EXACT same configuration and used the same provider and it would connect. I’m getting Error 7312 and Error 7399 every time I try to connect and view the tables.

{Error 7399: OLE DB provider ‘MSDAORA’ reported an error. OLEDB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ]}

{Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider ‘OraOLEDB.Oracle’. [OLE/DB provider returned message: ORA-00942: table or view does not exist]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare::Prepare returned 0x80040e14]. }

After playing around for 4 hours, I finally found the right combinations to connect and run my queries. Here is what I tried. I will say this, that there is no definitive solution for this. You are going to have to try each suggestion and see which one works for you. I should note that I do all of this inside of Enterprise Manager.

First off, pick one of the following providers and create your linked server:
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for ODBC Drivers
Oracle Provider for OLE DB

Now once you have your linked server created, I expanded the link server and clicked on TABLES to see if I got a list of the tables or if I got the 7399 error. If you get the error, try a different provider until you get the list of tables. When you finally get the listing of tables, remember the value in the “schema” column for that table, it might come into play in the next part.

After you get the list of tables, open up Query Analyzer and try to do a query against the linked server. I have always had to use OPENQUERY to get the query to work. There are two syntaxes that you will have to try:

First try this:

select * from openquery(my_linked_server, ’select * from table_name’)

If you get the 7321 error try this:

select * from openquery(my_linked_server, ’select * from schema_value.table_name’)

One of those should work. If anyone else comes up with weird errors or a difference method to connect, leave your suggestion in the comments below.

Optimizing your database with indexes

While browsing Reddit, I came across an article about why you should index your database.

In short, the author finally figures out that using an index can decrease the execution time of a query dramatically. An incredibly uninformative article, since there is no mention of what he indexed or any advice as how you should go about doing it yourself.

Well being a database guy at heart I thought that I would give some hints in this subject since it’s a fairly common one. Many a times in my career I’ve been asked by people about what they should be indexing in their databases. I don’t know why, but people seem scared to place an index on a table. Let me just tell you that you aren’t going to destroy your database by experimenting with indexes, you might slow it down a bit, but you won’t mess up any data. So don’t be afraid to start playing around.

So what should we index? As a rule of thumb you should be indexing the following:

1. Any foreign keys linking table together (you will be amazed that this is the most overlooked index to create)
2. Any columns that you will use to create joins between table (usually these are the same as the foreign keys, but they could differ sometimes)
3. Any columns that you will be filtering against in your queries (these are columns in your queries that you reference in your where clauses)

By going down those 3 items, you will most likely see a performance gain in your database. Now there are some gotchas with this:

1. Be careful to not index a column more then once. This could be common if you place an index on a foreign key and then include that foreign key within another index. There are obviously exceptions to the rule.
2. You don’t want to place an index on every column in a table even if you do use even column in where clauses throughout your application. By doing so you will be killing the performance of the table. Try to use judgment in selecting the most important columns within the table.

Can you have too many indexes on a table? Of course you can! One of the ways that you know that you have too many indexes is if you see a lag when altering data on a table (inserting, updating and deleting). Remember that your database server has to maintain and do housecleaning on all indexes affected by altering the data on a table. Because of this, if you place too many indexes on a table, it will slow down the write and updates of the indexes which will cause lags.

For people just getting started with indexes and using SQL Server, a great way to learn and practice is to use the Index Tuning Wizard that is included with SQL Server. To practice, place a slow running query or a query that you want to optimize in Query Analyzer and execute it. Next, try figuring out on your own which columns on which tables you should index in order to make the query perfomrm better and write them down. Run the Index Tuning Wizard and see if your suggestions matches the ones that the Index Tuning Wizard comes up with.

Good luck optimizing your tables with indexes. If you have any questions, suggestions or comments, leave them below.

Opinion: To Use Or Not To Use Stored Procedures?

I was browsing around on Reddit today and came across an article that particular struck my interest: To Use Or Not To Use Stored Procedures?

What intrigued me was that I have been a proponent of stored procedures in the past and now avoid them like the plague. I think the switch came when I started to realize how difficult and time consuming it was to write and maintain a project using stored procedures versus a project that just use parametrized queries. I also remember how difficult it was to write dynamic sql statements within a stored procedure.

I think really the only reason I used them in the past was because CF at the time didn’t support cfqueryparam and it as the only way to protect yourself against SQL injections. Once cfqueryparam came onto the seen, it took awhile for me to adopt it in my code and actually realized the benefits. Now I could write my SQL statements within CF and still have the security of a stored procedure protecting me against SQL injections. It was a blissful time; I could really pound out projects and code and debugging an application became a snap.

There were a few still out there that still voiced the myth that using stored procedures gave you a speed boost over
parameterized queries. Those people were soon put to rest when most RDMS and ODBC drivers made it so that using either way gave you the speed benefits of caching the query plan. Now this point is mute.

Today, I silently laugh at people that still think that using stored procedures in projects as the main way to communicate with their database. By creating views, using cfqueryparam and knowing the proper ways of using the different joins available; I can mimic almost any behavior a stored procedure can do when it comes to pulling data from a database.

To me I see the stored procedure in CF going the way of CFX tags… away.

SQL Server 2005: Import / Export reset identity keys… no workaround… 2005 sucks period, the end

BEGIN SOLUTION

There is now a solution to this problem although it’s not the greatest. The solution was found in the Microsoft Forums from this post. Another blog (High Hopes) has details on what to do. I’m includes parts of the post here as a reference to everyone finding this page and also so I don’t lost this information if the blog goes down.

STEPS TO ADD DTS WIZARD WITH SQL SERVER MANAGEMNT STUDIO EXPRESS

1. Try “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe”
If it worked, you already have the DTS Wizard. Start using it right away.

If it will not work, continue to step 2

2. Download the Microsoft SQL Server 2005 Express Edition Toolkit (223.9 MB) from

http://go.microsoft.com/fwlink/?LinkId=65111
select all components to install

3. Run “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe”

Thats it.. Now you can transfer data using the the great DTS wizard.

Follow the steps If you want to integrate it with the Sql server management studio express UI .
1. Open sql server management studio express.
2. Select Tools -> External Tools
3. Add a Title and Browse C:\Program Files\Microsoft SQL Server\90 \DTS\Binn\DTSWizard.exe for the Command field.
3. Click OK

I have personally tried the steps outlined above and they work like a champ. This issue is considered CLOSED(?)

END SOLUTION

I’m getting a lot of people saying that they are disappointed that this problem isn’t fixed. Although I feel for you all, it doesn’t do any good venting your frustrations here. You need to be telling this to your sales representatives for Microsoft or where ever you bought SQL2K5.

Another thing you can do (if you can do it and your infrastructure allows it) is to down grade to SQL2K and then demand a refund from Microsoft sighting that their product doesn’t live up to your expectation.

This is the only way that Microsoft will listen.

UPDATE: You gotta love Microsoft’s cop out answer for why this doesn’t work and for leaving this bug open. There is no word yet on whether this will ever be fixed.

UPDATE: Seems I’m not the only one who’s pissed off by this.

UPDATE: Digg this thing up!

UPDATE: Reddit me!

I have officially labeled SQL Server 2005 worthless beyond belief. I really have no idea why something as basic as the import / export wizard (IEW) inside 2005 is broken beyond belief. Let me explain.

I have a current database that is a 2000 database with a bunch of tables that use an identity field as the primary key. What I’m trying to do is export the data that is in those tables to the 2005 database using the IEW. I’ve must of done this a thousand times in my career and have never had a problem doing it before. Normally I just run the I generate the scripts first (see my other blog post about the workaround for this with 2005) and then after creating the database on the new server, I just export the data from the old to the new. Easy, quick, never fails.

Now with 2005 there is a BIG bug that when you go to export the data from the old database to the new one; all of your identity values are reset. To put into perspective, let’s say you have a table that has 20 rows and the identity values range from 1-30 because of deletions that have happened on the table. When you go to export this table to the new table in 2005, you identity values are now 1-20 corresponding to the number of rows that are in the tables.

As you can see this is a big problem because it completely corrupts your data and renders it useless.

I’ve tried some workarounds that people have suggested to no avail. I really can’t believe that a bug like this has gone unnoticed and still isn’t even addressed within SP2 for 2005. Seems that Microsoft really screwed the pooch on this one and it make 2005 a piece of shit. Futhermore there isn’t an offer on my hosting plan to downgrade SQL Server to 2000; so I’m screwed.

I have found though that this isn’t the case if you use GUID for your primary keys, which I do now but in this database I use identity columns since I didn’t know any better at the times.

If anyone out there knows a workaround or a patch for this please speak up. Also voice your concerns to Microsoft about this since this is a bug that should of never existed in the first place. Until this problem is fixed, I’m officially calling 2005 shit and I’m recommending to all my clients to not upgrade.

SQL Server 2005 sucks so far.

I finally had to take the plunge into SQL Server 2005 since HostMySite only offers 2005 and not 2000 on their VPS plans. Needless to say I’ve heard nothing good about 2005. I’ve talked to countless DBAs telling me that it’s dog slow and EXTREMELY clunky to use. Well I’ve come to the conclusion that they are right.

Mind you I’m just now getting into it, but already I found 2 gotcha.

First off the Script Generator sucks. The interface is so horrible, I feel like I’m programming in Visual Basic with the way you have to choose your options. Not to mention that thing is SLOOOOOOW. It took 3 times as long to generate scripts for my database as Enterprise Manager did. The big kick in the nuts is that it doesn’t write the script correctly! The old 2000 script generator would write the scripts for the tables first and then the scripts for all your views next. Not 2005’s, it intermingled the tables and views scripts so if a view has a table in it that hasn’t been created yet… it FAILS!!! Is that fucking stupid or what!?! The only way around this is to generate and run the scripts for your tables first and then generate and run the scripts for your views. Great, so not only do I have to wait longer, I have to do double the work as well. Thanks Bill!

Second thing I found was another speed issue. I don’t know how many of you have ever used the import / export wizard in Enterprise Manager; I use to use it all the time and it was a life saver. Well needless to say now in 2005 the import / export process takes a loooooooooooooong time…… I’m talking long like the Nile, like line at Domino’s in a fat camp, long like John Holmes! I couldn’t believe something that use to take 30 seconds before took 10 minutes. Thank God I only have to do that once in a while.

My other gripe is the Management Studio in general. Why Microsoft now feels that everything should look like Visual Studio is beyond me. Enterprise Manager (though ugly as hell) was very clean and uncluttered, compare to Management Studio which looks really slick but a total mess when it come to finding things.

I guess I just need sometime to figure everything out. I’m sure I’ll find other quirky things along the way in my journey and if I do I’ll post them.

UPDATE: I was about to rip Angus (see comments below) a new one when it kinda dawned on me that I never checked what service level SQL Server 2005 was on. I guess I just assumed that it would be at the latest level since it’s a VPS and HostMySite applies the upgrades to the VPS. Well let me fall on my sword by saying that I checked late last night and I was wrong. SQL Server was at SP1 not SP2. I tried installing SP2 and it wouldn’t let me, so now I have a ticket opened with HostMySite to see if I can get it updated to SP2. I’ll post something about my new experience with SQL Server 2005 once SP2 is installed.