Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Operating System - Microsoft
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