?
Solved

Need batch script to create SQL DSN and test the connection

Posted on 2011-10-10
13
Medium Priority
?
864 Views
Last Modified: 2012-06-27
Need a batch script which can create a SQL DSN, use the user id, Password and server name connect to a catalog to test the connection and to know whether it is able to access or not
0
Comment
Question by:sankok432
  • 6
  • 6
13 Comments
 
LVL 13

Expert Comment

by:Felix Leven
ID: 36940895
PowerShell possible ?
0
 

Author Comment

by:sankok432
ID: 36940911
we have to install powershell on every server which is not possible thats why i need .bat script
0
 
LVL 10

Expert Comment

by:gavsmith
ID: 36941350
What type of DSN do you require? System DSN/User DSN/File DSN?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 10

Expert Comment

by:gavsmith
ID: 36941377
Actually scrap that question, either way create another file, either a reg file or dsn file and either import the keys or copy the file.

Create the dsn you require on your machine then in regedit browse to

System DSN:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

User DSN

HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI

And export the appropriate key to a file (DNSSettings.reg)

put that file with the script(.bat) and use the command:

REGEDIT /S DNSSettings.reg

to import the settings into the pc

if you using file just use xcopy to copy the file onto the client pc

hope that helps
0
 
LVL 10

Expert Comment

by:gavsmith
ID: 36941417
Don't know of any way to test the connection using batch script if you are free to use vbscript you could use the script provided here:

http://stackoverflow.com/questions/970724/simple-method-to-test-system-dsns

Let me know if you can use vbscript & I'll let you know how to insert the DSN first also.
0
 

Author Comment

by:sankok432
ID: 36941461
in System DSN we have to create.
i can go for VB script since batch script is not possible.
is the above VBSCRIPT creates new data source which i give and also test the connection whether it is able to access or not?
0
 
LVL 10

Accepted Solution

by:
gavsmith earned 1000 total points
ID: 36941499
The script above only tests the connection, create a vbscript which is like below (example based on sql server, trusted connection) you will need to change YOUR_DSN, ServerName, DatabaseName, YOUR_TABLE_NAME to match your criteria:


On Error Resume Next

'Create the main shell object
Dim objShell
Set objShell = WScript.CreateObject("WScript.Shell")

Dim sRegKey
sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YOUR_DSN\Server"
objShell.RegWrite sRegKey,"ServerName"
sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YOUR_DSN\Trusted_Connection"
objShell.RegWrite sRegKey,"Yes"
sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YOUR_DSN\Database"
objShell.RegWrite sRegKey,"DatabaseName"
sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YOUR_DSN\Driver"
objShell.RegWrite sRegKey,"C:\WINDOWS\system32\SQLSRV32.dll"
Set objShell = Nothing

'Test the connection
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=YOUR_DSN;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT count(*) FROM YOUR_TABLE_NAME" , objConnection, _
    adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Wscript.Echo objRecordSet.RecordCount
objRecordset.Close
objConnection.Close

Open in new window

0
 
LVL 10

Expert Comment

by:gavsmith
ID: 36941525
Opps take out:

On Error Resume Next

or it would be hard to tell if it's failling, once it's working well or you have some other way of reporting the results of the connection test you could put that back in
0
 

Author Comment

by:sankok432
ID: 36941640
let me test it and let u know...
0
 

Author Comment

by:sankok432
ID: 36941907
i have removed line 24 because its giving some error and have added userid and password in the script and have tested and it worked . one question when i create new datasource with the script i am able to see in the registry its created. but when i go to ODBCCP32.CPL and look in system DSN i am not finding der?


Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "DSN=name;UID=;PWD=;SERVER=;"

MsgBox "Connected"

objConnection.Close
0
 
LVL 10

Expert Comment

by:gavsmith
ID: 36942056
You should declare the variables before using them so put line 24 back in and before it add:

Dim objConnection, objRecordSet

Open in new window


To show in ODBCCP32.CPL also add:

sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\YOUR_DSN"
objShell.RegWrite sRegKey,"SQL Server"

Open in new window


0
 

Assisted Solution

by:sankok432
sankok432 earned 0 total points
ID: 36959974
hi,
thank you the script which u have given has solved my issues.
0
 

Author Closing Comment

by:sankok432
ID: 36978281
Thank you dude for giving the exact outside what i require
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question