Re: Connection error from VBScript

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


.

Relevant Pages

  • IPTables with Virtual Interfaces and Multiple Public IPs
    ... Multiple Public IP addresses feed into a single ubuntu 7.04 server ... a server for web hosting (again port 80), ... $IPT -P FORWARD DROP ... Then at the end of the script I have ...
    (comp.os.linux.networking)
  • IPTables with Virtual Interfaces and Multiple Public IPs
    ... Multiple Public IP addresses feed into a single ubuntu 7.04 server ... a server for web hosting (again port 80), ... $IPT -P FORWARD DROP ... Then at the end of the script I have ...
    (comp.security.firewalls)
  • Re: Scripting Printers
    ... Steve Seguis - MCSE, MVP Windows Server, SCJP ... > just need some way to get the Port Name and IP address and then the Print ... > loss as to why the only sample script supported by NT is the List Printer ... I have a couple of scripts from Script ...
    (microsoft.public.scripting.vbscript)
  • Re: ms sec fix caused SocketException: An attempt was made to access a socket in a way forbidden by
    ... As you mentioned that it is the TCP server channel that cause the problem. ... maybe the sec patch has reset the port setting. ... Microsoft MSDN Online Support Lead ... When the MS sec fixes were installed last night, this code now fails with: ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: To IPSec Packet Filter OR Not To IPSec Packet Filter - that is the question
    ... that I wrote a script that can make the registry changes: ... > assigned a random port from Winsock that it uses until the machine is ... > filter, to come into a specific server port, but in this case I do not ... >> sets the above policy during that window of time, ...
    (microsoft.public.win2000.security)