Skip to main content

Latest Articles: SQL Server


SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects
Entry Date: Oct 2009 site: SQL Authority Rating:
keywords:
Article Description:
Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.
Comments:

Ecellent tip if your server policies does not allow to upload a backup, you can just generate the whole DB script, including even data !!!

Click Here To Navigate to the Article

Analysis Services 2008 $SYSTEM.MDSCHEMA_% DMVs
Entry Date: Sep 2009 site: SSAS Info Rating:
keywords:SSAS; MDX;
Article Description:

In Analysis Services 2008 there are 13 MDSCHEMA Dynamic Management Views (DMVs).

These DMVs describe cubes, dimensions, functions, measures, etc in Analysis Services database.Here is a diagram that I got after exporting structure of above DMVs into SQL Server database.

Comments:

One of the tasks that we developers hate to do is... generation documentation :-)

SSAS provides you with DMV, you can create a linked server and via openqueries can extract the info that you need (you can use that queries on SSRS to get a nice layout)

In this post you will get a tables diagram that will tell you which info can you extract from that DMV.

Click Here To Navigate to the Article

Using SSRS to report SSAS 2008 database structure using DMVs
Entry Date: Jul 2009 site: SSAS Info Rating:
keywords:SSAS;
Article Description:
New SSAS 2008 DMVs allows you to easily access Microsoft SQL Server Analysis Services (SSAS) metadata – information about cubes, dimensions, measure groups, measures, etc. As with DMVs metadata information is returned in the data set format, it is very easy to build Reporting Services reports to generate documentation about your database.
Comments:

Quite useful, autogenerate cube documentation, dimensiones, hierarchies...

Click Here To Navigate to the Article

SSIS Compress File Task
Entry Date: Jul 2009 site: The Joy Of Code Rating:
keywords:SSIS; Performance;
Article Description:
It was only a matter of time following my post the other day about System.IO.Compression, before I put this into an SSIS custom task. Well, here it is.
Comments:

Excellent post... SSIS 2005 is getting old, it has some inherent limitations... no SFTP, no good tasks to consume XML, no native task to zip files !

In my case, we perform an extract and dump it into a csv file... that csv file extract it's huge, what about zipping it before transfer it over the network? (the ratio is hundred of Mb vs less than 10 Mb).

Checking in SSIS for that, we found that we can use Winzip or similar at command line level (try to convince the IT Admin to install such tool on their machines), use some commercial SSIS custom task (not expensive, about 500 $), or code it your self, using some of the open source libraries available (cowboy developer, let's reinvent the wheel :) ).

Hopefully, the folks from the Joy of Code have posted this excellent task for SSIS... zipping files task for free, excellent work !!!

Click Here To Navigate to the Article

SQL Server 2005 Report Builder and Basic Authorization
Entry Date: Jul 2009 site: Blogs MSDN Rating:
keywords:SSRS; Security;
Article Description:
I have a customer who needs to use Basic Auth with SQL Reporting Services 2005, so I wanted to make sure they could get at Report Builder too.
Comments:

Have your clients ever complained about an error when trying to execute Report Builder? Something like:

Cannot retrieve application. Authentication error.

Why this happens? This happens when the account that the user is using to login is different from the one that he uses to enter in SSRS. It's a bit crappy but click once just use the current identity of the logged user, not the one provided in SSRS to login. Solution ? Enable anonymous access to the folder where Report Builder binaries are placed.

More info:

ClickOnce always runs as a separate process on the client computer. The process identity is the default Windows user credentials. ClickOnce does not share session data with Internet Explorer or obtain the current user security context from Internet Explorer.

ClickOnce sends requests that specify Windows integrated security in the authentication header. If a server is configured for a different authentication type, the server will fail requests from ClickOnce with an authentication error. To work around this issue, you must either configure a server for Windows integrated security or you must enable Anonymous access to eliminate the authentication check.

Click Here To Navigate to the Article

How to identify your SQL Server version and edition
Entry Date: Jun 2009 site: Microsoft Rating:
keywords:SSAS; SSIS; SSRS;
Article Description:
This article describes how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to identify the specific edition of SQL Server you are using.
Comments:

Determine which version or which service pack is installed on a given SQL Server instance is not something straight forward.

In this doc you will find how to check which version of SQL Server is installed just be execution a single SELECT statement.

Click Here To Navigate to the Article

Installing OfficeWriter on the Server
Entry Date: Jun 2009 site: Office Writer Rating:
keywords:SSRS;
Article Description:
To install OfficeWriter the first time, run the automatic installation. Once InstallShield has unpacked the dll files that contain OfficeWriter, you can re-install OfficeWriter manually (for example, if you are moving OfficeWriter to a different server).
Comments:

SSRS OfficeWriter it's quite a powerful plugin... only a "but"... sometimes it doesn't install straight forward, here you will find manual steps to install it (just in case the wizard install fails).

Click Here To Navigate to the Article

List of known issues that may occur when you use SQL Server 2005 Analysis Services cube-based report models in SQL Server 2005 Report Builder
Entry Date: Jun 2009 site: Microsoft Rating:
keywords:SSAS; MDX;
Article Description:
This article lists known issues that may occur when you use Microsoft SQL Server 2005 Analysis Services cube-based report models in SQL Server 2005 Report Builder.
Comments:

Report builder is not the best tool for cube navigation / browsing, rather evaluate PPS Analytics, Excel Services... or at least upgrade to 2008 version.

Here you will find some limitations of the product applied to SSAS.

Click Here To Navigate to the Article

The HAVING clause
Entry Date: Jun 2009 site: CWebbi spaces Rating:
keywords:SSAS; Performance; MDX;
Article Description:

The fact that there's now a HAVING clause at all in AS2005 MDX doesn't seem to be publicly documented anywhere, as far as I know; it's one of those hidden features we found out about while researching 'MDX Solutions'. While George has a full write-up on how it behaves in the book (coming soon to a store near you) here's a quick overview of what it does...

Basically, it allows you to filter the contents of an axis without having to use the FILTER function.

Comments:

SSAS MDX sometimes seems to be like black magic, or too hard for a SQL Developer :-).

Interesting thing to take a look: HAVING clasule on an MDX statement.

Click Here To Navigate to the Article

Dynamic MDX in Reporting Services
Entry Date: May 2009 site: Adam J Cooper Rating:
keywords:SSAS; Performance; MDX;
Article Description:
This post explains how you can build a dynamic MDX query in Reportins Services, customised to the users requirements. This can often bring some quite major performance benefits.
Comments:

Defining dynamic MDX queries on SSAS is complex but can bring you in some cases good performance benefits (watch out, don't use this approach for every query).

Click Here To Navigate to the Article

Analysis Services 2005 Aggregation Design Strategy
Entry Date: May 2009 site: SQL Cat Rating:
keywords:SSAS; MDX;
Article Description:
Designing effective aggregations are a well known way to improve query performance (for the sake of AS processing time). As you may have noticed in Tip #2 of the of the Analysis Services Query Performance Top 10 Best Practices whitepaper, the best way to define effective aggregations is to use the Aggregation Manager sample application. Here we discuss one of the strategies for adding effective aggregations using this tool.
Comments:

How to create effective aggregations based on logging MDX queries.

Click Here To Navigate to the Article

Page (1) Of 10 Next Articles >>