BI projects in SQL Server Data Tools

SQL Server  Data Tools(SSDT) installs when you install SQL Server 2012 (even the SQL Server 2012 Express edition). SSDT has all the necessary project templates to start a Business Intelligence project. However it will not have the templates for other language projects such as C#, VB.NET etc.

SSDT allows you to create all the Business Intelligence related projects by providing templates for the projects. You can also create SQL Server projects as shown here:

ssisProj03
Here is an example of creating a SQL Server Integration Services project using the Visual Studio Shell launched by SSDT that gets installed with the SQL Server.

http://hodentekmsss.blogspot.com/2015/02/sql-server-data-toolsssdt-installs-when.html

More posts on SSDT here:
http://hodentek.blogspot.com/2013/10/ssdt-bi-templates-for-sql-server-2012.html

http://hodentek.blogspot.com/2014/07/new-update-to-sql-server-data-tools.html

http://hodentek.blogspot.com/2014/03/sql-server-data-tools-to-go-with-sql.html

These may just be the types of articles you are looking for

There are some 50 articles covering most areas of contemporary database/web related technologies.

SQL Server 2008, MySQL, Oracle, Analysis Services, Twitter, Microsoft Lightswitch, SSIS, SSRS, Linked Servers, Enterprise DB, SQL Anywhere 10, Data Transfer Access to SQL Server, LinqDataSource, XAML. XBAP, Data Migration, jquery in Dojo, PocketPC, and many more...

Review the following link: Articles on Packt
 

How do you add a database connection to Server Explorer in VS 2012?

Report authoring is perhaps one of the most frequently required activities in an enterprise. Knowledge of report authoring is an invaluable asset when one is looking for a job in an enterprise. Before you can write a report you will have to know where your data is and how to get connected to it from your authoring software. Report authors who are not very comfortable with using data on a back end server could use the more powerful and highly interactive Power View reports, but this requires a developer in your organization to create a model for you to work with. Once the model in place it is super easy to create stunning reports. This is only possible on SQL Server 2012 with the reporting services installed in SharePoint integrated mode.

The following is specific to SQL Server Reporting Services, but this is important for any authoring tool. In the following Visual Studio 2012 is presumed and a web project is to be created to demonstrate the use of Report Viewer Control.

Presently the Server Explorer has the following items:

 

If you do not see Server Explorer you click View in the main menu and click Server Explorer as shown:

Presently there is a connection established with the Adventure Works 2012 database on the SQL Server 2012 named Kailua.

Click Connect to Server item on Server Explorer tool bar shown.

The Add Server window is displayed as shown.

The name of the computer is provided (you provide the name of your computer). Click OK.

Adding a Data Connection

Click Connect to Database toolbar item as shown.

Or you can right click Data Connection and start as well.

Assume that we clicked Connect to Database.

The Add Connection window is displayed as shown.

This is because before getting to the database you must first get connected to the server. Notice that the Microsoft SQL Server (SqlClient) is the default. It can be changed to others by hitting the Change... ellipsis button and following the wizard.

For now accept the default. Click on the handle for Server Name after hitting the Refresh button. You see three servers, a SQL Server 2012 (Kailua); a SQL Server 2008 R2 (Maui) and a SharePoint Server.

Click HODENTEKWIN7\KAILUA to choose the server. The Add Connection window is updated as shown. We accept the default authentication, Windows Authentication.

Click on the handle for the Select or enter a name for the database field to display the drop-down list as shown.

Click Northwind. It could be another database in your case. Click the Test Connection button and verify that the connection was successful as shown.

Click OK on the above message window and click OK on the Add Connection window. The new database connection will be added to the Visual Studio 2012’s Server Explorer as shown.

That is all for now.

Mahalo

My new book uses images / screenshots such as the above to guide you through the learning process. The instructions are very simple and most of the steps are described.

http://www.packtpub.com/learning-sql-server-2008-reporting-services/book

http://www.packtpub.com/learning-sql-server-reporting-services-2012/book

Are you ready for SQL Server 2008 SP2?

The Service Pack 2 is ready for downloading from this site here:
To download the SQL Server 2008 SP2 release please select from the links below:
 •SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550
 •SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551
 •SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815
Note


 This is a service pack for the 2008 server and not the 2008 R2.

I have only Express editions and this executable (SQLServer2008SP2-KB2285068-x86-ENU.exe, 284MB ) from the above link applied the service pack to the two SQL Express servers on my notebook computer (Aspire 4810TZ-4011) with Windows 7 Ultimate.

