Setup DSN Sql entry - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » Setup DSN Sql entry

Page 1 of 1

Topic Options

#158510 - 2006-03-08 05:12 AM Setup DSN Sql entry
Dmartin05 Dmartin05 Fresh Scripter Registered: 2006-02-01 Posts: 49	Whats up everyone, I'm trying to find out if there is a way that I could via login script or from the cmd prompt I could configure DSN for my pcs. There is a 3rd party application that I need to configure DSN for on each machine. Any help would be great thanks
Top

#158511 - 2006-03-08 05:53 AM Re: Setup DSN Sql entry
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11634 Loc: Space	Get a copy of REGSHOT and then on a clean system run the first snapshot of the system, then create your DSN, then take the second snapshot of the system. Then do a compare and: You should see these keys modified (and maybe a few more depending on options selected) HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources HKLM\SOFTWARE\ODBC\ODBC.INI You can then easily write some Admin code to remotely add this entry to any system you have Admin rights on. If you need further assistance please let us know.
Top

#158512 - 2006-03-08 05:54 AM Re: Setup DSN Sql entry
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11634 Loc: Space	Here is a bogus sample of a test creation of a DSN. Regshot 1.7 Comments: Datetime:2006/3/8 04:33:42 , 2006/3/8 04:35:42 Computer:A , A Username: , ---------------------------------- Keys added:6 ---------------------------------- HKLM\SOFTWARE\Microsoft\MSSQLServer HKLM\SOFTWARE\Microsoft\MSSQLServer\Client HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources HKLM\SOFTWARE\ODBC\ODBC.INI\PeopleSoft ---------------------------------- Values added:7 ---------------------------------- HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\A: "DBMSSOCN,A,1433" HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\PeopleSoft: "SQL Server" HKLM\SOFTWARE\ODBC\ODBC.INI\PeopleSoft\Driver: "C:\WINDOWS\System32\SQLSRV32.dll" HKLM\SOFTWARE\ODBC\ODBC.INI\PeopleSoft\Description: "HR Application Database" HKLM\SOFTWARE\ODBC\ODBC.INI\PeopleSoft\Server: "A" HKLM\SOFTWARE\ODBC\ODBC.INI\PeopleSoft\LastUser: "owner" HKLM\SOFTWARE\ODBC\ODBC.INI\PeopleSoft\Trusted_Connection: "Yes"
Top

