Using WSH to set up a system DSN

[rnpIII]

Hello All,

I note a lot of these questions have not been answered for awhile. Hope this forum has not been forgotten.

I am attempting to set up a system DSN for a SQL2K database using WSH (Windows Scripting Host) or any other programming language native to Windows 2K and XP.

I have been through so many different possibilities these last few days, I am in a haze.

Scenario:
- SQL 2K ODBC drivers on all clients
- Mix of 2K and XP clients
- ODBC connection with SQL server authentication...
Need to use something programmatic so I do not have to touch about 100 or so clients.

In most cases the ODBC connection can be set up using a program call "TrackIT Deploy". The problem is it does not set up the password, (SQL server authent...) so I still have to touch each client.
This calls for a late night.

SO! Would anyone be able to assist with some suggestions as to how to go about this?

I have some code written but it does nothing, as far as I can tell. See below:

Function LinkToLiaison()

strDVR = "SQL Server"
strSVR = "SomeSQL2KServer"
strDSN = "Some description"
strUID = "sa"
strPWD = "sa"
strdB = "master"

strConnect = "driver="& strDVR & _
";SERVER=" & strSVR & _
";DSN=" & strDSN & _
";Database=" & strdB & _
""
Conn.Open strConnect, strUID, strPWD
End Function

Any suggestions, assistance will be greatly appreciated.

rnpIII

 

[ArtieChoke]

Can you use a file DSN for you application? If so, put it on a LAN and have the users connect to it.

If it must be on the machine, the way I've done it is to create the DSN on a local machine, then extract the entries from the registry (local machine\software\odbc). You'll need the entry from the odbc.ini section. Then you can use a script to install it on each machine by writing to the registry directly. I think the wscripting host supports this.

 

[rnpIII]

Hello ArtieChoke,

Thanks for the response.
I have noted in most cases that is what they are doing, copying the info from the registry.
As for the ODBC.ini file, I see no entry for the system DSN in question.
Seems strange, I just assumed it was something to do with the fact that it was a system DSN.

Thank you for your input and advice, I guess that is exactly what I will do and hope it works.

Just one thing, have you verified this with passwords to know for sure it will work?

Thanks again.

rnpIII

 

[ArtieChoke]

I'm not connected to a network right now, so I can't verify the password is saved. I know when I've editted a DSN with SQL authentication it always asks for the password, so maybe not.

What's problem are you trying to solve? Do you have a 3rd party app or custom program that needs to connect via ODBC?

If you have that many clients, using a file DSN (assuming the app supports it) is the best way to go, as long as the clients have network access. That way, you only have to change it in one place.