Rob Bowman

Generate SQL Server Test Data

2011-10-11T11:50:23-05:00:00

I came across (thanks Paul) a great way to quickly generate test data for SQL Server today.

DECLARE @rows INT, @a int

SET @rows = 50

SELECT * INTO dbo.RobTest FROM

(SELECT TOP (@rows)

Rownumber = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),

SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,

SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY) ,

SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 AS DATETIME) ,

SomeHex12 = RIGHT(NEWID(), 12),

TrueRandomZerotoOne = RAND(CHECKSUM(NEWID()))

FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 CROSS JOIN sys.all_columns ac3

) AS a

XML Namespaces

2009-11-24T16:43:47-06:00:00

I've been working with XML for several years now but I've never been entirely sure that I fully understand all there is to know about namespaces. Of course namespaces are central to how document instances are defined by BizTalk, so I certainly had some understanding. However, it's quite easy to just "get by" without a complete understanding because of the inteli-sense and other helpful features provided by tools like Visual Studio and XML Spy. If I'd had only notepad in which to create the XSDs and XML documents then I'm sure I would have been stuck.

Anyhow, this has been bothering me for a while, so today I decided I was going to learn all there is to know about XML namespaces - and make it stick! This learning involved reading sections from the excellent book "The XML Schema Companion" by Neil Bradley, and otherwise messing around with XSDs and XML documents in Visual Studio.

Key Points

It is possible to create an XSD that does not define a namespace. Such schemas can be used to validate unqualified XML documents. Below I have pasted a simple XSD that does not define a namespace:

<?xml version="1.0" encoding="UTF-8"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

<xs:element name="RootNode">

<xs:annotation>

<xs:documentation>Comment describing your root element</xs:documentation>

</xs:annotation>

<xs:complexType>

<xs:sequence>

<xs:element name="Name"/>

<xs:element name="Age"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

I then used XML spy to create an XML document instance from the above XSD, this is what it came up with:

<?xml version="1.0" encoding="UTF-8"?>

<Name/>

<Age/>

</RootNode>

Note, the root node has the attribute "xmlns:xsi", this is simply defining a prefix for a namespace - it is not setting the document level namespace. In effect it is saying that elements, attributes etc from the namespace http://www.w3.org/2001/XMLSchema-instance can be used by prefixing XSI, however where there is no prefix then the element, attribute etc does not belong to a namespace - it is unqualified. The attribute noNamespaceSchemaLocation is interesting. This gives the URL reference to the XSD that will be used when validating the XML document. It also infers that the XSD does not define a namespace - and that this document is not "namespace qualified" at document level.

I then went back to the XSD and defined a namespace for it by adding a "TargetNamespace" attribute to the root node as follows:

<?xml version="1.0" encoding="UTF-8"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified" targetNamespace="http://samplenamespace">

<xs:element name="RootNode">

Etc...

I then used XML spy to generate a new sample XML instance document from this, updated XSD - this is what it came up with:

<?xml version="1.0" encoding="UTF-8"?>

<RootNode xmlns="http://samplenamespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://samplenamespace

D:\Rob\LearnXMLNamespaces\Person.xsd">

<Name/>

<Age/>

</RootNode>

A couple of things have changed:

The attribute xmlns has been added to the root node and assigned the value "http://samplenamespace". This specifies that, unless explicitly qualified with a different namespace, all elements and attributes defined with the XML instance belong to the namespace "http://samplenamespace"
The attribute schemaLocation (which happens to belong to the xsi schema instance namespace) has been added to the root node. The attribute contains a pair of values, the name of the namespace defined by the schema followed by a space then a URL reference to the XSD that defines the namespace

The above just skims the surface of what can be done with namespaces but in does provide the basics. There are many cases where an XML document will contain items defined in multiple namespaces. However, understanding such a document is straight-forward once you are aware of the key concepts and implementation as described above.

