none
Creating a DSN using a script

    Question

  • Hello,

    I read the forum article "Can I Create and Delete a DSN Using a Script?" http://blogs.technet.com/b/heyscriptingguy/archive/2004/11/10/can-i-create-and-delete-a-dsn-using-a-script.aspx.  I was able to get the script running to create a SQL Server ODBC.  However, when I compared the manually created ODBC to the script created ODBC there was one difference.  The manual method allowed changing the default database.  I examined both ODBC registries and could not find a registry key that specifies the default database value.  Can anyone provide me with info on how to change the default database as part of this script.

    Thank you for any help.

     

    Monday, October 24, 2011 7:03 PM

Answers

  • Hi,

    One option is to create the DSN manually on a reference machine and then use regedit to export the .reg file containing the needed registry information. You can then import the .reg file (regedit /s filename.reg) on other machines to recreate the DSN.

    HTH,

    Bill


    Tuesday, November 08, 2011 9:26 PM
    Moderator

All replies

  • In script we don't use DSN.  ADO is very good at creating DSN-less connections.

    See this link: http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx

    YOu can create a link file (udl) and lod that as text

    Yuo can also run the connection manager wich will disply the same dialog that is described in the UDL instructions above.

    ODBC is pretty much obsolete.  No madern databases use ODBC anymore.  Mostly when someone says ODBC DSN they mean a UDL and they are tralking about a modern provider like SQLServer OLEDB or Oracle OLEDB.  It is either that or direct via the API.  Only ADO/OLEDB is available to scripting.  FOr legace systems we use OLEDB support for ODBC.

    If you open the UDL editor you will not see any ODBC providers but you will see Microsft OLEDB Provider fo rODBC drivers which implements a generic OLEDB interface throut teh ODBC API.

     

     


    jv
    Monday, October 24, 2011 7:55 PM
  • I connect to SQL Server in scripts often, and never user a DSN. See this reference on connection strings for various DBMS, including SQL Server:

    http://www.connectionstrings.com/

    For example, I use VBScript code similar to below to access an SQL Server database:

     

    Option Explicit

    Dim strConnect, adoConnection, adoRecordset, strSQL
    Dim strFirst, strLast

    ' Connection string. This assumes Windows Integrated Authentication.
    ' If the database is in the default instance, use "SERVER=MyServer".
    strConnect = "DRIVER=SQL Server;" _
        & "Trusted_Connection=Yes;" _
        & "DATABASE=MyDatabase;" _
        & "SERVER=MyServer\MyInstance"

    ' T-SQL query.
    strSQL = "SELECT FirstName, LastName FROM dbo.Class"

    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.ConnectionString = strConnect
    adoConnection.Open

    Set adoRecordset = CreateObject("ADODB.Recordset")
    Set adoRecordset.ActiveConnection = adoConnection

    adoRecordset.Source = strSQL
    adoRecordset.Open

    Do Until adoRecordset.EOF
        strFirst = adoRecordset.Fields("FirstName").Value
        strLast = adoRecordset.Fields("LastName").Value
        Wscript.Echo strLast & ", " & strFirst
        adoRecordset.MoveNext
    Loop

    adoRecordset.Close
    adoConnection.Close

    -----

     


    Richard Mueller - MVP Directory Services
    Wednesday, October 26, 2011 9:10 AM
    Moderator
  • From VBS try this:

    Set dlg = CreateObject("DataLinks")
    connectionString=dlg.PromptNew()
    MsgBox connectionString

    From PowerShell this:

    $dlg=(new-object -com DataLinks).PromptNew()
    $dlg.connectionString

     


    jv
    Wednesday, October 26, 2011 9:52 AM
  • Thanks for the suggestions, however in this case the script is not connecting through the ODBC, just creating it on the local PC.  The database is a MS Access database with linked tables from several SQL Server databases.  The number of potential users is expanding and there is no local IT support at the location to manually update each PC with the required ODBCs.  Hence, the use of the script to create the ODBCs.
    Wednesday, October 26, 2011 4:18 PM
  • Thanks for the suggestions, however in this case the script is not connecting through the ODBC, just creating it on the local PC.  The database is a MS Access database with linked tables from several SQL Server databases.  The number of potential users is expanding and there is no local IT support at the location to manually update each PC with the required ODBCs.  Hence, the use of the script to create the ODBCs.


    Sorry but there are no 'ODBCs' anywhere on a PC.  An ODBC is a driver.  Are you talking about a DSN?  A DSN is a connection specification.  You can create on anywher and copy it to any PC.  Use the DSN editor to create the DSN conenction file.

    If you are talking about the emb3edded conenction strings inside of an Acess database then you will need to create a new copy of teh database and set the connectionstring as needed.

    In Access we can point the connection at an older ODBC definition and that will be used to create a DSN.  SQLServer does not use ODBC in any new cases and hasn't for many years.

    Changing the registry values for the ODBC manager string will not necessarily change your Access connection.  If Access i susing the older registry definition then you can export a working key and easily import that on the remote machines or use it as a template for editing the keys.

    Knowing your exact setup would make this easier but you are only giving out part of the information. Maybe some of what I have posted will be helpful.

    I reviewed the script you referenced and it seems to have all of the values needed. 

    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"
    
    objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
    
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"
    
    strValueName = "Database"
    strValue = "Script Center"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
    
    

    Note that it has a database value defined.

     


     

     

     


    jv
    Wednesday, October 26, 2011 5:47 PM
  • Thanks for the suggestions, however in this case the script is not connecting through the ODBC, just creating it on the local PC.  The database is a MS Access database with linked tables from several SQL Server databases.  The number of potential users is expanding and there is no local IT support at the location to manually update each PC with the required ODBCs.  Hence, the use of the script to create the ODBCs.


    Are you creating what you call the ODBCs using the Data Source Definition Wizard?

    You say this is for an Access database. Is it an Access application or an application that uses and MDB database?

    Linked tables cannot be relinked by changing the ODBC(? Data Source Manager) settings.  The links have to be regenerated by Access.  This can be scripted but it takes some pretty tricky Access code to do it and is slightly different in each version of Access.

     


    jv
    Wednesday, October 26, 2011 6:00 PM
  • Hi,

    One option is to create the DSN manually on a reference machine and then use regedit to export the .reg file containing the needed registry information. You can then import the .reg file (regedit /s filename.reg) on other machines to recreate the DSN.

    HTH,

    Bill


    Tuesday, November 08, 2011 9:26 PM
    Moderator
  • In Windows 8 (Release Preview version) and Windows Server 2012 (Release Candidate version), you can create your ODBC DSN with PowerShell script much easily.

    You can use the following line of command to create a 64-bit ODBC System DSN (named MyDsn) pointing to the database "MyTestDB" on the server "MySqlServer" with the SNAC 11.0 driver.

    Add-OdbcDsn MyDsn –DriverName "SQL Server Native Client 11.0" -DsnType System -Platform 64-bit -SetPropertyValue @('Server=MySqlServer', 'Database=MyTestDB', 'Trusted_Connection=Yes')

    See the blog article for more detail: http://blogs.msdn.com/b/data/archive/2012/04/06/odbc-dsn-management-in-the-next-release-of-windows-code-named-windows-8-and-windows-server-8.aspx


    Thanks,
    Ming.
    WDAC Team, Microsoft.
    (This post include information about a pre-release windows and is subject to change in future releases.)


    Pak-Ming Cheung - MSFT


    Wednesday, June 27, 2012 2:42 AM
  • In Windows 8 (Release Preview version) and Windows Server 2012 (Release Candidate version), you can create your ODBC DSN with PowerShell script much easily.

    You can use the following line of command to create a 64-bit ODBC System DSN (named MyDsn) pointing to the database "MyTestDB" on the server "MySqlServer" with the SNAC 11.0 driver.

    Add-OdbcDsn MyDsn –DriverName "SQL Server Native Client 11.0" -DsnType System -Platform 64-bit -SetPropertyValue @('Server=MySqlServer', 'Database=MyTestDB', 'Trusted_Connection=Yes')

    See the blog article for more detail: http://blogs.msdn.com/b/data/archive/2012/04/06/odbc-dsn-management-in-the-next-release-of-windows-code-named-windows-8-and-windows-server-8.aspx


    Thanks,
    Ming.
    WDAC Team, Microsoft.
    (This post include information about a pre-release windows and is subject to change in future releases.)


    Pak-Ming Cheung - MSFT



    This should be the answer.
    My only comment is that the -SetPropertyValue switch possible properties are not documented anywhere. That's probably fair enough because there are many different drivers possible, and many are not from Microsoft. It would be hard to expect Microsoft to document every possible property, however, they could at least document the MSFT ones e.g. for SQL Server drivers.
    Friday, April 22, 2016 4:44 AM

  • This should be the answer.
    My only comment is that the -SetPropertyValue switch possible properties are not documented anywhere. That's probably fair enough because there are many different drivers possible, and many are not from Microsoft. It would be hard to expect Microsoft to document every possible property, however, they could at least document the MSFT ones e.g. for SQL Server drivers.

    Yes I agree. Microsoft is a conspiracy. They will ruin us all by secretly controlling the universe.

    Look at this post.  It is years after any answer.  Clearly these people would not waste their time ere if they were not terrorized by the MS beast.

    Microsoft.  I agree.  Please save us all by posting all of your secret knowledge.


    \_(ツ)_/

    Friday, April 22, 2016 5:13 AM
  • Yep, there are too many driver-specific properties that we cannot document. And you should check the manual / documentation of each driver. A quicker way is to create a DSN on a test machine with the GUI and then use Get-OdbcDsn to obtain the list of settings you want.

    For SQL Server Native Client properties, most of them should be documented at: https://msdn.microsoft.com/en-us/library/ms130822%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396. Although the page is about connection string properties, most of these properties can be applied to a DSN as well (if my memory is correct). Of course, the "DSN" property cannot be used in a DSN :-).  

    If you have further questions in SQL Server, you may want to post at the SQL Server Data Access forum where more experts can take care you(https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqldataaccess)

    Thanks,

    Pak-Ming Cheung

    Bing Team (Yes, I moved to Bing team already ~~)


    Pak-Ming Cheung - MSFT

    Friday, April 22, 2016 4:36 PM
  • ohh...that's cool
    Thursday, April 28, 2016 7:14 AM