SSIS OLE DB source missing records in SQL Server 2005 SP2

Issue: I found out recently that a SSIS package I was developing was missing random records/rows when extracting data from a complex database view from SQL Server 2005 SP3. I was using the "table or view" data access mode and picking the view from the dropdown list.

Solution/Workaround: After troubleshooting and verifying that nothing was interfering with the extraction (i.e. anti-virus software, etc.), I found a workaround by changing the data access mode to "SQL command" and just typing "select * from " instead. I am pretty sure this is a bug with SSIS, but I don't know if it is caused by my use of common table expressions and linked server in the database view.

xcopy - Insufficient memory error

Today, noticed errors were occurring with some of my backup xcopy jobs. When I run the xcopy command via command line, it had something like this at the end of it:

.
.
.
Insufficient memory
376 File(s) copied

C:\>

I did a bit of research and realized that the cause was that some of the filenames in folders to be xcopied had really long names. And coupled with the long path, they were exceeding 254 characters in length. This is a Windows 2003 server, so I downloaded and installed the Windows Server 2003 Resource Kit Tools (http://www.microsoft.com/download/en/details.aspx?id=17657) to use the robocopy.exe along with the /MIR (mirroring switch) .

Now the job runs smoothly without the 254 limitations in path plus filename string .

Magento USPS string is not in UTF-8 error

Issue: After migrating Magento from dev to production environment, USPS shipping method stopped working.

in system.log file, the following error is shown:
Warning: SimpleXMLElement::addAttribute() [simplexmlelement.addattribute]: string is not in UTF-8 in {magento install dir}/app/code/local/Mage/Usa/Model/Shipping/Carrier/Usps.php

Resolved by:
going to Magento Admin console -> Configuration -> System -> Shipping Methods -> USPS, and re-enter/paste your USPS User ID supplied by USPS to you (usually thru email when you first signed up for the account). and hit save configuration.

Magento Error: Base table or view already exists: 1050 Table ’sales_order_status’ already exists

Scenario: After changing permissions on folders/files in var/cache/, the Magento site was crashing with this error:

Base table or view already exists: 1050 Table ’sales_order_status’ already exists

________
It looks as if the software is trying to upgrade from 1.4 to 1.5, but the new tables are already there.

Workaround: Log into database with the MySQL account and run these SQL statements (in the following order to avoid foreign key constraints) to drop the new tables:

drop table sales_order_status_label;
drop table sales_order_status_state;
drop table sales_order_status;
drop table paypal_cert;

After executing these statements, hit the site again via web browser, and the software should recreate the 4 tables along with finishing the upgrade.

Note: I've locked down the permissions on folders and files, but looks like Magento upgrade creates new folder/files with 777 permission owned by apache user. You may have to change the permissions on these new folder/files to your liking...

Weird permissions error when changing password through SQL Server Management Studio

Ran into a case this week when a business superuser could not change other users' passwords. I checked and the superuser had the securityadmin role. Basically after sleuthing around, I came to the conclusion that the issue is caused by incompatible service pack levels between the database server (which is at SQL Server 2005 SP3) and the business superuser who had recently had to reinstall management studio (but installed a non SP version of the client).

This is not the first time something strange happens when using a lower service pack client against a higher service pack server. I've ran into weird maintenance plan issues before with this scenario...

I just wish Microsoft can just warn users when connecting to a higher service pack SQL Server to help track down issues or to remind to update management studio clients.

coalesce() function translated to case expression in SQL Server ?

Error message in SQL Server Management Studio:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.

I just wanted to note this down before I forget... a couple of weeks ago I was deploying a view to a test environment and had to re-point the table source to a linked server and received this message.

the original view was something like this (simplified):
select *
from t
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null

the above worked when t is on same server.

after pointing t to linked server:
select *
from .dbT.dbo.t
where coalesce(a,b,c,d,e,f,g,h,i,j,k,l) is not null

I got the error msg:
Case expressions may only be nested to level 10.

Apparently, SQL Server translates the coalesce function internally to nested case expressions and ran into some limitations via the linked server interface.

To work around the issue, I rewrote the coalesce function to OR expressions like:
select *
from .dbT.dbo.t
where
(a is not null
or b is not null
or c is not null
or d is not null
.
.
.
or l is not null)

Annoying SQL Server 2008 Management Studio message saving changes is not permitted

Have you tried to edit a table design in 2008 and got this message?

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

quick google search got me to this page

which shows the solution to go Tools menu -> Options -> Designers -> and uncheck "Prevent saving changes that require table re-creation"

The only thing to note here is that, if you have a slow system, these type of DDL changes may cause the GUI to not respond quickly.

Deltek Time & Expense partial timesheet start week for rehires

Applies to Time & Expense 8.2.1 and possibly other versions sync'ed to Costpoint.

We ran into a scenario where a rehired employee started on a Wednesday and was not able to submit timesheet for a partial week (24 hrs).

The issue is that T&E does not have a field for rehire date even though there were 2 employee history records (1 with orig start date and 1 with rehired start date), so it uses the original hire date, thinking the Monday and Tuesday are valid days too.

The workaround is to set the hire date in T&E to the new rehire date and have employee submit timesheet before Costpoint export/T&E import will rewrite that field with original hire date.

Windows 7 batch script behavior difference vs. XP

One of my clients is in midst of migrating a few reporting applications over from XP to Windows 7 and called me to assist in looking at an issue where they could not register some dll components.

I took a look at the Windows batch file and it contained lines like:
copy xxx.dll "%WINDIR%\System32"
regsvr32 xxx.dll

When I launched the script, it complained about access denied, so I right-clicked the script and ran it as administrator. But running as administrator, it complained it cannot find the xxx.dll.
That's when I noticed the execution directory became c:\windows\system32 when I executed the script as administrator, even thought the script was located in another drive letter and path.

The solution was to update the batch file as follows:
copy <path>\xxx.dll "%WINDIR%\System32"
regsvr32 xxx.dll

and then run it as administrator.

Deltek Software Manager does not correctly download files

I started planning for our Time & Expense upgrade to 8.3 today and used Deltek Software Manager to download more than 2GB of installation files and documentation. After a couple of hours, it reported the download was finished. But one of the zip files was corrupt.

I had to click on view and pick the menu item to show already downloaded software and re-download the zip file again. This time, the file size was 700MB for that particular zip file vs 100MB.

Magento 1.5.1.0 fix for USPS shipping

Background: finally got the production url from USPS so I can update the configuration in Magento.

First issue: USPS email sent to me said to use URL: http://production.shippingapis.com
Resolution: I had to add the dll name at the end to make it work http://production.shippingapis.com/ShippingAPI.dll

Second issue: Only library mail shows up as a USPS shipping method.
This is caused by USPS renaming the shipping method names and also possibly by Magento core developers who relied on the shipping names instead of some type of unique id's.
I read thru many posting on the web regarding fixes... I had to end up using a combination of the 2 different solutions to make mine setup work.
Resolution:

•  Copy Usps.php from app/code/core/Mage/Usa/Model/Shipping/Carrier/
  to app/code/local/Mage/Usa/Model/Shipping/Carrier/
• Edit the new Usps.php file in app/code/local/.....
• Find
  

                              if (is_object($xml->Package) && is_object($xml->Package->Postage)) {
                                  foreach ($xml->Package->Postage as $postage) {
  and Replace with
  

                              if (is_object($xml->Package) && is_object($xml->Package->Postage)) {
                                  foreach ($xml->Package->Postage as $postage) {
                                      $postage->MailService = str_replace('®', '', strip_tags(htmlspecialchars_decode(htmlspecialchars_decode((string)$postage->MailService))));

  • then Find
                                if (is_object($xml->Package) && is_object($xml->Package->Service)) {
                                    foreach ($xml->Package->Service as $service) {
    and Replace with
                                if (is_object($xml->Package) && is_object($xml->Package->Service)) {
                                    foreach ($xml->Package->Service as $service) {
                                        $service->SvcDescription = str_replace('®', '', strip_tags(htmlspecialchars_decode(htmlspecialchars_decode((string)$service->SvcDescription))));
  
  I had to restart my Zend Server and clear its cache to take in the new code in local folder