TSQL Max and Over Clause

You can use the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values on multiple rows.

For instance:

SELECT Name

 , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary

 , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary

 , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary

 ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept

FROM EmployeePayHistory AS eph

      JOIN EmployeeDepartmentHistory AS edh

            ON eph.BusinessEntityID = edh.BusinessEntityID

      JOIN Department AS d

            ON d.DepartmentID = edh.DepartmentID

http://msdn.microsoft.com/en-us/library/ms187751.aspx

WCF Ria Services vs WCF Services

WCF RIA Services

Pro’s:

• Service can be consumed easily from Silverlight, AJAX, and WebForms. (Not winforms(??))
• Non-discoverable.
• Richer client side object model by including business rules and relationships between objects.
• Quicker to set up. Good for Rapid development.
• More control over what is exposed. Uses a set Interface.
• Integrated change tracking by default.
• Can monitor changes in client and batch the changes to the server.
• Uses reflection at design time to see objects (doesn’t use service contracts)
• Creates client code in Silverlight.
• Design time experience with data sources, drag drop etc. (Contains these features)
• Shares validation logic in client.
• Can use a set interface. Also provides Context object in client.
• Can support Linq queries between client and server but these are based on Interface.
• Examples:
• GetGamesByIdQuery(1);
• Var qry = GetGamesQuery().Where(g =>; g.Price < 50)
  LoadOperation op = cnt.Load(qry);

Con’s:

• Harder to Customize than WCF Services
• No message-level security
• Serialization is not well supported (has limitations)
• Gives up flexibility compared to WCF
• Harder to debug.
• Not easy to integrate with large enterprise applications
• More tightly coupled than WCF Services.

WCF Services

Pro’s:

• Standardized on OData protocol
• Loosely coupled clients and servers
• Service can be consumed from .NET, Silverlight, AJAX, PHP and Java (libraries available).
• Cross platform interoperability as a goal: Out-of-box support from future MS products such as SQL2008 R2, Azure, Excel 2010, SharePoint 2010, etc.
• RESTful
• Discoverable
• Uses JSON and ATOM (xml-based feed format)
• Can interact WCF service using standard HTTP verbs such as GET/POST/DELETE
• Uses Entity data modeling (EDM), entity-relationship derivative
• Non-relational sources can be represented using EDM exposed as data service.
• Support for Membership => activate membership endpoint.
• Allows you to add operations to fill custom operations.
• Includes Data Context class to monitor changes, issue changes in batches with transactional support.
• Queryable end-points (not a set interface).
• Flexible querying, paging association traversal.
• Supports Linq queries between client and server. Client can create Linq query and run it on server.
• Example:
  var qry from g in ds.Games WHERE Id == 1
  select g
  Qry.BeginExecute();
• Code written on client is small (Linq calls and context class work)

 Con’s:

• Does not give tight control over Interface to data access

SSRS Issues attaching Reports to Email subscriptions

I was attempting to set up an email subscription on our production server the other day but kept getting errors when the subscription ran. The development server subscriptions ran fine, the configuration settings for both servers appeared identical.

The weird thing was that I could send a subscription through email as long as I didn't attach the report.

After much searching, and pulling out my hair, I finally found this KB article:

A call to the "AuthzInitializeContextFromSid" API function fails during the delivery of an e-mail subscription in SQL Server Reporting Services

My hint was the error in the error log:
ERROR: Throwing Microsoft.ReportingServices. Diagnostics. Utilities. ServerConfigurationErrorException: The Report Server has encountered a configuration error; more details in the log files, AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.;


KB article 842423 walks you through how to give the account the permissions it needs to read the SID values.

Display all SQL Server's on a Network

cd C:\Program Files\Microsoft SQL Server\90\Tools\Binn
sqlcmd -L

Coalescing (??) Operator for .Net

The Null-Coalescing Operator acts similiar to TSQL IsNull.

The ?? operator defines the default value to be returned when a nullable type is assgned to a non-nullable type. This works for both reference types and value types

C#.Net Example:
string message = strOldMessage ?? "This is the default";
TSQL Example:
IsNull(printDate, GetDate())

Bulk Import into AD LDS Using cvsde command line utiltiy

Discovered how to bulk import users into AD LDS by using cvsde command line utiltiy.

Example:
cd C:\Windows\System32
csvde -i -f c:\importFile.csv -s ServerName -t PortNumber
...
This uses SSPI. More info can be found at: http://technet.microsoft.com/en-us/library/cc787549%28WS.10%29.aspx

MMRWA Presentation - Social Media 101

Tomorrow I'll be giving a presentation on Social Media 101 for the Mid-Michigan Romance Writers Association.

Blogging Jargon
Comparison of Blog Software Features and Tools
Helpful Links

I'll add more tomorrow after the meeting.
See you there!!