speakTECH - BI

SQL Server 2008... free shot at certification! by Gus Collazo

2008-08-16T03:41:00Z

Hi everyone, with SQL Server 2008's launch just around the corner, Microsoft has announced 2 new certification exams:

Beta Exam 71-452: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008
Beta Exam 71-450: PRO: Microsoft SQL Server 2008, Designing, Optimizing, and Maintaining a Database Administrative Solution

The exams are currently in beta so Microsoft is offering them for free at the moment (while seats are available). Just visit http://www.register.prometric.com/ClientInformation.asp and sign up for one of these above tests. On the final screen where you are prompted to pay for the test, simply enter one of the promo codes below to take the test for free:

Beta Exam 71-452: Promo Code is 3568C
Beta Exam 71-450: Promo Code is 239F4

To get ready for these exams, read the study guides below

http://www.microsoft.com/learning/exams/70-450.mspx
http://www.microsoft.com/learning/exams/70-452.mspx

If you need some motivation and want to find out why you should get certified (besides the fact that it's free!), read this:

http://www.microsoft.com/learning/mcpexams/faq/benefits.mspx

Finally, you might be asking yourself "how the heck am I supposed to take a certification test for SQL Server 2008 when I've never even used SQL Server 2008?". Well, you're covered there too. Read my earlier blog on signing up for a free account that allows you to access a virtual machine with SQL Server 2008 installed:

http://blogs.officezealot.com/speaktech-bi/archive/2008/04/15/try-out-sql-server-2008-the-easy-way-by-gus-collazo.aspx

Happy testing everyone! :)

SQL Server Data Services, Part Uno! by Gus Collazo

2008-07-19T01:40:00Z

SQL Server Data Services is Microsoft's exciting addition to their Software Plus Services strategy! It's an on-demand data storage and retrieval service that will offer customers data access from anywhere on the Web. A few good points about this new service (as of the time of this writing):

Microsoft has placed no limits on the amount of data that can be stored!
It will support the XML standards-based Representation State Transfer (REST) and Simple Object Access Protocol (SOAP) interfaces
Microsoft will provide VB.Net and C# client libraries to access the service.
Microsoft will support Language-Integrated Query (LINQ)-based data access to the service.
Microsoft will store the data on their own servers and will provide geo-redundant data for disaster recovery. You'll always have access to your data!
All data transfer between the client and the service will be secured via Secure Sockets Layer (SSL)

The above is just a quick summary I gleaned from http://www.sqlmag.com/ (98881), but you can learn more by visiting http://www.microsoft.com/sql/dataservices/default.mspx as well. The service is in beta now, but you can signup now for it and start exploring the service!

Stay tuned for more updates as I dive into this latest offering from Microsoft.

Microsoft's Common Solutions for T-SQL Problems by Gus Collazo

2008-07-05T03:05:00Z

Hey everyone, Happy 4th fo July! I ran across this nice article by Microsoft with detailed solutions to some of the more common T-SQL problems you may encounter. Check it out!

http://code.msdn.microsoft.com/SQLExamples

A simpler alternative to backing up data in SQL Server by Gus Collazo

2008-06-24T03:27:00Z

There are times when creating a database backup is not convenient. For example, you may be connected remotely to a SQL Server and not have access to the backup files that may be created. For times like these, I love the SSMS Tools Pack. You can find this handy gem of a SQL Server addon at http://www.ssmstoolspack.com/Download.aspx.

Once you download and install the addon, run SQL Server Management Studio and right-click on the database you'd like to backup. Notice the "SSMS Tools\Generate Insert Statements" command in the popup menu. This handy command will create a script file with INSERT statements for every tables in your database. You can then copy and paste these commands to another SQL Server to recreate the data there or just keep the script tucked away as a handy script-based backup of your data.

Check it out! The tools pack also has many other features for you to explore such as a query history and text regions for your SQL code, but I find the Generate Insert Statements my favorite tool in this addon by far.

--Gus

Fun with Reports and Maps! by Gus Collazo

2008-05-06T07:45:00Z

In this blog, let's take a quick look at how to use a map to display nationwide sales data on a SQL Server report. You can jump quickly to the bottom of this blog entry to see how the report will finally turn out. To try out the steps below, you first need a server running SQL Server 2005 with the AdventureWorks database. You also need SQL Server Reporting Services configured and you'll need Visual Studio 2005 or 2008 on your machine.

Let's first start out by installing the Dundas Map for Reporting Services control on your machine. You do this by visiting http://www.dundas.com/Downloads/index.aspx and clicking the "Download Now" button next to "Dundas Map for Reporting Services". Follow the instructions on the Dundas website to install the control.

Once the control is installed, fire up Visual Studio. Create a new "Report Server" project.

Next, right-click on the Reports folder in the Solution Explorer and add a new report. Click through the wizard to setup a data source that points to your AdventureWorks database.

In the "Design the Query" step of the wizard, paste this SQL code in. The query will retrieve the total sales for each state.

SELECT z.[Name], CONVERT(INT, SUM(z.TotalDue)) AS StateTotal
FROM (
    SELECT DISTINCT s.[Name], o.TotalDue
    FROM Person.StateProvince s INNER JOIN Person.Address p ON s.StateProvinceID = p.StateProvinceID
    INNER JOIN Sales.SalesOrderHeader o ON o.BillToAddressID = p.AddressID
    WHERE s.CountryRegionCode = 'US'

UNION

    SELECT DISTINCT s.[Name], 0 AS TotalDue
    FROM Person.StateProvince s
    WHERE s.CountryRegionCode = 'US'
) z
GROUP BY z.[Name]

Click the Next button from this screen, then click Next/Finish to end the wizard. You can accept the defaults of the wizard after the "Design the Query" step. Once the wizard is done, select everything on the report page and delete it. We will be using the map control and do not need the controls the wizard created.

Next, we'll need to add the Dundas Maps control to the toolbox if its not already there. Right-click the Toolbox window, and select the "Choose Items..." menu item. In the ".NET Framework Components" tab click on the "Browse..." button and browse to the installed Maps assembly (DundasRSMapDesigner.dll). After selecting the chart assembly make sure the "Dundas Map for Reporting Services" item is checked.

Now drag the map control to the report layout. This will start the Dundas Map wizard. On step 1, change the Gallery dropdown to "North America", click "USA", then click Next.

Next, select your data source for the map's data set. Add the "Name" and "StateTotal" fields to the "Displayed fields" list. Also, click "Row Groups" to add the "Name" field to the row groups list. Click Next.

Finally on the wizard, change the Field to StateTotal and Text to #STATETOTAL. You can also tweak the color settings to your liking.

We're not done yet. The wizard gets us started, but we still need to tweak the properties of the report a little. Right-click the map itself and select "Properties". On the General tab, change the settings to reflect the screen shot below.

On the Data Appearance tab, change the settings to reflect the screen shot below. In particular, set the Legend Text to "#FROMVALUE{N0} - #TOVALUE{N0}". Also, set the "Show In Color Swatch" setting.

Finally, in the Shape Appearance tab, you can configure the color settings as below.

That's about it. You should have a report like the one below. To test your report, click the Preview pane. In a later blog entry, we'll explore adding drilldown functionality to the map report so users can click on the map itself to load a map with more details for the state selected. Stay tuned!