VS 2013 - New Features - One ASP.NET

There is only one Web Project Type in Visual Studio unlike the previous versions of Visual Studio.

From Standard Web Forms to MVC all the project templates related to building websites and web applications are available in the same place.

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'xxxxxx.dll, Version=1.0.0.0, Culture=neutral, PublicKeyToken=7e7edcc6a6871fc7' or one of its dependencies. The system cannot find the file specified

• Check whether the DLL is deployed successfully in GAC (Global Assembly Cache)
• If not make sure that the DLLs are available in the following locations
• D:\Program Files\Microsoft SQL Server\100\DTS\Binn (depending on the version of SSIS)
• D:\Program Files (x86\Microsoft SQL Server\100\DTS\Binn (depending on the version of SSIS)
• Also make sure that the DLL is compiled for the right platform (32-bit or 64-bit or both)

SSIS FTP Step gives an error "The Password Was Not Allowed" and doesn't connect to the FTP Server

• Right click on your FTP connection manager, select properties and type the FTP Password.
• Save the package with EncryptSensitiveWithPassword property and provide a password and save the package and execute.

SQL Azure Reporting Services

Windows Azure SQL Reporting is a cloud-based reporting service for the Windows Azure Platform built on SQL Server Reporting Services technologies.

SQL Reporting service is available to current subscribers, but should not be used for new software development projects. The service will be discontinued on October 31, 2014.

An alternative to SQL Reporting is to use one or more instances of SQL Server Reporting Services (SSRS) running on Windows Azure Virtual Machines (VM).

A known issue of SQL Reporting is that report execution is slower than SSRS running on premises. In contrast, performance of SSRS on a VM is similar to the performance you might expect from an on-premises SSRS instance.

Transition from SQL Reporting to SQL VM provides the following advantages:

• Extensible report server for customized reporting
• Scheduled report execution and delivery
• Integration with hybrid solutions
• Faster Performance

SQL SERVER CLR Integration

SQLCLR allows integration of .NET Framework CLR components in SQL SERVER. .NET CLR Assemblies reside and run from database rather than file system. SQL SERVER need to check only once during deployment. This boosts performance and reliability.

SQLCLR allows to write stored procedures, functions, triggers, user defined types and aggregates using variety of .NET Framework languages like C#, VB.NET etc.

When to use SQLCLR?

Don't use SQLCLR for querying data from SQL SERVER. It is better to use T-SQL for that. Instead use SQLCLR when you can't perform some tasks using native SQL functions like GZip Compression/Decompression, PGP Encryption/Decryption, complex string manipulations etc.

How to enable SQLCLR?

exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE
GO

Cross Database Ownership Chaining in SQL SERVER

Consider a scenario where you have a view object in a database named "Database A" tries to access a table from "Database A" as well as a table from "Database B". In this scenario to run the view the user must also be a user in Database B along with Database A. If these databases are tightly linked, cross database ownership chaining can be used where user can access the table from Database B only through the view from Database A but doesn't have explicit permissions to query the table in Database B.

Steps to enable Cross Database Ownership Chaining

Set both Database A and Database B in the chaining as trustworthy.

ALTER DATABASE Database1 SET TRUSTWORTHY ON;
ALTER DATABASE Database2 SET TRUSTWORTHY ON;

Make sure that both Tables inside the Database A and Database B belong to the same owner.

Enable Cross Database Ownership Chaining. This can be done on whole instance level or specific databases.

For instance level

Exec sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;

For database level

ALTER DATABASE Database1 SET DB_CHAINING ON;
ALTER DATABASE Database2 SET DB_CHAINING ON;

Are nested Windows groups supported as SQL Server 2008 logins?

Yes, it is completely supported in SQL Server 2008 irrespective of the instance running on Windows Authentication or Mixed mode Authentication.

Make sure the AD Groups are created as Security Groups rather than Distribution Lists. If not created as Security Groups, then Nested AD Groups will not be working.

How to start SSMS with a different windows user account than the logged in account?

Attempt to start SSMS with a different windows user account if you are using credentials on the same domain

runas /netonly /user:domain\username ssms.exe

Use the "/netonly" switch, you can log in using remote credentials on a domain that you're not currently a member of, even if there's no trust set up

runas /netonly /user:domain\username ssms.exe

24 Hours of Pass - Business Analytics Edition

This 24 Hours of PASS: Business Analytics event will take place on February 5, beginning at 16:00 GMT and will feature 12 webcasts followed by 12 hours of on demand replay.

Sessions

Advanced Analysis Techniques

Advanced Analytics in Excel 2013
Excel is “the” analytical tool in Microsoft’s suite for advanced analysts. Of course, you know that...
Dejan Sarka
An Introduction to Predictive Modeling
This session will show you how to solve a real-world data mining problem by demonstrating the...
Carlos Bossy
Introduction to Querying in DAX
The DAX (Data Analysis Expressions) language can be used to query a Tabular and PowerPivot data...
Marco Russo
Predictive Analytics for Absolute Beginners
In this one-hour preview presentation, we cover the main motivation for learning something about...
Mark Tabladillo, Artus Krohn-Grimberghe

Analytics and Visualization

Adding Valuable Techniques to your Data Science Toolbox
Data Science requires us to see and understand complex patterns in data, so we use techniques like...
Mark Whitehorn
End-to-End Power BI
The aim of this session is to excite you with the potential of Microsoft's new self-service suite...
Peter Myers
Power Query: Beyond the Basics
You already know that you can accomplish a lot within the Power Query user interface. If you are a...
Chris Webb
Small Big Data - Phase 0 - Data Hygiene 
In this talk, we will talk about the starting point for every Big Data project - getting your...
Lynn Langit

Big Data

Has Big Data Killed the EDW?
Big Data technologies have given us the ability to process massive amounts of data at a low cost,...
Stacia Misner
Implementation and Design on the Parallel Data Warehouse
If you need to design a data warehouse that scales for today and into the future, then you need to...
Jason Strate

Information Delivery

DataViz You Thought You Could NOT Do with SSRS
Despite SQL Server Reporting Services (SSRS) being a very flexible and adaptable tool when it comes...
Jason Thomas

Strategy and Architecture

Panel: Myths, Misunderstandings, and Successes in Data Analytics
Big Data, Business Analytics, Data Analytics, NoSQL, Relational . . . do we even agree on what we...
Karen Lopez, Joseph D'Antoni, Lynn Langit, Stacia Misner

Milliseconds and SSIS DateTime Variables

Want to do delta loads for your Data Warehouse and having problems in storing Last Load time in an SSIS DateTime Variable?

SSIS DateTime data type truncates the milliseconds from the DateTime. To avoid this set the data type for the variable to store the Last Load time as String. e.g.

SELECT CONVERT(VARCHAR(23), LastModified, 121) LastModified FROM ........

Then you can change it to DateTime before passing it back to the database.

.......WHERE LastModified > CONVERT(DATETIME, @LastModified, 121)