I have documented some of the questions/issues in this BAM issues post in StackOverflow.

BAM Activity shows BAM.xla “variable not defined” Error in Excel on lvwTraces

I found the solution for this in this blog: “BAM ‘variable not defined” Error in Excel“.  But for some reason, that blog doesn’t come up at the top of the Google search when  you search for the various related keywords.  [That’s one reason I’m writing this blog post, to boost up that one.]

The solution is related to your Active-X control settings.  BAM uses a lot of Windows and Active-X plugins to accomplish what it does inside your Excel spreadsheet.   You have to go to “File” then “Options”, select Trust Center – Trust Center Settings – ActiveX Settings. Set ActiveX Settings to anything except “Disable..” as shown on the blog referenced above.

Every time you open a new spreadsheet (to create a new BAM Activity View), you will have to do this step again.  But if you save a spreadsheet and then re-open, those settings will be saved in that spreadsheet.

After doing the above, you may get this next error:

Unable to get resource string

The solution I chose was to install the “Office Multilingual User Interface (MUI) pack”.

It seems to me that Microsoft hides the links to these forums.  You can find them on the Microsoft site, but they are buried under a drop-down list with about 100 other forums.

You can ask questions and get answers in the forums below on the MSDN site (http://Social.Msdn.Microsoft.com).  You can also get good answers in StackOverflow.com.

Some people are still using the forums at BizTalk Gurus: http://www.biztalkgurus.com/forums/

 

BizTalk Geenral: https://social.msdn.microsoft.com/Forums/en-US/home?forum=biztalkgeneral&filter=alltypes&sort=lastpostdesc

 

BizTalk EDI: https://social.msdn.microsoft.com/Forums/en-US/home?forum=biztalkediandas2&filter=alltypes&sort=lastpostdesc

 

Adapters and Adapter Pack: https://social.msdn.microsoft.com/Forums/en-US/home?forum=biztalkr2adapters&filter=alltypes&sort=lastpostdesc

 

BizTalk RFID: https://social.msdn.microsoft.com/Forums/en-US/home?forum=biztalkrfid&filter=alltypes&sort=lastpostdesc

 

BizTalk ESB Toolkit:  https://social.msdn.microsoft.com/Forums/en-US/home?forum=biztalkesb&filter=alltypes&sort=lastpostdesc

You can post a new question/issue to the MSDN Community Forum here:
https://social.msdn.microsoft.com/Forums/en-US/newthread

When you select “BizTalk Server” fro the “Forum category”, you will then get the list of specific BizTalk Forums on the right, as shown below:

 

 

Suppose you need to create an EDI file with the ISA header that looks like this:
ISA*00*1111111111*00*1111111111*ZZ*ABCDEFGPLUMBING*ZZ*SUPERCOMP

You need put “ZZ” in the ISA05 field, and “ABCDEFGPLUMBING” in the ISA06 field. How do you do that in BizTalk?

In the Trading Partner Management (TPM) of the BizTalk Admin Console,
when you set up a “party”, you can specify the identifiers. For the “ZZ” identiier, select “Mutually Defined (X12)” from the drop down list under the “Name” column, and specify “ZZ’ as the qualifier, and type in the value “ABCDEFGPLUMBING” or whatever the desired value is in the “Value” column.

The ISA and GS headers will NOT be in your BizTalk map.  They will be generated when you use the EDI Pipeline to read or write a file in a send or receive port.

 

 

I must admit, I didn’t know about Cassandra, other than perhaps a demo of Cassandra and/or MongoDB at a .NET user meeting.  So I decided it was time to give it a try.

  1. Download the tar http://cassandra.apache.org/download/.  This gave me a file called apache-cassandra-3.10-bin.tar.gz on my download folder.  I then created a c:\apache-cassandra-3.10 folder, and unpacked it using Total Commander (from Ghisler).  I knew it could hand tar files.  I’m not sure what other people use.
  2. I open the “Command Prompt”, and navigated to the above folder, then the bin subdirectory, and tried to run “Cassandra.bat”.  This lead to several issues and changes required:
  3. It runs a Powershell program, and if it fails for any reason, the logic falls through to an error about setting the “set-executionpolicy” to unrestricted.  I had done this along time ago on my machine, so this error was quite misleading.
  4. Had to set the environment variable JAVA_HOME to “c:\Program Files (x86)\Java\jre1.8.0_121”.   This might be slightly different on your machine.
  5. Then got  “the heap error”, and made change to configuration file, as shown in the “file compare” below (my change on left, the original on the right):
  6. Then I re-ran “Cassandra.bat” and everything seemed to start.  As a Windows user, you have to know it doesn’t run as a windows service, so you have to leave the command prompt window open the whole time you are using the database.
  7. Being a .NET developer, at first I tried to jump into C#.  From Visual Studio 2013 I ran the NUGET command:
    Install-Package CassandraCSharpDriverThis lead to the error:Install failed. Rolling back…
    Install-Package : Could not install package ‘Microsoft.Extensions.Logging 1.0.2’. You are trying to install this package into a project that targets ‘.NETFramework,Version=v4.5’, but the package does
    not contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.
    At line:1 char:1After some googling and guessing, I tried an older version and it worked:Install-Package CassandraCSharpDriver -Version 3.1.0Mind you, at this point, I really had no idea what I was doing it.
  8. I copied some code from the C# tutorial on this site:  https://academy.datastax.com/resources/getting-started-apache-cassandra-and-c-net
    // Connect to the demo keyspace on our cluster running at 127.0.0.1
    Cluster cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build();
    ISession session = cluster.Connect("demo");

    It failed on the Connect command, with the following .NET error:

    An unhandled exception of type ‘Cassandra.InvalidQueryException’ occurred in Cassandra.dll

    Additional information: Keyspace ‘demo’ does not exist

  9. So, now I realized I needed a demo database, or a tool to see what database are installed.
  10. I like GUI’s (Graphical User Interfaces) so I googled and found reference to DevCenter, downloaded it.  Got a file called DevCenter-1.6.0-win-x86.zip, so I created a folder C:\DataStax\DevCenter and unzipped it.  No MSI install needed.   Just run DevCenter.exe and the nice GUI opens up.
  11. Recall that I’m winging this whole experiment, trying to use past SQL Server Knowledge and tidbits picked up from the .NET meetup.  I ran the commands below, but one at a time. I’ll explain them after the picture.
  12. To see if the demo database existed, I ran:SELECT * FROM system_schema.keyspaces;The command and results are shown below:“demo” didn’t exist ,and neither did “nealdemo” the first time I ran this.
  13. So I preceded to create the keyspace called “nealdemo”.CREATE KEYSPACE NealDemo
    WITH replication = {‘class’: ‘SimpleStrategy’, ‘replication_factor’ : 3};
    SELECT * FROM system_schema.keyspaces;And it actually worked fine the first time!
  14. Then I needed a table, so I ran this:use nealdemo;
    CREATE TABLE archive (
    filename text PRIMARY KEY,
    fileext text,
    folder text,
    filedate date,
    dateinserted date,
    filecontents blob
    ) WITH comment=’archive of files’
    AND read_repair_chance = 1.0;My experiment is to archive some files on disk into a database instead, including the entire contents of the file (and to play with some very big files that contains XML and EDI related to BizTalk B2B data integration).If you don’t include the “use nealdemo” it fails.
  15. I was then ready to insert a couple of rows:use nealdemo;
    INSERT INTO archive (filename, fileext, folder, filedate, dateinserted, filecontents)
    VALUES (‘test1.csv’, ‘csv’, ‘C:\Archive\EDI’, ‘2017-04-07’, ‘2017-04-07’, textAsBlob(‘file contents’));use nealdemo;
    INSERT INTO archive (filename, fileext, folder, filedate, dateinserted, filecontents)
    VALUES (‘test2.csv’, ‘csv’, ‘C:\Archive\EDI’, ‘2017-04-04’, ‘2017-04-07’, textAsBlob(‘innards of the file’));The first problem I had was trying to specify the blob as just a text string.  Then I google and found that I need to wrap it with the function “textAsBlob”.
  16. Then I was ready to query my data:use nealdemo;
    select * from archive;And that worked!   So once I got into DevCenter and ran a few commands, things were looking good.
  17. Now the C# above also runs (by substituting “nealdemo” for “demo”) and it has to be lower case, even though I typed “NealDemo” when I created the keyspace. I.e. it connects and establishes the connection
  18. So I took it one step further, and added some code to retrieve data and display it – similar to the sample code I found on the site above:static void test1()
    {
    // Connect to the demo keyspace on our cluster running at 127.0.0.1
    Cluster cluster = Cluster.Builder().AddContactPoint(“127.0.0.1”).Build();
    // looks like keyspacename (parm below) has to be all lower case
    ISession session = cluster.Connect(“nealdemo”);

    // Try data retrieval
    RowSet rows = session.Execute(“select * from archive”);
    foreach (Row row in rows)
    Console.WriteLine(“{0} {1}”, row[“filename”], row[“fileext”]);

    }

    It’s not fancy or anything, but I’m impressed – I got the results back:

Summary: Not too bad for a 3-4 hour experiment. A few frustrations were encountered. I will move forward now and try to do something a little more useful and exciting with it, and learn more about the architecture of Cassandra and it’s CQL commands (note it’s CQL, not SQL!).

I’m still thinking about whether I want to get AWS (Amazon”s cloud platform) certified.  But one question I had was about where I would have to go to take the exams.
In the past I’ve taken exams, and familiar with testing centers like Sylvan (Prometric), Vue, and 2Test and others.

Question: Are AWS exams online or at a testing center?
Answer: You go to a testing center to take the exam.

Question: How do you find the testing centers for AWS exams in your city?
Answer: Start the scshedule an exam process, identify the exam centers, then close the process if you just wanted to check the locations.

From this page: https://aws.amazon.com/certification/our-certifications/  click “Schedule an exam”.
You have to sign up with https://webassessor.com/wa.do?page=createAccount&branding=AMAZON and pretend you want to take an exam, just to see the exam locations and addresses.  You can then back out.

An example of Texas exam centers, such as Dallas And Austin can be seen in the image below (Dallas is highlighted). One of them is Sylvan, the other is “Business Communication Solutions”.  Those seem to be popular in major cities, along with many college campuses.

 

 

 

Do you need some part time help with your BizTalk system?

Hi, I’m Neal Walters from Irving Texas, a suburb of Dallas. I made this live stream video to introduce myself:

I was a Microsoft Certified Trainer, and taught BizTalk over 15 times.
Then I went into consulting, and I’ve been doing nothing but BizTalk since 2004.

So that’s over 13 years of BizTalk.

I can also help or debug C#, WCF web service, or SQL issues, MSMQ.
and I also have experience with MQ-Series, AS2 and EDI, and over 20 years of mainframe.
These days, it’s usually no big deal to VPN into your system to do the work.

I can work with you on a GoToMeeting to troubleshoot issues, or I can help you architect and/or program your new solution.
You can do the work while I “look over your shoulder”, or I can do the work, and let you “look over my shoulder” if you want to learn, rather than just “get-er-done”.  Sometimes, you just need someone to show you how to do something.

I can also do Admin work, or give you an analysis of your current system, and tell you what you need.

So, instead of going to a recruiter or a middle man, I invite you to work directly with me.

On this BizTalk YouTube Channel, you can see several hundred training videos that I did for BizTalk 2006 “back in the day”.  I used to sell them, but now I decided to donate them to the public.

I’d love to chat with you and see your needs can be met. 214-455-8060.

I’m creating my own checklist of things to watch out for on a BizTalk install, beyond the basic BizTalk hardware/software prerequisites.

Sites below are for BizTalk 2010 – but same applies for most any other release:

https://msdn.microsoft.com/en-us/library/aa577661.aspx – List of Active Domain (AD) groups and service accounts.
I took this, put it in MS/Word and added a column to the left with a suggested name for the client.  Then I asked the client to verify my suggest name to fit their existing AD group/account naming conventions.  I included an “X” for each environment, e.g. (D=DEV, T=Test, Q=QA, P=Prod), or a 3-character (DEV, TST, QUA, PRD).   Example, BTSPRDADMINS (or if you like mixed case, BtsPrdAdmins).


https://msdn.microsoft.com/en-us/library/gg634639(v=bts.10).aspx  Windows Configuration Checklist –
– turn off hyperthreading,
– Ensure Windows Server processor scheduling is set to “Background services”
– Place the Windows paging file on a separate local physical drive.
– Stop real-time virus scanning of database files and .xml, .csv, etc…


https://msdn.microsoft.com/en-us/library/gg634450(v=bts.10).aspx – IIS Configuration Checklist –


https://msdn.microsoft.com/en-us/library/gg634642(v=bts.10).aspx – SQL Configuration Checklist
  – enable Trace Flag 1118 (TF1118) – to reduce contention
  – sp_configure ‘Max Server memory (MB)’,(max size in MB)sp_configure ‘Min Server memory (MB)’,(min size in MB)
 – Autogrow to fixed MB not % for BizTalk DBs


https://msdn.microsoft.com/en-us/library/gg634496(v=bts.10).aspx  – SQL Server Settings that Should Not Be Changd


1) Max Degree of Parallelism (MDOP) is set to “1” during the configuration of BizTalk Server for the SQL Server instance(s) that host the BizTalk Server MessageBox database(s). This is a SQL Server instance-level setting. This setting should not be changed from the value of “1”. Changing this to anything other than “1” can have a significant negative impact on the BizTalk Server stored procedures and performance. If changing the parallelism setting for an instance of SQL Server will have an adverse effect on other database applications that are being executed on the SQL Server instance, you should create a separate instance of SQL Server dedicated to hosting the BizTalk Server databases.

