Create a DSN programmatically with network options

Hello,
I would like to create a brand new system DSN, programmatically,
targeting an MS-SQL Server 2000 database, and with networking options
(pipes,TCP/IP, etc.).
Until now the only solutions I managed to find on google.groups or
forums are:
1- The usual "Why don't you do it manually ?" and other classical
off-topic answers.
2- The "Dump the registry keys in HKLM\Software\ODBC etc." solution.
Relying on this single registry entry is not enough since it only
configures the DSN registration, not its advanced connection options (at
least for SQL Server). Particularly, networking options cannot be set
this way. With Sysinternals RegMon and some additional MSDN
documentation I finally understood that some stuff was written in
"HKLM\Software\Microsoft\MSSQLServer\...\ConnectTo". My problem is I
have not enough documentation on the value of these keys (plus the ones
in \TDS) to write a robust script.
3- I cannot find anyone making the odbcconf.exe thing to work. Anyway, I
think it would be useless for my purposes.
4- Solutions with ODBC API calls or Registry API calls which just
replicates one of the broken methods detailed above.
So my question is : is there any robust, efficient, professional
solution (I usually do not consider registry wizardry to fulfill these
criterions but I will take whatever you may propose) to export one SQL
Server DSN configuration from one machine to another ? Or is it hopeless
and someone in Microsoft definitely miss something here ?
By DSN configuration, I mean everything which can be set via the ODBC
administrator interface. And the export method should optimally not
require more knowledge (ie. parameters) than the one passed to the
administrator GUI.
I easily understand that some of these parameters are machine/domain
dependant, but there should be a way to edit them via scripts (perl
regexp or anything else) if the configuration files happened to be text
ones. Otherwise, the export would be meaningless.
I hope that what I am asking makes some sense. Maybe not, I might
overlook some SQL Server configuration issues here.
Thanks for any hints.
Patrick MzardThe following articles can provide a couple of different
methods as well as more information on creating DSNs
programmatically:
HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
HOWTO: Set Up ODBC Data Sources When Distributing Apps
http://support.microsoft.com/defaul...kb;EN-US;123008
-Sue
On Mon, 01 Mar 2004 20:20:47 +0100, Patrick Mzard
<patrick.mezard@.ifrance.com> wrote:

>Hello,
>I would like to create a brand new system DSN, programmatically,
>targeting an MS-SQL Server 2000 database, and with networking options
>(pipes,TCP/IP, etc.).
>Until now the only solutions I managed to find on google.groups or
>forums are:
>1- The usual "Why don't you do it manually ?" and other classical
>off-topic answers.
>2- The "Dump the registry keys in HKLM\Software\ODBC etc." solution.
>Relying on this single registry entry is not enough since it only
>configures the DSN registration, not its advanced connection options (at
>least for SQL Server). Particularly, networking options cannot be set
>this way. With Sysinternals RegMon and some additional MSDN
>documentation I finally understood that some stuff was written in
>"HKLM\Software\Microsoft\MSSQLServer\...\ConnectTo". My problem is I
>have not enough documentation on the value of these keys (plus the ones
>in \TDS) to write a robust script.
>3- I cannot find anyone making the odbcconf.exe thing to work. Anyway, I
>think it would be useless for my purposes.
>4- Solutions with ODBC API calls or Registry API calls which just
>replicates one of the broken methods detailed above.
>So my question is : is there any robust, efficient, professional
>solution (I usually do not consider registry wizardry to fulfill these
>criterions but I will take whatever you may propose) to export one SQL
>Server DSN configuration from one machine to another ? Or is it hopeless
>and someone in Microsoft definitely miss something here ?
>By DSN configuration, I mean everything which can be set via the ODBC
>administrator interface. And the export method should optimally not
>require more knowledge (ie. parameters) than the one passed to the
>administrator GUI.
>I easily understand that some of these parameters are machine/domain
>dependant, but there should be a way to edit them via scripts (perl
>regexp or anything else) if the configuration files happened to be text
>ones. Otherwise, the export would be meaningless.
>I hope that what I am asking makes some sense. Maybe not, I might
>overlook some SQL Server configuration issues here.
>Thanks for any hints.
>Patrick Mzard|||> The following articles can provide a couple of different
> methods as well as more information on creating DSNs
> programmatically:
> HOWTO: Create and Remove a DSN in Visual Basic
> http://support.microsoft.com/defaul...b;EN-US;q171146
> HOWTO: Programmatically Create a DSN for SQL Server with VB
> http://support.microsoft.com/defaul...b;EN-US;q184608
> HOWTO: Set Up ODBC Data Sources When Distributing Apps
> http://support.microsoft.com/defaul...kb;EN-US;123008
> -Sue
Thank you for the links but I have already read these articles and they fall
in the section 4 category described in my original post: API calls (via VB)
which do not provide more options than the basic registry tricks. Or maybe I
really missed something, could you configure the SQL Server network library
to use with these methods ?
Patrick Mzard|||Yes - It's one of the driver attributes you can supply, e.g.
in the first example, you can add the following to the
strAttributes:
Network=DBMSSOCN (tcp/ip)
or
Network=DBNMPNTW (named pipes)
You have to set the address accordingly.
-Sue
On Tue, 2 Mar 2004 06:51:46 +0100, "Patrick.Mzard"
<patrick.mezard@.ifrance.fr> wrote:

>Thank you for the links but I have already read these articles and they fal
l
>in the section 4 category described in my original post: API calls (via VB)
>which do not provide more options than the basic registry tricks. Or maybe
I
>really missed something, could you configure the SQL Server network library
>to use with these methods ?
>Patrick Mzard
>|||Sue Hoegemeier wrote:
> Yes - It's one of the driver attributes you can supply, e.g.
> in the first example, you can add the following to the
> strAttributes:
> Network=DBMSSOCN (tcp/ip)
> or
> Network=DBNMPNTW (named pipes)
> You have to set the address accordingly.
Great I finally got it.
Here is the solution (maybe it will help someone who knows...).
I was unaware that all the ODBC administrator options were actually DSN
connection string parameters. I found the SQL Server parameter list and
description in the SQL Server documentation (by looking for the network
string you gave me and stumbling onto the "SQLConfigDataSource" function ).
Then I managed to get the odbcconf.exe thing to work (the use of pipes
'|' as separator instead of semi-colons is really misleading) and
register a data source with it. I do not know if DSN can be removed via
odbcconf, but anyway I can rewrite a simple call to SQLConfigDataSource
with the correct options if needed.
Thank you very much for your help, I would have never found it, had not
you insisted your solution was a valid one.
Patrick Mzard|||Your quite welcome...and thanks for posting back your
results.
-Sue
On Tue, 02 Mar 2004 18:30:16 +0100, Patrick Mzard
<patrick.mezard@.ifrance.com> wrote:

>Sue Hoegemeier wrote:
>Great I finally got it.
>Here is the solution (maybe it will help someone who knows...).
>I was unaware that all the ODBC administrator options were actually DSN
>connection string parameters. I found the SQL Server parameter list and
>description in the SQL Server documentation (by looking for the network
>string you gave me and stumbling onto the "SQLConfigDataSource" function ).
>Then I managed to get the odbcconf.exe thing to work (the use of pipes
>'|' as separator instead of semi-colons is really misleading) and
>register a data source with it. I do not know if DSN can be removed via
>odbcconf, but anyway I can rewrite a simple call to SQLConfigDataSource
>with the correct options if needed.
>Thank you very much for your help, I would have never found it, had not
>you insisted your solution was a valid one.
>Patrick Mzard