SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 8 of 8
  1. #1
    Member
    Join Date
    May 2004
    Location
    Mumbai
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Unable to create SQLServer DSN

    Hi

    I am unable create SQLserver DSN through following code

    '-------------- This function Creates a DSN NAME in ODBC for SQL Server ----------------------
    Function BuildSystemDSN_SQLServer()

    Dim DataSourceName As String
    Dim DatabaseName As String
    Dim Description As String
    Dim DriverPath As String
    Dim DriverName As String
    Dim LastUser As String
    Dim Regional As String
    Dim Server As String

    Dim lResult As Long
    Dim hKeyHandle As Long

    'Specify the DSN parameters.

    DataSourceName = "CATEST"
    DatabaseName = "CA_Renewal_Conversion_Scripting.mdf"
    Description = "CA_Renewal_Conversion_Scripting"
    DriverPath = "Provider=MSDASQL.1;Persist Security Info=False" '"<path to your SQL Server driver>"
    LastUser = "PWIODC\278989" ' "<default user ID of the new DSN>"
    Server = "INFPW02415" '"<name of the server to be accessed by the new DSN>"
    DriverName = "SQL Server"

    'Create the new DSN key.

    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\DataSourceName", hKeyHandle)

    'Set the values of the new DSN key.

    lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, ByVal DatabaseName, Len(DatabaseName))
    lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, ByVal Description, Len(Description))
    lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, ByVal DriverPath, Len(DriverPath))
    lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, ByVal LastUser, Len(LastUser))
    lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, ByVal Server, Len(Server))

    'Close the new DSN key.

    lResult = RegCloseKey(hKeyHandle)

    'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
    'Specify the new value.
    'Close the key.

    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
    lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, ByVal DriverName, Len(DriverName))
    lResult = RegCloseKey(hKeyHandle)

    End Function

  2. #2
    Senior Member
    Join Date
    Apr 2003
    Location
    Wisconsin, USA
    Posts
    5,338
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to create SQLServer DSN

    Does creating this registry key outside of TP result in successful creation of the DSN?

  3. #3
    Junior Member
    Join Date
    Mar 2006
    Location
    ohio
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to create SQLServer DSN

    The answer you seek comes below. But first: it would have been nice to know if you were getting errors? If so, what errors? Are you including any Reference Libraries? Etc.

    Ok, the answer...

    1. If the code you supplied was ALL the code you have in your script/module, then you got a compile error when you attempted to execute it. If this is the case, then at the top of your script/module, etc, add the following code:

    <font class="small">Code:</font><hr /><pre>
    Private Const REG_SZ = 1 'Constant for a string variable type.
    Private Const HKEY_LOCAL_MACHINE = &amp;H80000002

    Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
    "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
    phkResult As Long) As Long

    Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
    "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
    ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
    cbData As Long) As Long

    Private Declare Function RegCloseKey Lib "advapi32.dll" _
    (ByVal hKey As Long) As Long

    </pre><hr />

    2. When you copied the code from the Microsoft article "How To Programmatically Create a DSN for SQL Server with VB" and modified it, you changed the following line...

    FROM (this is the CORRECT code, and your "fix"):
    <font class="small">Code:</font><hr /><pre>
    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" &amp; _
    DataSourceName, hKeyHandle)
    </pre><hr />

    TO (this is the BROKEN line from your message):
    <font class="small">Code:</font><hr /><pre>
    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\DataSourceName", hKeyHandle)
    </pre><hr />

    So, if you didn't have the declarations, add them. Plus, copy the "fixed" line of code and replace your "broken" line.

    Oh, and don't forget to clean-up your registry if you DID get the old code to run. You need to remove the "DataSourceName" from under the ODBC.INI entry, and remove the "CATEST" value from under the "ODBC Data Sources" entry.

    Enjoy!

    -- Terrill --

  4. #4
    Member
    Join Date
    May 2004
    Location
    Mumbai
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to create SQLServer DSN

    Everyting is fine in my code infact it is creating the DNS as well but when I am trying to access it throughs the following error

    "[Microsoft][ODBC Driver Manager] Datasource name not found and no default driver specified."

    If I try to open it from ODBC it throughs following error

    "CATest is not an existing datasource name" --Invalid DNS

    Pl. give me the solution

    thankx

  5. #5
    Senior Member
    Join Date
    Apr 2003
    Location
    Wisconsin, USA
    Posts
    5,338
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to create SQLServer DSN

    You never answered my original question. Does creating the registry keys outside of TP result in an error free DSN? If not, then the problem lies with your assumptions about the registry keys. If yes, then there is a TP code issue.

    Terrill also asked if you are using any reference libraries, which was not answered.

    We can't just give you the solution without knowing where the problem lies.

    You stated that your code is fine and is creating the DSN (by the way, DSN is quite different from DNS), when in fact it is NOT creating the DSN correctly, else you would not get the errors you are getting.

    Also, please note that this is an all volunteer group of professionals here. Posting messages such as you just did with the "Pl. give me the solution" demand poses two problems. First, you are texting, which is not good form in a professional forum. Secondly, we are not here to solve your problems for you. We are here to facilitate you finding solutions to your own problems. If you are not willing to provide the information that is asked for and just demand a solution, you will not get much help here.

  6. #6
    Junior Member
    Join Date
    Mar 2006
    Location
    ohio
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to create SQLServer DSN

    Sorry, your code my be running, but it is NOT correct!

    The problem is, as I pointed-out in my previous message, this:

    YOUR line of code hard-codes an ODBC connection named "DataSourceName". After running your code, if you look in the Registry, you will see it listed under ODBC.INI as "DataSourceName."

    Here, again, is your BROKEN line of existing code:

    <font class="small">Code:</font><hr /><pre>
    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\DataSourceName", hKeyHandle)
    </pre><hr />

    Look carefully at the string: "SOFTWARE\ODBC\ODBC.INI\DataSourceName"

    What you MEANT to say, and the code I gave you, is:
    <font class="small">Code:</font><hr /><pre>
    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" &amp; _
    DataSourceName, hKeyHandle)
    </pre><hr />

    You have a variable named DataSourceName. Your original line of code is NOT concatenating the value to the string as you expected. Mine does.

    I ran your original code, and looked at the results. Saw the problem. Fixed the problem. And re-ran the new code:

    * I can open the Windows ODBC Manger after your BROKEN code, and I get the same problems you get.

    * After I "fix" the code, I can open the DSN you are trying to create in the Windows ODBC manager... although, obviously, I can't physically connect to YOUR database.

    Change the line. Run the script. It should work.

    -- Terrill --

  7. #7
    Member
    Join Date
    May 2004
    Location
    Mumbai
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to create SQLServer DSN

    yes it's working and here is the correct code

    '========================

    Function BuildSystemDSN_SQLServer()

    Dim DataSourceName As String
    Dim DatabaseName As String
    Dim Description As String
    Dim DriverPath As String
    Dim DriverName As String
    Dim LastUser As String
    Dim Regional As String
    Dim Server As String

    Dim lResult As Long
    Dim hKeyHandle As Long

    'Specify the DSN parameters.

    DataSourceName = "CATEST"
    DatabaseName = "CA_Renewal_Conversion_Scripting.mdf"
    Description = "CA_Renewal_Conversion_Scripting"
    DriverPath = "Provider=MSDASQL.1;Persist Security Info=False" '"&lt;path to your SQL Server driver&gt;"
    LastUser = "PWIODC\278989" ' "&lt;default user ID of the new DSN&gt;"
    Server = "INFPW02415" '"&lt;name of the server to be accessed by the new DSN&gt;"
    DriverName = "SQL Server"

    'Create the new DSN key.

    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\CATEST", hKeyHandle)

    'Set the values of the new DSN key.

    lResult = RegSetValueEx(hKeyHandle, "Database", 0&amp;, REG_SZ, ByVal DatabaseName, Len(DatabaseName))
    lResult = RegSetValueEx(hKeyHandle, "Description", 0&amp;, REG_SZ, ByVal Description, Len(Description))
    lResult = RegSetValueEx(hKeyHandle, "Driver", 0&amp;, REG_SZ, ByVal DriverPath, Len(DriverPath))
    lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&amp;, REG_SZ, ByVal LastUser, Len(LastUser))
    lResult = RegSetValueEx(hKeyHandle, "Server", 0&amp;, REG_SZ, ByVal Server, Len(Server))

    'Close the new DSN key.

    lResult = RegCloseKey(hKeyHandle)

    'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
    'Specify the new value.
    'Close the key.

    lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
    lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&amp;, REG_SZ, ByVal DriverName, Len(DriverName))
    lResult = RegCloseKey(hKeyHandle)

    End Function

  8. #8
    Member
    Join Date
    May 2004
    Location
    Mumbai
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Unable to create SQLServer DSN

    thankx Darrel Damon for your suggestion I will keep this in mide from next time

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.71%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 05:54 PM.

Copyright BetaSoft Inc.