SSRS 2008 to 2014 Migration Excel Export error

migrationssrs-2008ssrs-2014

We are working on migrating our SSRS from SQL Server 2008 to SQL Server 2014. After brining the reports over I have a set of reports that will execute just fine but when I try to perform a standard export to excel from the 2014 Server I get a message box saying:

We found a problem with some content in ACM Consolidated
Invoie_2014.xlsx. Do you want us to recover as much as we can?
If you trust the source of this workbook, click yes.

However, I do not receive the same message when exporting to Excel from the 2008 server. I believe the problem is related to some Numeric fields that are coming back with a value of 0.0000000000000000000. Has something changed with the excel export function between these two versions?

Best Answer

This appears to be related to some implicit conversions that are occurring in the background when exporting the report to xlsx. Numeric columns are being sent to excel as if they are text columns. One report responded to a change to the query using an Explicit cast to Numeric (Cast as opposed to convert to remove any commas or other formatting that might confuse the conversion). Several other reports did not respond to the Cast but returned correctly after using an expression conversion of VAL(). I still have several reports that are not responding to either. If anyone out there has a good explanation for this I'd love to hear it.

Related Solutions

Sql-server – SQL Server to Oracle Export – Data conversion error

I tried your script and recreated your environment completely and i didn't see any error.

Ran perfectly and created all tables and transferred all data successfully

i tried running under a sql agent job and still no error was generated

but i guess you can update your insert script by making sure the column gets converted under all conditions

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE Null END

change your script to

INSERT INTO OPENQUERY (
    ZMMS_ORA 
    'SELECT 
        "COMPANYID",
        "BO_ID",
        "AS CM",
        "AS RSM",
        "AS SE",
        "TS CM",
        "TS RAE",
        "TS RSM",
        "TS SE",
        "BBONE AM",
        "ASM",
        "WCT ASM",
        "LS TSM",
        "WCT TSM",
        "TS TSM",
        "AS PM",
        "AS DEV",
        "TS PM",
        "TS DEV",
        "TS TECH",
        "BB EXEC",
        "CHM",
        "ANGEL Client Manager (CM)",
        "ANGEL Engagement Team",
        "BbOne Client Manager (CM)",
        "Blackboard Executive",
        "CE/Vista Hosting Mgr (ASM)",
        "CE/Vista Tech Support (TSM)",
        "Complex Hosting Manager (CHM)",
        "Learn Client Manager (CM)",
        "Learn Hosting Mgr (ASM)",
        "Learn Reg Sales Mgr (RSM)",
        "Learn Sales Engineer (SE)",
        "Learn Services Developer",
        "Learn Services ICM Mgr",
        "Learn Services Project Manager",
        "Learn Tech Support (TSM)",
        "Transact Client Manager (CM)",
        "Transact Project Manager",
        "Transact Reg Acct Exec (RAE)",
        "Transact Reg Sales Mgr (RSM)",
        "Transact Sales Engineer (SE)",
        "Transact Services Technician",
        "Transact Tech Support (TSM)",
        "Xythos Tech Support (TSM)"  FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
    )
SELECT "COMPANYID"
    ,[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END 
    ,"AS CM"
    ,"AS RSM"
    ,"AS SE"
    ,"TS CM"
    ,"TS RAE"
    ,"TS RSM"
    ,"TS SE"
    ,"BBONE AM"
    ,"ASM"
    ,"WCT ASM"
    ,"LS TSM"
    ,"WCT TSM"
    ,"TS TSM"
    ,"AS PM"
    ,"AS DEV"
    ,"TS PM"
    ,"TS DEV"
    ,"TS TECH"
    ,"BB EXEC"
    ,"CHM"
    ,"ANGEL Client Manager (CM)"
    ,"ANGEL Engagement Team"
    ,"BbOne Client Manager (CM)"
    ,"Blackboard Executive"
    ,"CE/Vista Hosting Mgr (ASM)"
    ,"CE/Vista Tech Support (TSM)"
    ,"Complex Hosting Manager (CHM)"
    ,"Learn Client Manager (CM)"
    ,"Learn Hosting Mgr (ASM)"
    ,"Learn Reg Sales Mgr (RSM)"
    ,"Learn Sales Engineer (SE)"
    ,"Learn Services Developer"
    ,"Learn Services ICM Mgr"
    ,"Learn Services Project Manager"
    ,"Learn Tech Support (TSM)"
    ,"Transact Client Manager (CM)"
    ,"Transact Project Manager"
    ,"Transact Reg Acct Exec (RAE)"
    ,"Transact Reg Sales Mgr (RSM)"
    ,"Transact Sales Engineer (SE)"
    ,"Transact Services Technician"
    ,"Transact Tech Support (TSM)"
    ,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;

Issue With SSRS Emailing Reports As PDFs

Another part of this puzzle was that the files that weren't able to send were created in a temporary location where they would then be deleted.

This issue is now completely resolved - in brief (after several false starts such as checking file/folder permissions and discovering reports would be sent via subscription as pdf if they contained a graph and not a matrix); we ended up using SMTP (set up on SQL Server) to send the email from the SQL Server to Exchange, which would then handle the rest of the journey.

Thank you all for your suggestions.

Best Answer

Related Solutions

Sql-server – SQL Server to Oracle Export – Data conversion error

Issue With SSRS Emailing Reports As PDFs

Related Question