Create an ODBC system DSN for connecting

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Create an ODBC system DSN for connecting

Logged in as: Guest

arrSession:exec spGetSession 2,2,39361

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Create an ODBC system DSN for connecting
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Create an ODBC system DSN for connecting - 11/1/2006 11:10:51 PM

buffi

Posts: 87
Score: 0
Joined: 3/28/2005
From:
Status: offline

Hi All,
I have found this script over the net and it is working but i need a little bit of help to modify it.
The script created a new ODBC connection with a default user and password.
How can i modify it in a way in which i can enter manually the username and password.
Thanks in advance.

On Error Resume Next

Dim Registry
Set Registry = WScript.CreateObject("WScript.Shell")

DataSourceName = "GreatPlains"
Description = "Connection to Great Plains server"
Server = "SRV3"
DriverName = "SQL Server"       
WindowsAuthentication = False
DriverPath = "C:\Windows\System"
                                                       
REG_KEY_PATH = "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DataSourceName

' Create the DSN only if it doesn't already exist.
Result = Registry.RegRead (REG_KEY_PATH & "\Server")
If Result = "" Then
    Registry.RegWrite REG_KEY_PATH & "\DataBase",DatabaseName,"REG_SZ"
    Registry.RegWrite REG_KEY_PATH & "\Description",Description,"REG_SZ"
    Registry.RegWrite REG_KEY_PATH & "\LastUser",LastUser,"REG_SZ"
    Registry.RegWrite REG_KEY_PATH & "\Server",Server,"REG_SZ"
    Registry.RegWrite REG_KEY_PATH & "\Driver",DriverPath,"REG_SZ"
    If WindowsAuthentication = True Then
         Registry.RegWrite REG_KEY_PATH & "\Trusted_Connection","Yes","REG_SZ"
    End If
    ' This key is required for the DSN to appear in the ODBC Control Panel
    REG_KEY_PATH = "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\" & DataSourceName
    Registry.RegWrite REG_KEY_PATH,DriverName,"REG_SZ"
End If

Set Registry = Nothing

Post #: 1

RE: Create an ODBC system DSN for connecting - 11/1/2006 11:56:19 PM

mbouchard

Posts: 1924
Score: 16
Joined: 5/15/2003
From: USA
Status: offline

I have not found a way to incorporate a password in the ODBC dataconnection. But I have created an ODBC dataconnection that requires a username and password. But, in my case, it was mainly used to test the connection. The creds of the user or ones supplied by the user are used to make the actual connection to the database.

_____________________________

Mike

For useful Scripting links see the Read Me First stickey!

Always remember Search is your friend.

(in reply to buffi)

Post #: 2

RE: Create an ODBC system DSN for connecting - 11/2/2006 12:41:48 AM

buffi

Posts: 87
Score: 0
Joined: 3/28/2005
From:
Status: offline

Hi mbouchard,

Is there a way to connect and excute some queries without the ODBC connection.
I searched and asked for help on such scripts but found nothing.
I tried to use the osql. I managed to get with the osql script to get the connection to the DB.
but i can not run the queries.

Any suggestions?

Regards,
Buff

(in reply to mbouchard)

Post #: 3

RE: Create an ODBC system DSN for connecting - 11/2/2006 2:19:00 AM

ehvbs

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi buffi,

this sample code

Dim sMethod : sMethod = "OLEDB:TRUST" ' ( ODBC | OLEDB : TRUST | STD )
   Dim sServer : sServer = "Server"
   Dim sDBName : sDBName = "master"
   Dim sUser   : sUser   = "user"
   Dim sPWord  : sPWord  = "pword"
   Dim sSQL    : sSQL    = "SELECT name FROM sysusers"
 
   Dim aMethod, sCS
   aMethod = Split( sMethod, ":" )
   Select Case aMethod( 0 )
     Case "OLEDB"
       sCS = "Provider=sqloledb;Data Source=@Server@;Initial Catalog=@DBName@"
       Select Case aMethod( 1 )
         Case "TRUST"
           sCS = sCS + ";Integrated Security=SSPI"
         Case "STD"
           sCS = sCS + ";User Id=@User@;Password=@PWord@"
         Case Else
           ' Add your error handling
       End Select
     Case "ODBC"
       sCS = "Driver={SQL Server};Server=@Server@;Database=@DBName@"
       Select Case aMethod( 1 )
         Case "TRUST"
           sCS = sCS + ";Uid=;Pwd="
         Case "STD"
           sCS = sCS + ";Uid=@User@;Pwd=@PWord@"
         Case Else
           ' Add your error handling
       End Select
     Case Else
       ' Add your error handling
   End Select
   sCS = Replace( sCS, "@Server@", sServer )
   sCS = Replace( sCS, "@DBName@", sDBName )
   sCS = Replace( sCS, "@User@"  , sUser   )
   sCS = Replace( sCS, "@PWord@" , sPWord  )
   WScript.Echo "sCS =", sCS
 
   Dim oCN : Set oCN = CreateObject( "ADODB.Connection" )
   Dim oRS : Set oRS = CreateObject( "ADODB.Recordset"  )
   oCN.Open sCS
   oRS.Open sSQL, oCN
 
   WScript.Echo "sSQL =", sSQL
   Do While Not oRS.EOF
      WScript.Echo oRS.Fields( 0 ).Value
      oRS.MoveNext
   Loop
 
   oRS.Close
   oCN.Close

- output -

lets you experiment with 4 ways to connect to a MS SQLServer (via OLEDB or ODBC, using
a trusted connection or user/password). To learn more about connection strings look at

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
http://www.connectionstrings.com/

All these methods avoid creating a DSN.

Good luck!

(in reply to buffi)

Post #: 4

RE: Create an ODBC system DSN for connecting - 11/5/2006 8:42:40 PM

buffi

Posts: 87
Score: 0
Joined: 3/28/2005
From:
Status: offline

Great work mate thanks for the scripts i will have a look on them.

cheers you all

(in reply to ehvbs)

Post #: 5

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Create an ODBC system DSN for connecting

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts