Stored Procedures in LightSwitch

I do understand that Visual Studio 2010 LightSwitch should be a simple tool for building simple applications, but I would like it to be able to run stored procedures as well.
If you agree with me, please vote for this proposal on Microsoft Connect: https://connect.microsoft.com/VisualStudio/feedback/details/632862/run-stored-procedures-from-lightswitch

As always there is a complicated way to run stored procedures in code.
You could to define a custom WCF RIA Service that exposes your database stored procedures. Then add the RIA Service as a data source.

It is also described in this article:
http://tejana.wordpress.com/2010/12/09/microsoft-lightswitch-and-stored-procedures-using-wcf-ria-services/

However, why should thousands and thousands of developers write something that is relatively complicated and clearly infrastructure work? The deal between business developers and MS developers is that MS provides infrastructure and we do business functionality.

Isn't it better for MS to include this in LightSwich so that developers does not have to write this again and again and again? (Maybe Microsoft should find motivation to do this in how much will they save on support if they do not have to explain and debug this to thousands of people.)

Visual Studio 2010 LightSwitch

Couple of months ago Microsoft announced that they are developing application for creating simple business applications without coding. It is still in Beta 1. This is a simple system that allows you to create forms for aditing database tables and glue them together in an application.

Application built with this are not amazing, but usefull little things that I can quicly deploy to allow customers to search and populate simple set of little tables. Instead of getting an application developer and doing a project for couple of days or weeks, I can provide solution in couple of hours.

Another intersting aspect is that applications can be deployed as Windows (2-tier or 3-tier ClickOnce) or Web (3-tier) applications. This is due to the fact that applications are built on Silverlight platform.

StarInix Database Compare – free SQL Compare alternative

Colleague showed me a nice free program as SQL Compare alternative – StarInix Database Compare. It’s a free program that can compare two database on SQL Server, MySQL or MS Access.

To display TSQL code differences, it invokes ExamDiff  (also freeware and which is installed along with StarInix Database Compare). Unlike Red Gate’s program, it does not produce script that would make databases identical.

Double-hop on SQL Server

How to set delegation for SQL Server?

How to resolve an error like:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

It typically occurs when you use linked server – when you request from machine A something from machine B which then requests something from machine C.

Excellent text:

http://kendalvandyke.blogspot.com/2008/11/delegation-what-it-is-and-how-to-set-it.html

Domain Groups membership list?

How to check which Domain Groups are you member of?

> net user MyLogin /domain

List members of Domain Group with Command Prompt NET command

I often look for a way to list members of Active Directory/domain group using NET command from Command Prompt:

>net group MyGroup /domain

SQL Server 2008 R2 Launch in Toronto

Whole day event on Monday May 31,2010 in MARS.

Time		Description
8:00 - 9:00 am		Registration
9:00 - 9:15 am	( SQL PASS Community Representant ) &Daniel Shapiro	Welcome & Introductions
9:15 - 11:15 am	John Hancock	Keynote Presentation: Data Platform Vision & SQL Server 2008 R2 Introduction
SQL Server 2008 R2 Overview: Trusted & Scalable Platform (20 min.), Managed Self-Service BI (20 min.), IT & Developer Efficiency (20 min.)
11:15– 11:30 am	Break	Break	Break	Break
		DBA TRACK	BI TRACK	Visual Studio TRACK
11:30 – 12:30pm	Session Title:	Ensure Business Continuity with SQL Server 2008 R2, Windows Server 2008 & Hyper-V Live Migration	Enable Managed Self-service BI with PowerPivot for Excel and SharePoint 2010	Developing SQL Applications with Visual Studio 2010
Speaker:	Saleem Kanji	Howard Morgenstern	Adam Gallant and Paul Sanford
Content Link:			TBC
12:30 - 1:30 pm		Lunch	Lunch	Lunch
1:30 – 2:30pm	Session Title:	Simplify management of your SQL Server data platform with Multi-server Management	Achieve Rapid Reporting with Reporting Services and Report Builder 3.0	Managing Change for SQL Server applications using Team Foundation Server
Speaker:	Marc Theoret	Doug Harrison	Adam Gallant and Paul Sanford
Content Link:
2:30 - 2:45 pm	Break	Break	Break	Break
2:45 – 3:45 pm	Session Title:	Deliver unprecedented access to business-critical data at a lower TCO with SQL Server 2008 R2 Parallel Data Warehouse	Importance of Master Data Management	Targeting SQL Azure using Visual Studio
Speaker:	Doug Harrison	Marc theoret	David Remmer
Content Link:

OneNote publishing

