Braulio_Malaga

I need to SUM by region hierarchy on a given measure, but use the average function when dealing with time entries

Braulio_Malaga — Tue, 15 May 2007 11:34:00 GMT

That's a common scenario and was a pain in the neck to get results using Analysis Services 2000.

In SSAS 2005 we have.... SEMI ADDITIVE MEASURES: by using this measures we use SUM for all the dimensions but not for the time dimensions (watch out you need to use proper time dimensions!), pretty cool.

Snippet from Projecr Real, technical overview:
(http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx)

The use of semi-additive measures, as exemplified by the inventory data. Semi-additive measures were not simple to implement in prior releases and did not always perform well. Now the system directly supports them, and their use in Project REAL illustrates this. (For readers not familiar with the term: Semi-additive measures do not sum up over time. Sales for the three months in a quarter can be added to get the total sales for the quarter; this is an additive measure. Inventories for the three months cannot be added to get the “total” inventory—it makes no sense. Inventories at a particular time at different locations can however be added to get a total. Hence, inventories are the classic example of a semi-additive measure.)

Links:

http://blogs.conchango.com/christianwade/archive/2005/04/07/Semi_2D00_Additive-Measures-in-Analysis-Services-2005.aspx

http://www.datadefractor.com/blog/

MSDB went nuts !!

Braulio_Malaga — Wed, 25 Apr 2007 17:53:00 GMT

It's the second time that I got an ugly message from my SQL Server:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space

