Friday, June 29, 2012

Delete duplicate rows from a table with an identity column

T-SQL statement to delete duplicate rows from a table with an identtity column. When several records have equal data in other columns and only one record should be kept.

DELETE FROM dbo.TableA
WHERE Id NOT IN
(
SELECT MIN(Id)
FROM dbo.TableA
GROUP BY Column1, Column2 -- combination of columns that gives duplicates
)

Thursday, June 21, 2012

Create a new Team project in TFS from VS 2008

To create a new Team project folder in TFS, in which new solutions will be added.

Prerequisites

Visual Studio must be connected to a TFS server.

  • In Visual Studio select menu Team
  • Select connect to Team Foundation Server…
  • In the Connect to Team Project, select the Team foundation server, do not select a project.
  • click Connect

Procedure to create the TFS project

In VS 2008 file menu select New –> Team Project…

image

In the Connecti to Team Foundation Server, select the server that you have VS2008 connected to and click OK

image

In the New Team Project on <servername> dialog, enter a name for the Team project (the name has to be new for that server), click Next

image

In the next step choose a Process Template (I’ve used the first one available). click Next

image

In the settings for the project portal, specify the title of the team project portal (I use the same as the Team project name). Add a description. Click Next.

image

In the Specify Source Control Settings, select Create an empty source folder.

image

In the Confirm window, click Finish

image

A progress bar is displayed until the project has been completely created on the TFS server;

image

After a while (several minutes in muy case) the Created dialog appears, click on Close.

image

The TFS project is created with folders for Work items,… and Source control.

image

Add a Visual Studio 2008 Solution to the TFS project.

For simplicity I prefer to mirror my project structure under My Documents\Visual Studio 2008\Projects to the structure in the TFS project space. With Windows explorer I create a subfolder under the Projects folder with the same name as my TFS project. <MyTFS>.In this subfolder I will create new solutions and projects.

Create a solution and a project

With Visual Studio 2008 I create a new blank solution called MySln in the MyTFS subfolder. to this solution I add a new project MyPrj.

    • Visual Studio 2008
      • Projects
        • MyTFS
          • MySln
            • MyPrj

Once In TFS the result will be similar

  • MyTFS
    • MySln
      • MyPrj

Create a Source control Workspace

Before we add a solution to Team project, a new workspace has to be created with the Source control Explorer. Open the Workspace dropdown list and select Workspaces… at the bottom of this list.

image

This opens the ManageWorkspaces dialog. Click on the Add… button

image

In the Add Workspace dialog, give the Workspace the same name as the TFS project. In the first row of Working folders, set the status to 'Active. Set the Source control folder to the MyTFS folder. Set the local folder to My Documents\Visual Studio 2008\Projects\MyTFS

After a short while the workspace is created.

In the Source Control Explorer select the workspace you have just created. (Important, do not forget this before the next step)

Add visual studio solution to the source control

In the solution explorer, right click on the solution and select Add solution to Source control…

image

The solution will be added into the TFS project of the currently selected. The source files are not checked in yet, all files in the solution have a little plus sign in front of them, meaning that they are new and not yet checked in. Right click on the solution in the solution explorer and select 'Check in…

image

In the check in dialog, leave all checkboxes as checked and click on the Check In button. A progress bar is displayed while the files are being checked in. All the files now have a small lock in front of the name, meaning they have become read-only on your pc and they are checked in on the source control server.

Use SSIS package data as source for Report in Reporting Services

It is possible to use the destination output of a Package in SSIS as the input source of a report in Reporting Services. In SSIS the package can send output to a datareader destination object, this in memory output can then be captured by Reporting Services.

Configuring Reporting Services to Use SSIS Package Data

There is an article on MSDN that describes the configuration and also warns about possible security risks.

Creating Report Datasets from SQL Server Integration Services

The follow-up explains how to create a package and a report

Wednesday, June 06, 2012

Lightswitch deployment on Win2008R2 server

Before the first time installation of Visual studio Lightswitch 2011 apps on Windows 2008 R2 server, some prerequisites must be met on the server.
An article on MSDN can be found here

Access control in LightSwitch apps

Beth Massi has written an article about access control with  Lightswitch studio 2011 here.

Report Server cannot load the TERADATA / SQLPDW extension

Problem

In the Windows Application Log of a Windows 2008 server that hosts SQL server 2008 R2 reporting service, the following Error messages appear daily:

“Report Server (MSSQLSERVER) cannot load the SQLPDW extension.”

“Report Server (MSSQLSERVER) cannot load the TERADATA extension.”

Cause

I’ve found the explanation in the SSRS forums on MSDN (link can be found here)

“This error occurs because the Teradata extension is registered in the Reporting Services configuration file by default, but the Teradata assemblies are not shipped with SQL Server 2008 or as part of the .NET Framework. If the error message does not bother you, you can ignore the error when it is logged.

However, if you want to avoid this error, do one of the following:

  • Open the Reporting Services configuration file located in folder C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer, remove or comment out the Teradata extension. Do this only if you do not require functionality that the Teradata extension provides.

  • Install the .NET Data Provider for Teradata. Do this only if you require functionality that the Teradata extension provides. You can obtain the provider from the Teradata Web site. Reporting Services requires that the provider be version 12 or later.”

Solution

With SQL server configuration Manager or with Reporting Services Configuration manager, stop the reporting service.

Edit the file As suggested in the above article, edit the file rsreportserver.config in <C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer>

Search for SQLPDW, comment out 2 entries:

<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.DataExtensions.SqlDwConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/—>

<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQLADW.MSSqlAdwSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
    <Configuration>
        <EnableMathOpCasting>False</EnableMathOpCasting>
    </Configuration>
</Extension-->

 

Search for TERADATA, comment out 3 entries:

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/—>

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
    <Configuration>
        <EnableMathOpCasting>True</EnableMathOpCasting>
        <ReplaceFunctionName>oREPLACE</ReplaceFunctionName>
    </Configuration>
</Extension—>

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlModelGenerator,Microsoft.ReportingServices.SemanticQueryEngine"/-->

Start the reporting service.