Tuesday, December 22, 2009

The Great Web Project Conversion

Our current Intranet is an intricate mess of 3 different versions of ASP. It consists of classic asp pages and it's also a .NET 1.1 web project along with some .NET 3.5 web projects thrown in using different versions of .NET code libraries. We have grand plans of converting everything to the newest version of .NET while making a brand new Intranet. However, we keep getting new project that keeps preventing the continuation of this scheme. I recently got fed up with the issue and carved myself enough time to attempt to convert at least the main Visual Studio 2003 web project to Visual Studio 2008 to eliminate at least one version of .NET. As with everything Microsoft related, this did not go without some adventures.

Step #1. I ask our server admin to create a website. Then I copy over the files that belong to this particular project along with just enough of the classic ASP pages so I can make sure I can log in and the website works. (The default landing page is in classic ASP.)

Step #2. I open the project in VS 2003 to make sure I can do it and that I can build the project. So far so good.

Step #3. I open up the VS 2003 project file using the Microsoft Visual Studio Version Selector. I get a warning that this will convert my project. Great! That's what I want. The project converts without a hiccup.

Step #4 is where the trouble begins. I try to open the project in VS 2008. It tells me that it "The local IIS URL http://convertedproject.com specified for Web project convertedproject.com has not been configured. In order to open this project the virtual directory needs to be configured. Would you like to create the virtual directory now?" It is set up as a website so what does it mean it is not configured as a virtual directory? If I click No, the project won't open. If I click yes it tells me "Creating a virtual directory is only supported on the local IIS server." Now what?

Step #5. I remember that VS 2008 differentiates between a web site and a web application. Maybe the conversion process converted this to a web site? I go to File>Open>Web Site. I navigate to the folder where the project/site is located. And it opens!

Step #6. I try to build the project. I can't compile because I get the following error: "Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed." A little searching tells me I have to adjust the code access security on my computer for .NET 2.0 project because I am trying to access the project from another server on our network. However, I do not see the Microsoft .NET Framework 2.0 Configuration under Administrative Tools on my computer.

Step #7. It turns out I have to install the .NET 2.0 SDK to adjust the code access security.

Step #8. Adjust the level of trust to Full Trust for Local Intranet. Here is how you do it: Administrative Tools > Microsoft .NET Framework 2.0 Configuration > Configure Code Access Security Policy > Adjust Zone Security > Make changes to this computer > Local Intranet > set to Full Trust.

Step #9. Attempt another compile. It still won't compile. It complains about a web.config file that is part of another project that is located in a subfolder of this converted master project. I can't exclude the folder from the project because I do not get that option on right click. Back to searching. I find that I can either make the folder hidden in the properties (which I tried but didn't work) or I can make that folder a virtual directory and then it will be automatically excluded. This folder is a virtual directory/application in the original application but not here yet. I made it into an application. Still Visual Studio keeps insisting on including that folder. In fact after a while I got this message on build: "Failed to start monitoring changes to because the network BIOS command limit has been reached." The solution for this seem to mean messing with the registry but fortunately I didn't have to go that route. Read on.

Step #10. After another round of search I find out that the virtual directory will be excluded only if I open the application via HTTP. So I close the project and go back to File > Open > Web Site. On this dialog box I see several different options on the left side. I choose Remote Site. I specify the site URL and this time I get this message: "The Web server does not appear to have FrontPage Server Extensions installed." Are you kidding? Our server admin did not like this at all and was hesitant to install it. One of our programmers was there during our conversation as well and he said it really should be a web application not a web site. I told him I tried to go that route but bumped into a wall. However, I'm willing to give it another try.

Step #11. This is basically the same as Step #4 which is trying to open up the vbproj file. Same message. While I'm pondering I was clicking around and I see that while the project did not load I still get the following option on the right click: Edit convertedproject.vbproj.

Step #12. I open up the vbproj file which is just a confusing looking xml file. I am scrolling through it to see if anything makes sense. I get towards the bottom where I see this node: UseIIS and its value is set to True. Hmm, what happens if I set to False? OMG, the project opened up???!!!

Step #13. I don't dare get my hopes up. I was able to open up the project previously but could not compile. What happens if I try now? It did not compile. :(

Step #14. I have to fix a few compile errors that are at least not as mysterious as the previous messages. All I have to do is convert a few things explicitly to string.

And with this last step my great conversion project is essentially complete. I still need to do a few tests to make sure everything works but it's done!

It took wading through only 6-7 error messages. Not bad for a day's work!

Friday, December 11, 2009

SSIS - A rowset based on the SQL command was not returned by the OLE DB provider.

I have had a dataflow set up to transfer some snapshot data from one database to another that has been working fine. However, I got a task of adding additional data to the dataset as well as back-filling my stored snapshot data as well. Obviously I warned my managers this was not a good idea as the backfilled snapshot data will not be correct. Alas, I still had to do this.

To make this task quick I figured I would just reuse my already existing dataflow and I would just modify the stored procedure that returns the recordset temporarily to get the old data.

My original query in the procedure was just simple select statement but my temporary query had to use a table variable. I had everything set up and I was ready to run my dataflow. However, I got the "A rowset based on the SQL command was not returned by the OLE DB provider" error. First I thought that perhaps SSIS has a problem with my columns not having exactly the same datatypes as the original query. So I made sure that the temporary table uses the exact same datatypes as the original query. I still got the same error.

I did a little searching on the Internet and I found the very simple solution: put SET NOCOUNT ON at the beginning of my stored procedure. Sure enough everything was fine after that.

I did a couple of tests and it seems like that while the SET NOCOUNT ON statement in the beginning of stored procedures is always a good idea, in SSIS if you use a table variable you MUST have it.

Friday, December 4, 2009

Adventures in SQL Land: Edition and Version Upgrades

My latest task was to upgrade SQL 2005 Express version to SQL 2008 Standard version. The SQL 2005 instance was installed by Office Communicator Server 2007 R2. I thought it would be a straightforward task since I have done both SQL edition and version upgrades before. Oh how wrong I was!

Problem #1

There are two ways to get from SQL 2005 Express to SQL 2008 Standard. Both require two upgrades. One way is to first upgrade SQL 2005 Express to Standard and that to SQL 2008 Standard. The other way is to upgrade SQL 2008 Express and do an edition upgrade to Standard.

First I tried to go the Express-->Standard-->SQL 2008 route and that's where I hit the first problem. The SQL 2005 Express version had SP2 applied already. That meant that running the SQL 2005 setup.exe with the SKUUPGRADE=1 switch gave me the following error: "Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. "

Ok. No big deal. I'll just get the SQL 2005 files with SP2 or SP3 already included.

Problem #2

Unfortunately the only install files we had did not have a separate Setup.exe which is required to do an edition upgrade for SQL 2005. So I abandoned this route.

Problem #3

Let me try to upgrade to SQL 2008 Express first then. I didn't have the install files so I had to download it from Microsoft. I had a little trouble with that because I kept coming across with the web installation option as opposed to finding the old-fashioned download page. Finally our server admin suggested I'd search for the redistributable SQL 2008 Express so I found it. I feel a dummy for not thinking about it but I rarely have to download from Microsoft as we have everything on a network share. Here is the link if you need it: http://www.microsoft.com/downloads/details.aspx?FamilyID=58ce885d-508b-45c8-9fd3-118edd8e6fff&displaylang=en
Problem #4

Finally I have the exe to run but when I run it I don't get the option to select an instance to install. What on Earth is happening? After searching for this and searching for that I came across someone mentioning that you can't upgrade 32-bit version to 64-bit. Hmmm... Sure enough the SQL 2005 Express that the Office Communicator Server 2007 R2 installed is the 32-bit version even thought Office Communicator Server 2007 R2 can only be installed on a 64-bit server. Nice job, Microsoft. So I downloaded the 32-bit version of the installation file and now I was finally be able to upgrade from SQL 2005 Express to SQl 2008 Express.

Problem #5

I'm halfway there now let me do an edition upgrade. I run the Setup.exe file for SQL 2008 and under the Maintenance I select edition upgrade. On the system check there is a error: "Upgrade architecture mismatch" The upgrade is running in 64-bit so it can't upgrade the 32-bit version of SQL. You are trying to tell me that the upgrade can't detect what version I want to upgrade? OK. Onto some more searching. Aha! I found it. On the main screen before selecting the edition upgrade there is an Options section on the left side of the window. Clicking on that I can tell the upgrade process what architecture to use. I select x86 and the edition upgrade us finally successful!

Problem #6

