Posted by Stu on 07/28/06 04:37

Wow; a lot of meat here. Let me try to address some of the more
obvious ones.
Ted wrote:
> I managed to get it installed OK, along side MS Visual Studio 2005
> (with which I received it). During the install, I made sure I
> installed everything.
>
> I have developed a number of applications using MySQL v 5 and
> PostgreSQL, but I have not worked with MS SQL. Playing with it after
> installing it, and running through several tutorials, I generally like
> what I see, but there are a few issues (so far - I am sure others will
> crop up as time progresses).
>
> The first issue is that I have yet to connect to the server from a
> remote client. I have a couple machines I use for testing distributed
> applications, in my own, really tiny LAN. With clients on one machine,
> and the server on another, how can get the client apps on the client
> machine to talk to the server on my test server machine. Related to
> this, my client's MIS has acquired a dedicated machine, along with MS
> SQL Server, on which at least some of our applications will be
> deployed. Can this instance of MS SQL Server be managed from another
> machine (likely the notebook I use for development) without me being in
> the same room as the new server? The client runs a Windows only
> network, so I'll ignore connectivity issues from Linux clients. I
> assume the specific question of managing an instance of a server on one
> machine from another machine on the same network is just a special case
> of getting any of the client applications to talk to the server.
> Right?
>

By default, SQL Server 2005 Express, Evaluation, and Developer editions
allow local client connections only. You can change this setting by
using the SQL Server 2005 Surface Area Configuration tool. Once you
launch the tool, look under Database Engine and select Remote
Connections.

> The second issue involves a question I was asked to investigate is that
> of porting a MySQL database to MS SQL. I found one article on this,
> and none of the methods suggested seems to be practical. My
> inclination is to leave it and connect the MS SQL modules of the larger
> application to MySQL modules in the client application. The impression
> I have is that there are too many differences between the MySQL dialect
> and the MS SQL dialect. For example, MS SQL did not like either of the
> following options for creating a table:
>
> CREATE TABLE IF NOT EXISTS my_table (...)
>
> DROP TABLE IF EXISTS my_table;
> CREATE TABLE my_table (...)
>
> In both cases, it seems that the "IF NOT EXISTS" and "IF EXISTS" is
> what MS SQL is complaining about. It stops complaining if the
> statement is made unconditional. When I ask MS SQL to analyze my SQL
> scripts (hundreds of SQL statements), it seems there are countless
> things it doesn't like. I shudder to think about how it will respond
> to the more meaty SQL statements in some of my other scripts, including
> stored procedures, triggers, subqueries, &c.

You've been speaking Canadian French and just went to New Orleans :)
Both MySQL and SQL Server support ANSI SQL, but both also have their
own proprietary extensions which perform better or are legacy
components on their individual platforms. Unless your database is 100%
ANSI compliant, then you're going to have trouble porting it from one
platform to the next.

>
> Last, for now, a key part of our application so far involves running
> perl scripts that ultimately submit SQL scripts to the RDBMS back end.
> We get a data feed that we automatically load into the database, and
> then immediately analyse.
>
> We construct, and use Perl to construct and then execute (using
> qz/$cmdline/) use a statement like:
>
> mysql -u $uid -p $pwd --database=$the_db < my_sql_script.sql
>
> I did find the MS SQL equivalent of 'mysql', but I am not sure how this
> will change if my MS SQL client is on one machine and the server is on
> another in our client's LAN. And in the script is a sequence of LOAD
> INFILE statements. I expect this will be a big problem given how
> different MySQL and PostgreSQL are in how they load data from a file.
>
> There are business reasons for using MS SQL for some parts of the suite
> of applications we're working on, but I have my doubts about the
> viability of porting our existing code to MS SQL. Is there a good
> resource that compares the SQL dialects used by MySQL and MS SQL, in
> case my first impression is too negative?

Not that I'm aware of, but there are several resources on the
differences between MySQL and ANSI SQL; if you can get your database to
be ANSI-compliant, then it should run on most database platforms.

>
> Any guidance, or information, or pointers to useful information would
> be appreciated. Yes, I have been pouring over the documentation I have
> with MS Visual Studio andMS SQL Server, but there is so much it is like
> finding a needle in a hay stack.
>
> Thanks
>
> Ted

HTH,
Stu

[Back to original message]