2) Auto create statistics and auto update statistics are turned off for the BizTalk MessageBox DB. 
zTalk Server stored procedures have exact joins and lock hints specified on the queries. This is done to ensure that the optimal query plan is used by the BizTalk Server queries in SQL Server. The distributions and expected results for the queries are known; the approximate number of rows returned is known. Statistics are generally not needed.

3)  BizTalk Server does not support defragmenting indexes. “DBCC INDEXDEFRAG” and “ALTER INDEX … REORGANIZE …” are not supported since they use page locking, which can cause blocking and deadlocks with BizTalk Server. BizTalk Server does support database index rebuilds (“DBCC DBREINDEX” and “ALTER INDEX … REBUILD …”), but they should only be done during maintenance windows when BizTalk Server is not processing data. Index rebuilds while BizTalk Server is processing data are not supported.

Index fragmentation is not as much of a performance issue for BizTalk Server as it would be for a DSS system or an OLTP system that performs index scans. BizTalk Server does very selective queries and updates and BizTalk Server stored procedures should not cause table or index scans.

https://blogs.msdn.microsoft.com/biztalk_core_engine/2007/01/04/what-you-can-and-cant-do-with-the-messagebox-database-server/  – What you can and cannot do with the MessageBox Database

The next very, very important thing to understand is that while the database is installed on your server, it is not “your” database. It is our (or if you catch me on the wrong day, not thinking, “my”) database

A quick list of things which can effect the query plans:

– Statistics (don’t enable these)

– Parallelism (don’t turn this on )

– Table structure (don’t add indexes, columns, triggers, … If you do you will hear silence when you call for help)

– Stored procedures (don’t change them. You can look all you want, but no touching)