At 10:21 05/04/02 +0000, you wrote:

>--- In ib-support@y..., "todderamaa" <todd.compass@s...> wrote:
> > We are very happy with Interbase but, we have some clients who have
> > SQL Server as their corporate database and would like our application
> > to use SQL Server.
> >
> > How much of a learning curve is there to move from Interbase to SQL
> > Server?
> >
> > Does anyone have experience with connecting to a SQL Server database
> > via the BDE? What other connection options are there?
> >
> > Todd
>
>Connection : BDE is a thing of the past. Even Borland is trying to
>move away from it with dbExpress. Also have a look at www.sql-direct.com

I agree with you, but when I create this systems the only way was using BDE
with SQL Links or BDE via ODBC, I choose BDE via ODBC. I have no major
problems.

>Learning curve:
>function are different (e.g. Datepart vs Extract for dates, getdate()
>vs Cast ('TODAY' as date), date fields are datetime
>
>Stored procedures: there is no concept of SUSPEND, so you cannot use
>an SP in a select if you need to, you will then have to create
>temporary tables to mimic this behaviour.

oopsss.

You can create selectable stored procedures in MS SQL: just like this

Create Procedure Test as
begin
select * from Friends
end;

of course you could put "any" kind of logic inside your SP

>Triggers : no choice before or after, I believe it's always after (but
>could be corrected here), they run only once per batch if you do a
>batch update of a table for example

Ok. But you have pseudo-tables called inserted and deleted that you can
move, search, select, count, sum, etc. like an ordinary table, in case you
are doing a insert in the trigger escope the inserted table have all record
that were inserted, if you are doing an update in the trigger the deleted
table have the old values and the inserted have the new values.

>Table joins : if you've used the JOIN syntax instead of where
>Table.fieldname = table.fieldname syntax, you'll be safe, otherwise MS
>rekons that they do not guarantee the same results between the 2
>syntaxes. (since 6.5)

I don't know this "feature", but I always use join I think it's clear to
read the SQL code

>Autoincrement fields: FB/IB uses generators (like Oracle) MS uses a
>datatype. in 6.5 & 7 you could sometimes have to apply a repair of the
>database if the autoincrement counter got somehow corrupted.

it gets corrupted very often in my developemt database, but in the
deployment databse, where few backups/restores are done, this
works without problems.

>SQlserver allowed testing the value NULL using either field = Null or
>field is NULL, Version 7 still allows that but the first syntax does
>not work anymore (at all)
>
>Another are to look at is the security model: MS is pushing more and
>more towards 'integrated' security is the NT logon is the Sql server
>user name & password. (MSSQL 2000 does not have the SQL server-only
>model) This is somewhat different from FB

I think "integrated" security is a good thing since you have a central
point of users, just update in one place, and the user does not need to
"remember" the database password or even log in, since NT authenticates the
user trough his logon. I think it's a good feature.

>depending on what you users are running with, there is a large
>difference between 6.5 & 7, less between 7 and 2000. Make sure yopu
>always run with the latest service packs, and they MUST be in synch
>between Sql server and NT. Give a month to a SP before applying it
>(chances are there will be an 'a' version of the SP :) ) but then
>apply it for both the server and the db server)
>
>last, a personnal opinion, sql server has greatly improved over time
>but FB with all it's qualities (and the quality of the development
>team behind it) is still much better. I am converting more & more
>SQLserver users to FB. This takes time, and needs convincing but can
>be done.

Just one point where I think SQL beats FB/IB, it's the query optimizer,
they generate more "inteligent" use of indexes mainly in agregate functions
(like max(ID), min (ID)), it's lightning fast.

But you have a major problem, SQL just runs on NT, I think it's a very bad
point, having no choices is not a good thing.

>May be a bridge between the two would be a good idea ?
>
>Hope the above helped a little
>
>Didier

Just a thing that I remeber now, In 6.5 you do not have the:
"alter table X drop column Y"
So if you need to remove one column you will have a lot of work if it's
used in views, SP's, triggers and MAINLY referenced by FK's in other tables.

Will need to remove every reference in FK's
Rename the table
create another one
import the data from old.
recreate every FK's

It can be very anoying.


Hope I have show my points. Note I use SQL Server 6.5, so, maybe, some of
my statments could be wrong.

Alexandre