Microsoft Office Accounting Professional 2007

Microsoft Office wowed the audience through its coolness in graphics and features but what really surprised me is the coming of MS Office Accounting in it.

I've seen the demo and the product really is astounding since it is housed in MS Office, thus integration with the existing Office applications (like Outlook for contacts) is expected and welcomed. The product has also engaged with e-Bay, PayPal and Equifax to accomplish other transactions aside from online payroll and banking. You could even send your books online to your accountant through the application itself and your accountant can update and send it back to you promptly and conveniently.

This might be the start of the end of new projects for us software developers on customized accounting systems. (Good grief!) Of course, legacy systems die hard yet new businesses might (and will) bite this. Let's see how it sells on the corporate market in days to come.

Remarkable Features of CRM 3.0

I was about to delete my notes on CRM 3.0 (since I've already posted it here), when I scrolled down the pages and saw some notes from the TechNet Session I overlooked last time.

Refreshable Excel Reports

Business Intelligence in CRM includes refreshable Excel reports! I like. :)

It's like an offline report upon which you could update by clicking Refresh and the report can then connect directly to the Reporting Services to get the changes. Just like any export to Excel, you just choose the dynamic report option.

Sir Benjie demonstrated by exporting a report as a dynamic pivot table. What's so good with this is the fact that the user can create his own report then upload it back to CRM. CRM will then have a new report based on the uploaded pivot table --- so easy!

Integrations with MS Word

He also showed us how CRM is integrated with MS Word. A business template can be loaded with data from CRM on the MS Word window directly. Any changes in the document could also be synchronized in CRM. No need to shift from CRM to the document --- so convenient!

Next TechNet Session on CRM

So there were my last pointers on the subject. I'm looking forward for another TechNet Session --- extending CRM 3.0. I hope I could attend that one. :)

Excel Error: File Not Loaded Completely

Error encountered: File not loaded completely.


Why? Because I exceeded the row limit for Excel when I pasted my data. Therefore I have to cut my data into chunks so I could paste it. Or I could just paste it on a text file since it was not a requirement to use Excel in the first place. However the text file alternative is just for displaying purposes only, not for manipulation of data.





Details on the error explains the situation with some recommendations:

"This error is usually encountered when an attempt to open a file with more than 65,536 rows or 256 columns is made. Excel is limited to 65,536 rows of data and 256 columns per worksheet. You can have many worksheets with this number of rows and columns, but they are usually capable of fitting into one workbook (file). The number of worksheets you can have per workbook is limited only by the amount of available memory your system has. By default, Excel can manage 3 worksheets, more if there is available memory to support the quantity of data.


Truncation of rows or columns in excess of the limit is automatic and is not configurable. This issue can usually be remedied by opening the source file with a text editor, such as Microsoft Office Word, and then saving the file off into multiple files with row or column counts within the limits of an Excel worksheet. These files can then be opened or imported into Excel worksheets.


If you are using a data format that does not support use of a text editor, it may be easier to import the data into Microsoft Office Access and then use the export feature of Access to import the data to an Excel format. Other methods of importing large source material into multiple worksheets are available, but may be more complex than using either a text editor or Access."

File Error: Exclusively Used/Opened by Another User

Error encountered: "The Microsoft Jet database engine cannot open the file." It is already opened exclusively by another user, or you need permission to view its data.

Why? Because the Excel file I was processing in SSIS was open.

This is a common file error not only in SSIS but in the general development arena. This is encountered when the file is open or another process is using it (either the process hung or a running service is consuming the file). Therefore you need to close the file, stop the calling service or end the hanging application in the Task Manager.

CRM 3.0

"Microsoft Dynamics CRM is a customer relationship management (CRM) solution that provides the tools and capabilities needed to create and easily maintain a clear picture of customers, from first contact through to purchase and post-sales. With modules for sales, marketing, and customer service, Microsoft Dynamics CRM delivers a fast, flexible, and affordable solution that drives consistent, measurable improvement in every business process, enabling closer relationships with customers and helping to achieve new levels of profitability."

