December 2009 Entries

Variations quit propagating

If variations quit working, the it could be the relationship list (hidden list in the root site) could be corrupt. This could happen when you copy sites/pages from the target site back to the source site. The easiest way to fix is run the variations editor. You have to run the tool on the front end server (just on one of them if you have multiple, SharePoint will propagate the changes to any other front end servers).  You just download the zip file and extract the files to a folder, it doesn't install anything there is just a .exe...

Search across all fields in all tables in SQL server

I found this script that allows you to search across all fields in all tables of a database for a specific string. It’s helpful when you trying to where data might be stored in a strange/large/new database that you are not intimately familiar with. Being a consultant I have found myself digging though a clients database many times trying to figure how the data is stored and this has saved me from looking in every table manually. I did not come up with this code myself, but I didn’t save the link of the blog where I found it. So...

SharePoint performance tips

I attended the SharePoint Saturday today and attended a session on SharePoint optimal performance by Eric Shupps.  Here are a few of the tips I learned today. Have a separate server (not available to users) to host search indexes. Change the default SQL growth from 1 mb, 10% to 100 mb, 250%. Isolate the SharePoint DB, do not put any other databases on the same server as the SharePoint DB. Create custom scopes with unique crawl schedules. If you have a specific site that needs to crawled more often than the other sites Enable manual compression on IIS 6/7.  You will need to balance...

Get Google results that matter

More than once I have searched for something on Google and gotten back too many results that don’t matter.  One of the problems with IT is that everything moves and improves quickly.  It’s such a pain when get results that over 2 years old and don’t matter because the technology has moved on. Use the advanced search of Google to get results that are relevant.  You can tell Google what date range to use (how recent the page is), for example past week, past month, past year.  That way you will only get pages in the results that are from...

Optimize Firefox (speed it up)

For Firefox being such a good browser, I was surprised to find out to find out that Firefox can run faster.  There are some Firefox settings that can be adjusted to speed up your browsing experience. Of course type about:config in the address to get to the Firefox settings.  Change the following settings: network.http.pipelining from false to true network.http.pipelining.maxrequests from 4 to 15 network.http.proxy.pipelining from false to true Then add a new entry nglayout.initialpaint.delay and set it’s value to 0. This will speed up your Firefox browsing experience.

Show desktop icon missing from quick launch

