FreeTDS Development Group

["Daniel Morgan" <danmorg AT sc.rr.com>]

Hi,

Hopefully, this will help you.

In Mono's System.Data.SqlClient, support was committed to cvs (and is
included in Mono release 0.20) now for the MS SQL Server 2000 discovery.  If
you want to see how the C# source code,
go to http://www.go-mono.com/download.html and the mcs source for 0.20.

Look in mcs/class/System.Data/System.Data.SqlClient/SqlConnection.cs
for the code.

Let's say I want to connect to Instance NETSDK at Hostname MYHOST, I would
set the Server part in the ConnectionString like "Server=MYHOST\\NETSDK".
Of course,
my complete connection string would be something
like "Server=MYHOST\\NETSDK;Database=GrocerToGo;User
ID=someuser;Password=somepass"
The Server part will be separated into Hostname MYHOST and Instance NETSDK.
It will
send a UDP packet to hostname MYHOST and port 1434 with byte 0x04 and the
Instance name
encoded as ASCII bytes.  Then an attempt is made to receive a UDP packet
which data contains the TCP port to connect.   The actual reply has a bunch
of information separated by semicolons.

// excerpt from SqlConnection.cs

                int port = DiscoverTcpPortViaSqlMonitor (theServerName, 
theInstanceName);
                if(port == -1)
                        // throw exception that server not found
                else
                        // open a TDS 7 connection using the ServerName and 
discovered port

                private int DiscoverTcpPortViaSqlMonitor(string ServerName, 
string
InstanceName)
                {
                        SqlMonitorSocket msock;
                        msock = new SqlMonitorSocket (ServerName, 
InstanceName);
                        int SqlServerPort = msock.DiscoverTcpPort ();
                        msock = null;
                        return SqlServerPort;
                }

                // UdpClient is found in System.Net.Sockets and is
            // an UDP socket
                private sealed class SqlMonitorSocket : UdpClient
                {
                        // UDP port that the SQL Monitor listens
                        private static readonly int SqlMonitorUdpPort = 1434;
                        private static readonly string SqlServerNotExist = 
"SQL Server does not
exist or access denied";

                        private string server;
                        private string instance;

                        internal SqlMonitorSocket (string ServerName, string 
InstanceName)
                                : base (ServerName, SqlMonitorUdpPort)
                        {
                                server = ServerName;
                                instance = InstanceName;
                        }

                        internal int DiscoverTcpPort ()
                        {
                                int SqlServerTcpPort;
                                Client.Blocking = false;
                                // send command to UDP 1434 (SQL Monitor) to 
get
                                // the TCP port to connect to the MS SQL 
server
                                ASCIIEncoding enc = new ASCIIEncoding ();
                                Byte[] rawrq = new Byte [instance.Length + 1];
                                rawrq[0] = 4;
                                enc.GetBytes (instance, 0, instance.Length, 
rawrq, 1);
                                int bytes = Send (rawrq, rawrq.Length);

                                if (!Active)
                                        return -1; // Error

                                bool result;
                                result = Client.Poll (100, 
SelectMode.SelectRead);
                                if (result == false)
                                        return -1; // Error

                                if (Client.Available <= 0)
                                        return -1; // Error

                                IPEndPoint endpoint = new IPEndPoint 
(Dns.GetHostByName
("localhost").AddressList [0], 0);
                                Byte [] rawrs;

                                rawrs = Receive (ref endpoint);

                                string rs = Encoding.ASCII.GetString (rawrs);

                                string[] rawtokens = rs.Split (';');
                                Hashtable data = new Hashtable ();
                                for (int i = 0; i < rawtokens.Length / 2 && i 
< 256; i++) {
                                        data [rawtokens [i * 2]] = rawtokens 
[ i * 2 + 1];
                                }
                                if (!data.ContainsKey ("tcp"))
                                        throw new NotImplementedException 
("Only TCP/IP is supported.");

                                SqlServerTcpPort = int.Parse ((string) data 
["tcp"]);
                                Close ();

                                return SqlServerTcpPort;
                        }
                }

Here is how the SqlConnection is used:

string connstr = "Server=MYHOST\\NETSDK;Database=GrocerToGo;User
ID=myuser;Password=mypass";
SqlConnection con = SqlConnection(connstr);
con.Open();

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org]On Behalf Of Lowden, James K
Sent: Wednesday, February 26, 2003 2:01 PM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] MS SQL Server 2000


> From: Akshay Rao [mailto:Akshay AT crc.ufl.edu]
> Sent: February 26, 2003 11:06 AM
> To: 'freetds AT lists.ibiblio.org'
> Subject: [freetds] MS SQL Server 2000
>
>
> I have a linux box with Apache+PHP (latest of both) and
> Freetds 0.61. I also
> have a remote SQL 2000 Server that I need to connect to.  Here is the
> problem:  When I try to connect to a remote SQL 7.0 Server
> with the 7.0 TDS
> protocol, it works just fine.  However when I try to use the
> 7.0 protocol to
> connect to SQL 2000, it says that the connection couldn't be
> made.  When
> using the 8.0 protocol, I can't connect to SQL 7.0 or 2000.
> I think my
> problem lies with the instance name of the SQL 2000 Server.  How do I
> specify the instance to FreeTDS?  Also, when I use the tsql
> the following is
> my output:
>
> Msg 10019, Level 9, State 0, Server OpenClient, Line 0
> Server xxx.xxx.xxx.xxx not found!
> There was a problem connecting to the server

We haven't worked out the discovery protocol for named instances in SQL
Server 2000.  You have to know the port number you're connecting to, and
specify them individually in freetds.conf.

TDS 8.0 won't connect to SQL Server 7.0.

Does that answer your questions?

--jkl


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds