DATABASE JOURNAL – Featured Database Articles – MSSQL

http://www.databasejournal.com/features/mssql/archives.php

Archives
Combine BottomCount() with Other MDX Functions to Add Sophistication – 11/21/2008
Microsoft Windows PowerShell and SQL Server 2008 AMO – 11/18/2008
SQL Server: Measuring Space Allocation and Index Distribution – 11/14/2008
Installing a Two-node SQL Server 2008 Cluster – Advanced option – 11/12/2008
Establishing Distributed SQL Server Express’ Service Broker Conversations Using Certificate-based Authentication – 11/10/2008
SQL Server 2008 Recovery Models and Backups – 11/07/2008
Using Windows PowerShell to get SQL Server connection information – 11/05/2008
Attribute Member Values in Analysis Services – 11/04/2008
Introducing Reporting Services Charts for Analysis Services – 10/29/2008
Reports for SQL Server 2008 System Data Collections – 10/27/2008
Exploring SQL 2005’s Ranking Functions – NTILE() and ROW_NUMBER() – 10/24/2008
Configuring Certificate-based Authentication in SQL Server Express’ Distributed Service Broker Environment – 10/20/2008
Check your SQL Server using Windows PowerShell – Part 7 – 10/15/2008
MSSQL Analysis Services – Attribute Member Names – 10/13/2008
SQL Server 2005 Express Edition – Part 32 – Distributed Service Broker Environment – Conducting Dialogs – 10/10/2008
Setting up a Two-NODE SQL Server 2008 Cluster from the Command Prompt – Integrated Installation – 10/08/2008
Basic Set Functions: The BottomCount() Function, Part I – 10/06/2008
SQL 2008 Backup and Restore Part 1 – 10/03/2008
Check your SQL Server using Windows PowerShell – Part 6 – 10/01/2008
SQL Server 2008 Data Collections and the Management Data Warehouse – 09/29/2008
SqlCredit – Part 19: Exploring SQL 2005’s Ranking Functions – RANK() and DENSE_RANK() – 09/26/2008
Mastering OLAP Reports: Parameterized Grouping – 09/23/2008
SQL Server 2005 Express Edition – Part 31 – Distributed Service Broker Environment – Routing – 09/22/2008
Attribute Member Keys – Pt II: Composite Keys – 09/19/2008
Check your SQL Server using Windows PowerShell – Part 5 – 09/17/2008
Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt – Preparation – 09/16/2008
Intrinsic Member Properties: The MEMBER_VALUE Property – 09/12/2008
SQL Server 2005 Express Edition – Part 30 – Distributed Service Broker Environment – Endpoints – 09/08/2008
What is SQL Server – 09/05/2008
Terminate User processes in SQL Server – 09/03/2008
Attribute Member Keys – Pt 1: Introduction and Simple Keys – 08/29/2008
What Kind of DBA Are You? – 08/26/2008
SQL Server 2005 Express Edition – Part 29 – Implementing Service Broker Conversation – 08/25/2008
SqlCredit, Part 18: Exploring the Performance of SQL 2005’s OUTPUT Clause – 08/21/2008
Audit your Windows domain DBA group using PowerShell – 08/20/2008
Discover SQL Server TCP Port – 08/19/2008
Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part II – 08/18/2008
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property – 08/08/2008
Check your SQL Server using Windows PowerShell – Part 4 – 08/06/2008
SQL Server 2005 Express Edition – Part 28 – Implementing Service Broker Conversation – 08/05/2008
Create Your First SQL Server Database in 3 Quick Steps – 08/01/2008
Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I – 07/29/2008
SQL Server 2005 Express Edition – Part 27 – Implementing Basic Service Broker Objects – 07/28/2008
SqlCredit – Part 17: Exploring SQL 2005’s OUTPUT Clause – 07/25/2008
SQL Server 2008 MERGE Statement – 07/24/2008
Intrinsic Member Properties: The MEMBER_NAME Property – 07/21/2008
Check your SQL Server using Windows PowerShell – Part 3 – 07/16/2008
SQL Server Audit in SQL Server 2008 – Part 2 – 07/14/2008
SQL Server 2005 Express Edition – Part 26 – Introduction to Service Broker – 07/11/2008
Dimension Attributes: Introduction and Overview, Part V – 07/07/2008
SQL Server Profiler Part 2 – 07/02/2008
Check your SQL Server using Windows PowerShell – Part 2 – 07/01/2008
Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part II – 06/27/2008
SQL Server 2008 Date Functions, Part 2 – 06/26/2008
Enhancement in variable declaration – SQL Server 2008 – 06/24/2008
Implementing Upgrade of SQL Server 2005 Express Edition – 06/23/2008
Intrinsic Member Properties: The MEMBER_KEY Property – 06/20/2008
Check your SQL Server using Windows PowerShell – Part 1 – 06/18/2008
Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I – 06/13/2008
SQL Server 2005 Express Edition – Part 24 – Planning Upgrade of SQL Server 2005 Express Edition – 06/09/2008
SQL Server Audit in SQL Server 2008 – Part 1 – 06/06/2008
Compound Assignment Operators in SQL Server 2008 – 06/04/2008
Introduction to SQL 2005 Profiler Part 1 – 06/02/2008
Dimension Attributes: Introduction and Overview, Part IV – 05/29/2008
New Date Data Types with SQL Server 2008 – 05/27/2008
SqlCredit – Part 16: The Cost of Bloat – 05/23/2008
Intrinsic Member Properties: The MEMBER_CAPTION Property – 05/22/2008
SQL Server 2005 Express Edition – Part 23 – Manual Upgrade from Microsoft SQL Server Desktop Engine (MSDE) – 05/22/2008
Table-valued parameters – SQL Server 2008 – 05/21/2008
Dimension Attributes: Introduction and Overview, Part III – 05/15/2008
Policy-based Management in SQL Server 2008 – Part II – 05/13/2008
SQL Server 2005 Express Edition – Part 22 – Upgrading from Microsoft SQL Server Desktop Engine (MSDE) – 05/09/2008
Row Value Constructor in SQL Server 2008 – 05/06/2008
SQL Server Management Studio Reports and Dashboard – 05/02/2008
Support Parameterization from Analysis Services – Parameter Defaults – 04/29/2008
SqlCredit – Part 15: The Cost of Distribution – 04/25/2008
SQL Server 2005 Express Edition – Part 21 – Using Replication Management Objects – 04/24/2008
Connection Strategy for Multiple Database Environments – 04/22/2008
Dimension Attributes: Introduction and Overview, Part II – 04/18/2008
Policy-based Management in SQL Server 2008 – Part I – 04/17/2008
UPSERT Functionality in SQL Server 2008 – 04/16/2008
SQL Server 2005 Express Edition – Part 20 – Authenticating Merge Web Synchronization – 04/11/2008
Set Functions: The StripCalculatedMembers() Function – 04/07/2008
Storing Images and BLOB files in SQL Server Part 4 – 04/04/2008
Top Queries in SQL Server 2005 – 04/02/2008
Exam 70-443 Practice Test from uCertify.com – 03/31/2008
SqlCredit – Part 14: The Cost of Translation – 03/28/2008
Parameterization from Analysis Services – Cascading Picklists – 03/26/2008
SQL Server 2005 Express Edition – Part 19 – Authenticating Merge Web Synchronization – 03/24/2008
Using dtutil to copy SSIS packages stored in SQL Server – 03/20/2008
Find space Usage by Table , Schema in SQL Server 2005 and 2008 – 03/18/2008
Dimension Attributes: Introduction and Overview, Part I – 03/13/2008
SQL Server 2005 Express Edition – Part 18 – Merge Web Synchronization Setup – 03/11/2008
Storing Images and BLOB files in SQL Server Part 3 – 03/07/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 4 – 03/05/2008
Set Functions: The AddCalculatedMembers() Function – 03/03/2008
SQL Server DBA Dashboard – 02/29/2008
Support Parameterization from Analysis Services – 02/26/2008
SQL Server 2005 Express Edition – Part 17 – Merge Web Synchronization – 02/25/2008
SqlCredit – Part 13: More on Indexed Persisted Computed Columns – 02/22/2008
DST checking with Windows Powershell – 02/21/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 3 – 02/20/2008
Dimensional Model Components: Dimensions Part II – 02/15/2008
SQL Server 2005 Express Edition – Part 16 – Transactional and Merge Replication – 02/08/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 2 – 02/06/2008
MDX Numeric Functions: The Min() Function – 02/04/2008
Storing Images and BLOB files in SQL Server Part 2 – 02/01/2008
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets – 01/31/2008
Building Custom Reporting Services Reports for SQL Server Management Studio – 01/30/2008
SQL Server 2005 Express Edition – Part 15 – Snapshot Replication – 01/28/2008
SqlCredit – Part 12: Exploring Indexed Persisted Computed Columns – 01/25/2008
Dimensional Model Components: Dimensions Part I – 01/24/2008
Microsoft Windows PowerShell and SQL Server 2005 WMI Providers – Part 2 – 01/22/2008
Reporting Services: Customize Automatically Created Parameter Support Objects – 01/16/2008
Microsoft SQL Server 2008 – Change Data Capture – Part I – 01/11/2008
SQL Server 2005 Express Edition – Part 14 – Replication Support – 01/07/2008
Storing Images and BLOB files in SQL Server – 01/04/2008
New datetime datatypes in SQL Server 2008 – 01/02/2008
MDX Numeric Functions: The Max() Function – 12/31/2007
SQL Server 2005 – Hacking password Encryption – 12/28/2007
Disk Space Usage and SQL Server Performance – 12/26/2007
SQL Server 2005 Express Edition – Part 13 – ClickOnce Deployment and Security – 12/24/2007
BACKUP compression in SQL Server 2008 – 12/19/2007
Manage Unknown Members in Analysis Services 2005, Part II – 12/14/2007
SQL Server 2005 Express Edition – Part 12 – ClickOnce Deployment and Updates – 12/10/2007
Set Functions: The .AllMembers Function – 12/07/2007
SQL Server 2005 Encryption types – 12/05/2007
Installing SQL Server 2008 – 12/03/2007
Snapshot Reports II: SQL Server Management Studio Perspective – 11/30/2007
SQL Server Security Model – 11/28/2007
SqlCredit – Part 11: Change Tracking Using History Records – 11/26/2007

