I need a programmatic way of creating a SQL Server ODBC Data Source. I can do this by directly accessing the Registry. It would be better if this could be done via an available (SQL Server/Windows) API to protect against changes in the registry keys or values with updated SQL Server drivers.
Accepted Answer Note: Using SQLConfigDataSource abstracts the code from the details of Registry keys etc. so this is more robust. I was hoping, however, that SQL Server would have wrapped this with a higher level function which took strongly typed attributes (rather than a delimited string) and exposed it through the driver.
To do this directly in the registry you can add a String Value to:
to add a System DSN, or:
to add a User DSN.
The Name of the Value is the name of the Data Source you want to create and the Data must be 'SQL Server'.
At the same level as 'ODBC Data Sources' in the Registry create a Key with the name of the Data Source you want to create.
This key needs the following String Values:
For example, using the reg.exe application from the command line to add a User Data Source called 'ExampleDSN':
For VB.NET it can be done this way:
Import for 'DllImport':
Declaration of SQLConfigDataSource:
Example usage:
Sample Using C#:
( Detailed SQL Server param reference at http://msdn.microsoft.com/en-us/library/aa177860.aspx )
SQLConfigDataSource() does the job.
MSDN article
Just in case here is a VB6 example:
I'd use odbcad32.exe which is located in your system32 folder.
This will add your odbc data sources to the correcct location, which won't be effected by any patches.