I ran Select @@version on each of them and got these responses:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)   Sep 16 2010 20:09:22   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600: )
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)   Sep 16 2010 20:09:22   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition on Windows NT 6.1 <X86> (Build 7600: )[I call this the Vanilla]
The first of these has Reporting Services server.

So what does this service pack accomplish?
* Reporting Services in Sharepoint Integrated Mode. It also helped SQL Server 2008 R2 to integrate with Sharepoint 2007 Products.
* SQL Server 2008 R2 Application and Multiserver Management Compatibility with SQL Server 2008
    *SQL Server 2008 Instance Management. You can enroll SQL Server 2008 with SQL Server 2008 R2 Utility Control Point as a managed instance.
    *Data-Tier Application(DAC) support. With the SP2 applied I can do all the DAC Operations just like in SQL Server 2008 R2. However I am using the      SSMS tool that was installed with SQL Server 2008 R2.
     Here is an example of the DACs that you can do with SQLExpress (vanilla).

You want to jump start on DAC, the coolest feature of SQL Server, then make sure you read these articles:
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html
or
here
http://hodentek.blogspot.com/2010/02/working-with-data-gotten-lot-easier.html

Have some fun!

Moving data from MS Access 2003 to SQL Server 2008

The Export and Import Wizard is an extremely useful tool for transferring data. In fact it is the simplest tool to copy over data from one database to another and to create data transfer packages that can be persisted. Data can be transferred between SQL Servers (between versions of SQL Servers for example) as well as between even two non-Microsoft databases. Another good reason for such a transfer (in the case of MS Access) could be to leverage the better security on the SQL Server while retaining the front end intact. Both Microsoft and proprietary data source providers are available to connect to many different database products.



This article is brought to you from Packt Publishing, the publisher of books on software



Review the following article and see how data is moved from MS Access 2003 to SQL Server 2008:
Transferring Data from MS Access 2003 to SQL Server 2008

MS Access Linked Server, comparing providers

In this article on SSWUG.org web site, the MSDASQL OLE DB ODBC driver is compared with Microsoft Jet 4.0 as related to creating a linked server on SQL Server 2008. Details of creating a linked server as well as accessing data using queries are also discussed.

Microsoft Access 2003 Linked Server on SQL Server 2008 using the JET and MSDASQL Providers

This is a subscription site and you can join as a guest and read the article. If you like what you have read you can become a member.

Here are my recent articles on EnterpriseDB

I find it interesting to write articles on EnterpriseDB. Here are a couple of the recent ones.


Transferring a table in a MS Access 2003 database to PostGres using SQL Server Integration Services
This is on a site to which you should subscribe. But you can sign up as a guest to read this article. SSIS 2008 is used to make the transfer

Creating a linked Postgres Server on SQL Server 2008
SQL Server 2008 provides a convenient GUI to make this possible. You will be using the ODBC provider from EnterpriseDB

Creating a VB.NET application with EnterpriseDB
Visual Studio 2008 is a great IDE for developing a desktop application such as this one.


Migrating from MS SQL Server 2008 to EnterpriseDB
Migration Studio from EnterpriseDB makes this a piece of cake.

Installation and basic features of EnterpriseDB
Install this product and take it to a test drive. You will like it

More to come in the coming months.

Learn to work with LINQ using the Linq Data Source Control

Linq to SQL is an object relational model that you can leverage in Visual Studio 2008. Your connection to SQL Server 2008 database is through a new control in Visual Studio 2008, the LinqDataSource control. This control needs a data context. The data context is provided by the Linq to SQL classes, a class generator that maps SQL server objects to the model. The class files generated support CRUD operations.

In this article a LinqDataSource control is the starting object for displaying data from an instance of a resident SQL Server 2008.

Read on and get a head start....

Displaying SQL Server Data using a Linq Data Source 

Chapter 1 hands-on exercises in "Learning SQL Server 2008 Reporting Services"

The step-by-step hands-on in this chapter is really to perusade and guide the user to install appropriate software after evaluating the resources he/she has on the user computer. This is going to be a one-man/woman show. One user with one computer doing all the stuff.

The hands-on exercises are:

* Installing a named instance of SQL Server 2008

* Reviewing the installation

* Installing a test database

* Configuring the Reporting Services

There is a lot of information and a lot of screen shots to make it an effortless experience.

Have fun.

This book may be purchased from here :

Migrating from SQL Server 2008 to Postgres on EnterpriseDB

