Brian Harry has just posted on his blog about a bug in SQL page compression (found in SQL Enterprise) that can cause problems with TFS 2010 or TFS1(preview), previous version of TFS did not support page compression.
So if you are using TFS and SQL Enterprise have a read and make sure the correct updates are in place.
There is a good chance if you are using TFS that you will want to create some custom reports. You can write these in Reporting Services via BI Studio or Excel, but I wanted to use Report Builder, but could not see the Report Builder button on this Reporting Services menu
The problem was multi-levelled
First I had to give the user access to the Report Builder. This is done using folder property security. I chose to give this right to a user (along with browser rights) from the root of the reporting services site
But still no button. Forums and blog posts then talk about changing options on the ‘Site Settings’ menu, the above screenshots shows that this is also missing from the top right.
To get this menu option back, I had to run my browser as administrator and then this option appeared. Turns out that the TFS Setup user I was using had not been made a Reporting Services site administrator, just a content administrator.
But still this was not enough, I also add to add users as System Users to allow the Reporting Services button to appears. So my final Site Settings > Security options were
Once all this was done I got my Report Build button and I could start to write reports.
A webcast recording of SQLBits IV session ‘Making the SQL developer one of the family with Visual Studio Team System’ is now available on the SQLBits site. This discusses the features of the VS2008 Database GDR Edition.
Unfortunately I will not be proposing a session for this years SQLBits community event on the 21st of November 2009 at Celtic Manor in Newport, as I will be travelling back from the Microsoft PDC in LA
On my development PC I had a 2005 instance of SQLExpress that was installed as part of the VS2008 setup. I thought I had upgraded it when I put on the SQL 2008 Management tools and/or VS2010 beta, but it seems I didn’t. I thought I would try the new Microsoft Web Platform Installer, but this also thought I had done the upgrade to 2008, I suspect due to the fact I had the 2008 management tools.
Note: If you are using the Microsoft Web Platform Installer 2.0 RC remember you can’t just click on it to run from the web if you are running as a non-administrator user on your PC (as you should be, running least privilege). You need to download it and ‘run it is administrator’ or open it in a browser running as administrator to get it t even load.
So I needed to download the SQLExpress 2008 media to do a manual upgrade, as I remembered I could not use the developer edition media I had to hand to upgrade and Express instance. This download in itself proved problematic. I did a downloaded from MSDN, but the file I got gave a ‘not a valid win32’ error when I tried to run it. Also I noticed each time I tried to download it in IE8 it was a different size – not a good sign! Once I swapped to Firefox it downloaded without issue.
Anyway in the end I got the right media and access rights and the upgraded went smoothly. However then I tried to attach a 2008 DB (the reason I needed the upgrade in the first place) I got the error
Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)
I had yet again forgotten to run SQL Management Studio as an administrative user. This error dialog is SQL Management Studio was of saying you don’t have the rights!
Thanks to everyone who attended my session at VBug Newcastle last night, hope you enjoyed.
As I mentioned in my session, to celebrate my talking at VBug, Microsoft chose to release the Visual Studio Team System 2008 Database Edition GDR R2 yesterday. If you are using DataDude you do need to get this installed, it addresses many know issues.
If you are in Newcastle on the evening of the 22nd I will be speaking at Vbug on Visual Studio 2008 Database Edition.
Hope to see you there.
At my session at SQLBits IV on Visual Studio 2008 DBPro GDR it was mentioned that there was a major patch just about to be released to address some known issues. Well Brian Harry has provided links to the release of the release candidate of the GDR QFE
Whilst preparing for my session at SQLBits next weekend I was re-watched Gert Drapers' PDC session (TL45) where he used a command tool to deploy a database via a USB pen drive (about 30 minutes into the session). Now it seems that the createdeployment command line tool he used is not currently available outside Microsoft, but the same effect can be achieved use the VSDBCMD command.
Step 1 - get the files onto the distribution device
The first step is to build the distribution media, this is just an XCOPY process. As the MSDN documentation says you need to end up with the following directory structure on your USB drive; for this example I used G: for USB drive letter and Database1 for the name of the database I want to distribute
G:\ copy the contents of [Program Files]\Microsoft Visual Studio 9.0\VSTSDB\Deploy & sub directories
G:\ copy the dlls from[Program Files]\Microsoft SQL Server Compact Edition\v3.5 folder
G:\Database1\ copy the contents of [ProjectsFolder]\DataBase1Solution\Database1\sql\debug or release directory after the DB project is built
Step 2 – A script to do the deploy
It is now a simple process of running the command line tool, but this is a bit long to type each time so I used a batch file. My command usage was
deploy [DB name] [SQL server instance]
e.g. deploy database1 .\sqlexpress
The contents of the actual deploy.bat batch file is as follows. Note there are many more options you can set but this seems to be the basic minimum
VSDBCMD /a:Deploy /dsp:Sql /cs:"Server=%2;Database=%1;Trusted_Connection=yes;" /model:%1\%1.dbschema /manifest:%1\%1.deploymanifest /script:%1%.sql /dd
Using this batch file a new instance of a database can be created or an existing one updated.
Note: When I first tried to get this going I keep getting SQL file create errors which appears as TSD01268 errors in the deployment log. Eventually I realised the problem. I was running on a 64Bit Windows 7 PC. My default SQLExpress instance, running as the Network Service account, was setup to and had rights to create files in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data but not in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Unfortunately the VSDBCMD tried to use the second location. Once the SQL instance was set to default to the second location and suitable rights provided all worked correctly.
It was announced overnight by Microsoft that the Database Professional SKU for Visual Studio will be made available to all people who have a licensed copy of Team Developer.
This is great news as it addresses the problem of where to put the expensive copy of DataDude (which I think has been a barrier to it's uptake), in most companies there is not the clear distinction between code and DB devs.
So as of tomorrow you can get DataDude via MSDN. You can read more about the recent announcements here: http://msdn.microsoft.com/en-us/vstudio/products/cc948977.aspx
Thanks to everyone one who attended my session at the Leeds SQL User group on Visual Studio Database Edition and to UPCO for hosting the event.
The slide stack is available on the Black Marble web site