Update: 4th April 2012, had some more fun with XSDs today. Must remember, adding xmlns="x" to an XSD sets the namespace for that XSD; setting targetNamespace for an XSD gives the namespace for which other XSDs and XML can reference with an Import.

While creating the new schemas I came across a problem when trying to validate what seemed to be valid XML. Under the root element was <Invoice> , on attempting a validate from within XML Spy I received the message: "Unexpected element 'Invoice' in element 'InvoiceBatch'. Expected: Invoice". It turned out that this was because I had removed "elementFormDefault='qualified'", once I added this back in then it validated OK.

www.biztalkers.com

Scripting Perfmon

2009-07-08T15:17:05-05:00:00

Scripting Perfmon

We have a test team in India that I would like to run some load and performance testing. They have access to the required test server and load tools but limited experience using tools such as perfmon.

I have previously only used Perfmon to take a real-time view of performance counters, or used the ui to configure logging to a data file for later evaluation. In this particular case I didn't have access to the test server to enable me to manually configure the logging, and I know that trying to walk someone through this on the phone would be painful. After a little searching the web I discovered that it's possible to script the perfmon counter logging - exactly what I needed!

I started by creating a config file containing the counters that I wished to monitor. I called this baseling.cfg . It simply contains a unique line for each counter to be logged, as follows:

"\Memory\Available MBytes"

"\Memory\Pool Nonpaged Bytes"

"\Memory\Pool Paged Bytes"

"\PhysicalDisk(*)\Current Disk Queue Length"

"\PhysicalDisk(*)\Disk Reads/sec"

"\PhysicalDisk(*)\Disk Reads Bytes/sec"

"\PhysicalDisk(*)\Disk Writes/sec"

"\PhysicalDisk(*)\Disk Writes Bytes/sec"

"\Process(*)\% Processor Time"

"\Process(*)\Private Bytes

"\Process(*)\Virtual Bytes"

I then created a batch file containing the required command line to setup the data capture. I called the file create_data_collector.bat, it contains the following:

logman create counter BASELINE -f bincirc -max 200 -si 00:00:05 --v -o "c:\perfmon\serverbaseline" -cf "c:\perfmon\baseline.config"

To find out what the various parameters mean and other options available just run a quick google for 'logman'. In my particular case:

I am creating a data collector called 'BASELINE', logging to file up to a max size of 200mb
The file is binary circular file, meaning that once it reaches maximum size it will begin overwriting the oldest records within the file
The log is to be updated every 5 seconds
The file will be output to the folder c:\perfmon\serverbaseline
Counters to include in the log are contained within the previously mentioned configuration file: "c:\perfmon\baseline.config"

On first running the batch file locally (to test it) I received a message telling me that the data capture vehicle couldn't be created because of a permissions issue. I adjusted the permissions to the logging folder "c:\perfmon\serverbaseline" and all was good.

All that remained was to script to start the data collection. For this I created the batch file start_collector.bat, containing the following single line:

logman.exe start baseline

A few minutes after starting the logging I could see the log file beginning to grow. Great, I thought! I went back to the perfmon ui, clicked the 'view log data' button (looks like a db drum) and selected my new and continually growing log file. It didn't complain, and the current data view was cleared - but where was the historic data that should be been logged? I opened the log file in notepad - just binary data. I wanted to check that it was logging the data I expected so I deleted the BASELINE data collector, then re-created, choosing to log to text file, rather than binary. I then started logging, gave it a few minutes and opened the newly logged data in notepad. It appeared just as I expected, with column headers for my counters and lots of data for each subsequent row.

Great - I was logging the data but couldn't get it to appear in the perfmon graph! Then it struck me, in addition to choosing the data file as the data source for the perfmon graph I needed to add the counters to the graph. I hit the add button on the toolbar (+ symbol) and was presented with a dialog offering a limited set of counters - just the ones I'd specified in the baseline.cfg file. After selecting the counters, my historic data was displayed in the graph. Such a result called for celebratory cup of tea!

