General
cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with connectivity of Ms SQL through VBScript

SOLVED
Go to solution
Nitin Kumar Gupta
Trusted Contributor

Problem with connectivity of Ms SQL through VBScript

Hi

I want to connect the SQL through VBScript. But it is not working. And I am not able to fine where is the problem.

So if anyone understand the problem, please suggest me what should I change, or what is going wrong.

The VBScript I am using:



' Initialize variables

Dim cn
Dim ServerName
Dim DatabaseName

Set cn = CreateObject("ADODB.Connection")
' Put text box values into connection variables.
ServerName = "pfabwsus.dlh.st.com"
DatabaseName = "NITIN"

WSCript.Echo "Test 1"


' *******************************************************
' Specify the OLE DB provider.
cn.Provider = "SQLOLEDB"

' Set SQLOLEDB connection properties.
cn.Properties("Data Source").Value = ServerName
cn.Properties("Initial Catalog").Value = DatabaseName

' Windows NT authentication.
cn.Properties("Integrated Security").Value = "SSPI"
'********************************************************

'WScipt.Echo cn.Properties("ConnectionTimeout")

WScript.Echo "Connection Properties are set."
' Open the database.
cn.Open

WScript.Echo "Connection Established."
cn.Close
cn.Connection = Nothing



The same thing I am writing in the attachment.


Thanks and Regards
Nitin Kumar Gupta
2 REPLIES
Soujanya
Valued Contributor

Re: Problem with connectivity of Ms SQL through VBScript

Hello Nitin,

Soujanya
Valued Contributor
Solution

Re: Problem with connectivity of Ms SQL through VBScript

Hi again

This weblink would be of much help .. Check this out :

http://www.databasejournal.com/features/mssql/article.php/2238221


You can also try this chunk of code :

sql="SELECT * FROM UserLogs"
Set Connection = CreateObject("ADODB.Connection")
Connection.Open
(" Provider=sqloledb;server=MyMSSQLServerNa
me;database=NameOfDatabaseIUsed;uid=SQLU
serName;pwd=SQLUserPWD;")
Set RS = CreateObject("ADODB.RecordSet")
RS.ActiveConnection = Connection

RS.Open(sql)
Do While Not RS.BOF And Not RS.EOF
WScript.Echo(RS(1))
RS.MoveNext
Loop
RS.Close

Set RS = Nothing
Connection.Close
Set Connection = Nothing

-- code ends --

word wrapping might occur for Set Connection and Connection.Open statements.

If you have trouble authenticating with domain credentials, create a
SQL user and passed those credentials. Everything will work fine.

For more help on VBScript, you can find the attachment in my previous post.


Cheers,
Soujanya.R