Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
Creating a DSN using a script

Answered Creating a DSN using a script

  • Monday, October 24, 2011 7:03 PM
     
     
    Sign In to Vote
    0

    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.

     

     

All Replies

  • Monday, October 24, 2011 7:55 PM
     
     
    Sign In to Vote
    0

    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
     
  • Wednesday, October 26, 2011 9:10 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    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:52 AM
     
     
    Sign In to Vote
    0

    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 4:18 PM
     
     
    Sign In to Vote
    0
    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 5:47 PM
     
      Has Code
    Sign In to Vote
    0
    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 6:00 PM
     
     
    Sign In to Vote
    0
    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
     
  • Tuesday, November 08, 2011 9:26 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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


     
  • Wednesday, June 27, 2012 2:42 AM
     
     Proposed Answer
    Sign In to Vote
    0

    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