As I was attempting to generate some schemas in BizTalk based on the results of a stored proc, I was having problems passing the necessary parameters to the procedure - even though I specified valid param values in the wizard, the generated SQL script always passed NULL for every parameter every time. Here's how the params are initially set in the wizard once you select the desired proc:
...and once you check the value box, the string NULL is initially populated as the value.
Now, here's where the problem occurs - if you set the values you actually want, and click generate, the generated script still has null values for all params, like
EXEC UpsertGlobalHeader @ContentType=NULL, @CustomerId=NULL,...
even though valid values are specified:
I finally found the answer here. Apparently, checking the checkbox in the Value column means set to NULL no matter what value you enter in the text area. So you need to UNcheck the value box, enter the text value you want for that param's value, and click generate. That sets the desired value in the script. In the case below, the @ContentType param would be set to 'PROD', all others would be still set to NULL.
http://msdn2.microsoft.com/en-us/library/aa578440.aspx
September 04, 2007
BizTalk SQL Adapter schema generation wizard confusion
Subscribe to:
Post Comments (Atom)
That's about as unintuitive as they come.
ReplyDeleteHi,
ReplyDeletei have done a application using sql adapter with storproc but at the end of wizard addgenerated items it will be closed un expectedly and doesn't generate any schema r orchestration so plz let me know wat is the problem ?
thanks inadvance
bye
kumar
The first two things I would check are:
ReplyDeletea: Make sure the stored procedure returns a valid recordset with the parameter values you are sending.
b: Make sure the stored procedure has the For XML Auto, XMLData clause at the end. (After the schemas are generated remove the XMLData keyword)
Good luck.
This probelm has happened with me too, even when a) and b) above where satisfied.
ReplyDeleteMy solution was to generate these things in a new solution and BizTalk project. Strangely, that works fine and I can go ahead and import them in to the master BizTalk project.