Occasionally I end working on PC that is missing the show desktop icon.  I always end up looking for how to recreate it on Google.  In Windows XP, go the run command then type the following command: regsvr32 /n /i:u shell32.dll Then press enter.  A dialog will let you know it was successful. The show desktop won’t be there until you reboot or type the following command: %appdata%\microsoft\internet explorer\quick launch This will open a folder.  Then you can drag the show desktop icon to your quick launch bar (it will create a short cut in the folder that just opened...

What to do with User Account Control in Vista

Tired of Vista asking you if it’s okay to do anything, that’s the User Account Control (UAC).  You can turn it off or use free third party tools to adjust. I choose to turn it off because it is too annoying.  Just go the control panel-> user accounts.  Then click Turn User Account Control on or off.  Click continue at the UAC prompt and on the next screen, uncheck the Use User Account Control (UAC) to help protect your computer. That will turn off UAC and you won’t have to worry about it.

Excel 2007, determine count of rows in a spreadsheet

I had to determine how many rows are in a speadsheet with VBA. Dim rowCount as Integer rowCount = ActiveSheet.UsedRange.Rows.Count

Excel 2007, determine count of worksheets in a spreadsheet

  If you need to know how many worksheets in a spreadsheet. Dim sheetCounter as Integer sheetCounter = ThisWorkbook.Worksheets.Count

Record count of all tables in a database

Here is a sql script that will give a count of all records in every table in your database. This comes from my friend David McCollough. CREATE TABLE #sizes ([name] nvarchar(200), -- BOL says (20), but that obviously wrong [rowcount] varchar(25)) DECLARE @tablename VARCHAR (128) DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename WHILE @@FETCH_STATUS...

Search for column name across all tables

  Here is another sql script I got from my friend David McCollough that will allow you to search all tables for a specific field name. Just set the @ColumnName variable and let ‘er rip.   DECLARE @ColumnName varchar(1000) SET @ColumnName = 'PlayerId' select as TableName, as [ColumnName] from sysobjects o inner join syscolumns c on = where o.type = 'u' and = @ColumnName ...

No back button

If your web app just can't tolerate a user clicking the back button, you can tell the browser to not cache the page. Most of the time it will listen if you use this code, which covers all the bases: Response.Expires = 60 Response.ExpiresAbsolute = DateAdd(DateInterval.Day, -1, Now()) Response.AddHeader("pragma", "no-cache") Response.AddHeader("cache-control", "private") Response.CacheControl = "no-cache"

JS to tell if an object is viewable on the screen

Here is some JavaScript that has a function to tell if an object is viewable on the screen. This is quite helpful if you have something on the screen you want to make sure the user sees, like a an error or license agreement, etc. <HTML> <HEAD> <SCRIPT> function isinView(oObject) { var oParent = oObject.offsetParent; var iOffsetTop = oObject.offsetTop; var iClientHeight = oParent.clientHeight; if (iOffsetTop > iClientHeight) { alert("Special Text not in view. Expand Window to put Text in View."); } ...

Dynamic searches without dynamic sql

Search functionality is a feature that comes up with many applications. I have written more than one search stored procedure (and screen), but I usually ended up with writing dynamic sql (building a SQL statement and using the exec command, see Figure 1 below). The main disadvantage to dynamic sql statements is that they are not compiled and performance always matters. I was reading CoDe magazine one day and noticed a part of an article that mentioned dynamic sql without dynamic sql. I read it and was quite pleased with the approach. So the basic approach is to have all...

Sql Server Keyword search across Stored Procedures, Functions, Triggers

Here is some T-SQL I got from a friend of mine (David Mccoullgh). Just set the @SearchPhrase to search across stored proc, functions, triggers to see where that string is in use. Very helpful if you are changing a field name and want to know where it is in use. DECLARE @SearchPhrase varchar(1000) SET @SearchPhrase = 'PlayerBonus' SELECT DISTINCT AS [Object Name], case when sysobjects.xtype = 'P' then 'Stored Proc' ...

VS copy instead of paste problem

So in Visual Studio when accidently copy (ctrl-c) instead of paste (ctrl-v) you lose what was on your clipboard. Quite annoying behavior, but you can fix it.  Tools-> Options –> check Show All Settings –> expand the Text Editor node –> select All Languages –> under Settings –> clear the Apply cut or copy commands when there is no selection. This will change the behavior in visual studio to work like word when it comes to copy and paste.     This is a tip I read about in the Code magazine for Jan/Feb 2009. Technorati Tags: Visual Studio,copy,paste

What's new in SQL 2008

I talk about SQL server every chance I get because of some the great features available (SSRS, SSIS, SSAS).  So I looked into SQL 2008 the first chance I got.  More than once I have been asked about the new features of SQL server 2008.  So here is a list I complied (not 100% of the features, just the ones I thought they were easy to sell) Top new features: T-SQL Intellisense – Intellisense is now available for T-SQL. This allows for easier and quicker development. Date/Time data types – There is a new...

Performance Point, can't move data from Staging to App DB

You go through all the effort of loading your Staging database and you click on the “Load this model from Staging Area”.  Then you check your application database and there is nothing there.  You check your staging database and there no errors (BizSystemFlag = 0). So what’s the problem?  There are no errors in the database (or in the event log). You have to update the BizSystemFlag = 200.  Now when you load the model from the staging area it will actually move the data.

SharePoint myths

I was catching up on some blog reading and I was reading Eric Shupps blog post about SharePoint myths and item 3 (SharePoint  is not suitable for large internet facing sites) really hit home for me.  In March a large internet facing web site (for an oil and gas company) that I worked on went live.  I just checked and the site 1.3 million (and change) pages views for March.  That’s the biggest site I have ever worked on and SharePoint handles it wonderfully.  So there is an example of that myth being debunked.

Dashboard Designer,what's my connection string again?

In the Performance Point Server 2007 Dashboard Designer, you create a data source using either a standard connection or with a connection string.  I use the connection string option myself, but I never remember the syntax of the connection string (to a AS cube). So here how’s I get the connection string: Open excel (I use 2007) Go to the data tab From Other sources From Analysis Services Put in your server name (I leave the default for Log on credentials)  Choose next Then choose what cube you want (if you have more than one cube)  Click Next Click Finish (if...

Regular Expressions Tool

A co-worker of mine showed me Expresso, a regular expressions development tool. If you need to create some regular expressions (which are a pain to create) from scratch expresso is the best tool. Of course there is also if you want a great site to search for regular expressions.

Run Windows Server 2008 as a workstation

So if your tired of running Vista, you can run Windows 2008 as your workstation.  Windows 2008 runs much smoother than Vista.  I have run Windows 2008 as my main OS and I have enjoyed (until I tried Windows 7).  Here is link to using Windows 2008 Server as a workstation, I found this info in the Jan/Feb 2009 issue of Code magazine.

PerformancePoint 2007 Planning Server Roles

Here is an excerpt from the MS white paper on deployment of Performance Point Server 2007 on the server roles of PerformancePoint. Here is the link to the document (which is a must read) Deployment Guide for PerformancePoint Server 2007 (   Using Planning Server roles The security model for Planning Server is based on roles. Users are assigned to roles, and their permission levels in the Planning Server system are dictated by the roles to which they belong. The two types of roles are administrative roles and business roles. Planning Server administrative roles Planning Server includes four predefined administrative roles, which support the separation of...

PerformancePoint Excel Add-in prerequisites

So if you want to make sure your clients can use the Excel add-in. Here are the prerequisites for the add-in (from the Deploying PerformancePoint white paper). All client computers require the following software before PerformancePoint Add-in for Excel can be installed: · Windows Vista, Windows XP with Service Pack 2, or Windows Server 2003 with Service Pack 1 · Microsoft Office Excel 2003 Service Pack 1 or Office Excel 2007 · Office Excel 2003 Primary Interop Assembly (.NET Programmability Support) · The Microsoft .NET Framework version 2.0.50727.42 or later. Note: To enable Export to Excel functionality or Export to PowerPoint functionality in Microsoft...

PerformancePoint Planning Server 2007 hardware prerequisites

Here are the hardware prereq’s for Performance Point Server 2007. Planning Server hardware prerequisites The following table lists the minimum and recommended hardware requirements for clients and servers used by PerformancePoint Planning Server. Hardware requirements ...

Delete a Performance Point 2007 Cycle

So I have been doing a PPS proof of concept and I have created cycles (I am not going to explain what a cycle is) that I no longer want (or need).  So I wanted to delete some cycles and found it not easy to figure out. So here are the short instructions: In the Workspace Browser, click Process Management, then select the cycle you want to delete.  In the cycle tasks pane, click Available Actions in the Select Action list, then select Close.  Then repeat and select Purge from the Select Action list.  After you click ok all the assignments...

Dashboard Designer can not connect to cube

When I tried connecting to my cube from Dashboard Designer, I got the wonderful error “The PerformancePoint Server could not connect to the specified data source. Verify that either the current user or application pool user has Read permissions to the the data source, depending on your security configuration.  Also verifty that all required connection information is provided and correct.” This was caused by the application pool PPSMonitoringWebService running as the wrong account.  No matter what else you do you have to make sure that account has permissions to your database.

PPS 2007 Excel add-in Gotcha

If you are trying to create a form (allowing for data entry) with the PPS excel add-in, you must have a time member in the data period set for the cycle.  If you don’t, no data entry will be allowed.  So this means you have to have a time member for any form template you are creating.

Performance Point excel not updating problems

So I found out a few tips about the excel add-in for Performance Point Server 2007. Item 1, do NOT change a form template if you have a cycle created using that form template.  You can not guarantee that users will get the correct version of the form template. Item 2, cache not clearing.  If you are sure you are not getting the latest form template when you open an assignment you can open the performance point excel add-in and click the clear cache button.  That may not always work.  To get around that problem you can browse to the folder...

Performance Point 2007, how to move a matrix

So you created an excel form on the Performance Point tab and you realized that put your matrix in the wrong cell (defaults to the cell you have clicked when click the add new matrix button).  How can you fix it (without deleting the matrix and starting over)? You can move the matrix by changing it’s location. Go to Reports –> Report properties    From there expand the workbook and sheet and click on the matrix name. Scroll down to the bottom and the second to last property is the location. You can change that to move the matrix to what ever...

SQL Server 2008, can't save changes to tables

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes. You will get an error stating, “You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table be re-created.” What a pesky problem.  It prevents you from making progress when you making database changes. It’s a good thing you can turn it off. In Management Studio, go to Tools –> Options –> Designers...

Performance Point Server, approval's don't work

When you create an approval workflow in Performance Point, it appears to work.  Unfortunately it doesn’t.  When a user submits an excel template, to an approval it actually save the data to the database.  You can the record in your fact table to see what happens. This is too bad that doesn’t work.  This one of those easy pieces of functionality. that sells the whole product..

Performance Point BizSystemFlag and what they mean

My last blog focuses on what the BizSystemFlag field and what it needs to be to get data into the Application database.  So I figured I should mention what other values it can be and what they mean. Here are the options: 0 – Do nothing with this record 100 – Record is already in the database 200 – New record to move to app database 300 – Updated record 400 – Record will be deleted 900+ – Error Code

SharePoint WSP right click add solution

For anyone doing SharePoint development I have one of the best tips I would recommend to anyone (a coworker of mine, Bo Cherry, found this tip).  You can add a solution with just right clicking on a WSP.  No more stsadm commands (mostly).  You have to edit your registry (don’t forget to back it up first), but it is well worth it.  From what I have seen, if you use the right click option, you have less trouble deploying solutions  than just using stsadm to deploy. Just open notepad, add the following code and save as a .reg file. Now double...

Performance Point Server 2007 cycle tip

When you creating a cycle in Performance Point set the assignment start time (defaults to current date time), so that you have the time to setup the form assignment.  You don’t want a cycle to start until you have the form assigned.  It could be a little unpredictable.

Enable IIS 6 Compression

So I copied this post from a coworker of mine, Dennis Bottjer.  IIS compression makes a huge difference and it’s an easy batch file to set it up.   How to Enable IIS 6.0 Compression for SharePoint: Microsoft’s Internet Information Server (IIS) has long support HTTP Compression.  However, supporting and implementing compression are two different things.  Many settings within IIS are easily configured from the IIS Admin GUI.  Unfortunately, enabling and configuring compression is not supported through the GUI and requires editing the IIS Metabase.  THe metabase can be edited manually or updated from a command prompt.  The commands can be combine...

SQL Server 2008 sp1 released

So SP1 for SQL 2008 has been released by Microsoft.  You can download it here.  There is not much info about what is included.  It appears to be a cumulative update.  You can slipstream it with SQL server install image.  You can uninstall the service pack separate from the DB engine.

Community Credit, live writer plug in

A coworker of mine (David Mccollough) told me about Community Credit.  You use the website to track how your giving back to the developer community, ex. any blog posts or presentations you give at user groups, etc. There is also a live writer plug-in, so when your posting it automatically updates Community Credit. Here is the link that explains how to install the plug-in for 32 bit windows. If you have 64 bit windows the path to the plug-in directory is “C:\Program Files (x86)\Windows Live Writer\Plugins”.

Want to learn Performance Point Server 2007

If you’re interested in learning about Performance Point Server 2007, here are some links to start with. Technet white papers (go ahead and read or skim over them). Video Tutorials on creating dashboards. List of books that MS recommends as resources.

SharePoint 2010 preliminary requirements News about the next version of SharePoint, it going to be all 64bit.

SQL Server 2008 intellisense not correct

Sometimes when you writing TSQL in sever 2008 you will get the red squiggles (indicating you typed a table or field name incorrectly).    Yet everything will compile and run correctly, so what’s the deal. To fix this problem you just need to press ctrl+shift+r, which will rebuild the sql cache responsible for intellisense.’

SPSource, a SharePoint helper

Here is the link to SPSource on codeplex.  Basically it allows you create your sites/lists/content types/etc in the SharePoint UI and export it all out as CAML.  This is a big saver (if your using CAML for deployment) for site creation/deployment.   It is a command line tool that takes one input parameter, the url to your site.  It will create the xml files for you. This is much easier than hand coding the CAML for your site. Where I see this adding tremendous value is where you have a customer who you have deployed a SharePoint site for.  They make changes through...

SharePoint service pack 2 hotfix - expiration date

When you install SP 2 for SharePoint it will change the expiration date of SharePoint to be a trial product. This does not affect WSS 3.0. For a work around you can re-enter your product ID into Central Admin (but who knows their product id). Here is the link for x86 and here is the link for x64.

stsadm Technical Reference for SharePoint 2007

Micorosoft posted on TechNet a technical reference for stsadm (done with SilverLight).  Much easier to use than help from the command line.

Summay of changes with SharePoint 2010

Microsoft has been very quiet about the new features of SharePoint 2010, but they have finally started providing some info. They have produced some videos explaining some of the new changes here. I was going to watch them, but I did find a someone else who watched them and blogged about it. So here is a summary of the changes that Microsoft has announced. I did copy all this below from Craig Roth. I wanted it on my blog so I would be able to remember where it is. Sharepoint 2010 Overview (Tom Rizzo) Mentions how they...

SSIS Error using a stored proc as source

SSIS is a great tool to move data around.  Sometimes it’s easier to use a stored proc to pull together some data before you do stuff with it. To use a stored procedure you can use an OLE DB source (in a Data Flow task) SQL command. Works great, but if it doesn’t and you get the error code 0xC02092B4, you will have to add to your stored procedure. Just add the following 2 lines to the top of your stored procedure: SET NOCOUNT ON SET FMTONLY OFF This will clear up the metadata issues with SSIS trying to understand what your stored procedures...

So you want to retrieve your test results?

When you create a test project in Visual Studio, you can load test your application, but you won’t be able to save/view results without a storage type set to database.  You can check your storage type by checking the properties on the Run Settings. (if you set it to none, you will be able run the test) By default you do not have a database to store the results of you load test.  (Why your not asked during installation to create one is beyond me). You need to run the SQLscript to create the LoadTest database. Here is the path to the...

Can't open a Visual Studio test project

The test tools in Visual Studio can help you stress test your application.  The only problem is that they are not included with the regular developer edition of Visual Studio.  So if have the wrong version installed you need to uninstall and reinstall a version that does have those project types (like Team Suite). What I found though was after you uninstalled and reinstalled you were not able to open/create test projects.  The project types were there, but you receive an error:  The following project languages must be installed C#, C++, VB.NET (or something like that).  Everything else will work, but...

PerfomancePoint Server 2007, want to create dimensions from a csv file

When you create dimensions in PerformancePoint Server you have an option to create a dimension from a csv file. This sounds like a great way to kick start your dimensions because you can take a csv file with data and upload it to create a dimension and  have it populated with some data. Great idea, but only if you know the format of the csv file. The first 2 rows are header records that must be there to define the dimension and hierarchy before you list the column headers. See the screen shot below. If you have data, go ahead and add it...

PerformancePoint Server 2007, why can't I synchronize dimensions/models

If you create an application in Performance Point and start creating dimensions/models and you notice Synchronize all dimensions to Staging Area.. is grayed out, you wonder what it is going on. The problem is that your user is not in the Modeler Role. Once your user is added to the role you will have no problem synchronizing. I find this to be weird behavior since you just created the application (and app and staging database). I would assume you be in the correct role to synchronize by default, but apparently that is not the case. This through me for a...

Tweet from Outlook

Outlook is the one application I have open all day long.  I don’t like most twitter apps. So I was happy to find an add-in for outlook that will tweet for you.  It can stuff all your new tweets in a folder (or your inbox) and you can save everything you tweet in a folder (like sent mail) If puts a new toolbar in Outlook.     Here is the link

Compress you CSS

A great way to help with performance of your website is to compress (or minify) your CSS and javascript. Removing spaces, comments, etc will shrink the size of your files which will decrease the payload of your page.  Usually the larger your site the larger your CSS will be. The only disadvantage to compress your CSS is maintenance (since your entire CSS file ends up on one line it’s difficult to read/maintain).My approach to this is to have 2 CSS files in your project, one compressed and the other not.  I have my code reference the compressed file.  When changes are...

Track Changes to objects in your database

Ever have the need to be able to track changes to the objects in your database? This code sample was provided by a friend, Josh Shilling. It provides you with a change log on your database. This will be helpful in multi developer environments when you think someone made a change to your database, but you don’t know what changes were made. So you can run Select * from dbversion.ChangeLog to see what changes have been to your db objects. I had a hard time getting the image of the results to show. GO /****** Object: Table [DBVersion].[ChangeLog] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING...

Visual Studio 2008 web test recorder not working on 64-bit

The load test in Visual Studio are a great help to stress/performance test you applications.  With the web test you “should” be able to record actions as you use your application.  When you add a web test IE starts and there should be recorder bar on the left. When ever I created a web test I never saw the web test recorder bar show up.  I didn’t even know I was missing it.  So, I was never able to get to do anything besides record that you launched a page which helps you test page load time, but doesn’t help...

SharePoint error in Event Log

If you notice the following error in the event, there is a simple fix. The Execute method of job definition Microsoft.Office.Server.Administration.ApplicationServerAdministrationServiceJob (ID c7f87cbb-e67d-44a2-9061-5321094aa81e) threw an exception. More information is included below.   Open Com Services and check the permissions on OSearch and IIS Admin Services. The WPG group need to have permissions on Local Launch and Local Activate.

TFS 2008 get latest version automatically

When using  the Visual Studio integration for TFS 2008, you will notice that the latest version will not be retrieved when you start making changes to a file. There is an option that you need to have an option.  To get to this option perform the following steps: Launch Visual Studio 2008 Click Tools->Options Expand the Source Control section Click on Visual Studio Team Foundation Server Ensure that the Get Latest version of item on check out is checked. Now when you check out a file if you don’t have the latest version, it will prompt that you don’t and fetch it for you.

So you want to blog on ASP.NET

When I first started blogging I wanted to blog on  I have a friend who already blog’s on (David Mccollough) and I asked him what the steps were (because they are not on Here is what he told me. Well it’s a multistep process. 1st you have to create an account to be able to login, which I assume you have already done. 2nd you have to read the terms of use here. By the way there is nothing you have to sign here, unless they have changed it. I was kinda of confused at first, because I was looking...

SharePoint Error tab in navigation

If you notice your navigation has gone away and you have a tab called “Error:”, you have a problem.  You may also notice that authenticated users can’t navigate your site. This is caused when you have setup the Site Collection output cache with “Page Layouts can use a different page output cache profile”. Here is an another article explaining this problem in more detail.

What does that PerformancePoint error code mean

When you validating your label tables in PerformanePoint, any errors are reported by  their error codes (ex 930). If you would like a little more detail about what the error codes mean, then check out the dbo.SystemErrorDetails table. It contains a list of error codes and descriptions.  This was a big help for me to determine what I doing wrong when trying to validate my data.

SSIS Error using a stored proc as source

SSIS is a great tool to move data around.  Sometimes it’s easier to use a stored proc to pull together some data before you do stuff with it. To use a stored procedure you can use an OLE DB source (in a Data Flow task) SQL command. Works great, but if it doesn’t and you get the error code 0xC02092B4, you will have to add to your stored procedure. Just add the following 2 lines to the top of your stored procedure: SET NOCOUNT ON SET FMTONLY OFF This will clear up the metadata issues with SSIS trying to understand what your stored procedures...

So you want to retrieve your test results?

When you create a test project in Visual Studio, you can load test your application, but you won’t be able to save/view results without a storage type set to database.  You can check your storage type by checking the properties on the Run Settings. (if you set it to none, you will be able run the test) By default you do not have a database to store the results of you load test.  (Why your not asked during installation to create one is beyond me). You need to run the SQLscript to create the LoadTest database. Here is the path to the...

Browser Compatibility Check for IE versions

When doing web development you rarely have a one browser to support.  Having to test with multiple browsers is hard and even more difficult with different versions of the same browsers.  Now there is a solution for different versions of IE.  I used to keep different VM’s with different versions of IE.  That was quite a pain. There is now a tool that will allow you to view you web pages in different versions of IE (5.5, 6, 7, and 8).