Addition

I've now also started working on a script to run a SQL profiler trace. My initial results were disappointing, the trace file was generated but my filters were not applied.

I had used the profiler UI to generate the script: file\export\script trace definition. Unfortunately, it seems that there's a bug in SQL 2005 which means that the script generated by this option will OR each of your filters. For details see http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic60213.aspx

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=258393

www.biztalkers.com

Slow File Receive

2009-06-02T18:08:28-05:00:00

I encountered a problem a few days ago with files not being picked from a receive location. The configuration was very simple, a two node group with a file receive location pointing at a share on a windows 2003 server in a different domain. The volume of files being dropped for collection by BizTalk wasn't high - about ten per minute. The behaviour was; files collected very slowly - say one file every five minutes. If I re-started the host instance then all the remaining files would be collected very quickly but ten minutes later it would be back to slow mode.

After digging around the blogs, it occurred to me that the problem may be due to BizTalk throttling the receive host. One very helpful blog I used to determine this is at http://blogs.msdn.com/biztalkcpr/default.aspx

It is quite simple to identify if throttling is being applied. Run perfmon on the BizTalk box and add counters for "BizTalk:Message Agent - Message publishing throttling state" (publish to msgbox) and "Message delivery throttling state" (delivery to endpoint) . Ensure that you select the host instance hosting your receive port.

The following tables describe what throttling, if any is active:

Message delivery throttling state

A flag indicating whether the system is throttling message delivery (affecting XLANG message processing and outbound transports).

0: Not throttling
1: Throttling due to imbalanced message delivery rate (input rate exceeds output rate)
3: Throttling due to high in-process message count
4: Throttling due to process memory pressure
5: Throttling due to system memory pressure
9: Throttling due to high thread count
10: Throttling due to user override on delivery

Message publishing throttling state

A flag indicating whether the system is throttling message publishing (affecting XLANG message processing and inbound transports).

0: Not throttling
2: Throttling due to imbalanced message publishing rate (input rate exceeds output rate)
4: Throttling due to process memory pressure
5: Throttling due to system memory pressure
6: Throttling due to database growth
8: Throttling due to high session count
9: Throttling due to high thread count
11: Throttling due to user override on publishing

After configuring perfmon I could see that I had a counter value of 6 for the publish state - the receive host was being throttled due to database growth. I needed to take a look at the MsgBox!

After posting the question on StackOverflow it was suggested that I run the MsgBox viewer application against the BizTalk db (thanks Chris). This is a great tool, it can be downloaded at

http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx. After running the MsgBox viewer I was presented with a report which re-enforced the case that the BizTalk was running low on DB space.

On checking the MsgBox db through SQL workbench I could see that it was just over 4gb in size and had 0 mb available! The db was set to autogrow and the disk hosting the db had plenty available space to grow. The problem was the rate at which the db would grow. This can be checked by right-clicking the db from the SQLWB object explorer selecting properties \ files. Both the data and log was set to grow by 1mb. This value is set based on the value of the model database at the time that the BizTalk databases are created. One to remember for the future - ensure the growth is set to a more reasonable chunk -10% would be good.

www.biztalkers.co.uk

BizTalk Assembly Viewer

2009-05-19T12:22:44-05:00:00

I'm currently preparing to deliver Microsoft Course 2934A - Deploying and Managing Business Process and Integration Solutions Using Microsoft BizTalk Server 2006.

So far, I've been impressed with the content, it's well structured and I think the topic selection has been good.

One benefit of delivering a course is that you're bound to run through every lab before-hand, there's nothing worse that the trainer getting stuck on a lab! This is turn encourages reading on a topic that you may otherwise skip, thinking that you already know about it or that's it's not relevant to your day job. I ran through such a lab today and learned of a feature of BizTalk that I'm sure will use in future. The feature is the BizTalk Assembly Viewer, here's the instructions for setup:

Open a command prompt window, navigate to BizTalk Server Install Director\Developer Tools\
Run regsvr32 BtsAsmExt.dll

After registering the dll, load windows explorer and double-click my computer. In the Other section, you'll see system folder called 'BizTalk Server Assemblies'. Open this up and you'll see all of the BizTalk assemblies which have been deployed to the local GAC. From here, you're able to drill into each assembly to display an entry for each artefact that it conatins. At the next level down you can double click the artefact to view the XML behind each schema, map or orchestration - nice!

www.biztalkers.co.uk

More Haste Less Speed - BizTalk Builds

2009-04-29T20:23:47-05:00:00

I know it's never a good idea to try and "rush" a build but I did just that the other day and fell victim to unexpected behaviour on the part of the BizTalk admin console. A critical defect had been found in a BizTalk map and a new release had to be deployed ASAP. Ordinarily all of our BizTalk builds are run through a script - nothing fancy (yet!), just using batch files and BTSTask. However, because I only needed to deploy one of the BizTalk apps in a single MSI I chose to right-click and select ExportMSI from the BizTalk admin console, rather than wait for the scripts to run or edit them so that only the required app would be built. All looked fine; the MSI was exported, including the binding files that had been added previously using AddResource.

The problem came when I tried to import the MSI onto the test server. An error message was displayed complaining that a particular send port was attempting to use a host that didn't exist. My first reaction was to rush back to my dev machine and check the binding file for the particular environment I had selected when importing the MSI. However, this looked fine, the send port in question should not be attempting to use the host that the error message was complaining about – or so I thought!

After talking my colleague (thanks again Brad) through the process I saw a light bulb flash over his head. Thankfully he'd seen the same problem previously. The issue was caused by the fact that the send port had been manually created on the dev box within the admin console. This existed in addition to the setting for the port contained within the binding file of the application resources. So, it seems that on attempting the Import, BizTalk was first trying to apply settings for the send port that were pulled directly from the application on the build machine, despite the fact that the same send port was defined in the binding file resource. On reflection, I would say that this is to be expected. Taught me a valuable lesson, always use scripts to build you MSIs - humans make mistakes!

www.biztalkers.co.uk

BTSTask - Import Bindings

2009-02-17T20:18:36-06:00:00

I have a simple batch file that executes BTSTask ImportBindings on a previously exported binding file. There don't seem to be any errors thrown. The send / receive ports and orchestrations are created ok. The problem is that the orchestrations are not bound and the hosts are not set.

When importing the same binding file through BizTalk Admin Console it goes in no problem.

My colleague (thanks Brad) found the answer to this - it relates to the <Filter> node in the binding file. I think the problem was caused by opening the binding file from XML Spy at some point, then hitting the "Pretty Print" button. This has inserted spaces within the filter node which BizTalk seems to be unhappy with. More details can be seen on the StackOverflow thread: http://stackoverflow.com/questions/483116/btstask-importbindings

www.biztalkers.co.uk

BizTalk Orchestration Opens in Plain Text

2008-12-29T17:48:47-06:00:00

Had a strange problem this morning. I'd installed a BizTalk hotfix for the BizTalk Map TFS issue. All seemed to be ok until I tried opening an orchestration - VS displayed the plain text rather than the orchestration designer. When I right-clicked the odx file and selected 'open with', there was no option to select 'BizTalk Orchestration Designer'.

To fix the problem I ran a repair from the BizTalk installation DVD.

www.biztalkers.co.uk

Password in SOAP Ports

2008-11-28T14:31:01-06:00:00

