Error trying to re-install SQL Server 2005 on a Failover Cluster environment

I had to un-install the SQL instance that I have on my virtual cluster.  The reason?  I could not get the Service Pack 2 to be recognized by my BizTalk configuration.  Apparently, the SP2 that I had installed was the 9.00.3027.0 and not the 9.00.3042.1  Monish pointed that out that I might have an older version of the Service Pack [he is the only person I know that reads those EULA information...]

 

I am trying to start from scratch the installation of SQL 2005, and when I run the setup I get this message:

TITLE: Microsoft SQL Server 2005 Setup
There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=50000&EvtType=datastore%5cmachineconfigscopeproperties.cpp%40InvokeSqlSetupDllAction%40SqlInstallConfigScope.InstanceName%400x2

Well, clicking on that link, does not provided any more help. Click on the help and I get this other screen:

the last line tells me about the event type that has failed:

datastore\machineconfigscopeproperties.cpp@InvokeSqlSetupDllAction@SqlInstallConfigScope.InstanceName@0x2

Now, I have the code that causes the installation to fail. What's next?  ;)

Then I found this other technical article on the MSDN 925976, this suggested cleaning up the registry. I went and clear all of the registry entries from my SQLNode1 and I still get the same error.  I then follow the same instructions on my SQLNode2.  This still did not allowed me to run the setup.  So I went one step deeper and instead of removing just the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\ registry key like they suggested, I removed all hives starting from HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server.

Success..! now I am able to run the setup on my primary node, and I *WILL* install the correct Service Pack this time.. ;)

Data Dude for Studio 2008?

Does DataDude works for Visual Studio 2008?  The answer can be found on this blog post by Gertd:

visual-studio-team-system-2008-database-edition.aspx

Awesome, so it is time to create a new DB project.

I get this dialog box:

going back. I have followed the instructions and set my options to use my SQL server instance:

and my data connections is set to my local instance as well

since I don't have SQLExpress running, I decided to follow the instructions on it. I click CANCEL on the previous dialog box, and I get this error:

---------------------------
Microsoft Visual Studio
---------------------------
An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I remember this error from before.  I has something to do with having the SQL Server Browser running for it to find it.

I go and turn this service on. I usually have ALL services stopped and I only run the services on demand when I need to use them. Yes, I do have lots of batch files that the only thing they do is to start/stop services... :D

humm. I still getting the error about not being able to connect.

I wonder if this has anything to do with the fact that I am running the 64-bit version of SQL server? Just for kicks, I delete the name of my instance on the tools-Options

BINGO.  It works like a charm.  I went back and turn that SQL Server Browser service off.  It still works... :D [one more service that I don't need to run.!]

My environment is Vista  Enterprise 64-bit, SQL Server Developer Edition 64-bit, BizTalk 2006-R2, Visual Studio Team Suite 2008.  I don't know if this has anything to do with it, but by leaving the instance name blank, Studio 2008 was able to connect to my local SQL Server instance.

 

oh well.

Evaluation Period has expired

I have run into this issue today. I was trying to update some business rules on a client's computer. They have a SQL and a separate BizTalk environment. So I have tried opening the SQL Management Studio from the BizTalk box, and I got this dialog box:


it says:

Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy

which of course takes you to a Microsoft Page not found. ;)

Apparently, they had installed the evaluation trial version of SQL Server 2005 client tools. BTW, this was never an issue with SQL Server 2000. If you went and installed the client tools, they did not expire.!

I put the Enterprise SKU binaries for SQL Server 2005, and ran the Setup.exe from the tools directory, and then I got this information:



clicking on the messages details, it mentions this:

- Edition Change Check (Warning)
Messages

• Edition Change Check

• To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.

FYI, you will need to run the setup.exe that is located in the SERVERS folder and not on the tools folder. Bring up a command prompt window and execute the

cmd> SETUP.exe SKUUPGRADE=1

then after running through the wizard, it will upgrade your SQL Management version to not be the trial version.



ahhh, that's much better.!

BizTalkDTADb (Suspect)

While trying to undeploy a Biztalk BRE Policy, I had this error:

Error inserting tracking information for ruleset "Final_WI_EmpMedical_HospitalCare" version 1.0 into the tracking database: Error encountered while connecting to database: "Initial Catalog=BizTalkDTADb;Data Source=xxxxx;Integrated Security=SSPI;"

Looking at the Management Studio, I soon discover that the tracking database was marked as suspect.



a quick google for it, turn out some instructions to get the DB out of the suspect state. I have tried most of them (reset status, emergency mode, detach data files, DBCC with data loss, etc.)



However, nothing seems to be able to get this database out of that status. The Sql configuration manager was taking a long time to execute a refresh. I have tried to shut down the SQL Agent service, and it was not allowing me to stop it.

Then I decided to check on the System event log and I found this entry in there:

The driver detected a controller error on \Device\Harddisk0.

hummmm... Nothing else seems to be wrong. Then the BiztalkMsgboxDb went into suspect mode as well.

I went ahead and reboot this server. However, it did not come back and it was giving me the gray screen on the Virtual Server Manager. Running out of time, and being pressure to get this machine up and running, I hit the PANIC button and undo all changes (Turn Off Virtual Machine and Discard Undo Disks)



Now the machine is back and running fine. I have started deploying all of my artifacts and BRE's to it. Then I noticed than on the other VPC's I have the same message on the system event log.

Jim Holmes pointed out that I should be looking at the Host Event Log instead, and yes, there it was:

The volume "E" only has 0 MB of space left. The virtual hard disk "E:\Virtual Machines\bztXXXXX\VirtualPCUndo_biztalkXXXXX_0_0_0_14495508212007.vud" associated with the virtual machine "biztalkXXXXX" has been expanded, and very little space remains. The virtual machine will be paused.

It would have been nice to see this error somewhere else, but I guess I have learned to not hit the PANIC button to soon and start thinking outside the box.

Like BLindman says "...the error in front of you might just be the symptom of a bigger issue..."

I am wiser now.

SQL Server Database Publishing Wizard

I keep losing this url, so I am keeping a reference in here.

http://www.codeplex.com/sqlhost

this is the link to download it:

SQL Server Database Publishing Wizard 1.1

SQLEXPRESS Needed for VStudio for DB Professionals

Running on Vista 64bits, I get this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified)

I have read that SQL Express is no longer needed if you have SQL server installed locally. However, this error is getting to me. This error indicates that my surface configuration is no set up to allow remote connections. However, this is already setup to allow remote connections:




So far it looks like the Visual Studio for DB professionals does not support SQL 2005 64bits.




I have compare the installation of the VStudio for DB professionals with other developers in here , and they do not have SQL Express installed. So the SQL Express dependency is indeed gone now. However, I am not sure if this is a supported scenario, so I am forced to install SQL express on my machine to see if this solves the issue.



Whoahhh... SQL Express 64bits... ;) nice.
Once I clicked NEXT, Visual Studio wanted the correct version of the vs_setup.msi that I originally used to set the install Visual Studio.

It runs through the setup screens and it stops when it is checking for the Editions? I have originally setup Visual Studio Team System. Then added Biztalk, TFS. Then I added the Visual Studio for DB Professionals. Then I added SP1 in the mix. So I am guessing it is getting confused as to what edition I am running.

I get this error:
Error 1706. An installation package for the product Microsoft Visual Studio 2005 Team Suite - ENU cannot be found. Try the installation again using a valid copy of the installation package 'vs_setup.msi'.




No matter what I do, it is still giving me the error. So I went and download the Microsoft SQL Server Express Edition and installed that one instead.

After installing the SQLExpress, I still cant get VStudio to open a DB project. You will have to go into the surface area configuration tool and set the defaults in there.



Close all instances of Visual Studio. Bring up the SQL Server configuration Manager and enable remote connections. Another thing you need to do to get this working is to install the SQLExpress Service Pack 2 and there are some other goodies at http://msdn.microsoft.com/vstudio/express/sql/download.

Biztalk 2006 Installation on Multiple Servers

So this is the 3rd time I have encountered this error, so I am blogging about it... Hoping, that by blogging it, it will re-inforce this in my brain and next time I encounter this error, it wont take me that long to figure it out.

Installing Biztalk 2006 on an environment that was previously setup by an IT department with a zealous security guy making the decisions on naming conventions and user accounts is a NIGHTMARE. You can have firewall, ports, security policies, insufficient rights to SQL Server, application pools not setup correctly, etc...