MS SQL Archives

2008   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2007   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2006   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2005   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2004   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2003   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2002   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2001   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2000   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1999   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1998   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

How Do I Use the new Entity Data Source?

http://www.microsoft.com/downloads/details.aspx?familyid=E02A5EF4-F913-4E9C-B022-6CB7C064257E&displaylang=en

Getting Started with ADO.NET Data Services

http://codebetter.com/blogs/david.hayden/archive/2008/01/08/getting-started-with-ado-net-data-services.aspx

At the Sarasota Visual Studio 2008 InstallFest I showed off a number of ASP.NET 3.5 Extensions CTP Features. One was ASP.NET Dynamic Data, which I have talked about in several posts:

• ASP.NET Dynamic Data Website Getting Started – ASP.NET RAD Development for Data-Driven Websites
• ASP.NET Dynamic Data Websites – Customizing Pages and Validation
• ASP.NET Dynamic Data and Validation Using LINQ To SQL OnPropertyChanging Partial Methods
• ASP.NET Dynamic Data Websites – Creating Custom Dynamic Data Fields
• ASP.NET Dynamic Data Tutorial – BuddyMetadataProvider and Custom Metadata Providers

Another was ADO.NET Data Services.

For those of you who haven’t played with ADO.NET Data Services, you owe yourself a quick 5 minute look to think about the possibilities. Here is the quick introduction I provided at the Visual Studio 2008 InstallFest.

