Some issues regarding using MS SQL

[Ted]

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?

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.

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?

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

[Stu]

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