Jeff Lynch [MVP]

Sponsors

The Lounge

Syndication

Recent Posts

Tags

View more

News

BizTalk Bloggers

Commerce Server Bloggers

Microsoft Heros

Team System Bloggers

Archives

Advertisement

Jeff Lynch [MVP] » BizTalk Server 2004: SQL Adapter Tips
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
BizTalk Server 2004: SQL Adapter Tips

Tip #1: Be Very Careful Naming Your SQL Objects

This one caught me completely by surprise! Having successfully run through the SQL Adapter (BizTalk Server Sample) in the SDK, I thought I had the basics down pretty well but for some mysterious reason I couldn't get the SQL Adapter to insert records into my database table. The error "XML encoding or decoding error occurred with a SQL object name" was rather cryptic as well. I posted this to the public newsgroups with no luck until I found this little tidbit of gold in the documentation.

Database object names (tables, columns, stored procedures, and parameters), which are retrieved from sysobjects need to be scanned for the following special characters.
! @ # $ % ^ & * ( ) _ - + = { [ } ] | \ : ; " ' < , > . ? / ~ `
If one or more are present, add an XSD restriction to the schema.

Not being an expert in XSD, I did the next best thing and changed my table's column names from "po_date" to "podate", revised the schema to match and now everything works great!

Tip #2: Beware of Date/Time Formats

Another one that caught me by surprise! The SQL Send Adapter (I suspect this is due to SQLXML) requires your date/time formats to be in a very specific format as shown below or the conversion will fail.

1999-05-31T13:20:00.000

To accomplish this I use a Scripting Functoid in my map using the Inline C# code shown here.

public string SQLDate(string strMyDateTime)
{
   System.DateTime myDateTime = System.DateTime.Parse(strMyDateTime);
   string curdate = myDateTime.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);
   string curtime = myDateTime.ToString("T",System.Globalization.CultureInfo.InvariantCulture);
   string retval = curdate + "T" + curtime + ".000";
   return retval;
}

Anyone care to explain why the SQL Adapter can't just convert from the xs:date to the SQL datetime format?

Posted 08-03-2004 6:44 PM by Jeff Lynch
Filed under:

[Advertisement]

Comments

Alan Smith wrote re: BizTalk Server 2004 - SQL Adapter Tips
on 08-31-2004 9:17 PM
Hi,

I'm getting really poor performance when looping through a message, and calling an SP to insert data with the adapter. It runs at about one update per second. I tested calling a simple C# DAL from the loop, with the same SP, and it ran at 500 inserts per second.

Is this a known issue with the SQL adapter, and is it my design that is bad, or is there a way to get the performance up to something repectable.

Cheers,

Alan
Jeff Lynch wrote re: BizTalk Server 2004 - SQL Adapter Tips
on 09-01-2004 12:47 AM
I'm currently using a simple updategram to insert 200 records in one table in just under 10 milliseconds. Try running a SQL trace to see if you are getting into a locking problem. Make sure you aren't calling your sproc multiple times in an orchestration loop.
Curtis Herrick wrote re: BizTalk Server 2004 - SQL Adapter Tips
on 02-22-2005 3:16 PM
Hi,
My company's corporate databases have many many columns with underscores in them (as a standard). I've seen the same tidbit on "adding an XSD restriction", but haven't been able to make sense of it. What are they talking about, and how / where would you do that?

Another Link I found on the same topic:
http://groups-beta.google.com/group/microsoft.public.biztalk.appintegration/browse_frm/thread/c4f281b28961b7d5/a2070f8e3c9f39ce?q=BizTalk+SQL+xsd+restriction&_done=%2Fgroups%3Fq%3DBizTalk+SQL+xsd+restriction%26&_doneTitle=Back+to+Search&&d#a2070f8e3c9f39ce

Any additional info you've found would be greatly appreciated!
- Curtis
Alex Bögli wrote re: BizTalk Server 2004 - SQL Adapter Tips
on 10-21-2005 5:24 AM
There's no need for the ".000" at the end. All you have to do is parse the datetime and format it to the SortableDateTimePattern (which happes to be what the adapter likes).

So the method then looks like

public string SQLDate(string strMyDateTime)
{
DateTime dateTime = DateTime.Parse(strMyDateTime, CultureInfo.InvariantCulture);
return dateTime.ToString("s");
}

Cheers,
Alex

Add a Comment

(required)  
(optional)
(required)  
Remember Me?