KB: Errors while configuring SQLXML

This post is just a knowledge base reference for me with the issues I have encountered while configuring the SQLXML.

We use SQLXML to execute the SQL query and display the xml document in the web browser.

(As part of the BizTalk BAM portal, users can see the actual message being processed by BizTalk. For example on clicking a link “Original message” in BAM portal, the actual message which is being processed by BizTalk to track the data for BAM has to be shown in the portal. This message(XML) can be retrieved from BAMPrimaryImport database. On clicking the link(Original message) we post the URL (“http://webserver/MsgView?sql=select%20LongReferenceData%20from%20dbo.bam_HireExtension_AllRelationships%20where%20ActivityID='18271dc1-6cd6-4b44-a2db-0bc37b2926c1'%20and%20ReferenceType='Message” ) which executes the SQL query contained in it as part of the querystring. Though this approach is prone to SQL injections, this is just a quick and dirty way to get the work done-The above description may be irrelevant to others, but will remind me the scenario where I have used SQLXML)

To setup the SQLXML, follow the instructions as in:http://sqlxml.org/faqs.aspx?faq=97

The steps in the above URL create a virtual directory in IIS. The only configuration change it performs to the default virtual directory is, mapping to the “sqlis3.dll” from C:\Program Files\Common Files\System\Ole DB directory.

Apparently it just performs simple steps with the virtual directory setting. But it is strictly suggested not to create the above using the IIS wizard, rather do it using the SQLXML wizard.

After configuring the virtual directory, when I executed the above query in the web browser, I received the following errors.

Error 1:

HResult: 0x80046000
Source: Microsoft SQL isapi extension
Description: Invalid connection settings: access denied

This error is due to the invalid SQL user credentials supplied when executing the query by using the virtual directory configured in the above steps.

Follow the instructions from Microsoft to solve the issue: http://support.microsoft.com/kb/820874/en-us

What I did was, created a separate login in SQL Server (Under Security à Logins) and provided this newly created SQL-User in the SQLXML configuration as specified in the article.

After the above, I received the

Error 2:

“Login failed for user ‘UserNameJustCreated’. The password of the account must be changed.”

Then I executed the following query

ALTER LOGIN UserNameJustCreated

WITH PASSWORD = 'password123',

CHECK_POLICY = OFF,

CHECK_EXPIRATION = OFF

Then I had the following error

Error 3:

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON

Executed the following to solve this error

ALTER LOGIN UserNameJustCreated WITH PASSWORD = 'password123'

GO

ALTER LOGIN UserNameJustCreated WITH

      CHECK_POLICY = OFF,

      CHECK_EXPIRATION = OFF;

(So I should have not executed the query as specified in the Error-2 section)

Then when I executed the SQL query hoping it would work, but I received another error

Error 4:

XML document must have a top level element. Error processing resource 'http://nfp-util01/MsgView?sql=select%20LongReference...

To know the exact error, select “View Source”  option and following is the error:

<?MSSQLError HResult="0x80040e09" Source="Microsoft OLE DB Provider for SQL Server" Description="The SELECT permission was denied on the object 'bam_HireExtension_AllRelationships', database 'BAMPrimaryImport', schema 'dbo'."?>

So the error was, it required the execute permission for the SQL-user (UserNameJustCreated) I have created in the one of the above step. Gave the execute permission, then I can see the XML in the browser as a sweety J

Error: Exception has been thrown by the target of an invocation

In our BizTalk project, I have used a C# helper class, whose method uses Microsoft’s Enterprise Library to invoke a SQL-stored procedure. The SQL stored procedure does some data validation and raises errors for business validation errors using its RAISERROR keyword.

When our tester was testing the above application, they had this strange error

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> <<SQL Server’s error message>>

But the expected error is the <<SQL Server’s error message>> only  which has been appended wrongly with the strange error “System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation”

After spending sometime, I found out that the problem could be due to “Instrumentation” in Enterprise Library. So I installed the “Instrumentation” from

Start -> All Programs -> Microsoft pattern & practices -> Enterprise Library 3.1 – May 2007-> Install Instrumentation

It solved the issue.

Resources on testing with BizTalk Server

Michael Stephenson has shared his resources and reference on testing with BizTalk Server.

http://code.msdn.microsoft.com/BTSTestGuide

Thanks Michael.

Cheers

Powershell providers for BizTalk Server

I was reading the latest edition of BizTalk Hotrod magazine, one interesting article which captured my eye was PowerShell provider for BizTalk developed by Maxime Labelle, Michel Hubert and Randal van Splunteren.(Page 42 onwards: http://biztalkhotrod.com/Documents/Issue8_Q4_2009.pdf)

Microsoft also announced its out of box support to PowerShell for management tasks in its BizTalk Server 2009 R2 release which is expected this year. (http://www.microsoft.com/biztalk/en/us/roadmap.aspx#2009r2)

Interesting to see Microsoft’s effort to next generation technologies to replace scripting languages and batch files for the management task. The obvious benefits of PowerShell over the scripts and batch file is object oriented programming model and much better debugging facilities.

I am going to try the PowerShell provider for my BizTalk Server 2006 projects before Microsoft’s PowerShell providers in 2009 R2.

BizTalk (BRE)Business Rules Engine “Failed to Load” error

One of the common error, we would encounter while working with BRE –Business Rules Engine is “Failed to Load”. Most possible cause for this error is the reference assembly is missing in GAC. It’s obvious to identify this cause, as the reason is given very descriptive as shown.

I had this same issue, so GACed the missed the assembly, then reloaded the policy (either by pressing F5 or by physically re-loading the policy). But the error still persisted. Then it reminded me the IDE cache error we all would have encountered during the pipeline development. So the solution is “Just close and open the IDE-Visual Studio again”

I am sure this no-blogging error, but for me. As I have mentioned in my profile, this blog is a knowledge base repository for me, where I store all the things I learn at work so that I can reference latest. If its useful for you as well, I am happy otherwise you have the whole world of web to spend.