That happens when the msdb database gets corrupted :-(, you can find instruction to restore this database:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

Watch out ! yo uare going to loose all your schedulings (jobs...), to get more info about what is the msdb database:

http://msdn2.microsoft.com/en-us/library/ms187112.aspx

Next time... do a backup :-(

... Well if none of this options works for your there is an emergency solution (specially if the crashed SQL Server is on a development machine).. stop the SQL Server Service, and overwrite the mdf and ldf files from a msdb db belonging to another machine (we setup a fresh copy again :-)), restart the service and everything will start working again. Not very clean, but works.

In my ASP .net application I need to generate a word document and send it to the client

Braulio_Malaga — Tue, 17 Apr 2007 09:12:00 GMT

Quite common request...

If you use Excel, you have defined methods to do this, the most elegant is to use XMLSS format, transform an XMLSS file to an XSL and combine it with a DataSet, pretty cool.

but on Ms Word... mmm.. if you only have to support Office 2007, then you are saved, it has a native XML format, but if you "poor soul" have to support older formats, you can play generating RTF files and stream them to the client (you can convert them to an XSL template and combine them with a DataSet as well), about streaming RTF, here you have an interesting link:

http://support.microsoft.com/kb/270906/

HTH

SQL Server Management studio - Connect To Server, remove entries from list

Braulio_Malaga — Thu, 12 Apr 2007 08:53:00 GMT

SQL Server management studio has a very good feature it remembers that last X server that you connected using this tool. That's superb... but... what happens if you want to remove your "fingerprints".

You can modify or remove a file called "mru.dat" that is located in the following path:

C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat.

More info about this:

http://stevenharman.net/blog/archive/2006/10/25/SQL_Server_2005_Management_Studio__Clear_Recent_Servers_List.aspx

GridView RowCommand Hidden Columns, Width 0... no more nasty tricks use DataKeys !!!

Braulio_Malaga — Sun, 01 Apr 2007 16:32:00 GMT

Sometimes when we have to migrate our knowledge from 1.1 to 2.0... we just try to make things as it was before, just because it worked out fine, or we don't had the time to go that inner details, this post belongs to that kind of stuff....

Are you still using hidden columns or width 0 to keep you internal ID's on your grid ? If you are doing it that way, don't do it anymore !!!, just use the 2.0 GridView DataKeys field, you will only have to add this declaration on you GridView (on DataKeyNames enter the name of your ID field in the DB/DataSource):

<asp:GridView ID="grdMyGrid" runat="server"

DataSourceID="MyDatasource"

(...) OnRowCommand="grdMyGrid_RowCommand"

datakeynames="Id"

And to access in the rowcommand:

protected void grdDotnetGrid_RowCommand(object sender, GridViewCommandEventArgs e)

{

int index = Convert.ToInt32(e.CommandArgument);

int idSelected = (int) grdMyGrid.DataKeys[index].Value;

}

Remember you can use composite keys as well !!

More info:

http://weblogs.asp.net/despos/archive/2005/06/30/416783.aspx

SSIS SQL Server Destination, WATCH OUT !!

Braulio_Malaga — Sat, 31 Mar 2007 19:15:00 GMT

Have you ever experience INSERT BULK errors and crashes on you SSIS process when running unattended ? Normally when you perform some research you realize that changing an SQL Server Destination by the slow good old chap OLE DB Destination, things start working again.

In the following article Jamie Thomson has taking the time to snif the commands generated by each destination using SQL Server profiler, it turns out, that you cannot use SQL Server Destination to write on a different server, and that the account running this process must have active a "Local Security Police" to create global objects in memory.

What does this mean ? Only use this SQL Server Destination, if you don't plan to distribute your system in several computers (if you do that in the future, you will have to modify your SSIS code, imagine that in two years time... GLUPS !!!).

Well, here comes the excellent link:

http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx

GridView.SortExpression ... beware my friend

Braulio_Malaga — Fri, 30 Mar 2007 13:05:00 GMT

Tweaking ASP .net 2.0 Gridview with custom paging and sorting to speed up our application, it's a good idea, but apart from the manual work that you have to do (exciting anyway to grasp full control :-)), take care with sort expression, you may be tempted to pass in this field the column of the SQL table that you want to use... is it a good idea?... NO !, two bullets here:

- If you work with an n-tier system, suddenly you are breaking up the isolation between the UI Later and the DataLayer by using physical column names of tables.

- A hacker could insert an injection attack you SQL, because finally you are just passing that string as a concat for the you dynamic SQL stored proc, the evil !!.

A better approach is to use logical names for the sorting, and map this in your datalayer, never trust user input. A very cool link about this issue:

http://www.dotnetjunkies.com/WebLog/joshuagough/archive/2006/08/29/145416.aspx

HTH

Passing Dynamic DataSet to ReportViewer or SSRS

Braulio_Malaga — Thu, 29 Mar 2007 09:52:00 GMT

Many people misses a push mode like Crystal Report Does, passing a DataSet at runtime to the report engine.

Well... the truth is... YOU CAN DO THIS IN SSRS !!!

About how to do this in a Windows Forms Application (ReportViewer):

http://www.codeproject.com/useritems/reportdisplay.asp

About a workaround to do this using SSRS (pass the dataset in a WebService and use it as a datasource, cool stuff):

Dynamic DataSet using XML Web Service DataSource

http://prologika.com/CS/blogs/blog/archive/2005/11/20/695.aspx

Other options

http://www.devx.com/dbzone/Article/31336/1954?pf=true

Quick check: Test that you can send e-mails using a given SMTP Server

Braulio_Malaga — Mon, 26 Feb 2007 18:44:00 GMT

In the last projects that I have been working I need to rely on an SMTP server with some "security policies"... sometimes it was a problems to check if sending e-mails was a problem of my application, or just that the SMTP server sucks...

I have found a cool link that let's you make a quick test (Send an e-mail using a telnet), so you can call the Admin telling that for sure their servcer is not working (EHLO !):

http://msexchangeteam.com/archive/2006/07/14/428324.aspx

HTH

Braulio

Crash Intro to Reporting Services

Braulio_Malaga — Tue, 06 Feb 2007 10:48:00 GMT

Here is a link to a lite and quick introduction to Reporting services:

http://www.simple-talk.com/content/print.aspx?article=268

Useful if you don't know what is Reporting Services and just want to see how it looks like (create an easy report and do some fancy formatting), Crystal Reports guys will like it.

SSAS Display member properties on SSRS Reports

Braulio_Malaga — Mon, 05 Feb 2007 16:48:00 GMT

Hello,

When you use Reporting Services Dataset designer,you can only go adding dimension members to a given report, that's a risk because adding fields there means that the cube will try to slice and dice by every single column added, What happens if you want to show a member property (an attribute that is only information, e.g. you have Project ID, Project Name would be that kind of attribute), if you try to drag and drop a member property, the designer won't let you do so :-(...

What to do? Here you have the solution:

There is a well known bug in SQL Server 2005, "912702", you need to install the Service Pack 1 of SQL Server 2005.
To install the service pack correctly stop the AS service (dono why but I had to make that in order to install the patch correctly).
In reporting, forget to use the nice design mode, switch to query mode, and where it is used the dimension that you want to get the attribute, add your attribute name (just after the DIMENSION PROPERTIES declaration), e.g.:

SELECT

NON EMPTY { [Measures].[MyMeasure] } ON COLUMNS,

NON EMPTY { ([MyDimension].[MyHierarchy].[MyLevel].ALLMEMBERS) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,[MyDimension].[MyHierarchy].[MyLevel].[MYMEMBERPROPERTY]
ON ROWS

(...)

4. Run the query (it should work fine), now go to the report and you have two options:

Directly drag and drop the member property into the reports (it's available as a dataset column).
Or just select the field (dimension, hierarchy level..) that should contain the attribute and insert the following text into the cell:.

=Fields!MyDataSetField.Properties("MYMEMBERPROPERTY")

Run the report,and everything should work fine !!!

HTH
Braulio

On thing to take into consideration MDX editor is not very stable, you will get from time to time a crash on Visual Studio :-(.

SSRS Create Subscription programmatically

Braulio_Malaga — Wed, 31 Jan 2007 14:55:00 GMT

Cool link about how to create an SSRS subscription via Web Service API

http://www.odetocode.com/Articles/114.aspx

SQL Server 2005 DB Diagrams: Database diagram support objects cannot be installed because this database does not have a valid owner

Braulio_Malaga — Mon, 15 Jan 2007 15:21:00 GMT

Sometimes when you try to create a database diagram on SQL Server 2005, you get a nasty message:

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects. "

Well, there are some magic statements to remove this error message:

Select your database in the Management console

EXEC sp_changedbowner 'sa'

EXEC sp_dbcmptlevel 'YOUR_DATABASE_NAME', '90';

In the forums they say that this happens when you try to use a SQL Server 2000 database in 2005, you have to change your compatibility level to SQL 90... well not sure if it's true, but at least it works :)

Advanced tips for Reporting Services 2005

Braulio_Malaga — Thu, 04 Jan 2007 17:52:00 GMT

Some solutions for tricky problems on Reporting Services:

http://blogs.msdn.com/ChrisHays/

Tips like:

How can I give my users the ability to dynamically select fields on which to group within a report?
How can I get print-quality output from my custom report item?
How can I enable users to select "All" as the value of a multivalue query parameter?
I have variable sized images I want to display in a table column. How can I center these images without stretching them to fill the table cell?
How can I get a green-bar effect (alternating colors) in a matrix?
Does Reporting Services support horizontal tables (fixed rows and dynamic columns)

SSIS How to convert a Recordset into a Dataset ready to use by .net custom script task

Braulio_Malaga — Wed, 20 Dec 2006 16:12:00 GMT

With SSIS is easy to get a recordset in a variable, then you can play with it.. assign it to a loop, iterate through all the rows... BUT if you need to use that recordset in a vb .net script task your nightmare just begins:

You cannot cast an Recordset into a Dataset, recordset, recordset is old VB6 COM stuff.
You cannot easily use the Recordset object in the vb script, you have to make special configurations (install a COM DLL, register it.. :-(), not good stuff.

Solution... take a dataset and fill it with the recordset data (object) using an oldDbAdapter !!

http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

And if you need to do it the other way around, Jack Pham pointed out a good link to convert from DataSet to ADO Recordset:

http://support.microsoft.com/kb/316337/en-us

HTH