Prepare for the 70-536 Certification Exam: Microsoft .NET Framework 2.0 - Application Development Foundation
What you need to know to pass the 70-536 certification exam:
Visit the following links for resources to their individual topics and study them completely and thoroughly until you're confident you've mastered them! The best way to accomplish this is by actually building Visual Studio .Net 2005 solutions and projects and incorporating the topics listed below into them accordingly. There's no better way to study for an exam than by actually doing it in real-world scenarios. You can get Visual Studio .Net 2005 by clicking on this link.
Sunday, February 05, 2006
Andrew Coates ::: MSFT : Studying for another Beta Exam (71-536)
Andrew Coates ::: MSFT : Studying for another Beta Exam (71-536)
Looks like Mitch is studying for 71–536 as well. I’m signed up to do it AFTER the Ready Launch – on Dec 20. For 71–528, we had a study group chat. Mitch has taken a different tack, doing individual blog posts on each of the skills being measured as he looks at them.
So far he’s done:
Value types and nullable types
Reference types
Attributes
Generic types
Exception classes
Boxing and Unboxing
Forwarding types? and
ArrayList class
This looks like a great approach. Leave comments on Mitch’s individual posts if you’ve got stuff to add and get ready to get certified!
Looks like Mitch is studying for 71–536 as well. I’m signed up to do it AFTER the Ready Launch – on Dec 20. For 71–528, we had a study group chat. Mitch has taken a different tack, doing individual blog posts on each of the skills being measured as he looks at them.
So far he’s done:
Value types and nullable types
Reference types
Attributes
Generic types
Exception classes
Boxing and Unboxing
Forwarding types? and
ArrayList class
This looks like a great approach. Leave comments on Mitch’s individual posts if you’ve got stuff to add and get ready to get certified!
Eric Gunnerson's C# Compendium : Nullable types in C#
Eric Gunnerson's C# Compendium : Nullable types in C#
Right off the bat, on the first thing I study in .NET 2.0, there is something that looks fundamentally wrong to me - nullable types and boolean.
Skills measured by exam 70-551
Section 1
Developing applications that use system types and collections
Manage data in a .NET Framework application by using .NET Framework 2.0 system types. (Refer System namespace)
Value types
Nullable type
Syntax:
bool? nullBool = null;
- Nullable, hasvalue property,boolean now has 3 values! return (int?) cmd.ExecuteScalar(); Null coalescing operator (x ?? Y) - default x to Y if null
G. Shaw said:
I hope you and the other language designers know what you are doing for the long run. The current C# language is thing of beauty with very few warts. By adding the Big Four plus this new feature I worried that you are starting to move the language from a simpiler easier to use language more into the realm of C++ hell. I've worked with C# for years now (since beta 1) and find the language has been perfect. The language is so explicity about what it is doing, adding new funky operators makes the language more confusing as it isn't immediately obvious what is being down.
Please do a lot of usability testing with the syntax of this feature. I can see a need but I really think it should be done as a keyword and not an operato. For example,
nullable int a;
nullable int b;
nullable int c = a+ b;
Is immediately obvious what we are doing. The int? syntax is alien and is painful to read/understand. Since this is a feature that isn't going to be used all the time people are going to have look it up to understand how it works when the encounter it.
How can a bool have three values! It can't, that's why you need a new type: nullable bool. In fact I'd keep it as Nullable but provide operator overloading so that the operators work correctly to return null types.
What's next... Reference types and attributes... Hmm. Only 450 or so items to cover in 2 weeks.
Right off the bat, on the first thing I study in .NET 2.0, there is something that looks fundamentally wrong to me - nullable types and boolean.
Skills measured by exam 70-551
Section 1
Developing applications that use system types and collections
Manage data in a .NET Framework application by using .NET Framework 2.0 system types. (Refer System namespace)
Value types
Nullable type
Syntax:
bool? nullBool = null;
- Nullable, hasvalue property,boolean now has 3 values! return (int?) cmd.ExecuteScalar(); Null coalescing operator (x ?? Y) - default x to Y if null
G. Shaw said:
I hope you and the other language designers know what you are doing for the long run. The current C# language is thing of beauty with very few warts. By adding the Big Four plus this new feature I worried that you are starting to move the language from a simpiler easier to use language more into the realm of C++ hell. I've worked with C# for years now (since beta 1) and find the language has been perfect. The language is so explicity about what it is doing, adding new funky operators makes the language more confusing as it isn't immediately obvious what is being down.
Please do a lot of usability testing with the syntax of this feature. I can see a need but I really think it should be done as a keyword and not an operato. For example,
nullable int a;
nullable int b;
nullable int c = a+ b;
Is immediately obvious what we are doing. The int? syntax is alien and is painful to read/understand. Since this is a feature that isn't going to be used all the time people are going to have look it up to understand how it works when the encounter it.
How can a bool have three values! It can't, that's why you need a new type: nullable bool. In fact I'd keep it as Nullable
What's next... Reference types and attributes... Hmm. Only 450 or so items to cover in 2 weeks.
What's the monetary value of Microsoft certification?
What's the monetary value of Microsoft certification?: "It's pretty clear that the MSDN2 material was consulted when the Exam 70-526 objective were drafted. For example, the objective �Use the Windows Forms Designer to create default event handlers.� maps directly to the topic How to: Create Default Event Handlers on the Windows Forms Designer. Need a resource for the exam objective �Connect multiple events to a single event handler�? Check out the MSDN2 topic How to: Connect Multiple Events to a Single Event Handler in Windows Forms. Objective 'Set page details for printing by using the PageSetupDialog component.' maps to PageSetupDialog Component (Windows Forms). There's not a one-to-one mapping of all objectives, but there are a lot of direct mappings. It's just hard not to learn when you use this approach, and it makes it easier to find related information in the future because it drills the content structure into your head."
What's the monetary value of Microsoft certification?
What's the monetary value of Microsoft certification?: "The technique I use is this: I create an Excel spreadsheet with all the objectives listed, and I create a column listing resources (usually hyperlinks) for each objective. For Exam 70-526, I've found this to be easy. Better yet, in the process of searching for resources, I'm definitely getting a much better grasp at the wealth of material and organization of material posted on MSDN2 (http://msdn2.microsoft.com). It's really impressive - kudos to the VS 2005 product teams and documentation team. "
Upgrade Microsoft Certifications + Promo Codes
Microsoft News
Until March 14, there are some promo codes for these.
MCAD Exam 71-551 (Promo code UPG551) MCAD Web
MCAD Exam 71-552 (promo code 552BTA) MCAD Windows
MCSD Exam 71-553 (promo code BTA553) MCSD #1/2
MCSD Exam 71-554 (promo code UPG554) MCSD #2/2
You also get 1 free exam per pass. So if you pass 3 exams you'll get $375USD worth of vouchers, on top of the $375USD you already saved, + you won't write all 7 of the new exams.
Something tells me these won't be easy....
Until March 14, there are some promo codes for these.
MCAD Exam 71-551 (Promo code UPG551) MCAD Web
MCAD Exam 71-552 (promo code 552BTA) MCAD Windows
MCSD Exam 71-553 (promo code BTA553) MCSD #1/2
MCSD Exam 71-554 (promo code UPG554) MCSD #2/2
You also get 1 free exam per pass. So if you pass 3 exams you'll get $375USD worth of vouchers, on top of the $375USD you already saved, + you won't write all 7 of the new exams.
Something tells me these won't be easy....
Sunday, January 29, 2006
Expressions in SSIS
So they are rather convoluted in my opinion, but very powerful.
Expressions in SSIS. The key to making dynamic packages is to use variables and expressions. Go to the variables window and create a new string variable, say DATE. (Use upper case for variables - they're case sensitive and this will save you headaches.) Then create another variable, let's call it SQL_QUERY. Make sure they're both string. Set the default date to 20050112 and the default sql to select * from mytable where date = '20051112'.
Here's where it gets interesting.
Go to the Package tab, then browse to the variables collection. Click on the SQL_QUERY variable and view the properties. Turn on Use as Expression. I use notepad to type in the expression, since they didn't put any ... window in there yet. (SP1??) And the expression is...
"Select * from mytable where date = '" + @[User::DATE] + '"
Your query should show up in the variable value property with the correct date at the end.
Voila. This should remove a bunch of useless script from your code. (See Project REAL where they were passing around the XML/A variable to the Analysis Services command task)
I can think of a few other places this would work (connection strings in variables maybe?).
Next goal is to try and figure out why it takes 5-15 seconds just to load a package from DTRUN.
Expressions in SSIS. The key to making dynamic packages is to use variables and expressions. Go to the variables window and create a new string variable, say DATE. (Use upper case for variables - they're case sensitive and this will save you headaches.) Then create another variable, let's call it SQL_QUERY. Make sure they're both string. Set the default date to 20050112 and the default sql to select * from mytable where date = '20051112'.
Here's where it gets interesting.
Go to the Package tab, then browse to the variables collection. Click on the SQL_QUERY variable and view the properties. Turn on Use as Expression. I use notepad to type in the expression, since they didn't put any ... window in there yet. (SP1??) And the expression is...
"Select * from mytable where date = '" + @[User::DATE] + '"
Your query should show up in the variable value property with the correct date at the end.
Voila. This should remove a bunch of useless script from your code. (See Project REAL where they were passing around the XML/A variable to the Analysis Services command task)
I can think of a few other places this would work (connection strings in variables maybe?).
Next goal is to try and figure out why it takes 5-15 seconds just to load a package from DTRUN.
SQL Server 2005 articles
SQL Server 2005 articles: "Generating Test Data" + Everything else you ever wanted to know about SSIS
Friday, January 27, 2006
Sunday, January 22, 2006
Presentation Zen: Gates, Jobs, & the Zen aesthetic
Presentation Zen: Gates, Jobs, & the Zen aesthetic
Presentation Zen is a very clean web site with some great links on powerpoint presentations.
Presentation Zen is a very clean web site with some great links on powerpoint presentations.
8 mistakes when creating PowerPoint presentations
8 mistakes when creating PowerPoint presentations
Here's one they missed: hide reminders/desktop alerts so you don't get a message like "refill herpes meds" etc during your presentation:
Powerpoint > Tools > Options. > Preferences tab > E-mail Options > Advanced E-mail Options.
Under When new items arrive in my Inbox, clear the Display a New Mail Desktop Alert (default Inbox only) check box.
Here's one they missed: hide reminders/desktop alerts so you don't get a message like "refill herpes meds" etc during your presentation:
Powerpoint > Tools > Options. > Preferences tab > E-mail Options > Advanced E-mail Options.
Under When new items arrive in my Inbox, clear the Display a New Mail Desktop Alert (default Inbox only) check box.
Thursday, January 19, 2006
Thursday, January 12, 2006
Connecting to a Remote SSIS Server
Connecting to a Remote SSIS Server
There are some hoops to jump through for access denied errors with SSIS.
There are some hoops to jump through for access denied errors with SSIS.
Wednesday, January 11, 2006
Paolo's WebLog on SQL Server
Paolo's WebLog on SQL Server: "Parent child dimensions in Analysis Services 2005 "
Monday, January 09, 2006
Sunday, January 08, 2006
Download details: PSS Service Center Labs - 2005
Download details: PSS Service Center Labs - 2005
Overview
Do you want to know how Microsoft SQL Product Support Services (PSS) engineers break down problems? Originally designed as an opportunity for customers to work with Microsoft SQL Server Escalation Engineers at the SQL PASS 2005 Conference, these labs can be used for self-paced troubleshooting training on SQL Server 2005.
All labs are documented so that users can work through different scenarios in a self-study environment. These labs cover popular topics such as blocking, performance, memory, and debugging, and data recovery.
My way of hacking myself out of problems with software is usually Sysinternals Filemon & Regmon. There should be some interesting tips from above.
Overview
Do you want to know how Microsoft SQL Product Support Services (PSS) engineers break down problems? Originally designed as an opportunity for customers to work with Microsoft SQL Server Escalation Engineers at the SQL PASS 2005 Conference, these labs can be used for self-paced troubleshooting training on SQL Server 2005.
All labs are documented so that users can work through different scenarios in a self-study environment. These labs cover popular topics such as blocking, performance, memory, and debugging, and data recovery.
My way of hacking myself out of problems with software is usually Sysinternals Filemon & Regmon. There should be some interesting tips from above.
Microsoft Analysis Services - Wikipedia, the free encyclopedia
Microsoft Analysis Services - Wikipedia, the free encyclopedia: "History
Microsoft's foray into OLAP Server business began in 1996 with acqusition of OLAP technology from Israeli company Panorama. In 1998 Microsoft released the first version, named OLAP Services, part of SQL Server 7. OLAP Services supported MOLAP, ROLAP, HOLAP and Hybrid OLAP architectures, used OLEDB for OLAP as the client access API and MDX as a query language. It could work in the client server mode or in offline mode with local cube files. In 2000 Microsoft released the next version, named Analysis Services 2000. The rename from OLAP Services happened because of the inclusion of Data Mining services, so the product wasn't just about OLAP anymore. Analysis Services 2000 was an evolutionary release, it was built on the same architecture as OLAP Services and was backward compatible with it. The major improvements included more flexibility in dimension design, by supporting parent child dimensions, changing dimensions, virtual dimensions etc. Another major area of improvements was much enhanced calculation engine with support for unary operators, custom rollups and cell calculations. Other new features were dimension security, distinct count, connectivity over HTTP, session cubes, grouping levels etc. In 2005 Microsoft released the next generation of OLAP and Data Mining technology as Analysis Services 2005. Unlike previous released, Analysis Services 2005 was a revolutionaly release. It maintained backward compatibility on the API level, i.e. applications written with OLEDB for OLAP and MDX continue to work, but the architecture of the product was completely different. The major change came to the model in the form of UDM - Unified Dimensional Model."
Microsoft's foray into OLAP Server business began in 1996 with acqusition of OLAP technology from Israeli company Panorama. In 1998 Microsoft released the first version, named OLAP Services, part of SQL Server 7. OLAP Services supported MOLAP, ROLAP, HOLAP and Hybrid OLAP architectures, used OLEDB for OLAP as the client access API and MDX as a query language. It could work in the client server mode or in offline mode with local cube files. In 2000 Microsoft released the next version, named Analysis Services 2000. The rename from OLAP Services happened because of the inclusion of Data Mining services, so the product wasn't just about OLAP anymore. Analysis Services 2000 was an evolutionary release, it was built on the same architecture as OLAP Services and was backward compatible with it. The major improvements included more flexibility in dimension design, by supporting parent child dimensions, changing dimensions, virtual dimensions etc. Another major area of improvements was much enhanced calculation engine with support for unary operators, custom rollups and cell calculations. Other new features were dimension security, distinct count, connectivity over HTTP, session cubes, grouping levels etc. In 2005 Microsoft released the next generation of OLAP and Data Mining technology as Analysis Services 2005. Unlike previous released, Analysis Services 2005 was a revolutionaly release. It maintained backward compatibility on the API level, i.e. applications written with OLEDB for OLAP and MDX continue to work, but the architecture of the product was completely different. The major change came to the model in the form of UDM - Unified Dimensional Model."
Propagating Stimula: Setting Up a SQL Server Stress Test Environment in 8 Steps
Propagating Stimula: Setting Up a SQL Server Stress Test Environment in 8 Steps: "Setting Up a SQL Server Stress Test Environment in 8 Steps"
Great article, even for general requirements gathering and analysis.
Great article, even for general requirements gathering and analysis.
Managing Analysis Services Partitions with Table-Valued Functions (table UDFs)
Managing Analysis Services Partitions with Table-Valued Functions (table UDFs): "Here's an Adventure Works cube example:
1.) Create the following function in the AdventureWorksDW database:
----------------------
CREATE FUNCTION dbo.udf_FactInternetSales_Date_Range
(@Start_OrderDateKey INT, @End_OrderDateKey INT)
RETURNS table
AS
RETURN
(
SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey >= @Start_OrderDateKey
AND OrderDateKey <= @End_OrderDateKey
)
----------------------
2.) Open up BIDS and the Adventure Work cubes and browse to the Partitions tab.
3.) Under the 'Internet Sales' measure group partitions, change the Internet_Sales_2004 source query to:
SELECT * FROM dbo.udf_FactInternetSales_Date_Range(915,1280)
4.) Do the same for the other partitions, except change the input OrderDateKey ranges passed into the UDF."
1.) Create the following function in the AdventureWorksDW database:
----------------------
CREATE FUNCTION dbo.udf_FactInternetSales_Date_Range
(@Start_OrderDateKey INT, @End_OrderDateKey INT)
RETURNS table
AS
RETURN
(
SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey >= @Start_OrderDateKey
AND OrderDateKey <= @End_OrderDateKey
)
----------------------
2.) Open up BIDS and the Adventure Work cubes and browse to the Partitions tab.
3.) Under the 'Internet Sales' measure group partitions, change the Internet_Sales_2004 source query to:
SELECT * FROM dbo.udf_FactInternetSales_Date_Range(915,1280)
4.) Do the same for the other partitions, except change the input OrderDateKey ranges passed into the UDF."
Friday, January 06, 2006
Geek Noise
Geek Noise: "$size = new-object System.Management.Automation.Host.Size [int] $width = 132 [int] $height = 45 switch( $args.Length ) { 2 { $width = $args[0] $length = $args[1] } 1 { $width = [int] $args[0] $height = [int] ($width / 3) } 0 { 'No args...' | out-host } } 'Setting to $width x $height' | out-host $bufferWidth = $host.UI.RawUI.BufferSize.Width if( $bufferWidth -lt $width ) { $size = $host.UI.RawUI.BufferSize $size.Width = $width $host.UI.RawUI.BufferSize = $size $size.Width = $width $size.Height = $height $host.UI.RawUI.WindowSize = $size } else { $size.Width = $width $size.Height = $height $host.UI.RawUI.WindowSize = $size $size = $host.UI.RawUI.BufferSize $size.Width = $width $host.UI.RawUI.BufferSize = $size } "
Time to download Monad.
Time to download Monad.
Friday, December 30, 2005
Donald Farmer on SQL Server Integration Services : Oracle and SQL Server Integration Services
Donald Farmer on SQL Server Integration Services : Oracle and SQL Server Integration Services: "SQL Server to Oracle. What can I say? S*!& happens. "
Wednesday, December 28, 2005
Friday, December 23, 2005
Brian Knight, SQL Server MVP
Speeding up the VS/SQL User Interface Load Time
One of the frustrating things about the new Visual Studio interface is the load speed. This applies to SQL Server 2005 as well. I have a very new computer which is quite speedy and the splash screen still takes 5-10 seconds to load. On my old machine, this is more like 10-15 seconds while the splash screen is loaded before SQL Server Management Studio would show the login dialog box. A remedy I stumbled across was to add the -nosplash switch at the end of the executables call in the shortcut. After killing the splash screen from displaying, my load time on my good machine was down to 1-2 seconds before being prompted for a login screen. I come in an out of tools so much, that this is quite useful.
To do this, right-click on the shortcut in the start menu for your tool like Business Intelligence Development Studio or SQL Management Studio and click Properties. In the target text box change the shortcut to point to the following for SQL Management Studio:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -nosplash
For Business Intellgience Managment Studio, the same logic applies:
"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" -nosplash
Also, this works for the Visual Studio environment in general. I personally hop in and out of these tools all day long and this solved a lot of frustration.
One of the frustrating things about the new Visual Studio interface is the load speed. This applies to SQL Server 2005 as well. I have a very new computer which is quite speedy and the splash screen still takes 5-10 seconds to load. On my old machine, this is more like 10-15 seconds while the splash screen is loaded before SQL Server Management Studio would show the login dialog box. A remedy I stumbled across was to add the -nosplash switch at the end of the executables call in the shortcut. After killing the splash screen from displaying, my load time on my good machine was down to 1-2 seconds before being prompted for a login screen. I come in an out of tools so much, that this is quite useful.
To do this, right-click on the shortcut in the start menu for your tool like Business Intelligence Development Studio or SQL Management Studio and click Properties. In the target text box change the shortcut to point to the following for SQL Management Studio:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" -nosplash
For Business Intellgience Managment Studio, the same logic applies:
"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" -nosplash
Also, this works for the Visual Studio environment in general. I personally hop in and out of these tools all day long and this solved a lot of frustration.
Thursday, December 22, 2005
Slowly Changing Dimensions
Thomas Pagel BI Blog :: "One of the very nice enhancements of SQL Server 2005 Integration Services (SSIS) compared to DTS is the introduction of the wizard for slowly changing dimensions (SCD). It makes it easy to create dimension tables storing changing data and tracking these changes. There are some blogs about that, i.e. Jamie has a nice example for a package using the wizard? http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx"
Thomas Pagel BI Blog :
Thomas Pagel BI Blog :: "So we?re getting closer and closer to RTM and we all hope that Microsoft will deliver a perfect product after such a long time of Betas (sorry, IDWs and CTPs). But you can be sure that this will not be a 100% finished product, just as Windows 2003 and other releases were not perfect on day one. I?m not one of guys telling you ?wait for Service Pack 1?, SQL 2005 is a too good product to spend more time without it. But I?m sure you have to expect some issues mostly related to performance.
One issue we got aware of is a performance flaw when you use NON EMPTY with ?non trivial? calculated members (well, this seams to be starting with already quite trivial calculations?). We had queries running for minutes and asked Microsoft why they take so long. They suggested exchanging the calculated member with the formula behind it. We had this with just a ParallelPeriod function we used in a calculated member. When we changed the query to use the base measure and put the ParallelPeriod in the query the performance was very good (some seconds for a quite complex MDX).
Remember that NON EMPTY is used very often, i.e. Reporting Service?s query designer uses it by default. So it?s quite likely that you get in touch with this issue.
It?s confirmed that this flaw will NOT be fixed in RTM.
And there are others I?m not aware of, yet?"
One issue we got aware of is a performance flaw when you use NON EMPTY with ?non trivial? calculated members (well, this seams to be starting with already quite trivial calculations?). We had queries running for minutes and asked Microsoft why they take so long. They suggested exchanging the calculated member with the formula behind it. We had this with just a ParallelPeriod function we used in a calculated member. When we changed the query to use the base measure and put the ParallelPeriod in the query the performance was very good (some seconds for a quite complex MDX).
Remember that NON EMPTY is used very often, i.e. Reporting Service?s query designer uses it by default. So it?s quite likely that you get in touch with this issue.
It?s confirmed that this flaw will NOT be fixed in RTM.
And there are others I?m not aware of, yet?"
Monday, December 19, 2005
Scrollable checklists
Scrollable checklists: "An alternative to the select element with enabled multiple attribute.
This example is described in the Check it, don't select it article on C82. See a bare-bones example."
This example is described in the Check it, don't select it article on C82. See a bare-bones example."
Thursday, December 15, 2005
Sequence number in Reporting Services
Want a running total like Excel's row numbering?
RunningValue(1, Sum, Nothing) gives you a sequence number
RunningValue(1, Sum, Nothing) gives you a sequence number
Wednesday, December 14, 2005
Office Business Scorecard Manager 2005
Office Business Scorecard Manager 2005: "SharePoint Lists as Data Sources in Business Scorecard Manager
posted Wednesday, December 07, 2005 1:42 PM by Patrick Husting
SharePoint runs on SQL, SQL supports ODBC so of course it makes sense to be able to use SharePoint lists as data sources in Business Scorecard Manager as soon as you have ramped up on the SharePoint schema.
Here's an example connection string:
Driver={SQL Server};Server=localhost;Database=STS_MACHINENAME_1;Trusted_Connection=yes;
Here's a sample query that does a count:
select count(*)
from userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'SOME_LIST_TITLE' where nvarchar3 = 'SOME_COLUMN_HEADER_1' and nvarchar2 = 'SOME_COLUMN_HEADER_2'
Here's one that does a ratio:
declare @nApples decimal
declare @nOranges decimal
declare @ratio decimal
set @nApples = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'RED') or(nvarchar3='REDISH')))
set @nOranges = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'ORANGE') or(nvarchar3= 'ORANGEISH')))
"
posted Wednesday, December 07, 2005 1:42 PM by Patrick Husting
SharePoint runs on SQL, SQL supports ODBC so of course it makes sense to be able to use SharePoint lists as data sources in Business Scorecard Manager as soon as you have ramped up on the SharePoint schema.
Here's an example connection string:
Driver={SQL Server};Server=localhost;Database=STS_MACHINENAME_1;Trusted_Connection=yes;
Here's a sample query that does a count:
select count(*)
from userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'SOME_LIST_TITLE' where nvarchar3 = 'SOME_COLUMN_HEADER_1' and nvarchar2 = 'SOME_COLUMN_HEADER_2'
Here's one that does a ratio:
declare @nApples decimal
declare @nOranges decimal
declare @ratio decimal
set @nApples = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'RED') or(nvarchar3='REDISH')))
set @nOranges = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'ORANGE') or(nvarchar3= 'ORANGEISH')))
"
Friday, December 09, 2005
daclark's WebLog
daclark's WebLog: "Comparing machines in SMS
Here's a query for comparing Services on two machines. Just Clone the 'Compare software inventory on two computers' report and replace the SQL with below.
select (case when inv1.Name0 is not null then @name1 else @name2 end) as Netbios_Name0,
(case when inv1.Name0 is not null then inv1.DisplayName0 else inv2.DisplayName0 end) as DisplayName0,
(case when inv1.Name0 is not null then inv1.Name0 else inv2.Name0 end) as Name0,
(case when inv1.Name0 is not null then inv1.StartMode0 else inv2.StartMode0 end) as StartMode0,
(case when inv1.Name0 is not null then inv1.StartName0 else inv2.StartName0 end) as StartName0
from ( select s.ResourceID, s.DisplayName0, s.Name0, s.PathName0, s.StartMode0, s.StartName0
from v_GS_Service s join v_R_System sys on s.ResourceID=sys.ResourceID where sys.Netbios_Name0 = @name1 ) as inv1
full outer join ( select s.ResourceID, s.DisplayName0, s.Name0, s.PathName0, s.StartMode0, s.StartName0
from v_GS_Service s join v_R_System sys on s.ResourceID=sys.ResourceID where sys.Netbios_Name0 = @name2 ) as inv2
on inv1.Name0=inv2.Name0 and inv1.StartMode0=inv2.StartMode0
where (inv1.Name0 is NULL) or (inv2.Name0 is NULL) order by DisplayName0, Name0, Netbios_Name0
Here's a query for comparing AddR"
Here's a query for comparing Services on two machines. Just Clone the 'Compare software inventory on two computers' report and replace the SQL with below.
select (case when inv1.Name0 is not null then @name1 else @name2 end) as Netbios_Name0,
(case when inv1.Name0 is not null then inv1.DisplayName0 else inv2.DisplayName0 end) as DisplayName0,
(case when inv1.Name0 is not null then inv1.Name0 else inv2.Name0 end) as Name0,
(case when inv1.Name0 is not null then inv1.StartMode0 else inv2.StartMode0 end) as StartMode0,
(case when inv1.Name0 is not null then inv1.StartName0 else inv2.StartName0 end) as StartName0
from ( select s.ResourceID, s.DisplayName0, s.Name0, s.PathName0, s.StartMode0, s.StartName0
from v_GS_Service s join v_R_System sys on s.ResourceID=sys.ResourceID where sys.Netbios_Name0 = @name1 ) as inv1
full outer join ( select s.ResourceID, s.DisplayName0, s.Name0, s.PathName0, s.StartMode0, s.StartName0
from v_GS_Service s join v_R_System sys on s.ResourceID=sys.ResourceID where sys.Netbios_Name0 = @name2 ) as inv2
on inv1.Name0=inv2.Name0 and inv1.StartMode0=inv2.StartMode0
where (inv1.Name0 is NULL) or (inv2.Name0 is NULL) order by DisplayName0, Name0, Netbios_Name0
Here's a query for comparing AddR"
Friday, December 02, 2005
calculated sum - MSDN
calculated sum - MSDN: "Not sure if this solution will work for you, but you can add a Calculated Field to the Data Set, say: 'FilteredQty', defined as (assuming Quantity is an integer):
CInt(iif(Fields!Country.Value = 'USA', Fields!Quantity.Value, 0))
Then in your report you can use this expression:
Sum(Fields!FilteredQty.Value), wich should return 4 rather than 10
To make it dynamic, 'USA' can be replaced by a report parameter."
CInt(iif(Fields!Country.Value = 'USA', Fields!Quantity.Value, 0))
Then in your report you can use this expression:
Sum(Fields!FilteredQty.Value), wich should return 4 rather than 10
To make it dynamic, 'USA' can be replaced by a report parameter."
Thursday, December 01, 2005
Download details: Microsoft KPIUtil.exe tool for Microsoft Office Business Scorecard Manager 2005
Download details: Microsoft KPIUtil.exe tool for Microsoft Office Business Scorecard Manager 2005: "Microsoft KPIUtil.exe tool for Microsoft Office Business Scorecard Manager 2005
Download files belowQuick InfoVersion:1.0
Date Published:11/28/2005
Language:English
Download Size:38 KB - 216 KB*
*Download size depends on selected download components. "
Download files belowQuick InfoVersion:1.0
Date Published:11/28/2005
Language:English
Download Size:38 KB - 216 KB*
*Download size depends on selected download components. "
Subscribe to:
Posts (Atom)