Hallo,
I am migrating complex database from Access 2000 to MSSQL server 8. This
database is used by web applications. Some time I need to be able swicth
between the old Access version and new SQL server driven one. I have
different connection strings, and connecting works well.
However, some sql query incompatibility has appeared...
Number of Access tables have boolean (Yes/No) fields. In Access, I could run
a query like this:
SELECT id FROM myTable WHERE myBooleanField
it returns all records where myBooleanField value is true. Now, when I
migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
every Access "true" value, and "0" for "false" values. With this new field
type, I can no longer run the above mentioned query. I must change it to
this:
SELECT id FROM myTable WHERE myBooleanField=1
Now, this would not be a crazy problem, however I need to be able to run it
for some time on both database solutions, Access and MSSQL, so I really need
to have compatible queries.
One solution I see now would be converting the field type in access from
boolean to byte, and then run the "=1" queries. But that could involve range
of incompatibilities within the application itself, that assumes that these
fields are "true" or "false" instead of "1" and "0". Browsing through the
application to find these places is not really the best thing I can imagine.
So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
to solve my problem?
Thanks,
PavilsOne issue is that Access/Jet booleans are not true booleans, but can
also contain null values. Change the Jet columns to Not Null and
provide a default value (same with SQLS). This lets you avoid the
three-valued logic trap. In your SQL statements and code, perform all
comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
everywhere, so both engines are going to interpret "<>0" the same way.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Hallo,
>I am migrating complex database from Access 2000 to MSSQL server 8. This
>database is used by web applications. Some time I need to be able swicth
>between the old Access version and new SQL server driven one. I have
>different connection strings, and connecting works well.
>However, some sql query incompatibility has appeared...
>Number of Access tables have boolean (Yes/No) fields. In Access, I could run
>a query like this:
>SELECT id FROM myTable WHERE myBooleanField
>it returns all records where myBooleanField value is true. Now, when I
>migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
>every Access "true" value, and "0" for "false" values. With this new field
>type, I can no longer run the above mentioned query. I must change it to
>this:
>SELECT id FROM myTable WHERE myBooleanField=1
>Now, this would not be a crazy problem, however I need to be able to run it
>for some time on both database solutions, Access and MSSQL, so I really need
>to have compatible queries.
>One solution I see now would be converting the field type in access from
>boolean to byte, and then run the "=1" queries. But that could involve range
>of incompatibilities within the application itself, that assumes that these
>fields are "true" or "false" instead of "1" and "0". Browsing through the
>application to find these places is not really the best thing I can imagine.
>So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
>to solve my problem?
>Thanks,
>Pavils
>|||Thanks Mary,
This is a good suggestion, it helps me to move further...
Now another problem has arised:
Access runs this query without complaints:
UPDATE myTable SET myBool = NOT myBool WHERE id=100
What could be a syntax working on both Jet and MSSQL engines?
I tried this one...
UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
but I get error -
'IIf' is not a recognized function name.
Maybe some other smart syntax?
Thanks,
-- Pavils
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@.mailbox.riga.lv> wrote:
> >Hallo,
> >
> >I am migrating complex database from Access 2000 to MSSQL server 8. This
> >database is used by web applications. Some time I need to be able swicth
> >between the old Access version and new SQL server driven one. I have
> >different connection strings, and connecting works well.
> >
> >However, some sql query incompatibility has appeared...
> >
> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
run
> >a query like this:
> >
> >SELECT id FROM myTable WHERE myBooleanField
> >
> >it returns all records where myBooleanField value is true. Now, when I
> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
for
> >every Access "true" value, and "0" for "false" values. With this new
field
> >type, I can no longer run the above mentioned query. I must change it to
> >this:
> >
> >SELECT id FROM myTable WHERE myBooleanField=1
> >
> >Now, this would not be a crazy problem, however I need to be able to run
it
> >for some time on both database solutions, Access and MSSQL, so I really
need
> >to have compatible queries.
> >
> >One solution I see now would be converting the field type in access from
> >boolean to byte, and then run the "=1" queries. But that could involve
range
> >of incompatibilities within the application itself, that assumes that
these
> >fields are "true" or "false" instead of "1" and "0". Browsing through the
> >application to find these places is not really the best thing I can
imagine.
> >
> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
approaches
> >to solve my problem?
> >
> >Thanks,
> >
> >Pavils
> >
>|||One thing that's going to continue to trip you up in writing your
queries is not understanding the fundamental differences between
Access SQL and T-SQL, the two main points of which are:
-- Access SQL relies heavily on the expression service and VBA to
compensate for the fact that it has no programming language features
on its own. You can execute VBA functions like Iif inside of a query
only against the Jet engine with Access as a FE. .
-- T-SQL contains many programming language features, like variables.
It does not rely on an external programming language to provide this
functionality the way Access does. When you try VBA syntax, it fails
because SQLS has no knowledge of VBA.
For the most part, you'll want to stay away from language elements
that are specific to either Jet SQL or T-SQL. I'd start by writing the
queries using the Query Analyzer, then copying/pasting them into an
Access query window to see if the syntax works in both. SQL Books
Online is a good reference--Access SQL documentation is practically
non-existant. Besides the language elements, be aware that you're also
going to have problems with delimiters when working with datetime
values -- Jet uses # and SQLS uses '.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 18:09:50 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Thanks Mary,
>This is a good suggestion, it helps me to move further...
>Now another problem has arised:
>Access runs this query without complaints:
>UPDATE myTable SET myBool = NOT myBool WHERE id=100
>What could be a syntax working on both Jet and MSSQL engines?
>I tried this one...
>UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
>but I get error -
>'IIf' is not a recognized function name.
>Maybe some other smart syntax?
>Thanks,
>-- Pavils
>"Mary Chipman" <mchip@.nomail.please> wrote in message
>news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
>> One issue is that Access/Jet booleans are not true booleans, but can
>> also contain null values. Change the Jet columns to Not Null and
>> provide a default value (same with SQLS). This lets you avoid the
>> three-valued logic trap. In your SQL statements and code, perform all
>> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
>> everywhere, so both engines are going to interpret "<>0" the same way.
>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com
>> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
>> <pavils@.mailbox.riga.lv> wrote:
>> >Hallo,
>> >
>> >I am migrating complex database from Access 2000 to MSSQL server 8. This
>> >database is used by web applications. Some time I need to be able swicth
>> >between the old Access version and new SQL server driven one. I have
>> >different connection strings, and connecting works well.
>> >
>> >However, some sql query incompatibility has appeared...
>> >
>> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
>run
>> >a query like this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField
>> >
>> >it returns all records where myBooleanField value is true. Now, when I
>> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
>for
>> >every Access "true" value, and "0" for "false" values. With this new
>field
>> >type, I can no longer run the above mentioned query. I must change it to
>> >this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField=1
>> >
>> >Now, this would not be a crazy problem, however I need to be able to run
>it
>> >for some time on both database solutions, Access and MSSQL, so I really
>need
>> >to have compatible queries.
>> >
>> >One solution I see now would be converting the field type in access from
>> >boolean to byte, and then run the "=1" queries. But that could involve
>range
>> >of incompatibilities within the application itself, that assumes that
>these
>> >fields are "true" or "false" instead of "1" and "0". Browsing through the
>> >application to find these places is not really the best thing I can
>imagine.
>> >
>> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
>approaches
>> >to solve my problem?
>> >
>> >Thanks,
>> >
>> >Pavils
>> >
>|||Thanks, Mary for your help
I've been doing databasing in Access, mySQL, and some generic work in
interBase, MSSQL, and Oracle... but in this case I have to port fairly
complex application to MSSQL and I really see all those blanks I miss in the
knowledge about MSSQL. Well, but that's the nature of learning curve, being
very steep :) I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
Showing posts with label applications. Show all posts
Showing posts with label applications. Show all posts
Friday, March 30, 2012
Migrating from Access, boolean field problem
Hallo,
I am migrating complex database from Access 2000 to MSSQL server 8. This
database is used by web applications. Some time I need to be able swicth
between the old Access version and new SQL server driven one. I have
different connection strings, and connecting works well.
However, some sql query incompatibility has appeared...
Number of Access tables have boolean (Yes/No) fields. In Access, I could run
a query like this:
SELECT id FROM myTable WHERE myBooleanField
it returns all records where myBooleanField value is true. Now, when I
migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
every Access "true" value, and "0" for "false" values. With this new field
type, I can no longer run the above mentioned query. I must change it to
this:
SELECT id FROM myTable WHERE myBooleanField=1
Now, this would not be a crazy problem, however I need to be able to run it
for some time on both database solutions, Access and MSSQL, so I really need
to have compatible queries.
One solution I see now would be converting the field type in access from
boolean to byte, and then run the "=1" queries. But that could involve range
of incompatibilities within the application itself, that assumes that these
fields are "true" or "false" instead of "1" and "0". Browsing through the
application to find these places is not really the best thing I can imagine.
So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
to solve my problem?
Thanks,
PavilsOne issue is that Access/Jet booleans are not true booleans, but can
also contain null values. Change the Jet columns to Not Null and
provide a default value (same with SQLS). This lets you avoid the
three-valued logic trap. In your SQL statements and code, perform all
comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
everywhere, so both engines are going to interpret "<>0" the same way.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Hallo,
>I am migrating complex database from Access 2000 to MSSQL server 8. This
>database is used by web applications. Some time I need to be able swicth
>between the old Access version and new SQL server driven one. I have
>different connection strings, and connecting works well.
>However, some sql query incompatibility has appeared...
>Number of Access tables have boolean (Yes/No) fields. In Access, I could ru
n
>a query like this:
>SELECT id FROM myTable WHERE myBooleanField
>it returns all records where myBooleanField value is true. Now, when I
>migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" fo
r
>every Access "true" value, and "0" for "false" values. With this new field
>type, I can no longer run the above mentioned query. I must change it to
>this:
>SELECT id FROM myTable WHERE myBooleanField=1
>Now, this would not be a crazy problem, however I need to be able to run it
>for some time on both database solutions, Access and MSSQL, so I really nee
d
>to have compatible queries.
>One solution I see now would be converting the field type in access from
>boolean to byte, and then run the "=1" queries. But that could involve rang
e
>of incompatibilities within the application itself, that assumes that these
>fields are "true" or "false" instead of "1" and "0". Browsing through the
>application to find these places is not really the best thing I can imagine
.
>So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
>to solve my problem?
>Thanks,
>Pavils
>|||Thanks Mary,
This is a good suggestion, it helps me to move further...
Now another problem has arised:
Access runs this query without complaints:
UPDATE myTable SET myBool = NOT myBool WHERE id=100
What could be a syntax working on both Jet and MSSQL engines?
I tried this one...
UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
but I get error -
'IIf' is not a recognized function name.
Maybe some other smart syntax?
Thanks,
-- Pavils
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.
4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@.mailbox.riga.lv> wrote:
>
run
for
field
it
need
range
these
imagine.
approaches
>|||One thing that's going to continue to trip you up in writing your
queries is not understanding the fundamental differences between
Access SQL and T-SQL, the two main points of which are:
-- Access SQL relies heavily on the expression service and VBA to
compensate for the fact that it has no programming language features
on its own. You can execute VBA functions like Iif inside of a query
only against the Jet engine with Access as a FE. .
-- T-SQL contains many programming language features, like variables.
It does not rely on an external programming language to provide this
functionality the way Access does. When you try VBA syntax, it fails
because SQLS has no knowledge of VBA.
For the most part, you'll want to stay away from language elements
that are specific to either Jet SQL or T-SQL. I'd start by writing the
queries using the Query Analyzer, then copying/pasting them into an
Access query window to see if the syntax works in both. SQL Books
Online is a good reference--Access SQL documentation is practically
non-existant. Besides the language elements, be aware that you're also
going to have problems with delimiters when working with datetime
values -- Jet uses # and SQLS uses '.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 18:09:50 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Thanks Mary,
>This is a good suggestion, it helps me to move further...
>Now another problem has arised:
>Access runs this query without complaints:
>UPDATE myTable SET myBool = NOT myBool WHERE id=100
>What could be a syntax working on both Jet and MSSQL engines?
>I tried this one...
>UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
>but I get error -
>'IIf' is not a recognized function name.
>Maybe some other smart syntax?
>Thanks,
>-- Pavils
>"Mary Chipman" <mchip@.nomail.please> wrote in message
> news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.
4ax.com...
>run
>for
>field
>it
>need
>range
>these
>imagine.
>approaches
>|||Thanks, Mary for your help
I've been doing databasing in Access, mySQL, and some generic work in
interBase, MSSQL, and Oracle... but in this case I have to port fairly
complex application to MSSQL and I really see all those blanks I miss in the
knowledge about MSSQL. Well, but that's the nature of learning curve, being
very steep
I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.
4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com|||Pavil,
I've been in this situation many times. As has been mentioned already, Acces
s'
YES/NO field is really a three-state field: YES/NO/NULL, where in SQL SERVER
the BIT type is 2-state.
The other problem is that Access treats TRUE as -1 where SQL Serrver actuall
y
treats TRUE as any non-zero value.
Armed with that information, you have two choices:
1) Use INT field in SQL Server, and you'll see good ol' -1 and 0 in that tab
le
anytime Access gets its ODBC/JET mitts on it.
2) When creating WHERE clauses in Access, get in the habit of ALWAYS using t
he
following test for TRUE:
Fld <> 0
Do that in VBA too, just to be consistent.
I am migrating complex database from Access 2000 to MSSQL server 8. This
database is used by web applications. Some time I need to be able swicth
between the old Access version and new SQL server driven one. I have
different connection strings, and connecting works well.
However, some sql query incompatibility has appeared...
Number of Access tables have boolean (Yes/No) fields. In Access, I could run
a query like this:
SELECT id FROM myTable WHERE myBooleanField
it returns all records where myBooleanField value is true. Now, when I
migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
every Access "true" value, and "0" for "false" values. With this new field
type, I can no longer run the above mentioned query. I must change it to
this:
SELECT id FROM myTable WHERE myBooleanField=1
Now, this would not be a crazy problem, however I need to be able to run it
for some time on both database solutions, Access and MSSQL, so I really need
to have compatible queries.
One solution I see now would be converting the field type in access from
boolean to byte, and then run the "=1" queries. But that could involve range
of incompatibilities within the application itself, that assumes that these
fields are "true" or "false" instead of "1" and "0". Browsing through the
application to find these places is not really the best thing I can imagine.
So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
to solve my problem?
Thanks,
PavilsOne issue is that Access/Jet booleans are not true booleans, but can
also contain null values. Change the Jet columns to Not Null and
provide a default value (same with SQLS). This lets you avoid the
three-valued logic trap. In your SQL statements and code, perform all
comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
everywhere, so both engines are going to interpret "<>0" the same way.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Hallo,
>I am migrating complex database from Access 2000 to MSSQL server 8. This
>database is used by web applications. Some time I need to be able swicth
>between the old Access version and new SQL server driven one. I have
>different connection strings, and connecting works well.
>However, some sql query incompatibility has appeared...
>Number of Access tables have boolean (Yes/No) fields. In Access, I could ru
n
>a query like this:
>SELECT id FROM myTable WHERE myBooleanField
>it returns all records where myBooleanField value is true. Now, when I
>migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" fo
r
>every Access "true" value, and "0" for "false" values. With this new field
>type, I can no longer run the above mentioned query. I must change it to
>this:
>SELECT id FROM myTable WHERE myBooleanField=1
>Now, this would not be a crazy problem, however I need to be able to run it
>for some time on both database solutions, Access and MSSQL, so I really nee
d
>to have compatible queries.
>One solution I see now would be converting the field type in access from
>boolean to byte, and then run the "=1" queries. But that could involve rang
e
>of incompatibilities within the application itself, that assumes that these
>fields are "true" or "false" instead of "1" and "0". Browsing through the
>application to find these places is not really the best thing I can imagine
.
>So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
>to solve my problem?
>Thanks,
>Pavils
>|||Thanks Mary,
This is a good suggestion, it helps me to move further...
Now another problem has arised:
Access runs this query without complaints:
UPDATE myTable SET myBool = NOT myBool WHERE id=100
What could be a syntax working on both Jet and MSSQL engines?
I tried this one...
UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
but I get error -
'IIf' is not a recognized function name.
Maybe some other smart syntax?
Thanks,
-- Pavils
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.
4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@.mailbox.riga.lv> wrote:
>
run
for
field
it
need
range
these
imagine.
approaches
>|||One thing that's going to continue to trip you up in writing your
queries is not understanding the fundamental differences between
Access SQL and T-SQL, the two main points of which are:
-- Access SQL relies heavily on the expression service and VBA to
compensate for the fact that it has no programming language features
on its own. You can execute VBA functions like Iif inside of a query
only against the Jet engine with Access as a FE. .
-- T-SQL contains many programming language features, like variables.
It does not rely on an external programming language to provide this
functionality the way Access does. When you try VBA syntax, it fails
because SQLS has no knowledge of VBA.
For the most part, you'll want to stay away from language elements
that are specific to either Jet SQL or T-SQL. I'd start by writing the
queries using the Query Analyzer, then copying/pasting them into an
Access query window to see if the syntax works in both. SQL Books
Online is a good reference--Access SQL documentation is practically
non-existant. Besides the language elements, be aware that you're also
going to have problems with delimiters when working with datetime
values -- Jet uses # and SQLS uses '.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 18:09:50 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Thanks Mary,
>This is a good suggestion, it helps me to move further...
>Now another problem has arised:
>Access runs this query without complaints:
>UPDATE myTable SET myBool = NOT myBool WHERE id=100
>What could be a syntax working on both Jet and MSSQL engines?
>I tried this one...
>UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
>but I get error -
>'IIf' is not a recognized function name.
>Maybe some other smart syntax?
>Thanks,
>-- Pavils
>"Mary Chipman" <mchip@.nomail.please> wrote in message
> news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.
4ax.com...
>run
>for
>field
>it
>need
>range
>these
>imagine.
>approaches
>|||Thanks, Mary for your help
I've been doing databasing in Access, mySQL, and some generic work in
interBase, MSSQL, and Oracle... but in this case I have to port fairly
complex application to MSSQL and I really see all those blanks I miss in the
knowledge about MSSQL. Well, but that's the nature of learning curve, being
very steep
I'd be happy if this project would be originally developed inMSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.
4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com|||Pavil,
I've been in this situation many times. As has been mentioned already, Acces
s'
YES/NO field is really a three-state field: YES/NO/NULL, where in SQL SERVER
the BIT type is 2-state.
The other problem is that Access treats TRUE as -1 where SQL Serrver actuall
y
treats TRUE as any non-zero value.
Armed with that information, you have two choices:
1) Use INT field in SQL Server, and you'll see good ol' -1 and 0 in that tab
le
anytime Access gets its ODBC/JET mitts on it.
2) When creating WHERE clauses in Access, get in the habit of ALWAYS using t
he
following test for TRUE:
Fld <> 0
Do that in VBA too, just to be consistent.
Monday, March 19, 2012
Migrate from non-cluster to Cluster
I have a single SQL 2000 Enterprise server and I need to turn it into a
cluster. We have several applications using SQL, so the hostname and
configuration of the cluster must be the same as the existing single server.
The operating system is WS2003E and they are in a AD domain.
Is there a fairly easy migration path from a single to clustered
configuration, or do I have to setup two new servers and somehow get the
cluster to use the old hostname? Do I just do a SQL backup of the old
server, power it off, setup the cluster using the old hostname and then do a
SQL restore? I need to minimize downtime and I can't reconfigure the
applications to point to another SQL server.
Do I register the SPNs for the virtual hostname of the cluster?
Thanks,
Derek
Reusing the name is not really an option for several reasons (AD name
conflict, DNS name conflict, SQL cluster naming options). You can use DNS to
alias the old name to the new name.
While it is possible to migrate a non-clustered instance to a clustered
instance, I would recommend setting up a new system. That way if anything
goes wrong, your existing system is right there to pick up and run. Failure
isn't so painful. You can use manual log shipping or a simple log shipping
script to minimize downtime by getting your new server 'close' to the old
one. Be sure and plan the cutover process and test it (maybe in a smaller
scale using a couple of SQL instances on a test box).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Derek" <dseaman@.spawar.navy.mil> wrote in message
news:ep1mEXCyEHA.3584@.TK2MSFTNGP10.phx.gbl...
> I have a single SQL 2000 Enterprise server and I need to turn it into a
> cluster. We have several applications using SQL, so the hostname and
> configuration of the cluster must be the same as the existing single
server.
> The operating system is WS2003E and they are in a AD domain.
> Is there a fairly easy migration path from a single to clustered
> configuration, or do I have to setup two new servers and somehow get the
> cluster to use the old hostname? Do I just do a SQL backup of the old
> server, power it off, setup the cluster using the old hostname and then do
a
> SQL restore? I need to minimize downtime and I can't reconfigure the
> applications to point to another SQL server.
> Do I register the SPNs for the virtual hostname of the cluster?
> Thanks,
> Derek
>
>
|||How about if I backup the non-clustered server, remove it from AD, and built
up a cluster with the virtual name being the name of the old SQL server and
then do a SQL restore? We have too many apps to be changing the name, and I
hate to play tricks with DNS that are prone to human error.
Derek
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKaQfWDyEHA.1188@.tk2msftngp13.phx.gbl...
> Reusing the name is not really an option for several reasons (AD name
> conflict, DNS name conflict, SQL cluster naming options). You can use DNS
> to
> alias the old name to the new name.
> While it is possible to migrate a non-clustered instance to a clustered
> instance, I would recommend setting up a new system. That way if anything
> goes wrong, your existing system is right there to pick up and run.
> Failure
> isn't so painful. You can use manual log shipping or a simple log
> shipping
> script to minimize downtime by getting your new server 'close' to the old
> one. Be sure and plan the cutover process and test it (maybe in a smaller
> scale using a couple of SQL instances on a test box).
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Derek" <dseaman@.spawar.navy.mil> wrote in message
> news:ep1mEXCyEHA.3584@.TK2MSFTNGP10.phx.gbl...
> server.
> a
>
|||Try this in a test environment first.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Derek" <dseaman@.spawar.navy.mil> wrote in message
news:uyD4T%23czEHA.3336@.TK2MSFTNGP11.phx.gbl...
> How about if I backup the non-clustered server, remove it from AD, and
built
> up a cluster with the virtual name being the name of the old SQL server
and
> then do a SQL restore? We have too many apps to be changing the name, and
I[vbcol=seagreen]
> hate to play tricks with DNS that are prone to human error.
> Derek
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKaQfWDyEHA.1188@.tk2msftngp13.phx.gbl...
DNS[vbcol=seagreen]
anything[vbcol=seagreen]
old[vbcol=seagreen]
smaller[vbcol=seagreen]
the
>
cluster. We have several applications using SQL, so the hostname and
configuration of the cluster must be the same as the existing single server.
The operating system is WS2003E and they are in a AD domain.
Is there a fairly easy migration path from a single to clustered
configuration, or do I have to setup two new servers and somehow get the
cluster to use the old hostname? Do I just do a SQL backup of the old
server, power it off, setup the cluster using the old hostname and then do a
SQL restore? I need to minimize downtime and I can't reconfigure the
applications to point to another SQL server.
Do I register the SPNs for the virtual hostname of the cluster?
Thanks,
Derek
Reusing the name is not really an option for several reasons (AD name
conflict, DNS name conflict, SQL cluster naming options). You can use DNS to
alias the old name to the new name.
While it is possible to migrate a non-clustered instance to a clustered
instance, I would recommend setting up a new system. That way if anything
goes wrong, your existing system is right there to pick up and run. Failure
isn't so painful. You can use manual log shipping or a simple log shipping
script to minimize downtime by getting your new server 'close' to the old
one. Be sure and plan the cutover process and test it (maybe in a smaller
scale using a couple of SQL instances on a test box).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Derek" <dseaman@.spawar.navy.mil> wrote in message
news:ep1mEXCyEHA.3584@.TK2MSFTNGP10.phx.gbl...
> I have a single SQL 2000 Enterprise server and I need to turn it into a
> cluster. We have several applications using SQL, so the hostname and
> configuration of the cluster must be the same as the existing single
server.
> The operating system is WS2003E and they are in a AD domain.
> Is there a fairly easy migration path from a single to clustered
> configuration, or do I have to setup two new servers and somehow get the
> cluster to use the old hostname? Do I just do a SQL backup of the old
> server, power it off, setup the cluster using the old hostname and then do
a
> SQL restore? I need to minimize downtime and I can't reconfigure the
> applications to point to another SQL server.
> Do I register the SPNs for the virtual hostname of the cluster?
> Thanks,
> Derek
>
>
|||How about if I backup the non-clustered server, remove it from AD, and built
up a cluster with the virtual name being the name of the old SQL server and
then do a SQL restore? We have too many apps to be changing the name, and I
hate to play tricks with DNS that are prone to human error.
Derek
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKaQfWDyEHA.1188@.tk2msftngp13.phx.gbl...
> Reusing the name is not really an option for several reasons (AD name
> conflict, DNS name conflict, SQL cluster naming options). You can use DNS
> to
> alias the old name to the new name.
> While it is possible to migrate a non-clustered instance to a clustered
> instance, I would recommend setting up a new system. That way if anything
> goes wrong, your existing system is right there to pick up and run.
> Failure
> isn't so painful. You can use manual log shipping or a simple log
> shipping
> script to minimize downtime by getting your new server 'close' to the old
> one. Be sure and plan the cutover process and test it (maybe in a smaller
> scale using a couple of SQL instances on a test box).
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Derek" <dseaman@.spawar.navy.mil> wrote in message
> news:ep1mEXCyEHA.3584@.TK2MSFTNGP10.phx.gbl...
> server.
> a
>
|||Try this in a test environment first.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Derek" <dseaman@.spawar.navy.mil> wrote in message
news:uyD4T%23czEHA.3336@.TK2MSFTNGP11.phx.gbl...
> How about if I backup the non-clustered server, remove it from AD, and
built
> up a cluster with the virtual name being the name of the old SQL server
and
> then do a SQL restore? We have too many apps to be changing the name, and
I[vbcol=seagreen]
> hate to play tricks with DNS that are prone to human error.
> Derek
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKaQfWDyEHA.1188@.tk2msftngp13.phx.gbl...
DNS[vbcol=seagreen]
anything[vbcol=seagreen]
old[vbcol=seagreen]
smaller[vbcol=seagreen]
the
>
Migrate from DB2 to MSSQL
Dear All,
I am exploring the feasibility of migrating from DB2 (v7) to MSSQL server 2005. My current applications are using UDB, but the DBA wants to change to MSSQL. I believe the impact is huge. For example, how to create schema in MSSQL, how to migrate the data...
Anyone had the experience before? Could kindly suggest some reference for such a migration? Thanks!
Regards,From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||String Manipulation?
Dates, OK, you are going to have to trim the DB2 dates as SQL Server only supports 3 ms...and only cpu clock speeds...I believe every .333 ms
But strings/chars?
Huh?
And what, besides sheer whim or only knowledge of sql sever does the dba want to move off DB2|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?
From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?Yes, there is. Your DBA can tell you for sure, but my first guess is "dbo".
You are treading very near one of the confusing points in the switch from DB2 to MS-SQL... In MS-SQL 2000 and all earlier versions, there was a logical concept called a "user" that actually spanned across many of the concepts such as schema, permissions, etc in the purely relational world. Each user could logically own objects within the database, giving them something quite close to their own schema. There was a kind of "uber user" named dbo (an acronym for DataBase Owner) that was the default user under the name resolution rules.
In MS-SQL 2005, true schemas were introduced into the product. The schemas don't behave quite the way that you are accustomed to in DB2, but they're pretty close. For the moment, I wouldn't worry too much about them unless you have to make adjustments to cope with them.
-PatP
I am exploring the feasibility of migrating from DB2 (v7) to MSSQL server 2005. My current applications are using UDB, but the DBA wants to change to MSSQL. I believe the impact is huge. For example, how to create schema in MSSQL, how to migrate the data...
Anyone had the experience before? Could kindly suggest some reference for such a migration? Thanks!
Regards,From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||String Manipulation?
Dates, OK, you are going to have to trim the DB2 dates as SQL Server only supports 3 ms...and only cpu clock speeds...I believe every .333 ms
But strings/chars?
Huh?
And what, besides sheer whim or only knowledge of sql sever does the dba want to move off DB2|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?
From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?Yes, there is. Your DBA can tell you for sure, but my first guess is "dbo".
You are treading very near one of the confusing points in the switch from DB2 to MS-SQL... In MS-SQL 2000 and all earlier versions, there was a logical concept called a "user" that actually spanned across many of the concepts such as schema, permissions, etc in the purely relational world. Each user could logically own objects within the database, giving them something quite close to their own schema. There was a kind of "uber user" named dbo (an acronym for DataBase Owner) that was the default user under the name resolution rules.
In MS-SQL 2005, true schemas were introduced into the product. The schemas don't behave quite the way that you are accustomed to in DB2, but they're pretty close. For the moment, I wouldn't worry too much about them unless you have to make adjustments to cope with them.
-PatP
Wednesday, March 7, 2012
MICROSOFT##SSEE blocks Surface Area Configuration in SQL 2005?
I am running a Windows Small Business Server with SQL Server 2005 on it. I am having a problem in that one of the applications I'm running needs to acess the xp_cmdshell component which is disabled by default. The problem I'm running into is, trying to run the Surface Area Configuration Wizard to turn this on fails because the system is unable to access the MICROSOFT##SSEE instance on the machine.
I dont want to modify the MICROSOFT##SSEE instance, I want to modify a different instance but the wizard detects ALL instances of SQL and craps out trying to access the MICROSOFT##SSEE instance.
Is there a way for me to activate the xp_cmdshell component on other instances without the wizard?
Hi,
Run this code in your query window:
Code Snippet
sp_configure 'xp_cmdshell', 1
reconfigure
Regards,
Janos
|||I get the following message:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
|||Hi Ron,
First you need to switch on the advanced options, then configure the xp_cmdshell.
run this please:
Code Snippet
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'xp_cmdshell', 1
reconfigure
regards,
Janos
|||Thank you, that did the trick... now I just have to get my code to work <grin>Microsoft Visual Sutdio for Applications has lost the link to .
Hello,
I am struggling with a really ugly problem in SSIS.
I am designing SSIS package where I have a couple of Script tasks. Let's call them Script Task 1 and Script Task 2. Everything was working fine until this morning I added another Script taks (Script Task 3), which was trying to do some rudementary file manipulation. At that point I started getting this message:
"Microsoft Visual Sutdio for Applications has lost the link to .
Your work will be exported to c:\documents and settings\".
This is it! "lost a link to "dot". After this, the VS environment continues to run in debug mode, so it doesn't even crash stirctly speaking. I can stop debugging and continue developing, but nothing would run anymore and I would keep getting the same error. Moreover, after removing Script Task 3 (which seems to be the culprit), I would still keep getting the same error! Only removing all the script tasks completely and then adding them back gets the package to run again.
Any ideas?
Hi Michael,
This is the first time we are seeing this problem. The only thing that I can think that happened is that somehow those scripts got in a bad state.
One way to fix this kind of errors is to try and setting the precompiled property to false on those script tasks, remove all the breakpoints from the scripts, save the package and then reset the precompiled to the desired value and re-add the breakpoints. By doing that the precompiled version of the scripts will be refreshed and hopefully everything will be back in sync.
Also make sure you have SQL Server SP2 installed because it fixes some problems with scripting that might cause these errors.
Another problem might be if you generated script 3 using copy/paste in the package designer from one of script 1 or 2. Some problems regarding that operation were fixed in SP2.
Let me know if that helped.
Silviu Guea [MSFT] SQL Server Integration Services
Friday, February 24, 2012
Microsoft SQL Server vs Sybase
Hello,
We have Trading Applications (Equities & Portfolio written on C++) on MS SQL Server 2000. Now the management is deciding to move it on Sybase 12.5. We currently don't have any issues on any matter, but because everyone is SYbase fan here, thinks applications are critical and we need to move to Sybase. Can anyone who has worked on both knows the major pros & cons of both the system. Actually I wanted these systems to be on MS SQL Server. I need solid reasons except being cheaper than Sybase to show why we should use MS SQL Server
If anyone can share their experiences, it would be really great.
Thanks
ReaI have used both Sybase ASE and MS SQL Server 2000 in mission-critical systems. Up front I will say that I personally prefer ASE.
ASE and SQL Server have much the same base T-SQL syntax. However SQL Server has introduced a number of functions to do analytics that Sybase thought best to place into a separate product, Sybase IQ. So if you are using CUBE and other functions they will have to be re-written in T-SQL or application logic.
Both ASE and SQL Server have triggers and stored procedures that use T-SQL.
ASE and SQL Server both support user defined datatypes, have identical clustered index approaches, and offer much the same stored procedures to administer the DBMS.
SQL Server has sort of implicit locking scheme in which SQL server will dynamically choose row or page locking (and of course table). ASE requires you to explicitly state whether the granularity of locks are row or page, and a row-lock table cannot lock a data page and vice versa.
ASE has a wider array of tuning tools to change parameters and such. SQL Server has more built-in 'self tuning' tools and can auto-grow devices, auto-backup, etc. Having administered both I prefer ASE's if I have a mission-critical DB that I can devote time to (since our DBAs tend to know more than the server does) but if I want a 'set it and forget it' system I prefer the more autonomous SQL Server.
ASE's next interim release is supposed to have a built-in scheduler feature much like SQL Server. It will have a number of prefab T-SQL templates built in to do things like backups, growing database devices, etc. which helps narrow the feature gap.
ASEs replication, via Sybase Replication Server, is much better than SQL Servers built-in replication. However it is a stand-alone product which costs additional money. ASE does contain ASE Replicator which is a small-scale replication system (I think it maxes out at something like 15 transactions per second) but can be useful if your replication needs are not as demanding for Rep Server.
You can run ASE on Solaris, AIX, Windows, Linux, etc. however SQL Server only runs on the Windows NT family. This can be a big plus if you have competent Unix admins because they can generally tune the system to outperform comparable Windows systems.|||MattR, I was with you up to your last sentance. I have seen Oracle, Sybase and SQL Server run head to head, 10 CPU boxes, 8 + gig of memory and large drive arrays each tunned for the OS/RDBMS. Of course each did some thinges better than the others but at the end of the day there was no "CLEAR" winner. Now that observation was based only on performance. Facter in cost of ownership, cost to impliment, available developers, code base etc... and it was a diffrent picture all together.
Rea, Botton line here is each RDBMS has sommething to offer over the other, you have to look at your needs and decide whit fits your needs best. If the majority of your servers run Unix and the majority of your DB apps use Sybase one has to ask what the benafits of running a few systems under NT/SQL Server are for the company!|||Paul,
That's just my pro-unix bias coming in. :) Generally we've seen more stability from our unix boxen than our NT (NT meaning 2000 and now XP) ones -- I attribute that to better admins being able to poke more at the OS (Linux for example can be ripped apart and pieced back together). But of course we're talking minor differences -- something like a weekly (now more like monthly) reboot on NT on off-peak hours vs. a quarterly one 'for good measure' on the unix ones.
But yes performance shouldnt be the deciding factor you have to take the big picture (TCO) into account.|||Matt, I would clearly take Unix of almost any flavor over a Windows OS. Our NT servers generally run 6 months before needing a reboot but some of our HP servers running HPUX have been up for almost two years!|||I have worked on both the system as a DBA and my personal feeling is that given a choice I would always recommend Sql Server over any database.Reason being Cheap,easily avaliable(resources),a very good tech support,easy to maintain and lot's and lot's of other benefits.
With Sql Server you can reach to the bottom of the problem(Profiler is a great help).
There are some plus points with Sybase which would have been nice with Sql server(like password expring,locking the account),but hey you don't get all the things in one DB.
We were so happy the day we moved from Sybase to Sql Server.
With Sql Server you can control the access of different users at different levels like 'db_dataread/write/denyread/denywrite'.
I would suggest that 'do what everyone is doing and Sql Server is the best choice' specially when you compare Sql Server with ASE.
Just take a small example : You post a Sql Server related problem at www.dbforums.com and within the first one hour you will get the reply,not so with ASE.
;)|||Thanks All of you for your input. Is there any performance gains? Mostly I've heard for very large databse SYbase can handle very well than MS SQL Server, Is it true?|||No, I guarantee you if Sybase is installed on a server with to few resources it will suck wind big time as will SQL Server.
The question should be, what is the hardware cost to properly configure a server to run Sybase Vs SQL Server for MY environment. 8 cpu Sun boxes are cheaper the 8 cpu Intel boxes.|||One more thing with Sybase you need a DBA for things like Backup and Restore,increasing the database size etc etc.
Also the performance is good with Sql Server,it's way way ahead of Sybase.
We have Trading Applications (Equities & Portfolio written on C++) on MS SQL Server 2000. Now the management is deciding to move it on Sybase 12.5. We currently don't have any issues on any matter, but because everyone is SYbase fan here, thinks applications are critical and we need to move to Sybase. Can anyone who has worked on both knows the major pros & cons of both the system. Actually I wanted these systems to be on MS SQL Server. I need solid reasons except being cheaper than Sybase to show why we should use MS SQL Server
If anyone can share their experiences, it would be really great.
Thanks
ReaI have used both Sybase ASE and MS SQL Server 2000 in mission-critical systems. Up front I will say that I personally prefer ASE.
ASE and SQL Server have much the same base T-SQL syntax. However SQL Server has introduced a number of functions to do analytics that Sybase thought best to place into a separate product, Sybase IQ. So if you are using CUBE and other functions they will have to be re-written in T-SQL or application logic.
Both ASE and SQL Server have triggers and stored procedures that use T-SQL.
ASE and SQL Server both support user defined datatypes, have identical clustered index approaches, and offer much the same stored procedures to administer the DBMS.
SQL Server has sort of implicit locking scheme in which SQL server will dynamically choose row or page locking (and of course table). ASE requires you to explicitly state whether the granularity of locks are row or page, and a row-lock table cannot lock a data page and vice versa.
ASE has a wider array of tuning tools to change parameters and such. SQL Server has more built-in 'self tuning' tools and can auto-grow devices, auto-backup, etc. Having administered both I prefer ASE's if I have a mission-critical DB that I can devote time to (since our DBAs tend to know more than the server does) but if I want a 'set it and forget it' system I prefer the more autonomous SQL Server.
ASE's next interim release is supposed to have a built-in scheduler feature much like SQL Server. It will have a number of prefab T-SQL templates built in to do things like backups, growing database devices, etc. which helps narrow the feature gap.
ASEs replication, via Sybase Replication Server, is much better than SQL Servers built-in replication. However it is a stand-alone product which costs additional money. ASE does contain ASE Replicator which is a small-scale replication system (I think it maxes out at something like 15 transactions per second) but can be useful if your replication needs are not as demanding for Rep Server.
You can run ASE on Solaris, AIX, Windows, Linux, etc. however SQL Server only runs on the Windows NT family. This can be a big plus if you have competent Unix admins because they can generally tune the system to outperform comparable Windows systems.|||MattR, I was with you up to your last sentance. I have seen Oracle, Sybase and SQL Server run head to head, 10 CPU boxes, 8 + gig of memory and large drive arrays each tunned for the OS/RDBMS. Of course each did some thinges better than the others but at the end of the day there was no "CLEAR" winner. Now that observation was based only on performance. Facter in cost of ownership, cost to impliment, available developers, code base etc... and it was a diffrent picture all together.
Rea, Botton line here is each RDBMS has sommething to offer over the other, you have to look at your needs and decide whit fits your needs best. If the majority of your servers run Unix and the majority of your DB apps use Sybase one has to ask what the benafits of running a few systems under NT/SQL Server are for the company!|||Paul,
That's just my pro-unix bias coming in. :) Generally we've seen more stability from our unix boxen than our NT (NT meaning 2000 and now XP) ones -- I attribute that to better admins being able to poke more at the OS (Linux for example can be ripped apart and pieced back together). But of course we're talking minor differences -- something like a weekly (now more like monthly) reboot on NT on off-peak hours vs. a quarterly one 'for good measure' on the unix ones.
But yes performance shouldnt be the deciding factor you have to take the big picture (TCO) into account.|||Matt, I would clearly take Unix of almost any flavor over a Windows OS. Our NT servers generally run 6 months before needing a reboot but some of our HP servers running HPUX have been up for almost two years!|||I have worked on both the system as a DBA and my personal feeling is that given a choice I would always recommend Sql Server over any database.Reason being Cheap,easily avaliable(resources),a very good tech support,easy to maintain and lot's and lot's of other benefits.
With Sql Server you can reach to the bottom of the problem(Profiler is a great help).
There are some plus points with Sybase which would have been nice with Sql server(like password expring,locking the account),but hey you don't get all the things in one DB.
We were so happy the day we moved from Sybase to Sql Server.
With Sql Server you can control the access of different users at different levels like 'db_dataread/write/denyread/denywrite'.
I would suggest that 'do what everyone is doing and Sql Server is the best choice' specially when you compare Sql Server with ASE.
Just take a small example : You post a Sql Server related problem at www.dbforums.com and within the first one hour you will get the reply,not so with ASE.
;)|||Thanks All of you for your input. Is there any performance gains? Mostly I've heard for very large databse SYbase can handle very well than MS SQL Server, Is it true?|||No, I guarantee you if Sybase is installed on a server with to few resources it will suck wind big time as will SQL Server.
The question should be, what is the hardware cost to properly configure a server to run Sybase Vs SQL Server for MY environment. 8 cpu Sun boxes are cheaper the 8 cpu Intel boxes.|||One more thing with Sybase you need a DBA for things like Backup and Restore,increasing the database size etc etc.
Also the performance is good with Sql Server,it's way way ahead of Sybase.
Subscribe to:
Posts (Atom)