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

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Wesley C. (Guest)
on 2009-04-07 19:23
(Received via mailing list)
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..
Sean O. (Guest)
on 2009-04-07 23:29
(Received via mailing list)
(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 C. (Guest)
on 2009-04-08 06:51
(Received via mailing list)
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..
Sean O. (Guest)
on 2009-04-08 10:33
(Received via mailing list)
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
Wesley C. (Guest)
on 2009-04-08 11:58
(Received via mailing list)
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..
Sean O. (Guest)
on 2009-04-08 19:59
(Received via mailing list)
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
Wesley C. (Guest)
on 2009-04-09 05:43
(Received via mailing list)
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..
Wesley C. (Guest)
on 2009-04-09 11:56
(Received via mailing list)
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..
Wesley C. (Guest)
on 2009-04-14 10:58
(Received via mailing list)
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..
Sean O. (Guest)
on 2009-04-14 15:36
(Received via mailing list)
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
This topic is locked and can not be replied to.