Tag: sqlserver

SQL 2008 Express installation errors with 0x84B10001

In my case I was trying to update SQL Server Management Studio (SSMS) from 2005 to 2008. Yea, I know it is 2012 but the 2005 stuff was working. As a general rule I don’t mess with stuff that is working but 2008 SSMS is a simple way to get 2008 SMO for PowerShell. The 2008 SSMS installation complained about the existing 2005 version and asked me to uninstall the 2005 version before proceeding. After I uninstalled the 2005 version I resumed the 2008 installation and it generated this error message.

SQL Server Setup has encountered the following error:

Unable to generate a temporary class (result=1).
error CS0006: Metadata file ‘C:\WINDOWS\assembly\GAC_MSIL\MSClusterLib\1.0.0.0__89845dcd8080cc91\MSClusterLib.dll’ could not be found

Error code 0x84B10001.

Although some folks recommended repairing the SQL installation, my fix was simple. I restarted the 2008 installation and it re-installed the missing items.

SQL Server and Subversion

Two years ago I started supporting a  Classic ASP application that used SQL 2000 for the data base. One of the complaints of the owner was that the previous person supporting the application did not keep track of program changes. The development environment was undocumented. A test system existed  but it was in an unknown state with the production system.  It looks like it was created from a restored backup. There was an old copy of SourceSafe and Visual Studio 2003 but I was pretty sure it was not being used. I could not find any commit logs. They had a source control system(SCS) but they had not used it. Initially I tried to like Sourcesafe and the Visual Studio 2003 environment but in a Classic ASP environment it does not bring a lot to the table. The work flow is slow and not very intuitive compared to Notepad++ and TortoiseSVN. Since this is a one man shop, I had some experience with Subversion, and we were not going to upgrade Sourcesafe, I installed Subversion, Notepad++, and TortoiseSVN. Next I synchronized the code for the production and development systems and made my initial load into Subversion. This was an adequate solution for the ASP, XSL, and XML files.

The data base documentation was not existent, too. So I used SQL Enterprise Manager to script all of the tables, stored procedures, functions and views. I loaded these files into Subversion, too. Within a very short period of time I adopted SQL Management Studio Express(SSMSE) as my testing environment for SQL changes. Here is where I ran into my first SCS conflict. SSMSE scripted the SQL objects slightly differently than SQL Enterprise Manager. I wanted to develop and test SQL changes using SSMSE but I wanted the ability to script all of the SQL objects using SQL Enterprise Manager at any time. I also found the scripts created by SQL Enterprise Manager to create tables were more trustworthy. My kludge solution was to manually apply the changes to the script files created by SQL Enterprise Manager every time I wanted to commit the changes. Using WinMerge this was not difficult but it was an extra step. I yearned for a more elegant solution.

On Friday I think I found it. It was not easy to find but it looks like I can make a significant upgrade to my development environment.  I think I found it on the second or third Google page. The project resides on CodePlex.com and here is it’s description.

DBSourceTools is a GUI utility to help developers bring SQL Server databases under source control. A powerful database scripter, code editor, sql generator, and database versioning tool. Compare Schemas, create diff scripts, edit T-SQL with ease. Better than Management Studio.

Although it lists it’s status as beta, I installed it and scripted my data base without problems. Here are the features that attract me the most.

  1. You can script the entire database including the data. I have not checked out the data scripting yet.
  2. You can edit T-SQL in the same format as you used to script the entire data base if you use DBSourceTools as your editor. This should make it much easier for me to keep the source control system up to date.
  3. If everything works as advertised this should be a relatively easy way to deploy new development systems. I have been promising to deploy a development system with updated table data for over a year. I really like the idea of deploying new systems as a way to verify the integrity of your source control system.

If I can compare schemas and create diff scripts, that’s frosting on the cake. As a SQL development environment it looks very promising and the documentation is remarkably good for a new project, too. Although it is a pain in the butt I started renaming my SQL subversion files(e.g. .PRC to .sql) on Friday. This will take a long time since I am renaming the files via the repo-browser. The SVN client rename is not a rename. It adds the file to the repository and you lose the history.

Script all data of a table – SQLServerCentral

I made a couple of minor changes to the provided script and I got Script all data of a table – SQLServerCentral to work with SQL 2000. I like the idea of creating scripts to transfer/update/re-create tables. I have been using Excel to create scripts to update shipping costs for some time. Nice work Florian!

How do I run sp_spaceused for all tables in a database? – TechNet Forums

I was looking for a way to list the size of all the tables in database again and I found this nice bit of code. I made a small change on the select statement to create a calculated integer field so I could get a nice descending sort on size. It worked for me!

    
select 'Database Name: ', db_name()

set nocount on

if exists(select name from tempdb..sysobjects where name='##tmp')

drop table ##tmp

create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))

go

declare @tblname varchar(50)

declare tblname CURSOR for select name from sysobjects where xtype='U'

open tblname

Fetch next from tblname into @tblname

WHILE @@FETCH_STATUS = 0
	BEGIN
	insert into ##tmp
	exec sp_spaceused @tblname
	FETCH NEXT FROM tblname INTO @tblname
	END

CLOSE tblname

deallocate tblname

go

select 
	nam Table_Name
	,rows Total_Rows
	,res Total_Table_Size
	,data Data_size
	,ind_sze Index_Size
	,unsed Unused_Space
	,CAST(replace(res,'KB','') as int) as Total_Table_Size_KB
from ##tmp
ORDER BY Total_Table_Size_KB desc

drop table ##tmp
–Vidhya Saga

How do I run sp_spaceused for all tables in a database? – TechNet Forums

SQL 2005 Express Tips

My version of SQL 2005 Express was installed when I installed Visual C++ Express Edition. Today I was trying to use the Upsizing Wizard included in Access 2003 when I ran into SQL Server problems. So here are my tips:

  1. Go into the SQL Configuration Manager and enable TCP/IP if it is disabled. Someone wrote a post stating that Access 2003 uses TCP/IP to communicate with SQL Server.
  2. Go into Services and check to see that SQL Server Browser is running. This service was disabled on my machine. This allowed me to see the correct hostname. My hostname is called, “MYCOMPUTERNAME\SQLEXPRESS”. You can disable the SQL Server Browser when you are comfortable.
  3. Download a copy of SQL Manager from EMS. I used the Lite version since my needs are small and it is free. I would seriously consider checking out the paid version if I was doing a lot of conversions.
  4. Sample databases are a great way to brush up on your dormant SQL skills. If you are looking for the Northwind database, it is not installed with SQL Server 2005. In fact SQL 2005 does not install any sample databases although a new sample database called AdventureWorks is available as a separate download. If you want the old familiar Northwind database, you can download the samples for SQL Server 2000.
    1. Download the SQL Server 2000 sample file from Microsoft.
    2. Extract the files from the archive and copy the Northwind MDF and LDF to your SQL Server 2005 data directory. See Jeff Atwood’s post for more details.
    3. Attach the database to your SQL Server. I used SQL Manager since the manual method described in the Readme file did not work for me.
  5. Now if you have successfully navigated the SQL maze, you should be able to run the Upsizing Wizard, access the SQL Server using an Access Project(ADP), access the SQL Server using Excel, and access SQL Server via your favorite programming language.