Install the ASP.NET 3.5 Extensions CTP

First things first, swing over to the ASP.NET Website to download and install the ASP.NET 3.5 Extesions CTP, which includes ADO.NET Data Services.

You can find it here: http://www.asp.net/downloads/3.5-extensions/

Note this is a CTP, so common sense applies to what machines you install it on.

Create a New ASP.NET Website

Run Visual Studio 2008 and Create a New ASP.NET Website

Add LINQ To SQL Classes and ADO.NET Data Services Items

We need some data, and one of the easiest ways to get data is to add a LINQ TO SQL Classes File to the solution and drag and drop Northwind Tables on it. We also need to add the ADO.NET Data Services Item to the solution as that is what we are trying to test. I called my items, Northwind and NorthwindDataService, respectively.

Hopefully LINQ To SQL is fairly easy to add. ADO.NET Data Services is even easier to add:

When all is said and done, the solution looks like this:

Configure NorthwindDataService to use NorthwindDataContext

Open up the NorthwindDataService.cs in the App_Code Folder and change the code to look like below:

public class NorthwindDataService :
            WebDataService<NorthwindDataContext> {

    public static void InitializeService(IWebDataServiceConfiguration config)
    {
        config.SetResourceContainerAccessRule
            ("*", ResourceContainerRights.AllRead);
    }
    
}

Note that I added the NorthwindDataContext and set the access rules to allow access to all the data provided by the NorthwindDataContext in a read-only fashion.

Run the Website and Begin Querying

Now you can just run the website and start querying products or anything else in the Northwind Database using REST type URL’s.

Fetch ProductID = 1 : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products(1)

Fetch the Supplier for the Product : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products(1)/Supplier

Fetch Products 11 – 20 : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products?$skip=10&$top=10

Fetch Name of Product : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products(1)/ProductName/$value

I think I change Chai to Chai2 playing with the database, so your version will probably say Chai

Read the Documentation

Check out the documentation on ADO.NET Data Services for the ASP.NET 3.5 Extensions CTP at:

http://quickstarts.asp.net/3-5-extensions/adonetdataservice/default.aspx

Pretty cool stuff…

}

Note that I added the NorthwindDataContext and set the access rules to allow access to all the data provided by the NorthwindDataContext in a read-only fashion.