#158513 - 2006-03-16 03:42 PM Re: Setup DSN Sql entry
Dmartin05 Dmartin05 Fresh Scripter Registered: 2006-02-01 Posts: 49	OK, I was finally able to run the Regshot.exe. I 1st removed the system dsn entry ran regshot, then added the dsn and ran regshot again: Here is outcome for Values added: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\SmartClinic: "SQL Server" HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic\Driver: "C:\WINDOWS\system32\SQLSRV32.dll" HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic\Description: "SmartClinic" HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic\Server: "MEDICALSERVER" HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic\LastUser: "sa" There was a whole bunch of other stuff, but it did not seem to be related to the dsn connection. I tried to run the WriteValue (Copy and pasted the strings above and it didn't add the entry) What am I doing wrong? and am i missing some registry entries? If worst comes to worst, I will copy and paste the whole log from regshot.
Top

#158514 - 2006-03-16 03:45 PM Re: Setup DSN Sql entry
Mart Mart KiX Supporter Registered: 2002-03-27 Posts: 4673 Loc: The Netherlands	You can't just copy the stuff. It needs to be in a specific format for writevluae. Code: WriteValue ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", "SmartClinic", "SQL Server", "PUT THE DATA TYPE HERE") _________________________ Mart - Chuck Norris once sold ebay to ebay on ebay.
Top

#158515 - 2006-03-16 07:09 PM Re: Setup DSN Sql entry
Dmartin05 Dmartin05 Fresh Scripter Registered: 2006-02-01 Posts: 49	Ok, I'm testing out another ODBC connection the the data on it is going to be a little different. Below I ran regshot in my test enviroment and created a .txt odbc connection: It added 3 keys and 11 values that used the odbc. I converted them so that it could work with my login script, but it did not execute: Here is the scriptWriteValue WriteValue ;Keys Added ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI","test") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test","Engines") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test\Engines","Text") ;Values Added ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources","test", "Driver da Microsoft para arquivos texto (.txt; .csv)", "Reg_SZ") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test\Engines\Text","ImplicitCommitSync", "", "Reg_SZ") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test\Engines\Text","Threads", "0x00000003", "REG_DWORD") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test\Engines\Text", "UserCommitSync", "Yes" ,"Reg_SZ") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test","Driver", "C:\WINDOWS\System32\odbcjt32.dll" ,"Reg_SZ") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test","DefaultDir", "Z:\","Reg_SZ") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test","DriverId", "0x0000001B","Reg_DWORD") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test","FIL", "text","Reg_SZ") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test","SafeTransactions", "0x00000000","Reg_DWORD") ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\test","UID" ,"","Reg_SZ") but it did not run, am I supposed to add write value to each line?
Top

#158516 - 2006-03-16 07:34 PM Re: Setup DSN Sql entry
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11634 Loc: Space	Well you're not going to be using a .txt for SQL connections Okay, try this. Remove the DSN using the ODBC applet, don't simply try to remove it in the registry yourself. Then start regshot and perform these tasks 1. Run 1st shot 2. Setup the ODBC DSN as though it is going to be live, real. 3. Run 2nd shot 4. Compare 5. Post the entire Compare here Once you post the compare I'll write a UDF type script for you to use.
Top

#158517 - 2006-03-16 08:18 PM Re: Setup DSN Sql entry
Dmartin05 Dmartin05 Fresh Scripter Registered: 2006-02-01 Posts: 49	I think I figured it out, I will have to try it at there site tonight to test it out. In my script, I was adding the I was using the wrong syntax for adding a key. I then read in my book that I don't have to use the "ADDKEY" function, I could simply just use the "writevalue" function and it will add the key as well as the value. Once I straightend everything out with my test script in my testing enviroment,upon login it added the .txt ODBC connection. Here is a portion of my script that I am using for my live enviroment: WriteValue ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources","SmartClinic", "SQL Server","Reg_SZ") WriteValue ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic","Driver", "C:\WINDOWS\system32\SQLSRV32.dll","REG_SZ") WriteValue ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic","Description" "SmartClinic","REG_SZ") WriteValue ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic","Server", "MEDICALSERVER","REG_SZ") WriteValue ("HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SmartClinic","LastUser", "sa","REG_SZ") I will test it and let you guys know how it worked: Thanks again
Top

#158518 - 2006-03-16 08:45 PM

Re: Setup DSN Sql entry

NTDOC

Administrator

Registered: 2000-07-28
Posts: 11634
Loc: Space

Well your code won't work as there is a missing comma in it.

Here is an example of writing the code to use.

Dim $Key,$Set,$WinDir
$Key = 'HKLM\SOFTWARE\ODBC\ODBC.INI'
$WinDir = ReadValue('HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion','SystemRoot')
$Set=WriteValue ($Key+'\ODBC Data Sources','SmartClinic','SQL Server',Reg_SZ)
$Set=WriteValue ($Key+'\SmartClinic','Driver',$WinDir+'\system32\SQLSRV32.dll',REG_SZ)
$Set=WriteValue ($Key+'\SmartClinic','Description','SmartClinic',REG_SZ)
$Set=WriteValue ($Key+'\SmartClinic','Server','MEDICALSERVER',REG_SZ)
$Set=WriteValue ($Key+'\SmartClinic','LastUser','sa',REG_SZ)

Top

#158519 - 2006-03-17 06:24 AM Re: Setup DSN Sql entry
Dmartin05 Dmartin05 Fresh Scripter Registered: 2006-02-01 Posts: 49	Alright, I added the comma to my code and it worked. NTDOC, I will try out your code as well. Questions though? Because this is going in a login script, should I add anything else in the code so that it doesn't run everytime a user logs in? Thanks to all
Top

#158520 - 2006-03-17 08:55 AM

Re: Setup DSN Sql entry

NTDOC

Administrator

Registered: 2000-07-28
Posts: 11634
Loc: Space

Well there are a couple things.

1. Yes you should run code normally to check if it's been run before or not.
2. Unless your users are local administrators on their systems (not a best practice) then they won't be able to create these entries in the HKLM part of the registry.

Here is code for what you asked, though if you really need to do this and your users don't have admin rights then you'll need to run code to accomplish this as an admin script remotely.

If InStr(ReadValue('HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources','SmartClinic'),'SQL Server')
  'No update Required ' ?
Else
  CreateDSN()
EndIf
 
Function CreateDSN()
  Dim $Key,$Set,$WinDir
  $Key = 'HKLM\SOFTWARE\ODBC\ODBC.INI'
  $WinDir = ReadValue('HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion','SystemRoot')
  $Set=WriteValue ($Key+'\ODBC Data Sources','SmartClinic','SQL Server',Reg_SZ)
  $Set=WriteValue ($Key+'\SmartClinic','Driver',$WinDir+'\system32\SQLSRV32.dll',REG_SZ)
  $Set=WriteValue ($Key+'\SmartClinic','Description','SmartClinic',REG_SZ)
  $Set=WriteValue ($Key+'\SmartClinic','Server','MEDICALSERVER',REG_SZ)
  $Set=WriteValue ($Key+'\SmartClinic','LastUser','sa',REG_SZ)
  Exit @ERROR
EndFunction

Top

#158521 - 2006-03-17 09:24 AM

Re: Setup DSN Sql entry

NTDOC

Administrator

Registered: 2000-07-28
Posts: 11634
Loc: Space

Here is an example of doing this as a remote admin script.
You must have admin rights on the remote system.

Create a file called computers.txt and place the
names of the computers you want to run this against in it
with one computer per line like this

comp01
comp02
comp03
etc...

Break On
Dim $SO
$SO=SetOption('Explicit','On')
$SO=SetOption('NoVarsInStrings','On')
$SO=SetOption('NoMacrosInStrings','On')
 
Global $WinPath,$Computers
Dim $Ping,$sComputer
 
$Computers = ReadFile(@ScriptDir+'\'+'computers.txt')
For Each $sComputer In $Computers
  If $sComputer
    $sComputer = Trim($sComputer)
    $Ping = OSPing($sComputer)
    If $Ping
      $WinPath = WindowsInstallFolder($sComputer)
      $sComputer=IIf(Not $sComputer,'','\\'+Join(Split($sComputer,'\'),'',3)+'\')
      If InStr(ReadValue($sComputer+'HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources','SmartClinic'),'SQL Server')
        'No update Required on ' + $sComputer ?
      Else
        CreateDSN($sComputer)
        'Update key on: ' + $sComputer + '  -  ' +@ERROR + ' - ' + @SERROR ?
      EndIf
    Else
      'The computer ' + $sComputer + ' did not repsond to ping' ?
    EndIf
  EndIf
Next
 
Function CreateDSN($sComputer)
  Dim $Key,$Set
  $Key = 'HKLM\SOFTWARE\ODBC\ODBC.INI'
  $Set=WriteValue ($sComputer+$Key+'\ODBC Data Sources','SmartClinic','SQL Server',Reg_SZ)
  $Set=WriteValue ($sComputer+$Key+'\SmartClinic','Driver',$WinPath+'\system32\SQLSRV32.dll',REG_SZ)
  $Set=WriteValue ($sComputer+$Key+'\SmartClinic','Description','SmartClinic',REG_SZ)
  $Set=WriteValue ($sComputer+$Key+'\SmartClinic','Server','MEDICALSERVER',REG_SZ)
  $Set=WriteValue ($sComputer+$Key+'\SmartClinic','LastUser','sa',REG_SZ)
  Exit @ERROR
EndFunction
 
Function ReadFile($file)
  Dim $lf, $f, $_, $t
  $lf=CHR(10)
  $f=FreeFileHandle
  $_=Open($f,$file)
    If @ERROR Exit @ERROR EndIf
    Do $t=$t+$lf+ReadLine($f) Until @ERROR
    $_=Close($f)
  $ReadFile=Split(SubStr($t,2),$lf)
EndFunction
 
Function WindowsInstallFolder(optional $sComputer,$UNC)
  Dim $WinDir
  $sComputer=IIf(Not $sComputer,'','\\'+Join(Split($sComputer,'\'),'',3)+'\')
  If $sComputer And $UNC
    $WinDir = $sComputer+Join(Split(ReadValue($sComputer+'HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion','SystemRoot'),':'),'$')
  Else
    $WinDir = ReadValue($sComputer+'HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion','SystemRoot')
  EndIf
  If @ERROR
    $WindowsInstallFolder = ''
    Exit Val('&' + Right(DecToHex(@ERROR), 4))
  Else
    $WindowsInstallFolder = $WinDir
  EndIf
  Exit @ERROR
EndFunction
 
Function OSPing($sComputer)
  Shell'%comspec% /c ping -n 1 '+$sComputer+' >nul'
  $OSPing = NOT @ERROR
EndFunction

Top

#158522 - 2006-03-20 10:51 PM Re: Setup DSN Sql entry
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	You guys should be able to get a copy of ODBCSQL() from the UDF Library. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#158523 - 2006-03-20 11:25 PM Re: Setup DSN Sql entry
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11634 Loc: Space	That would work if it was run locally and the user had Admin rights Kent, but most users won't or shouldn't have Admin rights. Now if you were to update that UDF to support remote systems that would be cool
Top

#158524 - 2006-03-21 12:06 AM Re: Setup DSN Sql entry
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	There is a flag there to do create user DSN in the script. Or.. You could use something like Altiris or a Service to do the same thing. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#158525 - 2006-03-21 01:09 AM Re: Setup DSN Sql entry
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11634 Loc: Space	Yeah, but from my experience most people don't seem to want to use a USER DSN as it can be modified by the user and is more difficult to maintain consistency. You could easily modify your UDF to support both local and remote for Admins.
Top

#158526 - 2006-03-22 04:32 PM Re: Setup DSN Sql entry
Dmartin05 Dmartin05 Fresh Scripter Registered: 2006-02-01 Posts: 49	Thanks all for your responses, NTDOC I tested both your login script and remote script and they both worked excellent. I ended up using the remote script thanks again
Top

#175754 - 2007-04-27 03:10 PM Re: Setup DSN Sql entry [Re: NTDOC]
Phoenix1987 Phoenix1987 Fresh Scripter Registered: 2007-01-02 Posts: 49 Loc: Rotterdam, Netherlands	Originally Posted By: NTDOC Well there are a couple things. 1. Yes you should run code normally to check if it's been run before or not. 2. Unless your users are local administrators on their systems (not a best practice) then they won't be able to create these entries in the HKLM part of the registry. Here is code for what you asked, though if you really need to do this and your users don't have admin rights then you'll need to run code to accomplish this as an admin script remotely. <fieldset style='background:honeydew;border-style:solid;border-width:2'><pre><font face='Courier New' size=2 color=#4B0082>[color:#0000FF]If</font> [color:#FF0000]InStr</font>([color:#FF0000]ReadValue</font>([color:#000000]'HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources'</font>,[color:#000000]'SmartClinic'</font>),[color:#000000]'SQL Server'</font>) [color:#000000]'No update Required '</font> ? [color:#0000FF]Else</font> [color:#8B4513]CreateDSN</font>() [color:#0000FF]EndIf</font> [color:#0000FF]Function</font> CreateDSN() [color:#0000FF]Dim</font> [color:#8B4513]$Key</font>,[color:#8B4513]$Set</font>,[color:#8B4513]$WinDir</font> [color:#8B4513]$Key</font> = [color:#000000]'HKLM\SOFTWARE\ODBC\ODBC.INI'</font> [color:#8B4513]$WinDir</font> = [color:#FF0000]ReadValue</font>([color:#000000]'HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion'</font>,[color:#000000]'SystemRoot'</font>) [color:#8B4513]$Set</font>=[color:#FF0000]WriteValue</font> ([color:#8B4513]$Key</font>+[color:#000000]'\ODBC Data Sources'</font>,[color:#000000]'SmartClinic'</font>,[color:#000000]'SQL Server'</font>,[color:#8B4513]Reg_SZ</font>) [color:#8B4513]$Set</font>=[color:#FF0000]WriteValue</font> ([color:#8B4513]$Key</font>+[color:#000000]'\SmartClinic'</font>,[color:#000000]'Driver'</font>,[color:#8B4513]$WinDir</font>+[color:#000000]'\system32\SQLSRV32.dll'</font>,[color:#8B4513]REG_SZ</font>) [color:#8B4513]$Set</font>=[color:#FF0000]WriteValue</font> ([color:#8B4513]$Key</font>+[color:#000000]'\SmartClinic'</font>,[color:#000000]'Description'</font>,[color:#000000]'SmartClinic'</font>,[color:#8B4513]REG_SZ</font>) [color:#8B4513]$Set</font>=[color:#FF0000]WriteValue</font> ([color:#8B4513]$Key</font>+[color:#000000]'\SmartClinic'</font>,[color:#000000]'Server'</font>,[color:#000000]'MEDICALSERVER'</font>,[color:#8B4513]REG_SZ</font>) [color:#8B4513]$Set</font>=[color:#FF0000]WriteValue</font> ([color:#8B4513]$Key</font>+[color:#000000]'\SmartClinic'</font>,[color:#000000]'LastUser'</font>,[color:#000000]'sa'</font>,[color:#8B4513]REG_SZ</font>) [color:#0000FF]Exit</font> [color:#FF7F50]@ERROR</font> [color:#0000FF]EndFunction</font></font></pre></fieldset> im looking for this kind of script. Only i need a default database to be changed. Can someone give me a line for that. _________________________ A mistake is Human, But to make it a real mess you need a computer.
Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org