Convert MSSQL to MYSQL

For the sake of speed I developed a lot of ETL procedures in MSSQL 2008 and now have to convert them to MYSQL.

Some notes writing TSQL so that conversion is as painless as possible:

• table aliasses are case sensitive
• comments ("--") only work if there is a space after the dashes
• Use Cast() instead of Convert(). MYSQL does have a Convert function, but the syntax is different. Also, the names of the data types you can convert to are different

When converting your code:

• Datepart() does not work so change to Date_Format() instead. link
• Dateadd() does not work so change to Date_Add() or Interval instead. link
• All statements should be ended with a ";"
• DateDiff is available in MYSQL but has different parameters. link
• Cast() works in MYSQL but the type names differ. link

Be sure to run a find replace on the following:

• "dbo." replaced with "MYSQLSchemaName."
• ISNULL replaced with IFNULL
• "[" replaced with "`" 
• and "]" replaced with "`"

Bulk Excel Conversion from and to XLSM, XLS, XLSX

I was working on a project where I needed to import 32 XLSM files into a database using SSIS.
Unfortunately SSIS does not support XLSM files. So instead of manually saving all the files as XLSX,
I found this nifty utility.

Thanks goes to David Miller on Experts Exchange.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_8269-Convert-between-Excel-file-formats-XLS-XLSX-XLSM-with-without-macro-option.html

Validating South African ID

I ran into two projects now where the business users required me to validate a South African, 13 digit ID number. Every time it was one big search, trying to figure this out.

Here is a link that gives everything you need to know:
http://geekswithblogs.net/willemf/archive/2005/10/30/58561.aspx

Report Datadriven Subscription with multiple values

The issue I ran into here was that I had created a report running from a cube. I then setup the data driven subscription to read a parameter from a table, generate the reports, and store them in Excel format. My problem was that I had another parameter which had to have multiple values.

This is all good if you can hardcode the multiple values when you create the subscription, but what if the values had to change? As was the case.

Setting parameterized defaults on the report did not work... Don't know why though. When running the report manually it seemed fine, but running with a subscription caused the report to process indefinitely.

The Solution:
You will need to setup a scheduled storedproc or SSIS for this one... Probably at the same time you want your data driven reports to run.
Calculate the proper values for the multi value parameter, then store them in the "Subscriptions" table of the "ReportServer" database. This table holds the definitions for all the subscriptions created.
The values for the parameters will go in the "Parameters" column. It is in xml format so it will look like this:

<ParameterValues>
<ParameterValue>
<Name>Param1</Name>
<Field>Param1Field<Field>
</ParameterValue>
<ParameterValue>
<Name>Param2</Name>
<Value>Param2MultiValue1</Value>
</ParameterValue>
<ParameterValue>
<Name>Param2</Name>
<Value>Param2MultiValue2</Value>
</ParameterValue>
</ParameterValues>

Now just update the table in SSIS, and run the scheduled task (which can be found in SQL Agent) using the "Execute SQL Server Agent Job" task

‘Njoy
Tiaan Otto

SSRS - Report Subscription Error

We got an error when trying to create any type of report subscription.

Error Given:
The EXECUTE permission was denied on the object 'sp_verify_job_identifiers', database 'msdb', schema 'dbo'. The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

Part of the solution can be found at:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1520081&SiteID=1

This only states that you should redo the databse connection. We tried that and it did not work. What you should do is: Go into your Report Services Config Manager, goto the set the Credential Type to "Windows Credentials" and then use sqlservice as the "Account Name"

Hope this saves some time