Adding comments to an XMLA script

So I was trying to run an XMLA script and kept getting errors. Below is the script. You will notice that I used the same commenting as in T-SQL “- -”. Highlighting the XMLA script and executing it worked. So my commenting was wrong. It should be <!- – enter comments here – ->. The same as any XML document. I know for anyone familiar with XMLA or XML would say “DUH!, that is obvious” but it took me some poking to get it to work. I am so used to using the two dashes “- -” which is what is used in both T-SQL and MDX.

- – XMLA script to backup an SSAS database
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
<File>C:\Backup\SSAS\Adventured Works DW 20080218.abf</File>
</Backup>

Here is the correct way of commenting:

<!- – XMLA script to backup an SSAS database – ->
<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
<File>C:\Backup\SSAS\Adventured Works DW 20080218.abf</File>
</Backup>

Executing a BAT file in SSIS

I wanted to execute a batch file in SQL Server Integration Services, and I wanted to utilize two parameters from the Execute Process Task in SSIS: StandardOutputVariable and SuccessValue.

The reasons for using these two parameters is that I wanted to know if the external batch file failed or not. And I wanted to capture any error messages produced.

In SSIS, it is straightforward. Drag the “Execute Process Task” and select your .bat file.

For the StandardOutputVariable create a new string variable. For the SuccessValue specify 0.

Now, the tricky part is getting the SuccessValue to work with %ErrorLevel% from the bat file. To do that, make sure that the last line in your bat file is:

EXIT %ERRORLEVEL%

This will return the error level to the Execute Process Task. Otherwise, the process will always seem to succeed.

The other tricky part is getting an actual error message produced from the bat file. Apparently there are separate output streams produced by the bat file. So you have to use some redirection operators. I found this out via a blogger CrankyBit. Microsoft’s reference can be found here. Here is an example:

C:\WINDOWS\system32\ping.exe www.google.com >> mybat_log.txt 2>&1

Notice the 2>&1 at the end. That will send out the error message that you would normally see when executing in a command prompt.

So I used that to capture the error logs.

Finally, I echoed out a custom error message to send to the output variable. I can then use this variable to send out an email for notification.

So here is the complete bat file:

@Echo OFF

Echo ================== >> mybat_log.txt

Echo Start: %Date% %Time%

Echo.

Echo Start: %Date% %Time% >> mybat_log.txt

rem ==================================

rem 2>&1 sends the actual error message

rem http://technet.microsoft.com/en-us/library/bb490982.aspx

rem ===================================

C:\WINDOWS\system32\ping.exe www.google.com >> mybat_log.txt 2>&1

Echo ErrorLevel = %ERRORLEVEL% >> mybat_log.txt

IF %ERRORLEVEL% NEQ 0 (ECHO An error has occurred on the mybat.bat file.

Echo Please check the log file for more detail at D:\mybat_log.txt

) ELSE (ECHO The mybat.bat file ran successfully)

Echo.

Echo End: %Date% %Time%

Echo End: %Date% %Time% >> mybat_log.txt

Echo.

Echo —

Echo Sent from SQL Server SSIS Package

EXIT %ERRORLEVEL%

I’m still here and some great resources

Well, after a long break from blogging I am back. We have a new addition to the family so that kept me away. Actually, that and just being lazy to blog really.
Anyway, I recently came across some useful resources on SQL 2005 and thought I would share:

JumpStartTV
Great intro videos for beginners wanting to learn SQL 2005, SSIS, SSAS, etc. They are free but you have to register.

TechNet Virtual Labs: SQL Server 2005
These labs include SSAS, Management Studio, SSIS, Data Mining, etc.

How to use rank function in SQL Server 2005
A very easy to follow query example on the ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE

Restore DB with Different Name – Limitation

Got this error trying to rename a SSAS 2005 database:
“Backup and restore errors: Restore under different DatabaseId/DatabaseName can not be done for a database with partitions with specific location.”

Apparently, there is a limitation with SSAS 2005 when you try to restore a database with a different name AND you have specified a different folder location for storage. Hopefully this limitation is removed in SQL2008.

I had specified a different location because I ran out of space on my default location.

YTD and Time Dimension

I kept getting the following error when trying to do YTD calculations using MDX on a client cube:

VALUE #Error Query By default, a year level was expected. No such level was found in the cube.
FORMATTED_VALUE #Error By default, a year level was expected. No such level was found in the cube.

Here is the MDX to do a simple YTD:
SUM(YTD([Time].currentmember), [Measures].[Sales])

The MDX is good and works in every other cube including Adventure Works so I had to look deeper. I started changing keys thinking that the client did not set those up properly. I should have looked at the error message more closely because it alluded to what the problem was. It says, “No such level was found in the cube”. Well, I know it’s there because I can see it in the dimension structure. However, looking at the dimension properties, the Year attribute was set to Regular as the Type. Changing the Type to “Years” solved the issue. So that makes sense, the MDX parser was looking for a Year level but could not find it.

Sometimes just figuring out what the error says helps a lot

Moving SSAS 2005 database to another drive

I just got asked how to move a SSAS 2005 database from one drive to another. The default data folder is the C: drive when you install SQL 2005. Usually, the data drive that system admins assign is the D: , F: , etc drive.
So how do you move a SSAS 2005 database? Well, apparently you can’t move individual databases, you have to move the entire data folder to another drive. You also have to change the server property DataDir to the new location.
The steps are outlined here.
It would have been nice to allow moving individual databases like we do in regular SQL with detach/attach or restore to a new location.

CTE in SQL 2005

Common Table Expressions in SQL 2005 rule! One of the best uses of CTE is to do recursive queries. The syntax for it is as follows:

WITH myCTE (id, name, desc) AS

(

select id, name, desc

from table

you can reference the CTE here

)

select * from myCTE

The concept is similar to using derived tables in the way that your query can reference the CTE or derived table. The difference is that in a derived table you can only reference it after the derived table query has run. In a CTE, you can reference it while executing your query. And this makes it easy to build recursive queries.

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

There really is no wizard or documented procedure from Microsoft regarding this issue. Also, there isn’t a simple way of backing up in SQL 2005 and restoring in SQL 2000. Actually, you can’t; because there is no backward compatibility between the two versions. You can backup in SQL 2000 and restore in SQL 2005 but not the other way around.

Is this a case of Microsoft forcing you to upgrade or are the two DB versions structurely too different and complex to have backward compatibility? You can decide for yourself.

In the meantime, someone posted the procedure here on a Microsoft forum.

Basically, there are 3 simple steps:
Step 1 – Generate Scripts for the Database Elements and Structures (excluding FK and triggers)
Step 2 – Move the data from 2005 to 2000 using the export wizard in 2005
Step 3 – Generate Scripts for the Database Foreign Keys and Triggers

This works fine if you have a small database on the same network. But if your database is bigger and you have to export across your network, then having a backup file to restore from would be a much easier task. Those steps would be:
1. Backup your database
2. Compress the db file
3. FTP to your target server
4. Restore on target server