With many database vendor products in the market and data intensive applications using them, it is often required to port the application to use the data or, migrate the data so that the application can use it. Migration of data is therefore one of the realities of the IT Industry. Some of the author's previous articles on migration can be found at this link.

Read the full story here .

Data Access with ADO.NET Data Services

ADO.NET Data Services leverages the ADO.NET Entity Framework to build data services for relational data sources. These include support for MySQL, DB2, and Oracle in addition to MS SQL Server. It can also be used with non-relational data using an add-on provider model. With ADO.NET Data Services, the developers can build dynamic internet applications (AJAX, Silverlight, etc) that works across intranets as well as the internet. With this in place data access by applications is made possible using URI Syntax and using HTTP verbs operating on the data.

In the following article I describe the details of creating such a service using the familiar Northwind database.

Data Access with ADO.NET Data Services

Report Manager on the Current Browsers

The latest version of Reporting Services that gets installed when you
install SQL Server 2008 Enterprise Edition (not the RTM) has a slightly different
looking interface. This interface's look is slightly different in different
browsers.

As you can see they are different and surely Google Chrome has the wierdest look.

Firefox 3.0 and Safari

 

Google Chrome

 

IE 8.0 RC1

On establishing Primary Key / Foreign Key in SQL Server 2008

When you copy over tables to SQL Server you not necessarily copy all the 'Keys' although you have all the data. Now you want to recreate the relationships. How do you do it? Of course one could use scripts to do it. Here is how you can do it in the SQL Server 2008 Management Studio.

There are two tables Suppliers and Products.
To start with both tables have no Primary Keys
Products has SupplierID as one of its column which happens to be also in the Suppliers table.

Step 1:

You should get both the tables into the Database Diagram Pane.

Expand the database node in the SQL Server 200 Management Studio. Right click the Database Diagram folder and choose to create a New Database Diagram. The Database Diagram Pane opens up and a Add Table Window listing all the tables in the database opens. You can pick and choose tables to add and they appear in the diagram pane.
------------------------------------------------------------------------------------------------------------------------------------

Step 2:

Set up Primary Keys for both tables
Right click Products Table in the Database Diagram of SQL Server 2008
From the drop-down menu choose Set Primary Key

















Similarly set the Primary Key for the Suppliers table.
-------------------------------------------------------------------------------------------------------------------------------------

Step 3:

Establish the Primary Key / Foreign Key relationship

Click on SupplierID on its left and hold-down the mouse and click on Supplier ID in Products table shown schematically in









-----------------------------------------------------------------------
In the Tables and Columns window that pops-up verify that SupplierID in Suppliers table has a related item SupplierID in the Products table.

----------------------------------------------------------------------------
Click OK. The Foreign Key Relationship window pops-up as shown















--------------------------------------------------------------------------------------------------
Expand INSERT And UPDATE specific item.
Click on an empty space in Delete Rule and Update Rule text boxes in the expanded items and set them for Cascade.

Click OK and try to save your work from the File menu. You will get a error message if you already have data in your tables.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Step 4:

Enable saving of changes

In the Main menu click Options. In the window that pops-up deselect
"Prevent saving changes that require table recreation"
Click OK
Now save the Database diagram and it will be saved with a name that you provide

Read about Referential Integrity in: http://en.wikipedia.org/wiki/Referential_integrity

My problems with the Reporting Services in Visual Studio 2008

I have used Reporting Services with VS 2003 and VS 2005 and I am beginning to work with VS 2008. I have come across a strange problem. I used the Reporting Services Configuration utility that comes with the default installation of SQL Server 2008 November CTP. The Configuration gets completed without a hitch and I can see the Report Server databases in my Server but when I try to access the Web URL or even the Object Browser in the Management Studio I get an exception. I am trying to find out the reasons for this.



What I have found so far is that while the Reporting Services Configuration provides me with a default web URL [that I can happily click on] it does not write it to the configuration file, the RSReportServer.config. Could this be the reason?

Effortless migration of data from SQL Server 2005 to SQL Server 2008

It's so easy to transfer a database from SQL Server 2005 to SQL Server 2008. Well the same holds good from SQL Server 2000 to SQL Server 2008. Read on...

Moving database from SQL Server 2005 to SQL Server 2008 in three steps

SQL Server 2005 can be migrated to SQL Server 2008 quite easily by manually detaching the database from SQL Server 2005 and attaching it to SQL Server 2008. This is a much easier process than the 'Copy Database Wizard' which has two procedures one of which is the same as the manual process but carried out under the watchful guidance of a wizard and the other which does not require the stopping of the Source server.