I really enjoy using OneNote from Office 2007. It's a little known tool that looks like a notebook on your screen. It allows you to write using keyboard and pen and include other files like video, audio, diagrams... into it.
It is very cool way for people to collaborate on project. I know some companies that were using it to (co)write specs or design documentation in it. All the needed to do was to create a new notebook on shared folder and them everybody can start writing into it (at the same time).

What was missing for me was a way to collaborate over web. Naturally, Microsoft offered a way to collaborate through SharePoint, but I do not have SharePoint available for everything that I do. Is there some alternative that I do not know about?

In cases when you just want to publish your OneNote notebook over the web, you can use OneNote Web Exporter. It creates an .html file and set of OneNote files that you can put on a web server.

Mount ISO file

Somebody in MS, created WinXpVirtualCdControlPanel a small unsupported to for attaching ISO files as virtual CDs. That alleviates a need to burn ISO files on CD or DVD.

SQL Server 2008 Cost Savings Calculator | Microsoft

Microsoft created funny and useful page that compares total cost of ownership beween SQL Server 2008 and Oracle 11g.
SQL Server 2008 Cost Savings Calculator Microsoft

SQL Server 2008 R2 Express Edition supports 10GB databases

One of the major changes to SQL Server 2008 R2 Express Edition is that database size limit will be increased from 4GB to 10GB. It is still limited to use of 1 CPU.

SQL Server - Find biggest table

I've found a nice code snipet on how to find a biggest table in a database. It is based on sp_MsForEachTable, so works on all non-ancient versions of SQL Server.

Running Total in TSQL

There are many ways to do running total on SQL Server. Let's create a test table:

CREATE TABLE [dbo].[t](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Amt] [float] NULL
) ON [PRIMARY]
go

If you have SQL Server 2005 or earlier you could do a running total using a subquery or join:

select t.id, t.amt
, (select Sum(Amt) from dbo.t i where i.id <= t.id) RunTotal
from dbo.t t
order by 1

select t.id, t.amt, sum(a.amt) RunTotal
from dbo.t t
inner join dbo.t a
on a.id <= t.id
group by t.id, t.amt
order by 1

These look "natural" and seem to be using sets. Unfortunately, it is not really like that. If youre result has 4 rows, SQL Server will have to do a sum of 1 row for first, 2 rows for second, 3 rows for 3rd and 4 rows for 4th - in total 10 rows. For resultset with 10,000 rows it will have to total: (10,000^2+10,000)/2 = 50,005,000 (yes - 50 million rows). Even a solution with cursor is better then this.

If everyting is in a table, you could use a strange variation of Update statement to do thi faster.
Let's first add a column to the table:

ALTER TABLE dbo.t

ADD RT float NULL
GO

Now you can use a special form of Update statement that writes both into the table and in a varaiable:

DECLARE @rt float
SET @rt = 0


UPDATE t
SET @rt = RT = @rt + amt
WHERE id = id


select * from t

Apparently, this goes through the table just once and is much lighter on SQL Server. Unfortunately, it is based on non-documented behavior of SQL Server and behind a scene depends on row by row operation.

Alternatively, you can implement cursor a based solution (either in TSQL or in SQLCLR).

Unfortunately, it is not possible to do this using Over clause. SQL Server does not support (not even SQL Server 2008 R2) use of Over clause with Order By when using an aggregation function. We should ask Microsoft to implement this or this.

Allow everybody to see metadata

Since SQL Server 2005, a user/DBA can see definition of only those objects that he has permission to. Earlier versions of SQL Server were much more liberal in that respect. If you prefer that type of behavior on you server, you can issue a following command:

grant view any definition to public

Now, everybody can investigate all objects in a current database.

This may be a way to allow developers to investigate a production database without giving them ability to change anything. (It is better to do this then to make them database owners.)

ShareThis

Microsoft SQL Server 2008 R2 Release Date

May 21

SQL Server on Amazon EC2

• Currently SQL Server 2005 Standard
• Default drives are slow. You will want to allocate additional SAN space for it.
• You can move SAN space from one server (instance) to another - for example to use it with bigger instance.
• Use Junk Drive (Amazon S3) to store backups.

Full SQL Server in Cloud

You are not limited to SQL Server Azure (limited/cloud version of SQL Server) if you are locking for hosting solution. Some companies offer full version of SQL Server:

• Amazon EC2

• VMWare vCloud

• TerreMark

Technorati Tags: SQL Server,Azure,SQL Server Azure,virtual server,hosted SQL Server

Intro

A little bit of everything… TSQL code, server configurations, ideas…