Run the Website and Begin Querying

Now you can just run the website and start querying products or anything else in the Northwind Database using REST type URL’s.

Fetch ProductID = 1 : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products(1)

Fetch the Supplier for the Product : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products(1)/Supplier

Fetch Products 11 – 20 : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products?$skip=10&$top=10

Fetch Name of Product : http://localhost:1453/WebSite5/NorthwindDataService.svc/Products(1)/ProductName/$value

I think I change Chai to Chai2 playing with the database, so your version will probably say Chai

Read the Documentation

Check out the documentation on ADO.NET Data Services for the ASP.NET 3.5 Extensions CTP at:

http://quickstarts.asp.net/3-5-extensions/adonetdataservice/default.aspx

Pretty cool stuff…

ADO.NET Data Services (formerly known as Project “Astoria”)

http://msdn.microsoft.com/en-us/data/bb931106.aspx

The ADO.NET Data Services framework consists of a combination of patterns and libraries that enable the creation and consumption of data services for the web. The goal of the ADO.Net Data Services framework is to facilitate the creation of flexible data services that are naturally integrated with the web, using URIs to point to pieces of data and simple, well-known formats to represent that data, such as JSON and plain XML. This results in the data service being surfaced to the web as a REST-style resource collection that is addressable with URIs and that agents can interact with using the usual HTTP verbs such as GET, POST or DELETE.

Data Type Mappings in ADO.NET

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

The .NET Framework is based on the common type system, which defines how types are declared, used, and managed in the runtime. It consists of both value types and reference types, which all derive from the Object base type. When working with a data source, the data type is inferred from the data provider if it is not explicitly specified. For example, a DataSet object is independent of any specific data source. Data in a DataSet is retrieved from a data source, and changes are persisted back to the data source by using a DataAdapter. This means that when a DataAdapter fills a DataTable in a DataSet with values from a data source, the resulting data types of the columns in the DataTable are .NET Framework types, instead of types specific to the .NET Framework data provider that is used to connect to the data source.

Likewise, when a DataReader returns a value from a data source, the resulting value is stored in a local variable that has a .NET Framework type. For both the Fill operations of the DataAdapter and the Get methods of the DataReader, the .NET Framework type is inferred from the value returned from the .NET Framework data provider.

Instead of relying on the inferred data type, you can use the typed accessor methods of the DataReader when you know the specific type of the value being returned. Typed accessor methods give you better performance by returning a value as a specific .NET Framework type, which eliminates the need for additional type conversion.

Note:
Null values for .NET Framework data provider data types are represented by DBNull.Value.

In This Section

SQL Server Data Type Mappings (ADO.NET)

Lists inferred data type mappings and data accessor methods for System.Data.SqlClient.

OLE DB Data Type Mappings (ADO.NET)

Lists inferred data type mappings and data accessor methods for System.Data.OleDb.

ODBC Data Type Mappings (ADO.NET)

Lists inferred data type mappings and data accessor methods for System.Data.Odbc.

Oracle Data Type Mappings

Lists inferred data type mappings and data accessor methods for System.Data.OracleClient.

Floating-Point Numbers (ADO.NET)

Describes issues that developers frequently encounter when working with floating-point numbers.

See Also

Concepts

Configuring Parameters and Parameter Data Types (ADO.NET)

Other Resources

SQL Server Data Types and ADO.NET

Retrieving Database Schema Information (ADO.NET)

Common Type System

Converting Types

Entity Data Model Tools

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

The Entity Data Model (EDM) is a model for defining data as sets of entities and relationships to which common language runtime (CLR) types and storage structures can be mapped. The EDM enables developers to program against a conceptual data model instead of directly against a storage schema.

There are three tools that are designed to help you graphically build applications with the EDM: the Entity Data Model Wizard, the ADO.NET Entity Data Model Designer (Entity Designer), and the Update Model Wizard. These tools work together to help you generate, edit, and update an EDM, as follows:

• The Entity Data Model Wizard allows you to generate an EDM from an existing database, add database connection information to the application, and generate C# or Visual Basic classes based on the conceptual model. When the Entity Data Model Wizard finishes generating an EDM, it launches the Entity Designer.
• The Entity Designer allows you to visually create and modify entities, associations, mappings, and inheritance relationships. You can also validate an EDM with the Entity Designer.
• The Update Model Wizard allows you to update an EDM when changes have been made to the underlying database. You must launch this tool from within the Entity Designer.

There is also a command-line tool designed to help you build applications with the EDM: the EdmGen.exe tool. This tool can generate an EDM, validate an existing model, produce source code files that contain object classes based on the conceptual model, and produce source code files that contain views generated by the model. For detailed information about this command-line tool, see EDM Generator (EdmGen.exe).