Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: Detect TCP port number of a named instance on the local network?

    Hi!
    Our product uses JDBC to connect to named instances of MSDE running on the local network. Since JDBC doesn't support instance names in the connection string, we must use computername:portnumber in the connection string. I am doing the installation and must list the TCP port numbers on which listen all MSDE named instances running on the local network. I know how to collect the computer names running MSDE but how to get the port numbers? One possible way is to use the remote registry service and to get the port numbers directly from the registry of the remote computers, but this may not work on some editions of windows xp and windows 98 and even on the other windows versions if the remote registry service has been disabled... So is there another way?
    Thanks in advance!

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    FYI... Microsoft's free type 4 jdbc driver supports named instances.

  3. #3
    Join Date
    Dec 2003
    Posts
    3
    Excuse me, may be I'm too dumb, but I can not understand you - the only thing I understand is that JDBC can support named instances, but I don't understand how? Using the following convention given by Microsoft it seems to not work: "ComputerName\InstanceName"?
    Thanks for your reply!

  4. #4
    Join Date
    Sep 2003
    Posts
    364

    Smile

    Here are the connection string properties for the Microsoft 2000 JDBC Driver SP 1. I'm not sure if you can connect to named instances pre version SP1. I'll try to find an example to post that we're actually using.

    DatabaseName
    OPTIONAL The name of the SQL Server database to which you want to connect.

    HostProcess
    OPTIONAL The process ID of the application connecting to SQL Server 2000. The supplied value appears in the "hostprocess" column of the sysprocesses table.

    NetAddress
    OPTIONAL The MAC address of the network interface card of the application connecting to SQL Server 2000. The supplied value appears in the "net_address" column of the sysprocesses table.

    Password The case-insensitive password used to connect to your SQL Server database.

    PortNumber
    OPTIONAL The TCP port (use for DataSource connections only). The default is 1433.

    ProgramName
    OPTIONAL The name of the application connecting to SQL Server 2000. The supplied value appears in the "program_name" column of the sysprocesses table.

    SelectMethod
    SelectMethod={cursor | direct}. Determines whether database cursors are used for Select statements. Performance and behavior of the driver are affected by the SelectMethod setting.

    SelectMethod (cont.)
    Direct-The direct method sends the complete result set in one request to the driver. It is useful for queries that only produce a small amount of data that you fetch completely. You should avoid using direct when executing queries that produce a large amount of data, as the result set is cached completely on the client and constrains memory. In this mode, each statement requires its own connection to the database. This is accomplished by "cloning" connections. Cloned connections use the same connection properties as the original connection; however, because transactions must occur on a single connection, auto commit mode is required. Due to this, JTA is not supported in direct mode. In addition, some operations, such as updating an insensitive result set, are not supported in direct mode because the driver must create a second statement internally. Exceptions generated due to the creation of cloned statements usually return an error message similar to "Cannot start a cloned connection while in manual transaction mode."
    Cursor-When the SelectMethod is set to cursor, a server-side cursor is generated. The rows are fetched from the server in blocks. The JDBC Statement method setFetchSize can be used to control the number of rows that are fetched per request. The cursor method is useful for queries that produce a large amount of data, data that is too large to cache on the client. Performance tests show that the value of setFetchSize has a serious impact on performance when SelectMethod is set to cursor. There is no simple rule for determining the value that you should use. You should experiment with different setFetchSize values to find out which value gives the best performance for your application.
    The default is direct.

    SendStringParametersAsUnicode
    SendStringParametersAsUnicode={true | false}. Determines whether string parameters are sent to the SQL Server database in Unicode or in the default character encoding of the database. True means that string parameters are sent to SQL Server in Unicode. False means that they are sent in the default encoding, which can improve performance because the server does not need to convert Unicode characters to the default encoding. You should, however, use default encoding only if the parameter string data that you specify is consistent with the default encoding of the database.
    The default is true.

    ServerName
    The IP address (use for DataSource connections only).
    To connect to a named instance, specify server_name\instance_name for this property, where server_name is the IP address and instance_name is the name of the instance to which you want to connect on the specified server.

    User
    The case-insensitive user name used to connect to your SQL Server database.

    Wsid
    The workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the Transact-SQL HOST_NAME function.

  5. #5
    Join Date
    Sep 2003
    Posts
    364

    Smile

    Here's an code sample that I use. Hope this helps.

    jdbc:microsoft:sqlserver://ip address of server;ServerName=;DatabaseName=

    (Notice you don't have to specify the port, only the ip address of the server where the named instances reside.)


    jdbc:microsoft:sqlserver://10.5.20.208;ServerName=TLMSSQL3\DLMSSQL3;DatabaseN ame=prmmigrate1

  6. #6
    Join Date
    Dec 2003
    Posts
    3
    Thanks a lot, a lot, a lot!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •