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

[Wesley Chen]

Hi, Guys,

How to remote connect to the SQL server 2005 when there is a =91\=92 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=3DSqlServer.new('192.168.132.157', 'Wesley', '111111')*

*db.open(=91active_local=92)*

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=3DSqlServer.new('*192.168.0.11*\thesql', 'Wesley', 'abc?de')*

*db.open('active_int')*
or
*db=3DSqlServer.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 =3D 'sa', password=3D'')*

* @connection =3D nil*

* @data =3D nil*

* @host =3D host*

* @username =3D username*

* @password =3D password*

*end*

* *

*def open(database)*

* # Open ADO connection to the SQL Server database*

* connection_string =3D "Provider=3DSQLOLEDB.1;"*

* connection_string << "Persist Security Info=3DFalse;"*

* connection_string << "User ID=3D#{@username};"*

* connection_string << "password=3D#{@password};"*

* connection_string << "Initial Catalog=3D#{database};"*

* connection_string << "Data Source=3D#{@host};"*

* connection_string << "Network Library=3Ddbmssocn"*

* **@connection =3D WIN32OLE.new('ADODB.Connection')*

*End*
Appreciated to any suggestions.
Thanks.
Wesley Chen.

 

[Sean O'Halpin]

(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!

 

[Wesley Chen]

[Note: parts of this message were removed to make it a legal post.]

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 Chen.

 

[Sean O'Halpin]

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 Chen.

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

 

[Wesley Chen]

[Note: parts of this message were removed to make it a legal post.]

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 Chen.

 

[Sean O'Halpin]

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;"

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

 

[Wesley Chen]

[Note: parts of this message were removed to make it a legal post.]

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 Chen.

 

[Wesley Chen]

[Note: parts of this message were removed to make it a legal post.]

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 Chen.

 

[Wesley Chen]

[Note: parts of this message were removed to make it a legal post.]

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 Chen.

 

[Sean O'Halpin]

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 Chen.

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