I have seen several posts a more dynamically changeable SQL connection strin
g
in functoids, but not in the SQL Adapter (send port).

We tried the following experiment:

1) We created sendport with SQL adapter and used the GUI tool which builds
the connections string.   We also used the "add generated item" and built an
update gram to insert records into a SQL table.  Everything works fine, we
drop a file, and the items appear as a new row in the database table.

2) We ran the deployment wizard with the export binding file option.

3) We carefully edited the binding file changing the "hard-coded" connection
string with the following type syntax: "file name=C:\mypath\myudl.udl" in
order to hopefully connect using a UDL file instead.  Below is the actual XM
L
we changed.

<TransportTypeData><CustomProps><AdapterConfig vt="8">&lt;Send
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;connectionStrin
g&gt;file
name=C:\mypath\myudl.udl&lt;/connectionString&gt;&lt;documentTargetNamespace&gt;ht
tp://Diamond.com/Sqldemo&...t;/Send&gt;</AdapterConfig></CustomProps></TransportTypeDat
a>

5) We reran the deployment wizard and verified that the "file
name=C:\mypath\myudl.udl"  was in the connection string field (which of
course cannot be edited manually in the Biztalk Server Explorer tool).

6) We get error: 0x80070057 (which seems to be the general "trouble in SQL
land" message.  App Event log gives useless message:

The "SQL" adapter is suspending an outbound message going to destination
URL:"SQL://./Northwind/". Details:"Unknown Error Description ".

So I'm looking for either
1) How to get the above scenario work
OR
2) What is a better way to handle changing the connection string from test
to prod.

One possibility I have considered is a migration tools that reads the
binding file and does a replace on connection strings from test to prod.

Thanks in advance for any ideas.

Neal Walters
http://Biztalk-Training.com  - Free Biztalk Training Videos

[ Post a follow-up to this message ]

On Wed, 25 Aug 2004 12:21:04 -0700, Neal Walters wrote:

> I have seen several posts a more dynamically changeable SQL connection str
ing
> in functoids, but not in the SQL Adapter (send port).
>
> We tried the following experiment:
>
> 1) We created sendport with SQL adapter and used the GUI tool which builds
> the connections string.   We also used the "add generated item" and built 
an
> update gram to insert records into a SQL table.  Everything works fine, we
> drop a file, and the items appear as a new row in the database table.
>
> 2) We ran the deployment wizard with the export binding file option.
>
> 3) We carefully edited the binding file changing the "hard-coded" connecti
on
> string with the following type syntax: "file name=C:\mypath\myudl.udl" in
> order to hopefully connect using a UDL file instead.  Below is the actual 
XML
> we changed.
>
>         <TransportTypeData><CustomProps><AdapterConfig vt="8">&lt;Send
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;connectionStr
ing&gt;file
> name=C:\mypath\myudl.udl&lt;/connectionString&gt;&lt;documentTargetNamespace&gt;
http://Diamond.com/Sqldemo&...t;/Send&gt;</AdapterConfig></CustomProps></TransportTypeD
ata>
>
> 5) We reran the deployment wizard and verified that the "file
> name=C:\mypath\myudl.udl"  was in the connection string field (which of
> course cannot be edited manually in the Biztalk Server Explorer tool).
>
> 6) We get error: 0x80070057 (which seems to be the general "trouble in SQL
> land" message.  App Event log gives useless message:
>
> The "SQL" adapter is suspending an outbound message going to destination
> URL:"SQL://./Northwind/". Details:"Unknown Error Description ".
>
> So I'm looking for either
> 1) How to get the above scenario work
> OR
> 2) What is a better way to handle changing the connection string from test
> to prod.
>
> One possibility I have considered is a migration tools that reads the
> binding file and does a replace on connection strings from test to prod.
>
> Thanks in advance for any ideas.
>
> Neal Walters
> http://Biztalk-Training.com  - Free Biztalk Training Videos

Hi Neal,

Please see this post on Scott Woodgate's blog for a helpful tool:

http://blogs.msdn.com/scottwoo/arch.../09/151779.aspx

either combine it with NAnt, or create a CustomAction in the MSI installer
you use to deploy your files.

I myself use NAnt to patch my binding files, create MSI packages and zip
those up for easy deployment through e-mail to a client.

BTW, internally the SQL adapter uses Microsoft.Data.SqlXml.SqlXmlCommand to
connect, passing the connection string you've configured to the
constructor. I don't think that would work...

HTH,

Martijn Hoogendoorn

[ Post a follow-up to this message ]