I've inherited a BizTalk 2006 applicaton that uses serveral SOAP ports to request data from a 3rd party web service. The web service is secured by "basic" authentication - username / password. After making a few enhancements to the application I deployed to an integration test server which has access to the 3rd party web service. The BizTalk app was unable to retrieve the data and I soon realised that I had forgotten to set the username / password on the SOAP send ports. I wanted the make deployment of the BizTalk app as automated as possible because I may not be present when it is deployed to the live server. I opened up the binding file, located the 1st of the problem SOAP send ports and looked for the *** that BizTalk uses to replace the password - except that it doesn't! It seems that the password for SOAP ports is set to NULL rather than ***, see here for more details:

http://msdn.microsoft.com/en-us/library/aa547319.aspx

I proceeded to update the binding but when I came to test, after importing my amended binding file, I found that I had the same problem as before. I've double checked and can confim that the correct password is now present in the binding file but, although BizTalk doesn't complain during the import, when I run the app I get the following exception:

Details:"ArgumentNullException: String reference not set to an instance of a String.
Parameter name: s
".

If I then manually amend the password through the BizTalk admin console everything worked fine.

By this point, my binding file looked something like the following:

<TransportTypeData> <CustomProps> <AuthenticationScheme vt="8">Basic</AuthenticationScheme> <AssemblyName vt="8">WebService.ProxyClass, Version=1.0.0.0, Culture=neutral, PublicKeyToken=xyz</AssemblyName> <Username vt="8">soapUser</Username> <UseProxy vt="11">0</UseProxy> <UseSoap12 vt="11">0</UseSoap12><UsingOrchestration vt="11">0</UsingOrchestration> <UseSSO vt="11">0</UseSSO> <Password vt="1">MYPASSWORD</Password> <ProxyPort vt="3">80</ProxyPort><AssemblyPath vt="8">C:\ProxyClass\bin\Debug\ProxyClass.dll</AssemblyPath> <TypeName vt="8">ProxyClass.Webservice.servicesService</TypeName> <MethodName vt="8">PickupRequest</MethodName> <UseHandlerSetting vt="11">-1</UseHandlerSetting> </CustomProps> </TransportTypeData>

After posting a question onto www.stackoverflow.com I thankfully received the answer from Chris Loris - thanks Chris. You see the vt attribute on the Password node? It turns out that vt=1 equates to NULL therefore BizTalk doesn't bother reading the value given for password. I changed the attribute to vt=8 and all worked fine!

ps at vt value of 8 equates to a BSTR

www.biztalkers.co.uk

Parameters lost in SOAP Request

2008-11-21T12:26:06-06:00:00

I started testing a BizTalk application today which calls a 3rd party service, passing a couple of datetime parameters, to retrieve a dataset which is then persisted to a SQL Server database. This application had been running well in production for a while but enhancements were required to make use of web methods recently made available by the 3rd party. After making the changes I tested the BTS application on my dev box, calling a stubbed web service installed locally. I then deployed the BizTalk app to an integration test environment that has connectivity to the 3rd party web service. On running the application I started to receive exceptions such as the following:

SoapException: Server was unable to process request. ---> SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

It seems that for some reason the send port was removing / nulling the web method parameters comprising datetimes. After a fair amount of work to try an resolve, a colleague suggested I delete the SOAP send port, re-create and re-bind to the parent orchestration. This worked a treat - thanks Fabio :)

Not knowing why re-creating the send ports fixed the problem was keeping me awake at night. To find out what was going on, I exported the bindings from pre-fix and post-fix applications and ran a file compare. I could see that the only difference was in the element <PrimaryTransport><TransportTypeData><TypeName>. I then went back to the configuration page of the SOAP send port. On clicking the "web service" tab the problem became apparent. There's a "Type name: list box with two options; one to use the web reference for the live site and one for the test test stub site. The problem was caused by the fact that, on my integration test server, although I was calling the real 3rd party web service, the "type name" was still set to use the test web reference type. I guess this is an easy mistake to make; you set the target address on the "general" tab of the SOAP port configuration dialog box, whereas the web reference that the port will use for sending data to that address is located on the "web service" tab.