Three mantras of CRM

1. Works the way you do : It has almost the same interface as Outlook, making it user-friendly.
2. Works the way your business does : It has workflow management, for instance. Currently CRM 3.0 has an independent workflow management but with the coming of CRM 4.0 (codenamed Titan to be released by end of this year or early next year), it will be using Windows workflow then.
3. Works the way technology should : Security, integration and extensibility

.:Add-Ons:.

C360 is a leading provider of CRM add-ons for mobile CRM, application integrations, security, etc.

An example of these is field-level security, which is very useful for hiding sensitive data. For instance, a specific field (i.e. revenue) can be displayed for the user with a role of Salesperson but not visible for other users.

.:Customization:.

You could also customize not only the placement of controls in the application, but even their events like the OnClick event. However the editor only supports JavaScript to render in the browser.

.:Database Structure:.

For example, AdventureWorks installed CRM 3.0 in their server. There will be two databases created in the SQL Server as follows:

1. AdventureWorks_MetaBase : contains data on the data
2. AdventureWorks_MSCRM : contains the data used in the application. You will notice that there is an AccountBase table and an AccountExtensionBase table among others. Entities have their basic table then have a table for all fields that may be added when customizing, which makes it more extensible.

Friendly reminder: DO NOT alter any object in both databases for CRM. This policy is stated in the end-user license agreement and if violated, Microsoft will not support your application anymore.

Poor you if something gets affected and the application goes all wrong. Someone said to "Backup first." Hehehe.

.:Downloads:.

You can download a Virtual PC Demonstration with Microsoft Dynamics CRM 3.0 and Microsoft CRM 3.0 90-day trial versions among other CRM goodies in Microsoft Downloads.

Makati TechNet Session: Break Out with Microsoft Dynamics CRM 3.0

Last June 14, I attended the Technet Session on CRM 3.0 at 6750 Ayala Ave., Makati City. Our speaker was Benjamin Fallar III, the Chief Technology Evangelist of DBWizards.

Sir Benjie said that his presentations will be hosted in msforums.ph once they get a slot, so you could get more info on CRM 3.0 once that takes effect. I'm actually waiting for the slides from the TechNet Session on ASP.NET Security Best Practices. :) Will post my notes on that subject after my notes on CRM 3.0 tomorrow. So for now, here's a little background on CRM 3.0.

CRM 3.0

"Microsoft Dynamics CRM is a customer relationship management (CRM) solution that provides the tools and capabilities needed to create and easily maintain a clear picture of customers, from first contact through to purchase and post-sales. With modules for sales, marketing, and customer service, Microsoft Dynamics CRM delivers a fast, flexible, and affordable solution that drives consistent, measurable improvement in every business process, enabling closer relationships with customers and helping to achieve new levels of profitability."

Microsoft Dynamics, previously Microsoft Business Solutions, is comprised of CRM, Great Plains, NAV (formerly MS Navision), among others.

Trivia: CRM is the first-ever business application done by Microsoft using .NET Framework 1.1 in C#.

Also, watch out for CRM 4.0 codenamed Titan to be released at the latter part of this year or early next year.

Noob Tip: Windows Shortcuts

For Windows noobs out there, here are some of my oftenly used shortcuts.

Start > Run

Click Start > Run... then type the ff. keywords to open their respective programs upon pressing Enter:

• inetmgr = Internet Information Services
• mstsc = Remote Desktop Connection
• mmc = Microsoft Management Console
• regedit = Registry Editor (Edit the registry at your own risk!)
• notepad = Notepad. You could also open a text file by specifying the location through quotation marks. Example: notepad "C:\SampleFile.txt"
• mspaint = Microsoft Paint
• calc = Calculator
• cmd = Command Window
• devenv = Microsoft Visual Studio

Use Shift+Del...

To delete a file/folder permanently (meaning the deleted item is not sent to the Recycle Bin anymore).

To delete a whole line of code in MS Visual Studio or SQL 2005.

