Link to home
Create AccountLog in
Avatar of systematica
systematicaFlag for Italy

asked on

I cant connect to SQL 2005 instance via cscript

I have migrated my database (named LogOnOff) from Sql 2000 std to Sql Express 2005.
Database seems work fine locally but I can connect from any PC in the same netword.
TOP2K8 is the server and instance database is TOP2K8\SBSMONITORING

This is my cscript:

Dim SqlUserName: SqlUserName = "sa"
Dim SqlPassword: SqlPassword = "sa"
Dim SqlServer: SqlServer = "TOP2K8\SBSMONITORING"
Dim SqlDatabase: SqlDatabase = "LogOnOff"
Dim cn, rs
Set cn = CreateObject("ADODB.Connection")
On Error Resume Next
Dim strOpen
strOpen="Provider=SQLOLEDB.1;Data Source=" & SqlServer & ";Initial Catalog=" & SqlDatabase & ";User ID=" & SqlUserName & ";Password=" & SqlPassword & ";Persist Security Info=True"
MsgBox strOpen
cn.Open strOpen
If Err.number <> 0 Then
      MsgBox "Errore: " & Err.Description
      WScript.Quit
else
      MsgBox "Tutto OK"
      WScript.Quit
End If
cn.Close
--------------------------------------------------------
This is the connection string
 User generated image--------------------------------------------------------
This is my error:
 User generated image--------------------------------------------------------

Note: the script work fine locally.
The Sql has TCP protocol enabled.
There is no firewall problem.  

What is missing ?
Thanks Carlo
SOLUTION
Avatar of HugoHiasl
HugoHiasl

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of systematica

ASKER

There are 2 instances.
User generated imageThe first SSEE is accessible locally only.
The second is accessible via TCP from the network
In the second instance (SBSMONITORING) is activated OLE:
 User generated imageI have tried to connect from Sql Server management studio from network and it seems work fine:
 User generated image User generated imageBut it doesn't work if I use cscript.
I have tried to change connection string without results.
Why ???
Carlo
The TCP port for the default instance it seem correct, i think.
 User generated imageCarlo
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
There is no Alias:
 User generated imageIn the second instance TOP2K8\SMSMONITORING i have this TCP settings and port
 User generated imageBut no 1433 port is open
 User generated image
There are meny IP (IP1, IP10, IP11, IP2, IP3.... IP9, IPAll) Activated, Active, TCP port and Dyna TCP port.... I'm very confused ....
What is the right combination of this settings ?
Thanks Carlo
you see the tcp enabled under the SQL client configuration, you need to check under the server options. i.e. under the sql server configuration tool, you have three main areas.
You posted the images that deal with settings for the SQL client. The second is where the server related settings are and that is where you need to check the networking settings and enable the named pipes or TCP as those two are disabled by default.  once you make this change, you would need to restart sql.
Shared Mem, Name Pipes and TCP was activated.
 User generated imageServer restarted again.
No good news...
CL
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Reading documentation I have discover the correct setting:
First I have set up TCP protocol on "Attesa su tutti"
 User generated imageWhen thi soption is set the IP settings are irrelevant. The only important setting is the last IPAll
 User generated imageNow choosing a free port and reload SQL services I can connect to my database with the right port
 User generated image
Many many thanks for your help.
Carlo
My last post is the result of your suggestions.
Thanks