Author: Christian Cote

Reserve your September 25th for SQL BI Saturday!

I am proud to announce that we are doing a full day presentation on SQL Server BI suite. The following topics will be covered during the event:

  • Multidimensional data modeling from an OLTP data model: AdventureWorksLT
  • Solution architecture and SQL Server Integration Services framework
  • Use SQL Server Integration Services to load data in the multidimensional model
  • Create cube with SQL Server Analysis Services to do ad-hoc analysis and create reports
  • Use SQL Server Reporting Services to create reports on multidimensional model
  • Use PowerPivot and Excel 2010 new BI features

Presentations will be a mixture of english and french. French presentations in the morning and english presentations in the afternoon. Attendees questions will be answered in both language.

You can register for the event at http://samedidotnet092010.eventbrite.com/

 

Hope to see you there!

Christian

0

Role membership with VS 2010 Database development tools

One question that arises when it comes to database deployment is how do we deal with role memberships. The sample answer is; create a file that ends with .RoleMembership.Sql and VS2010 will automatically use it’s content.

The easiest way to do it is to create the role in the database using sp_addrolemember and retrofit the added roles in the database project by using a schema compare feature. Simply specify database as source and db project as target and use the “write update” button to have the file created at project level.

This is not ideal since roles can differ from one server to another. In a future post,I’ll describe some solutions or ideas on how to handle this situation.

Christian

0

My Christmas gift will arrive mid-October!

EXO

I pre-ordered an EXOPC slate yesterday. It will ship around October 15th. I can't wait to have it. I preferred this slate over the IPad for several reasons but the major one is because this is a real computer. And, since it runs Windows 7, I can install any program I want on it. Tests with prototypes seem pretty good from what I seen on YouTube and based on feedback on their forum, ordering this slate was a no brainer.

Now, I just have to wait…the toughest part of the pre-ordering process J.

4

Visual Studio Database Development Tools guide

The visual studio team published a Visual Studio Database Guide on CodePlex.

(From Codeplex page)

“Practical guidance for Visual Studio 2010 Database projects, which is focused on 5 areas:

  • Solution and Project Management
  • Source Code Control and Configuration Management
  • Integrating External Changes with the Project System
  • Build and Deployment Automation with Visual Studio Database Projects
  • Database Testing and Deployment Verification”

I encourage anyone who wants to learn or have deeper knowledge on to download and read this document. It is very well done and contains all you need to know (and even more) on VS2010 Database Development tools.

Christian

0

Script.PostDeployment glitch – error code 17

I faced a small problem when I tried to deploy one of my database projects yesterday. The script.PostDepooyment was not executed at the end of my database deployment script. This script is very useful for me since I use it to fill some tables with idempotent static data. Static data for me is unknown dimensions members that have their surrogate keys smaller than 0 (-1, -2, etc.). By idempotent I mean that my insert scripts do not blindly try to insert data into tables, they check whether the row exists before inserting it:

So, back to Script.PostDeployment problem. I noticed that there is a property that can be set to every SQL scripts: BuildAction.
For my script, it was set at "Not in build", which means no execited:

By default, its property is set to "PostDeploy":

So what happened is guess was that "Someone" (read "ME") changed this property value by mistake.
Settting back this property to "PsotDeploy", my Script.Postdeployment was properly executed!

Christian

0

Installing Oracle XE on Windows 7 64 bits

Today, I want to share my experience about installing and accessing an Oracle Express (XE) 10G R2 database. The installer is pretty simple and everything seems to be working as soon as the installation finishes. From the database home page that opens at the end of the installation process, I have been able to unlock "HR" account and see its database objects.

Since one of my goals of installing Oracle XE is to use it with Visual Studio, I started V2010. I then created an Oracle database project and try to connect to my local XE instance. I then questioned myself: "What is the name of my Oracle database instance?" Simple enough, I opened the TNSNAME.ORA located in my "C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN" folder:

From the first line, I can see that the instance name is "XE" and my server name "Christian-PC". So, I tried to connect to my instance using "Christian-PC/XE" (without the double quotes). I got the error "ORA-12154: TNS:could not resolve the connect identifier specified". I was a bit puzzled by this, so I went to command window and started the old SQLPLUS and I was able to connect to Oracle.

So, what was the problem with VS2010? I tried VS2008 with same results. I googled for VS and Oracle XE connectivity. So I thought that I needed to install Oracle client. But, as per Oracle web site, th client is not needed if I use the local server on my local machine .It seems that people using Windows 32 bits did not have any problem connecting to Oracle from VS without client installed.

Finally found out that I needed to install ODP.net on my machine in order to have it working with VS on a 64 bits machine. So, I downloaded and installed it with all default options and:

Now, I'll be able to test Toad Extensions for Visual Studio! I send other post(s) over the week on it.

Christian

2

Manage Oracle Database with Visual Studio Database Development Tools

Oracle databases can now be integrated with your visual studio solution. Quest Software made a product: TOAD Extension for Visual Studio that enables Visual Studio 2010 Database Development Tools (aka DBPro, VSTS4DBP or datadude) to create database projects based on Oracle database. There is a freeware version of the extension which has limited functionalities compared to the commercial version, but still, you are able to create db projects with it.

I tested this extension and it works great. I’ll surely blog over the next few weeks about my “user experience” with this product.

 

Christian

0

Ottawa Code Camp presentation

I just finished my presentation at Ottawa Code Camp on VS2010 Database development tools. There was about 20-25 attendees there and I had to answer some interesting questions. I have a lot of admiration for the attendees that invest a whole Saturday on a weekend to learn something new that might help them further long in their career.

Christian

0

Presenting at Ottawa Code Camp on VS2010 ALM

I will have the pleasure to present at Ottawa Code Camp Saturday April 17th. I will talk about Visual Studio 2010 ALM Database Development Tools (Application Lifecycle Management; formerly known as Team System for Database Professional, Datadude, DBPro, etc. J). I will demonstrate DBpro’s new features, version management of databases as well as its new capability of managing Oracle database thanks to Quest Software Oracle DSP.

Hope to see you there,

Christian

0

Blog at WordPress.com.

Up ↑