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

Discussion in 'Ruby' started by Wesley Chen, Apr 7, 2009.

  1. Wesley Chen

    Wesley Chen Guest

    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.
    Wesley Chen, Apr 7, 2009
    #1
    1. Advertising

  2. Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

    (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!
    Sean O'Halpin, Apr 7, 2009
    #2
    1. Advertising

  3. Wesley Chen

    Wesley Chen Guest

    Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

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


    On Wed, Apr 8, 2009 at 3:26 AM, Sean O'Halpin <>wrote:

    > (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, Apr 8, 2009
    #3
  4. Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

    On Wed, Apr 8, 2009 at 3:50 AM, Wesley Chen <> wrote:
    > 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
    Sean O'Halpin, Apr 8, 2009
    #4
  5. Wesley Chen

    Wesley Chen Guest

    Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

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


    On Wed, Apr 8, 2009 at 2:33 PM, Sean O'Halpin <>wrote:

    > On Wed, Apr 8, 2009 at 3:50 AM, Wesley Chen <> wrote:
    > > 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, Apr 8, 2009
    #5
  6. Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

    On Wed, Apr 8, 2009 at 8:57 AM, Wesley Chen <> wrote:
    > 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
    Sean O'Halpin, Apr 8, 2009
    #6
  7. Wesley Chen

    Wesley Chen Guest

    Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

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


    On Wed, Apr 8, 2009 at 11:59 PM, Sean O'Halpin <>wrote:

    > On Wed, Apr 8, 2009 at 8:57 AM, Wesley Chen <> wrote:
    > > 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, Apr 9, 2009
    #7
  8. Wesley Chen

    Wesley Chen Guest

    Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

    [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, Apr 9, 2009
    #8
  9. Wesley Chen

    Wesley Chen Guest

    Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

    [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.
    Wesley Chen, Apr 14, 2009
    #9
  10. Re: How to remote connect to the SQL server 2005 whe n there is a‘\’ in the SQL server name, such as 192.168 .0.11\active?

    On Tue, Apr 14, 2009 at 7:58 AM, Wesley Chen <> 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 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
    Sean O'Halpin, Apr 14, 2009
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve - DND
    Replies:
    0
    Views:
    343
    Steve - DND
    Nov 11, 2003
  2. XJ
    Replies:
    8
    Views:
    1,754
  3. Wesley Chen
    Replies:
    0
    Views:
    221
    Wesley Chen
    Apr 7, 2009
  4. Replies:
    2
    Views:
    279
  5. Peter Makholm

    Substitutions based on Posix ERE's in perl

    Peter Makholm, Apr 5, 2009, in forum: Perl Misc
    Replies:
    1
    Views:
    115
    smallpond
    Apr 6, 2009
Loading...

Share This Page