The original task has been completed but as a good DBA I need to set up some maintenance plans for this instance. However, SQL Agent is missing. I go to SQL Server Configuration Manager and I see that the Agent is there but disabled. Oh it will be easy! I just have to enable the Agent and start it. I should have learned this by now but nothing is easy anymore. I can't start the Agent because of the following error: "SQLServerAgent could not be started (reason: Error creating a new session)." Urgh! Back to searching for a solution. I found it on a KB article page: http://support.microsoft.com/kb/955763 Since the server is running Windows Server 2008 I had to specify a user for the Agent and gave it sysadmin rights in SQL Server. I am not happy about the last one but there is not much I can do about it.

So what should have taken me just a couple of hours at most took me more than a day. Learn from my example: run "Select @ @Version" before you do any upgrades and note both the service packs applied and architecture it is running on. If I had paid more attention to that information I could have saved some time for myself.

Wednesday, November 4, 2009

DBCC to the Rescue!

It's rare when I find myself in a situation like this. One of the hard drives (E) died on one of the servers that housed the log files for some databases. So log files are gone but the database (mdf) files are still intact. No big deal, we'll just recover everything from backup. Right.

Well, there was no backup done for this server. At all.

I know! I know! What am I doing as a DBA as that's the first thing a DBA makes sure of is to have backups of everything. It happened because our server admin neglected to tell me that they had SQL Express installed on this box and not only that but what he has originally intended only as a dev box for the Office Communicator backend has been promoted to live a while back!

I have tried a few tricks to get the database back online but nothing worked. In fact I made matters worse because somehow I managed to make the database dissapear (a.k.a. detach) which was not my intention.

Eventually I got the database to attach by doing the following:
  • I created a blank database with the same name.
  • I shut down SQL Server.
  • I replaced the blank database file with the original database file.
  • I deleted the blank log file.
  • I restarted SQl Server.

So the database appeared in the list but it was in a recovery mode so still useless and I was back on square one.

I thought that's it, we lost everything... until I found this page from SQLSkills. I would like to give full credit to Paul S. Randall for this solution. His page explains everything about how to use DBCC to have SQL Server rebuild the log file in emergency mode. I will just post the actual script to use. If you need to use this script, replace DB_in_distress with the name of your own database.

--put the database in emergency mode
--set it to single user mode
--rebuild log
--check to make sure DB is back online
SELECT state_desc FROM sys.databases WHERE name='DB_in_distress'
--put it back into multi-user mode

I'd like to mention one important point. SQL Server insisted on using the original location to recreate the log. Therefore this solution did not work for us until we recreated the E drive that caused all this problem.

Obviously, as Mr. Randall mentions, this should be used only as the very, very last resort and you can expect some data loss. However, some data loss is still better then all data lost.

Wednesday, October 28, 2009

Deleting a TFS Project

I spent part of my day today in Team Foundation Server reorganizing. TFS is still a fairly new addition to our development processes and no one is the true expert to whom I could go with a problem. I wanted to delete one of the TFS Projects and I could not figure out from the interface how to do it.

This is what I discovered.

You have to open the Visual Studio 2008 Command Prompt and run the following:

tfsDeleteProject /server:teamserver "Project Name"

In this example the teamserver is the name of your team foundation server.

You will get a dire warning upon entering this command that it will delete everything relating to this project and it will let you change your mind. It scared me enough that before entered "Y" as a confirmation I double-checked to make sure that all TFS databases were backed up today without a fail.

Friday, October 23, 2009

Failed to notify 'operator' via email.

I had to shuffle some SQL Servers around along with SQL Jobs. Our SMTP server has changed as well so I had to run through all SQL Jobs to make sure notifications are properly set. As I'm testing some notifications I realize that e-mails are not going through. What can be wrong?

  • Database mail enabled.
  • Test e-mail goes through.
  • Operator set up correctly.
  • Notification is set for job completion so it should go through no matter what.

However, in the History of the SQL Job, there is the following note: Failed to notify 'operator' via email.

I vaguely remember this error from the last time I was setting up a new SQL Server but I could not find my notes as to what the fix was. So I do my usual search and I find the solution.

Solution: Go to SQL Agent Properies>Alert System and enable the mail profile and restart SQL Agent.


I have been working for the University of Advancing Technology for a number of years. I started as a programmer and over the years I have transitioned into a database administrator role that encompasses everything from database programming to BI report design. As everyone else the Internet has been my greatest source for information whether I was trying to dabble in something new or running into a problem.

With this blog I am trying to give back something to the SQL community as well as document my efforts in the ever-changing world of technology. Please comment to critique, to expand on the post or simply thank me if I was able to help.