Monday, October 31, 2011

MSSQL - Saving Changes in Not Permitted

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.

When getting this error in SQL Server Management Studio 2008, be aware that when you redesign a table is a way that affects it's structure, the action requires the table to be recreated. By default the Database Management Studio will not allow you to recreate it, therefore no changes allowed.

The solution is simple.
1) Go to Tools -> Options
2) Select the tab Designers and inside of it Tables and designers
3) Uncheck Prevent saving changes that require table re-creation
4) Save
SQL Saving Changes Is Not Permitted


This configuration is very obvious, but the path to it is rather obscure so it is here for future reference.

Friday, August 26, 2011

401 - Unhautorized (Invalid Credentials in IE)

We started having problems with Integrated Authentication in our Outsystems solution. We made tests for two months with a dozen of different users and all main browsers (IE8 and 9, Firefox 5 and 6, Chrome 13) with absolutelly no problems of that type.

This is a technology I strongly recommend for it's speed, potential and ease of use, so such a basic problem days before going to the production environment was totally unexpected.

Problem


The Integrated Authentication with Active Directory wasn't working. It usually enters automatically in IE and requests for AD login in other browsers. This time Mozilla and Chrome were working as usual, but IE was requesting credentials and giving error 401 - Unhautorized validating them.

Solution


We debugged the application from start to finish not finding a reason for it. The solution was found in a forum, not about Outsystems, but about IIS7 (LINK).

The answer given by jay-dubb was:
To whoever this may help, this saved my life...

IIS 7 was difficult for figuring out why i was getting the 401 - Unauthorized: Access is denied due to invalid credentials... until i did this...

1.) Open iis and select the website that is causing the 401

2.) Open the "Authentication" property under the "IIS" header

3.) Click the "Windows Authentication" item and click "Providers"

4.) For me the issue was that Negotiate was above NTLM. I assume that there was some kind of handshake going on behind the scenes, but i was never really authenticated. I moved the NTLM to the top most spot, and BAM that fixed it.


To us it worked like a charm, proving once again that the problem is never in Outsystems.

Thursday, March 24, 2011

Basics - How to confirm a Biztalk update

Here are the version numbers of current Biztalk 2006 R2 as seen at Microsoft.

BizTalk Server 2006 R23.6.1404.0
BizTalk Server 2006 R2 SP13.6.2149.10January 27th, 2010
Cumulative Update 1 (CU1)3.6.2210.12April 12th, 2010
Cumulative Update 2 (CU2)3.6.2217.12June 24th, 2010
Cumulative Update 3 (CU3)3.6.2224.12August 30th, 2010


For other Biztalk versions you can explore another Microsoft Link.

We made an update on Biztalk to CU3 and the version seen at About was still 3.6.2149.10, referring to the SP1.



To know the specific add-ons like CU's, and hotfixes, the only place to go is Add and Remove Programs. Just remember to mark the Show Updates. checkbox.

Thursday, March 17, 2011

Using SAP Dates in Biztalk

Half the maps I've been doing have SAP in one side or the other. One of the biggest problems are in map transformation.

Making all those substring concatenations is ugly. Creating a functoid is the best thing to do, but in case it is needed just in a map or two, how can it be made?


Convert .NET Date to SAP using C#


