How to remote connect to the SQL server 2005 when th ere is a ‘\’ in the SQL server name, such a


#1

Hi, Guys,

How to remote connect to the SQL server 2005 when there is a ‘\’ in the
SQL
server name, such as 192.168.0.11\active?

In fact, when I try to remote connect it by the SQL 2005 client,
everything
goes well.

On local, there is a SQL 2005 server, it is:
Server name: 192.168.132.157
User: Wesley
Password: 111111
I can connect it successfully by:
db=SqlServer.new(‘192.168.132.157’, ‘Wesley’, ‘111111’)

db.open(‘active_local’)

But on another remote server, it is SQL 2005 with the value like:
Server name: 192.168.0.11\thesql
User: Wesley
Password: abc?de
I can’t connect it successfully by:
db=SqlServer.new(‘192.168.0.11\thesql’, ‘Wesley’, ‘abc?de’)

db.open(‘active_int’)
or
db=SqlServer.new(‘192.168.0.11\thesql’, ‘Wesley’, ‘abc?de’)

db.open(‘active_int’)**

I get error message:

E:/NetbeansProjects/ActiveTrainer/lib/sql-server.rb:26:in
`method_missing’: Open (WIN32OLERuntimeError)

OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server

 * [dbmssocn] **General network error. Check your network

documentation. *

HRESULT error code:0x80020009

  Exception occurred.        from

E:/NetbeansProjects/ActiveTrainer/lib/sql-server.rb:26:in `open’

    from E:/NetbeansProjects/ActiveTrainer/lib/sql-server.rb:76

The *26 *above points to the sentence below in Red color.

My connect string is:

def initialize(host, username = ‘sa’, password=’’)

  • @connection = nil*
    
  • @data = nil*
    
  •     @host = host*
    
  •     @username = username*
    
  •     @password = password*
    

end

def open(database)

  • Open ADO connection to the SQL Server database*

  • connection_string = “Provider=SQLOLEDB.1;”*

  • connection_string << “Persist Security Info=False;”*

  • connection_string << “User ID=#{@username};”*

  • connection_string << “password=#{@password};”*

  • connection_string << “Initial Catalog=#{database};”*

  • connection_string << “Data Source=#{@host};”*

  • connection_string << “Network Library=dbmssocn”*

  • *@connection = WIN32OLE.new(‘ADODB.Connection’)

End
Appreciated to any suggestions.
Thanks.
Wesley C…


#2

(Disclaimer: It’s a while since I did anything with SQL Server)

The name after the \ is the instance name which you need to specify
separately from the host.

You should be able to find what you need in the connection string
here: http://www.connectionstrings.com/sql-server-2005

Good luck!


#3

Hi, Sean,
Thank you very much for your post.
It is not SQL express.
I still can’t connect it following the comment in the URL you provided.

Would you please give me suggestion about how to specify separately from
the
host?

Thanks.
Wesley C…


#4

On Wed, Apr 8, 2009 at 3:50 AM, Wesley C. removed_email_address@domain.invalid wrote:

Wesley C…
As I said, it’s a long time since I used this. I think you have to do
something like:

Server=myServerName\theInstanceName

or specify the actual port of the instance:

Data Source=190.190.200.100,1433

where 1433 is the instance port number or maybe both.

I’m afraid I can’t help more than that as I have no way of testing any
of this.

Regards
Sean


#5

Hi, Sean,
Thank you very much.

I have tried following your advice.
In the connection string below, I replace the @host with the format
myServerName\the
InstanceName
or myServerName\the InstanceName
I can’t succeed.
On local machine, when the @host is just an IP address, I can succeed.

connection_string = “Provider=SQLOLEDB.1;”
connection_string << “Persist Security Info=False;”
connection_string << “User ID=#{@username};”
connection_string << “password=#{@password};”
connection_string << “Initial Catalog=#{database};”
connection_string << “Data Source=#{@host}, 1433;”
connection_string << “Network Library=dbmssocn”
@connection = WIN32OLE.new(‘ADODB.Connection’)
@connection.Open(connection_string)

Thanks.
Wesley C…


#6

Hi, Sean,
Thank you very much for your post.

In fact, the developers in my team using Java, when they connect the
remote
SQL server instance, they use the same host and port 1433 as I set in my
script.
When I use
connection_string << “Server=myServerName\theInstanceName”
and
connection_string << “Data Source=#{@host}, 1433;”

Then run my script, I will get the same error *[dbmssocn] **General
network
error. Check your network documentation.

*Have you got any other idea? I have searched a lot from the net, but I
can’t succeed.

Additional: I can
ping Local_IP
telnet Local_IP 1433
successfully.

I can
ping remote_IP
But when telnet remote_ip\instance 1433, I failed, no connection.
Does it matter?

Thanks.
Wesley C…


#7

On Wed, Apr 8, 2009 at 8:57 AM, Wesley C. removed_email_address@domain.invalid wrote:

connection_string = “Provider=SQLOLEDB.1;”
connection_string << “Persist Security Info=False;”
connection_string << “User ID=#{@username};”
connection_string << “password=#{@password};”
connection_string << “Initial Catalog=#{database};”
connection_string << “Data Source=#{@host}, 1433;”

Did you try

connection_string << “Server=myServerName\theInstanceName”

I’m not sure this is right:

connection_string << “Data Source=#{@host}, 1433;”

As far as I remember, named instances won’t be running on port 1433.

Regards,
Sean


#8

Hi, Sean,
The string of local Java developers link to the remote SQL server.

jdbc:inetdae7a://192.168.0.6\thesql:1433?database=remote_int

Thank you very much for your patient.

Wesley C…


#9

One nice guy in my company gets this problem resolved.
Two steps need:

  1. Change the default port from 1433 to the right one.
  2. Remove the instance name from the initialize host name.

Thanks.
Wesley C…


#10

On Tue, Apr 14, 2009 at 7:58 AM, Wesley C. removed_email_address@domain.invalid wrote:

One nice guy in my company gets this problem resolved.
Two steps need:

  1. Change the default port from 1433 to the right one.
  2. Remove the instance name from the initialize host name.

Thanks.
Wesley C…

Hi Wesley,

Glad to hear you solved your problem.

Perhaps you could post a small, complete example to show anyone else
with the same problem how to solve it?

Regards,
Sean