Re: Connection error from VBScript
- From: "SpecialK" <SpecialK@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 17 Feb 2006 11:50:32 -0800
The only firewall we use is Windows Firewall and that is disabled by default
when on the network. So this does not appear to be a firewall issue.
"Richard Mueller" wrote:
Comments below:.
"SpecialK" <SpecialK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0BA6CD1E-79D9-469F-B10E-BEAB30482B1E@xxxxxxxxxxxxxxxx
I'm running a vbscript to return a recordcount from a SQL Server 2000
table.
The script runs successfully on one workstation but not on others. When it
fails I'm receiving the following errors:
-2147467259 from MS OLE DB Provider, Specified SQL Server not found.
This error was probably raised on
objConnection.Open
This is followed by:
3709 from ADODB.Recordset, The connection cannot be used to perform this
operation.
Once the objConnection.Open fails, then objRecordSet.Open will also fail.
In my experience, you only need TCP/IP connectivity and TCP port 1433 to
query an SQL instance over the network. If your query works on one machine
and not another it makes me think a firewall is blocking or a port is
disabled.
Here are a couple variables: I've tested this against our production
server
(across the WAN) and a backup copy on a local SQL server on the LAN. The
production server uses a SQL instance (servername\instance) while the
backup
copy uses only a server name.
One workstation has no trouble running the script against either database.
The other runs successfully against the local db with only a server name,
but
fails on the WAN server with servername\instance.
I've read that the default instance listens on TCP port 1433. Only named
instances use dynamic port allocation. UDP port 1434 is used to query the
server to determine which port to use for a named instance. If UDP port 1434
is disabled, I think you can connect to the default instance, but not a
named instance. This is really beyond my expertize, but seems relevant.
Again, the issue seems to be firewall related. For example see this link:
http://support.microsoft.com/kb/823938
Here's the connection code:
dataSourceString = "Provider=sqloledb; Network Library=DBMSSOCN;Data
Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";User ID=" &
DBUser & ";Password=" & DBUserPassword &";"
strSQL = "SELECT * FROM CALASGN WHERE ID20GRPID = 'INTERNET' and
ID20STATUS
= 'OPEN'"
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.recordset")
objConnection.ConnectionString = datasourcestring
objConnection.Open
objRecordSet.CursorType = 3
objRecordSet.locktype = 2
objRecordSet.Open strSQL, objConnection
intCount = objRecordSet.RecordCount
THANKS!!!
I hope this helps.
--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
- References:
- Re: Connection error from VBScript
- From: Richard Mueller
- Re: Connection error from VBScript
- Prev by Date: Re: DataSet, Stored Procedures and Return Values
- Next by Date: Re: Service crashing on Calling ADODB->execute method.
- Previous by thread: Re: Connection error from VBScript
- Next by thread: Re: How to get meta data info in ADO.NET 2.0
- Index(es):
Relevant Pages
|
