Attaching a database the express way

By Henning Eiben in Programming 0 Comment

Are you also stuck with that stupid limited express edition of SQL Server 2005, which has no GUI whatsoever?

Well, I already tried the hassle of installing the Management Studio of the full blown SEL Server once – and I don’t feel like doing that any time again. So there has to be a better way.

OK – so check out the SSEUtil and with just:

SSEUtil.exe -s .\SQLEXPRESS -attach Northwnd.mdf

we get our beloved Northwind Database in SQL Express!

Wow – this is slick!

UPDATE:

OK, the excitment settled pretty quick – seems like I cannot access that attached Database from anywhere. This makes attaching databases kinda useless.

Anyway, I figured another approach – and this time it’s for real. Try:

%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn>SQLCMD.EXE -S.\sqlexpress -E -iattach.sql

where ‘attach.sql” contains the SQL to attache a database:

use master go exec sp_attach_db @dbname=’northwind’,@filename1=’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwnd.mdf’ go

Updating a SQL2005 mixed installation

By Henning Eiben in Software 0 Comment

For a pure dev-machine I only have the MS-SQL express edition installed, which is being distributed along with the current Visual Studio release.

Doing also some DBA stuff, I soon figured that I need to have the real management-console (Management Studio) as well. So I tried to install just Management Studio from the “real” SQL Server. This turned out to be quite difficult (with SQL 2008 this turned out to be no burden at all anymore).

So after doing a tremendous amount of tweaking I finally got everything working alright – until the SP2 for SQL 2005 was shipping.

Trying to install the SP2 was a bigger problem than I expected. The update for the Express Edition complained about the installed Management Studio, which created a version mismatch and the standard edition of SQL Server complaint about the mismatching database engine from the express edition.

Fortunately SP3 does a way better job. Just install both updates (for the standard edition and the express edition) and you’re all set. The last issue was an abort during the express edition update. This was somehow the sample issue, which stopped the KB 948109 update from installing. So opening up the registry and going to the key HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup (respectively HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.2005\Setup) and deleting the settings FTSGroups and SQLgroup solved this issue.

Running SQL Express and SQL Standard simultaneous

By Henning Eiben in Programming 0 Comment

On a simple dev-machine I would usually only install the SQL express edition. While this is sufficient for local development for simple databases, this doesn’t give me a GUI to manage my database.

Espically in bigger projects I might want to work with the database directly, or maybe I want to look or edit a database on an separate server. This doesn’t work at all with the express edition.

OK, so instead I install the regular edition of SQL Server. This gives me the Management Studio to easily manage my databases, but unfortunately this denies some features of the express edition, which only available in the express edition. One feature is attaching standalone mdf files as database. This is a feature needed especially for web-application, because a lot of ASP.Net data is stored in such an attached mdf file.

A common scenario for SQL 2005 was to have both express and the “regular” edition installed side-by-side. Beasue these editions come each with their own installer and seem to be considered separate products, this install does cause some problems. For example you will most likely not be able to install neither SP2 for the express edition nor the regular editions.

SQL 2008 seems to relief this pain a lot, because the installer offers to install the regular edition as well as express edition. This allows to install both editions and run them side by side.

Waiting for the SQL

By Henning Eiben in Programming 0 Comment

Sometimes you just need a slow query to simulated a real big load of data being processed by the SQL-Server. You could either fill-up you database with a whole shit-load of data, or you use WaitFor Delay instead:

WaitFor Delay '00:00:10'

Will pause the execution of the SQL-statement for 10 seconds – well, isn’t that sweet.

Defeading reporting services

By Henning Eiben in Software 0 Comment

I had a real hard time installing SQL-Server 2005 Reporting-Services on this one machine …

The MSI was executing just fine, but the step “removing backups” (don’t know the exact wording in english, since I’m using a german installation) seems to fail.

This is what the msi logfile has to offer:

Aktion 15:59:51: RSSP_CAInstall.28B19132_4741_4761_840F_AC515130EC08.
Aktion 15:59:52: RollbackCleanup. Sicherungsdateien werden entfernt
CA MSG : Running RSCustomAction
CA MSG : Launching C:\DOKUME~1\ADMINI~1\LOKALE~1\Temp\rsCustomAction.exe with command line parameter /i
CA MSG : rsCustomAction.exe failed to configure, Error code is: 1
Aktion 16:02:48: Rollback. Aktion wird rückgängig gemacht:

After much struggle I finally figrued something. First I installed SharePointRS.msi without the custom actions, by executing:

SharePointRS.msi SKIPCA=1

OK. So next I just run the rsCustomAction.exe (which is located in my temp-folder):

rsCustomAction.exe /i

This finally produced some logfiles and a more details error message. In my case the problem was, that the web.config of my portal-site was write-protected!!

After removing the write-protection I was able to install SharePointRS.msi just fine.

Analysis this (… in Excel)

By Henning Eiben in Programming 0 Comment

Although Excel is a great tool to analyze data, it does not support a native driver to access Analysis Servives 2005. In order to take full advantage of the analysis services you need to install a special addin.

If you want to have even more control about the data you’re accessing, you might want to connect to analysis services using ADO. The apropriate driver is supplied with the SQL Server 2005 Feature Pack.

Patching virtual, cloned SQL-Servers

By Henning Eiben in Software 0 Comment

Well, since the existence of the cloned sheep Dolly we all knew, that cloning isn’t just a piece of cake! Well, here is another one:

Today I tried to update one of my development-servers to SP2 of SQL-Server 2005. Everything went just fine … really everything? Nope, the database-engine, the olap-engine and the reporting-services-engine just would not update. All I got was a snappy error Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation..

OK, some time later I figured: it had something to do with me cloning my vmware-machines to save some time. Well, now I came kicking back at me!! But surprisingly Microsoft holds the answer in Knowledge Base article 925976.