|
Home > Archive > BizTalk Server General > August 2004 > Connection String for SQL Adapter
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Connection String for SQL Adapter
|
|
| Neal Walters 2004-08-25, 5:54 pm |
| I have seen several posts a more dynamically changeable SQL connection string
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 XML
we changed.
<TransportTypeData><CustomProps><AdapterConfig vt="8"><Send
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><connectionString>file
name=C:\mypath\myudl.udl</connectionString><documentTargetNamespace>http://Diamond.com/Sqldemo</...t;/Send></AdapterConfig></CustomProps></TransportTypeData>
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
| |
| Martijn Hoogendoorn 2004-08-25, 5:54 pm |
| On Wed, 25 Aug 2004 12:21:04 -0700, Neal Walters wrote:
> I have seen several posts a more dynamically changeable SQL connection string
> 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 XML
> we changed.
>
> <TransportTypeData><CustomProps><AdapterConfig vt="8"><Send
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><connectionString>file
> name=C:\mypath\myudl.udl</connectionString><documentTargetNamespace>http://Diamond.com/Sqldemo</...t;/Send></AdapterConfig></CustomProps></TransportTypeData>
>
> 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
|
|
|
|
|