This one environment I have had to work with was just like my IM name ;). Anyways, after about an hour or so trying to find out why the configuration of Biztalk 2006 will just suddenly die after creating 3 databases. I keep getting this one error:

BizTalk Server cannot access SQL server. This could be due to one of the following reasons:

1. Access permissions have been denied to the current user. Either log on as a user that has been granted permissions to SQL and try again, or grant the current user permission to access SQL Server.
2. The SQL Server does not exist or an invalid database name has been specified. Check the name entered for the SQL Server and database to make sure they are correct as provided during SQL Server installation.
3. The SQL Server exists, but is not currently running. Use the Windows Service Control Manager or SQL Enterprise Manager to start SQL Server, and try again.
4. A SQL database file with the same name as the specified database already exists in the Microsoft SQL Server data folder.

Internal error from OLEDB provider: "Cannot open database "BizTalkRuleEngineDb" requested by the login. The login failed."

And btw, I only had access to 2 domain accounts, instead of the 10+ accounts recommended to run Biztalk with. So finally my friend Monish mentioned about installing the SQL client connectivity tools on the Application server where Biztalk was being installed. duh. Just another case of RTFM. Here is the Biztalk Installation Guide. Cleaned up the old DB's and re-started the configuration, and this time it created all databases.

Makes me wonder, why is the configuration tool of Biztalk running and creating half way installation scripts, but yet it can't finish creating the rest of the databases. I guess I need to dig more information into this.

Search Macros vs Swickis

There is are so many customized search engines out there. We had the original Swickis, and now the Window Live team has come out with the Search Macros.

My only gripe about these macros is that it is very hard to discover what other search macros are available.

I have found this one that was created by the SQL Server 2005 BOL (Books Online Library) team.
http://search.live.com/macros/sql_server_user_education/booksonline

One click and you get access to the latest version of the books online. Versus:

1. downloading a 130+ MB msi file
2. installing it
3. opening CHM file to get to search prompt

If you are at a client site and need to research some sql info on the fly. I believe the Search Macros would be the winner.

SQL Server 2005 – SP2 available

We can *officially* run SQL Server 2005 on Vista... ;) All of those days of reading messages about SQL not being supported in Vista are finally over.!

Here is the query to determine your version of sql:

SELECT SERVERPROPERTY('ProductVersion')

9.00.1399	SQL Server 2005
9.00.2047	SQL 2005 with SP1
9.00.3042	SQL 2005 with SP2

You can download the SP2 at the SQL Server 2005 SP2 Download Page.

Returning GUID from STP

Calling a store procedure to return a GUID:

PROCEDURE lookupGetEquipmentGUIDFromDomainName
@piDomainName nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;

SELECT UPPER(EquipmentGUID) as EquipmentGUID
FROM Equipment
WHERE DomainName = @piDomainName
END

on the ADO.NET, make sure to get the .ToString of the object before converting it to a GUID type.

try
{
oConn.Open();
string s = oCMD.ExecuteScalar().ToString();
outEquipment.EquipmentGuid = new Guid(s);
}

This will assign the GUID coming back to a type GUID in the object.

SQL 2005 - Database diagram

This error always get me and it reminds me of something Brian has said on one of his agile presentations: First time it occurs, fix it. Second time is coincidence. Third time, refactor it (or something like that...) This is like the 4th time I get this error, so I'm writting it in here so that it get indexed and will help me remember how to fix it.

If you tried to restore a SQL 2000 database backup into a SQL 2005. You will find out that the database diagram does not upgrade easily. If after restoring the database, you click on the database diagrams and get this error:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

There are 3 solutions to this problem:
a. if the default compatibility is other that SQL 2005(90), then set the compatibility level back to sql 2005. This is done by right click on the database then select properties --> Options.



b. Execute the following command on the query analyzer window

ALTER AUTHORIZATION ON DATABASE::your_dbname_here TO sa

then after running this command, you can go ahead and add a new database diagram.

c. If you right-click the database you are interested on. Then select properties, select Files you will see that there is no owner specified on the text box for this database. Enter a valid userID (i.e. sa) in the box. Then the database diagram will start working.