I have moved to...

http://ihafidh.wordpress.com
Wordpress seems to be much faster than Blogger. Plus they have more themes with better designs.

Viewing Query Plans

This is more of a reminder to myself. I can't always remember the commands to show the execution plan in text. It's nice having the graphical interface but when you have a big execution plan then seeing it in text is much easier. Here are the commands:

-- simple estimated execution plan

set showplan_text on

GO

--*** estimated execution plan with more detail. best to use this ****

set showplan_all on

GO

-- actual execution plan. runs the query. so it's better to use showplan_all for a big query 

set statistics profile on

GO

More detail here

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.