vbscript SQL server connection

Question

4.00/5 (1 vote)

See more:

I use the following script to connect to a remote sql server

Dim sServer,sDataBaseName ,sConn,oConn,oRS
Dim sUserName,sPassWord 
 
sServer = "test.test.com,1057\testtable\test"
sDataBaseName = "DM_dbname"
sUserName = "username"
sPassWord = "password"
 
'sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=" & sDataBaseName & ";"
sConn="DRIVER={SQL Server};SERVER=" & sServer & ";DATABASE=" & sDataBaseName & ";Encrypt=Yes;"
Set oConn = CreateObject("ADODB.Connection")
oConn.CommandTimeout = 36000
oConn.Open sConn, sUserName, sPassWord

Set FetchData = CreateObject("ADODB.Recordset")
FetchData.open "SELECT COUNT (*) FROM [DM_Reports].[dbo].[Preferences]", oconn
While Not FetchData.eof 
          msgbox(FetchData.Preferences)
FetchData.movenext
Wend

I get this error

Microsoft OLE DB Provider for ODBC Drivers (19, 1) : [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
ADODB.Recordset (22, 1) : The connection cannot be used to perform this operation. It is either closed or invalid in this context.

what could be the problem?

Posted 9-Apr-11 2:00am

Cool Smith

Updated 9-Apr-11 2:03am

Slacker007

v2

Add a Solution

Comments

Cool Smith 9-Apr-11 12:05pm

when i set encrypt to no, i get timeout error
------------------------
Microsoft OLE DB Provider for ODBC Drivers (19, 1) : [Microsoft][ODBC SQL Server Driver]Timeout expired

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-04-09T02:05:00

Solution 1

Based on the error message, it's quite likely the encryption. Try setting Encrypt to no to test if that's the reason.

Seems that this may be MDAC related. There are several posts that re-installing MDAC 2.8 could help. On quite close bug reference: http://support.microsoft.com/kb/839428[^]

Posted 9-Apr-11 2:05am

Wendelius

Updated 9-Apr-11 2:14am

v2

Comments

Cool Smith 9-Apr-11 8:35am

when i set encrypt to no, i get timeout error
------------------------
Microsoft OLE DB Provider for ODBC Drivers (19, 1) : [Microsoft][ODBC SQL Server Driver]Timeout expired

Wendelius 9-Apr-11 8:52am

Seems that the connection cannot be made at all. Few things you could try:
- ping the remote server to see that the overall connectivity works.
- check that there are no firewalls blocking the connection (disable for test if you have any)
- include the username (uid=???) and password (pwd=????) to the connection string
- check the server name. If you're using a named instance, ensure that the instance name is specified

Cool Smith 9-Apr-11 12:11pm

how do i ping a mysl server?
which firewalls mine or on the server?

Wendelius 9-Apr-11 12:45pm

For pinging, open the command prompt, write ping serveripaddress
Both firewalls can cause problems. If you're using for example Windows 7 then it's firewall may be blocking the traffic to port 1433 to the remote server (which is the default port of Sql Server)

Cool Smith 25-Apr-11 5:23am

when i ping like this "ping host:port" i get a bad parameter. how do i ping a particular port. here is the connection string am using now.

"Data Source=www.host.com,57919;Integrated Security=True;User ID=myusername;Password=mypassword;Encrypt=True;TrustServerCertificate=True"