SQL Server 2008 R2 Update for Developers Training Kit

http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

add random numbers into a database

This code assumes you have an ID (identity) column.

 

DECLARE @counter smallint;
DECLARE @random_number float;
SET @counter = 1;
WHILE @counter < 2000
  
   BEGIN
  SET @random_number = RAND()
  SET @random_number = @random_number * 100 
     UPDATE [db_name].[dbo].[db_table]
   SET  [db_column_rand] = @random_number
  WHERE [ID] = @counter
       SET @counter = @counter + 1
   END;
GO

 

 

PPS and 2008 SQL and Windows Server

I am building out a PURE 2008 hyper-v environment for an upcoming POC. PPS doesn’t officially support SQL Server 2008 until SP2 due out in the beginning of Dec 2008. There is a beta PPS SP2 now available.

I want to use SQL 2008 as for my PPSMonitoring repository DB, and connect to SQL 2008 cubes. There are some SQL 2005 redistributables that are required and available here

• Sqlncli
• SQLServer2005_ADOMD
• SQLServer2005_ASOLEDB9
• SQLServer2005_XMO

Once installed they must be upgraded to at least CU7 of SQL Server 2005. The CU7 versions of those components are REQUIRED in order for PPS to talk to SQL 2008. That is the bridge-point version where SQL 2005 is able to talk to SQL 2008. These redists must be installed on all PPS machines. Contact support for these updated redists. CU7 is documented here.

Install RTM Version of PPS (version=3520). Do not run the configuration manager.

Install SP2 version of PPS. Run Configuration manager. I am running only SP2 beta M&A and my Dashboard Designer version is 3.0.4208.

Update Current Period inT-SQL

In case you ever need to do this, attached is an example how you can update the current period in a PPS Planning application in the AppDB via T-SQL.

Download Sample File

figure out how old someone is

Ever have to figure out how old someone is based on their birthdate? maybe you want to create age bands to do analysis.

Here is how to get that data, then pump it into a update statememt into a column, then even create a case statement and store the banding info in a db column.

 

Here’s the query… 

SELECT BIRTH_DATE, DATEDIFF(yy,BIRTH_DATE,GETDATE()) AS YEARS

FROM <tablename>

Useful SQL Snippets

I have some useful SQL code to share.

 

Since there is no MID function in SQL I had to use SUBSTRING, however it only works with a String, so here is a statement that will cast it for you

 

SUBSTRING(CAST(<column> AS CHAR(10)), <start>, <end>)

 

This CASE statement was useful to conditionally apply values to a column based on another columns data

 

USE <db_name>

GO

UPDATE <table_name>

SET <columnA> =

 CASE

   WHEN <columnB> = whatever THEN ‘FOO’

   WHEN <columnB> = somethingelse THEN ‘BAR’

   ELSE ‘Unknown’

  END

GO

 

HTH