Nokia 3650 Messaging Error

Error encountered: "System: Messaging Error!"

My officemate's phone just conked out on its messaging feature and she just couldn't view incoming messages. She could navigate all she want to the phone's other features except for messaging. To think she's into SMS so much.

She was just getting berserk when I asked her to search for the error in the net. We found a good site on tips for using Nokia 3650. She tried doing the soft reset by just going to:

Tools -> Phone settings ->General ->Orig Phone Settings. Enter lock code (default is 12345).

But she still encountered the error and had to do the difficult thing of hard reset. Here are the steps cited:

1. Backup all your phone data using Nokia PC Suite.
2. If you are not very sure about the backup on the PC (or if you don't have a PC), you can also create additional backup using the 'Memory' application to your memory card. This backup will restore everything to its original state (and sometimes you will also restore the error).
3. Make sure your phone is fully charged, or plug the charger before continuing to the next step.
4. Press *#7370# answer 'Yes' and enter 00000.
5. The phone will restart itself. Wait until it's done, test if the phone function works fine, and make sure everything got deleted.
6. Restore your backup, if necessary.

She backed up her images to her SIM card, hard reset her phone then the error was fixed. Bad thing was she forgot to backup her contacts! I just cajoled her that the images were more important than the phonebook. Tsk tsk tsk.

How to Create a Microsoft Universal Data Link (UDL)

I was having a hard time configuring my OLEDB connection to DB2 using the Microsoft OLEDB Provider for DB2. As I was going through the net, posts regarded the IBM OLEDB Provider for DB2 a much easier alternative than the MS counterpart. Using a UDL file for the connection string was also recommended.

I've tried searching for the IBM OLEDB Provider for DB2 but didn't find any match for a download. Instead I downloaded and installed the DB2 client to get the IBM provider.

I was not familiar with the UDL file and so I searched for ways to create one. Most posts said to right-click on the selected folder and pick Microsoft Data Link from the New menu. I was stomped since I didn't have that feature. (My OS is WinXP Pro.) Good thing I found this MSDN article which stated how to create a UDL file manually.

So I configured my OLEDB connection by clicking the "DataLinks..." button and selecting my pre-defined UDL file. Voila, the data link configured the connection string automatically and I just had to click Ok.

VSA Error in SSIS Design Script

Error message: Cannot show Visual Studio for Applications Editor.

I added a Script Component in my package to generate the error description for my fallout table when I clicked the "Design Script" button from the Script tab and got this error.

Through searching the net, I found answers that stated to remove the C# project and restart the Visual Studio application but I did not have any other project than SSIS on my solution.

James McAuliffe also commented a workaround on some forums/blogs like in Jamie Thomson's blog. He recommended registering all the assemblies once more in case of conflicts on Visual Studio or SQL versions but it didn't work out for me either.

I was just too stubborn to download SQL 2005 SP2 which solved the problem afterwards.

Error Descriptions for SSIS Error Outputs

I don't know why SSIS didn't put error descriptions automatically in the source error outputs. We just have the error code and error column. Thus we still need to place a Script Component to get the error description.

On the Script Transformation Editor, add an output column "Error Description" in the Inputs and Outputs tab. Then click the Design Script button from the Script tab and add the ff. code which gets the error description based on the error code:

Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

Take note to change the data type of the newly-created output column since the default data type is integer.

I almost always overlook this and jump to the script code soon and get a mismatched data type exception afterwards. :)

Thanks to Jamie Thompson's article for this very useful info on getting the error descriptions in SSIS.

SSIS Logging Configuration Error

Error encountered: “The connection is not found. The error is thrown by Connections collection when the specific connection element is not found.”

This happened when I copied a package that had logging enabled and then deleted the log file from the Connection Managers. I forgot to switch off the SSIS logging and thus encountered this error upon running the new package. If you search through the net, this error is also encountered as long as the SSIS logging is not configured properly.

Release 1.0

Started this at WordPress but I prefer Blogger instead. Sorry.