dateVariable.ToString("yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

Convert SAP Date to .NET using C#


DateTime.ParseExact(val1, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

(the CultureInfo attribute is optional but recommended in both methods)



This problem appeared when I had to calculate a difference between two SAP dates. It end up by being just this:

public int DaysToPay(string val1, string val2) {
DateTime startTime = DateTime.ParseExact(val1,"yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
DateTime endTime = DateTime.ParseExact(val2,"yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

TimeSpan span = endTime.Subtract ( startTime );

return span.Days-1;
}

Sunday, March 13, 2011

Count entries in a SQL table by minute

Imagine that you need to count entries on a table by date. That query is rather obvious and probably you already made it sometimes. And what if you need to count entries on a table by minute? For instance, to determine the period when more orders are received.

select count(*), datepart(HH, lastchange), datepart(MI,lastchange)
from ORDERSTABLE
group by datepart(HH, lastchange), datepart(MI,lastchange)
order by datepart(HH, lastchange), datepart(MI,lastchange)


Running that query two things are immediately wrong. The hours separated from minutes, the minutes with one digit, and some periods have no info and therefore no entry saying 0.


The first step is to fill the blanks with 0. For that, we'll generate a temporary table with all the hours and minutes from 0:00 until 23:59. We'll be using Common Table Expressions and a temporary table for that.
Just pick a day, and use DateAdd to increase it minute by minute. With Recursion it will fill the entire table based on that rule.

WITH mycte AS(
SELECT CAST('2011-01-01 0:00' AS DATETIME) DateValue
UNION ALL
SELECT DATEADD(MINUTE, 1, DateValue)
FROM mycte
WHERE DateValue < '2011-01-02'
)

SELECT DATEPART(HH,DateValue) hours,DATEPART(MI,DateValue) minutes
INTO #temptable
FROM mycte OPTION (MAXRECURSION 0)


Finally, we'll left join the complete table and our results, and write the time in a proper format.

SELECT REPLICATE('0',2-LEN(hours))+CAST(hours AS VARCHAR)+
':'+REPLICATE('0',2-LEN(minutes))+CAST(minutes AS VARCHAR),COUNT(lastchange)
FROM #temptable
LEFT JOIN ORDERSTABLE ON hours = DATEPART(HH, lastchange) AND minutes=DATEPART(MI,lastchange)
GROUP BY hours, minutes
ORDER BY hours, minutes


Don't forget to drop the temporary table.
DROP TABLE #temptable


And that's it.

Friday, January 21, 2011

Untyped Messages and WCF Services in a real scenario

Context:


This project was my first big Biztalk experience. It started more than two years ago (is still in Biztalk 2006 R2) and most of the work was made based on pre-conceived ideas of what was good, what was easy, what was trustable.
The project kept on growing and at a certain point the desire to trash it all and restart was too strong. This is an example of how to slowly modify a monster-like orchestration. The images and names aren’t always the same because they are not from a case-study, they were all taken from a real project. It consisted of several orchestrations and this post was made when each one of them was in a different stage of transformation so that all situations are represented.


In this project we had to deal with a bunch of different XML files, different types, different schedules, and, worst of all, different actions on the destination server.

Transport properties


We could make this by using maps on the send port, but the tracking of the messages was critical so we needed to keep track of the message on every step of the journey. So, an orchestration was the choice.

To reunite everything, the receive port that collects them (from FTP, SAP idocs, etc.) is the entry of the orchestration. The Message schema in the orchestration is System.Xml.XmlDocument so all schemas are accepted with no distinction.
A Decide component help us creating the basic structure for it, with a lot of conditions of type
OriginalMessage(BTS.MessageType) == http://Company/Schemas/BusinessPartnerActivityTypes#ActivityTypes

and a two-way destination port prepared for send/receive schemas of type System.Xml.XmlDocument, we could make it by repeating a lot of components.

For each flow we make

1. Transform Xml (Received message) to original schema
FCBusinessPartnerActivityTypes = OriginalMessage;


2. Map original schema to service schema
Transform Map


3. Transform ServiceSchema to Xml (port schema)
XmlMessage = SMUVASActivityType;


4. Send to Service

5. Receive Response

6. And after some flows made the general picture would be something like

messy orchestration


How to make it in a lighter way?



The first step is to remove the transformation.

1. Declare a string variable for the map, called mapName

1.1. Use an expression editor to select the map based on the message type

1.2. Use the Fully Qualified Name of the map

mapName="";
if(OriginalMessage(BTS.MessageType) == "http://Company/Schemas/OrderTypes#OrderTypes"){ mapName="Company.Biztalk.SmuasCommercialModule.Messaging.map_FCOrderTypes_to_SMUVASOrderTypes";
}
if(OriginalMessage(BTS.MessageType) == "http://Company/Schemas/OrderStatus#OrderStatus"){
mapName="Company.Biztalk.SmuasCommercialModule.Messaging.map_FCOrderStatus_to_SMUVASOrderStatus";
}


2. Then in a message assignment use the transform method.

mapSystemType = System.Type.GetType(mapName);
transform(XmlMessage) = mapSystemType(OriginalMessage);


3. The orchestration will be much simpler.

medium mess orchestration


The next optimization is to remove the multiple actions.


At the if that defines the map, you can also define the action. Something like

if(OriginalMessage(BTS.MessageType) == "http://Company/Schemas/Banks#Banks"){
mapName="Company.Biztalk.SmuasFinancialModule.Messaging.map_FCBanks_to_SMUVASBanks"; action="http://SmuAs/Services/BackendServices/CommonBackendSyncService/ICommonBackendSyncService/SetBankList";
}


And when the request message is created, give it the correspondent action.

mapSystemType = System.Type.GetType(mapName);
transform(RequestMessage) = mapSystemType(OriginalMessage);
RequestMessage(WCF.Action)= action;


The look of the orchestration is much lighter and pleasant to work with.
Simple orchestration

Using Business Rules Engine (I)

Making business rules without messages


The most common way of using business rules is by evaluating a XML message with XPath and return an XML message with more info. The problem is that most of the examples are orchestration dependents: by demanding a compiled DLL with the schemas to be used, they limit the possibilities.
In a project with many orchestrations an easy solution is to make a solution with the schemas, compile, define the rules based on that DLL, and then apply them in the remaining solutions. If it is a small project where schema and orchestration could be on the same DLL, a first compilation is needed for BRE to access the schema. Only then the orchestration can add the rule.

But, what if the message is not important in any direction? What if you just want to use the same rule in all orchestrations without referring each and every DLL?
Scenario: ten distinct schemas. One flow in and one flow out for each. The same tracking rules apply on each direction, depending only on the schema. Here I found a great way of corrupting the principles of the rules.
I recommend this blog because it was the first were I could find any comprehensible info about BRE.

Basic: Policies have versions, on each version one or more rules. By activating different versions different rules can be in use. A rule is divided in condition and actions. A straight-forward "If-Then".

BRE Policy, Versions, Rules


Usually each orchestration has its own rule and each rule is for an orchestration, a direct match.
The trick is to make a single rule, the if always true, and the orchestration will receive information that can be used or not.

The advantage of business rules engine is the low processing time and the changeable ease (copy version, edit, publish and deploy). There are no big processing advantages in using rules this way, but the changing method is still easy and no deployments required.

Making the if always true


Like always, just use the condition 1==1 or something that obvious.
On the actions list, we’ll use an ArrayList to return a list of words.
  • Go to facts explorer window
  • Select .NET classes
  • Right-click and browse .NET Assemblies
  • Select the mscorlib library
  • Press OK

    BRE Search Library


    The library will become available.

    BRE ArrayList Add


    Find ArrayList and the Add() method, and drag it to actions pane as many time as you’ll need.
    Fill in with the key strings and publish the version.

    BRE Actions


    Add to the orchestration variables list one of type ArrayList.
    Go to Orchestration View and right click Variables folder for New Variable
    At new variable define name (in this example rulesArray) and type (<.NET Class…>, mscorlib, ArrayList)
    Create another variable, of type Boolean called generateAck.
    Add Call Rules component and use that variable as destination for the output.

    BRE Cal Rules


    And then at a Expression editor, search for the value and do as you please. In this example, only for the flows listed we’ll generate Ack. The fixed value is the message type that the orchestration mandatorily knows.

    Expression Editor


    For instance we are searching for Purchase Orders, abbreviated PO. Since the rulesArray includes that value, ANY orchestration that has Purchase Orders will set generateAcks as true and therefore record Acks, if the proper code is made.
  • Tuesday, January 18, 2011

    RFC related Web Service doesn't seem to update

    Situation:

    You are exposing a RFC through Biztalk as a Web Service.
    An update was made on the RFC and on development environment, and works well. When passing to test/production environment still refers to the old version.

    Solution:


    You can even delete the application, it has nothing to do with that DLL. The problem is that for applications related to RFC, Biztalk generates another DLL, refering only to the RFC schema. Supposing that all RFC's are SAP-made and unchangeable, those DLL's are not updated when you reinstall your app.

    The only way to avoid that problem is:
  • Delete that DLL at a folder similar to C:\Program Files\Microsoft BizTalk Adapter v2.0 for mySAP Business Suite\Bin
  • Install your app MSI
  • Restart application, Host Instance and local IIS

    Thanks to http://timrayburn.net/blog/learning-adapters-sap/