topic Problem with connectivity of Ms SQL through VBScript in Operating System - Microsoft

Problem with connectivity of Ms SQL through VBScript

Nitin Kumar Gupta — Wed, 19 Jul 2006 01:58:45 GMT

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

Re: Problem with connectivity of Ms SQL through VBScript

Soujanya — Wed, 19 Jul 2006 03:21:23 GMT

Hello Nitin,

Re: Problem with connectivity of Ms SQL through VBScript

Soujanya — Wed, 19 Jul 2006 03:35:43 GMT

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