Saturday, January 15, 2011

Emailing embedded images from Dynamics AX

When I first tried sending an email with images it showed the standard image not found image from Microsoft internet exploder.


This was the image in all the emails sent by AX.

We are using AX 4.0 SP2 with hotfixes applied....I began to dig...I found that the image path is replaced with cid:1 because it is an embedded resource in the email.....then discovered two things needed to be true:

  • Any images that you include in an email must exist in the directory specified at SysEmailParameters.AttachmentPath.
  • You must change the code if the location is a shared directory starting with two forward slashes //
The code checking that the image exists in the correct directory has a BUG and well it is overly complex (KISS).  Just replace this method below...

public static boolean isFromAttachmentsFolder(str _pathName)
{
    str attachmentsFolder;
    str pathName;
    ;
    // Fix embedding images in emails
    attachmentsFolder = SysEmailParameters::find().AttachmentsPath;
    pathName = _pathName;

    attachmentsFolder = Global::strReplace(attachmentsFolder,'\\','/');
    pathName = Global::strReplace(pathName,'\\','/');

    // Fix embedding images in emails
    return Global::strStartsWith(strupr(pathName), strupr(attachmentsFolder));
}

Thursday, January 13, 2011

SQL Server Reporting Services: Fixing "Could not generate a list of fields for the query"



Many times when creating an SSRS report I get the message "Could not generate a list of fields for the query" especially when using datasources which include temporary tables or very complex queries.  Sometimes clicking the Refresh fields button on the query toolbar does indeed fix the issue and other times it does not help.

I found the workaround for making it always work.

  1. Close the design view of the report
  2. Right-click the report and choose View code
  3. Find the end of the SQL query that is giving you the error message...you can search for if you want.
  4. Add the area with any  report parameters which this data source uses.
  5. Save
  6. Close
  7. Reopen the report in design view


Monday, January 10, 2011

Determine table ID in SQL

Create this function to easily get the table number within a SQL statement.

-- =============================================
-- Create date: 2010.11.01
-- Description:    Gets the AX table ID for the table name
-- =============================================
ALTER FUNCTION [dbo].[fnAXTableID] 
(
    -- Add the parameters for the function here
    @tableName nvarchar(40)
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @tableNum int

    -- Add the T-SQL statements to compute the return value here
    SELECT @tableNum = TableID
    FROM SQLDictionary
    WHERE [Name] = @tableName
        AND FieldID = 0
        AND Array = 0

    -- Return the result of the function
    RETURN @tableNum

END