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 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

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.

Migrating Excel Spreadsheets to MSSQL

Hi All.

Would you happen to know how one could convert some Excel spreadsheets to MSSQL? There is some commercially available software, but there must also be a way for me to manipulate the files.

Many thanks!
Nazli

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/extran9/html/667419f2-74fb-4b50-b963-9197d1368cda.htm

That's where I started.

Good luck

ginnyK

|||

What kind of conversions are you trying to do?

Reason I'm asking is because I pull information from Excel spreadsheets all the time into SQL using packages I've built.

Let me know what you're trying to convert.

MIGRATE TO REPORTING SERVICE 2005

Good Morning,
I've a server mssql 2000 with reporting service and so also Visual
Studio.NET 2003.
Now I want to migrate to Reporting Service 2005,must I to upgrade also
Mssql to 2005 version and Visual Studio.NET 2005 or can I to upgrade
only Reporting Service?
ThanksYOu can upgrade Reporting Services without going to sql 2005... ( according
to Books On LIne)... Although we put the Reporting Services databases on an
instance of SQL 2005 - I don't think we had to...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Teo" wrote:
> Good Morning,
> I've a server mssql 2000 with reporting service and so also Visual
> Studio.NET 2003.
> Now I want to migrate to Reporting Service 2005,must I to upgrade also
> Mssql to 2005 version and Visual Studio.NET 2005 or can I to upgrade
> only Reporting Service?
> Thanks
>|||What is your current configuration? Local catalog (all on one server)?
Wayne is correct. You may install Reporting Services 2005 next to Reporting
Services 2000 and then use the 2005 Reporting Services Configuration Manager
to migrate your existing SQL2000 database to SQL2005... BOL is gray in the
fact that it appears that this is only supported in a remote catalog (SQL
and IIS on separate servers) configuration or a web farm/scale-out (multiple
IIS servers sharing a centralized SQL catalog) configuration but since
SQL2000 and SQL2005 can live on the same server it can be done on a local
catalog configuration... having said all that, unless you are needing to
maintain a SQL2000 environment for other reasons I don't see why you would
not want to upgrade you SQL2000 RDBMS to 2005, go for it!
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:3A2A647A-017C-442E-A1B9-D629DA7EEBEF@.microsoft.com...
> YOu can upgrade Reporting Services without going to sql 2005... (
> according
> to Books On LIne)... Although we put the Reporting Services databases on
> an
> instance of SQL 2005 - I don't think we had to...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Teo" wrote:
>> Good Morning,
>> I've a server mssql 2000 with reporting service and so also Visual
>> Studio.NET 2003.
>> Now I want to migrate to Reporting Service 2005,must I to upgrade also
>> Mssql to 2005 version and Visual Studio.NET 2005 or can I to upgrade
>> only Reporting Service?
>> Thanks
>>|||My configuration is SQL Server and IIS on the same box. I upgraded only RS.
My SQL Server is still 2000 (note for all, you do have to have a SQL Server
2005 license if you do this). So, it is incorrect to say that this is only
supported with a remote catalog. That is definitely not true.
All you have to do when upgrading is select only RS 2005 when upgrading and
it all works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Douglas McDowell" <douglas@.nospam.solidqualitylearning.com> wrote in
message news:e5L7GW7DGHA.2040@.TK2MSFTNGP14.phx.gbl...
> What is your current configuration? Local catalog (all on one server)?
> Wayne is correct. You may install Reporting Services 2005 next to
> Reporting Services 2000 and then use the 2005 Reporting Services
> Configuration Manager to migrate your existing SQL2000 database to
> SQL2005... BOL is gray in the fact that it appears that this is only
> supported in a remote catalog (SQL and IIS on separate servers)
> configuration or a web farm/scale-out (multiple IIS servers sharing a
> centralized SQL catalog) configuration but since SQL2000 and SQL2005 can
> live on the same server it can be done on a local catalog configuration...
> having said all that, unless you are needing to maintain a SQL2000
> environment for other reasons I don't see why you would not want to
> upgrade you SQL2000 RDBMS to 2005, go for it!
> --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:3A2A647A-017C-442E-A1B9-D629DA7EEBEF@.microsoft.com...
>> YOu can upgrade Reporting Services without going to sql 2005... (
>> according
>> to Books On LIne)... Although we put the Reporting Services databases on
>> an
>> instance of SQL 2005 - I don't think we had to...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "Teo" wrote:
>> Good Morning,
>> I've a server mssql 2000 with reporting service and so also Visual
>> Studio.NET 2003.
>> Now I want to migrate to Reporting Service 2005,must I to upgrade also
>> Mssql to 2005 version and Visual Studio.NET 2005 or can I to upgrade
>> only Reporting Service?
>> Thanks
>>
>|||But RS need Visual Studio.Net so if I want to install RS2005 , I must
to upgrade VS from version 2003 to 2005 ?
it's true?|||RS 2005 comes with a version of VS that is used for the report designer. You
can run side by side (leave your VS 2003 alone). If VS 2005 is installed
then RS 2005 will install the report designer to use that.
So, you do not need to purchase VS 2005.
Note that VS 2005 comes with two new controls for web and winforms. If you
want to use these new controls then you would need to upgrade VS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Teo" <roninkaiser@.lycos.it> wrote in message
news:1136381218.597528.197920@.g43g2000cwa.googlegroups.com...
> But RS need Visual Studio.Net so if I want to install RS2005 , I must
> to upgrade VS from version 2003 to 2005 ?
> it's true?
>

migrate to MS SQL2000 from Sybase

Could somebody point me to websites that would help in the migration to MSSQL 2000 from Sybase ASE 12.5? I also need replication documents also. We have 7 SQL servers that all replicate and we are thinking about moving to MS because of some benifits for out inhouse program.

thanks for all help..Check out the following article - it is based on sql server 7:

article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsm/html/sybase2sql.asp)|||thanks..

Migrate MSSQL 2005 Express -> MSSQL 2005 Srv

Hi!

What is the best way to migrate MSSQL 2005 Express -> MSSQL 2005 Srv?

YOu can either do an inplace update of your database (running the setup) or do a complete new install with prior backing up the database and restoring them afterwards.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I was thinking more of the database it self.
Let's say I have developed an .NET site localy with a SQL Express-db, and later I want to upload it to my webhost and convert my express db to an "real" MS SQL server db that my webhost has supplied me with.

|||

Hi, thats no problem, either to a backup and restore or do a detauch and attach. The B/R solution would be the preferable on e for me.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

migrate msjet to mssql server

hi, i already have a project in vb6 / msjet 3.5 using dao methods .index .seek , ... I want to convert it to sql , is there a fast way (! converting .index .seek to select...where clauses is time consumming issue) plse reply since the package is already running and i am really desperate to the conversion.

many thanks and appreciation
amatouriI'm afraid you'll have to go through the pain of taking all this DAO s$%t out. And while you're at it, do yourself and those who'll come later a favor, - take out .Add/.Edit/.Update as well, - very nasty stuff, and while being a very lazy way to code, yields a very paiful and weekendless life for your DBA...

Migrate Login Control to SQL

Hi,

I was able to create an MSSQL database with the ASPNETDB.MDF file. But how do I get the Login Control to point to the MSSQL rather than the \App_Data\ASPNETDB.MDF file?

Hi

you need to see This

Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

|||

check out below solved thread..

Login control And DB - ASP.NET Forums

you might need to include the tables and stored procedure which requires for login control into your DB...

hope it helps./.

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

Migrate Btrieve database to MSSQL 2000 database

Hi,
I've a Btrieve database which I want to migrate to MSSQL 2000 database. May
I know what is the method I can use?
Thanks
andrew
andrew wrote:
> Hi,
> I've a Btrieve database which I want to migrate to MSSQL 2000 database. May
> I know what is the method I can use?
> Thanks
> andrew
Run Butil -save to get the data from btrieve files into txt format
Create a DTS job to read the files into your MSSQL2K database.
Obviously you need to create the DB first.
|||Thanks mattis. But may I know where can I find this butil tools?
andrew
"mattis" wrote:

> andrew wrote:
> Run Butil -save to get the data from btrieve files into txt format
> Create a DTS job to read the files into your MSSQL2K database.
> Obviously you need to create the DB first.
>
|||Another option is to use BtSearch32 at www.nssdd.com. It will export Btrieve
to dbase or ascii which you can then import into about anything. Also if you
do not have the DDF files it will help you analyze the structure of the file
and build the DDF files.
Gil
"andrew" wrote:
[vbcol=seagreen]
> Thanks mattis. But may I know where can I find this butil tools?
> andrew
> "mattis" wrote:

Migrate Btrieve database to MSSQL 2000 database

Hi,
I've a Btrieve database which I want to migrate to MSSQL 2000 database. May
I know what is the method I can use?
Thanks
andrewandrew wrote:
> Hi,
> I've a Btrieve database which I want to migrate to MSSQL 2000 database. Ma
y
> I know what is the method I can use?
> Thanks
> andrew
Run Butil -save to get the data from btrieve files into txt format
Create a DTS job to read the files into your MSSQL2K database.
Obviously you need to create the DB first.|||Thanks mattis. But may I know where can I find this butil tools?
andrew
"mattis" wrote:

> andrew wrote:
> Run Butil -save to get the data from btrieve files into txt format
> Create a DTS job to read the files into your MSSQL2K database.
> Obviously you need to create the DB first.
>|||Another option is to use BtSearch32 at www.nssdd.com. It will export Btriev
e
to dbase or ascii which you can then import into about anything. Also if yo
u
do not have the DDF files it will help you analyze the structure of the file
and build the DDF files.
Gil
"andrew" wrote:
[vbcol=seagreen]
> Thanks mattis. But may I know where can I find this butil tools?
> andrew
> "mattis" wrote:
>

Migrate Btrieve database to MSSQL 2000 database

Hi,
I've a Btrieve database which I want to migrate to MSSQL 2000 database. May
I know what is the method I can use?
Thanks
andrewandrew wrote:
> Hi,
> I've a Btrieve database which I want to migrate to MSSQL 2000 database. May
> I know what is the method I can use?
> Thanks
> andrew
Run Butil -save to get the data from btrieve files into txt format
Create a DTS job to read the files into your MSSQL2K database.
Obviously you need to create the DB first.|||Thanks mattis. But may I know where can I find this butil tools?
andrew
"mattis" wrote:
> andrew wrote:
> > Hi,
> >
> > I've a Btrieve database which I want to migrate to MSSQL 2000 database. May
> > I know what is the method I can use?
> >
> > Thanks
> > andrew
> Run Butil -save to get the data from btrieve files into txt format
> Create a DTS job to read the files into your MSSQL2K database.
> Obviously you need to create the DB first.
>|||Another option is to use BtSearch32 at www.nssdd.com. It will export Btrieve
to dbase or ascii which you can then import into about anything. Also if you
do not have the DDF files it will help you analyze the structure of the file
and build the DDF files.
Gil
"andrew" wrote:
> Thanks mattis. But may I know where can I find this butil tools?
> andrew
> "mattis" wrote:
> > andrew wrote:
> > > Hi,
> > >
> > > I've a Btrieve database which I want to migrate to MSSQL 2000 database. May
> > > I know what is the method I can use?
> > >
> > > Thanks
> > > andrew
> >
> > Run Butil -save to get the data from btrieve files into txt format
> > Create a DTS job to read the files into your MSSQL2K database.
> >
> > Obviously you need to create the DB first.
> >

Migrate Backup DAT file from MS SQL to MSDE 2000

Hi,
Anyone know how to migrate migrate backup file DAT from MS
SQL 7.0 to MSDE 2000?
Thanks
Michael
If this is a backup file created with SQL Servers BACKUP command then you
can simply restore the database to the MSDE 2000 instance. The RESTORE
command will convert the database for you.
Jim
"Michael" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9401c503f4$51839a90$a401280a@.phx.gbl...
> Hi,
> Anyone know how to migrate migrate backup file DAT from MS
> SQL 7.0 to MSDE 2000?
> Thanks
> Michael

Migrate ACCESS to MSSQL

Hi,
I created a table and filled data to it in ACCESS 2002. How can I migrate the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has primary key and also has records. I want to append the ACCESS data (2 columns) to the existing table with the s
ame columns names in MSSQL.
How can I do it?
Thanks
Tom,
you can use the upsizing wizard or DTS. Here are some links to help you:
http://builder.com.com/5100-6388-5059624.html
http://support.microsoft.com/default.aspx?kbid=285829
http://support.microsoft.com/default...NoWebContent=1
HTH,
Paul Ibison
|||Most folks prefer using DTS to the upsizing wizard... However if you do use
the upsizing wizard, go back and ensure that the sql column data types are
appropriate... My experience with the wizard is that it makes the columns
sizes too large.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <kerocow@.yahoo.com> wrote in message
news:221ADD36-EA26-45DA-A953-D700866C06F6@.microsoft.com...
> Hi,
> I created a table and filled data to it in ACCESS 2002. How can I migrate
the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has
primary key and also has records. I want to append the ACCESS data (2
columns) to the existing table with the same columns names in MSSQL.
> How can I do it?
> Thanks

Migrate ACCESS to MSSQL

Hi
I created a table and filled data to it in ACCESS 2002. How can I migrate the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has primary key and also has records. I want to append the ACCESS data (2 columns) to the existing table with the same columns names in MSSQL.
How can I do it
ThanksTom,
you can use the upsizing wizard or DTS. Here are some links to help you:
http://builder.com.com/5100-6388-5059624.html
http://support.microsoft.com/default.aspx?kbid=285829
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q237/9/80.ASP&NoWebContent=1
HTH,
Paul Ibison|||Most folks prefer using DTS to the upsizing wizard... However if you do use
the upsizing wizard, go back and ensure that the sql column data types are
appropriate... My experience with the wizard is that it makes the columns
sizes too large.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <kerocow@.yahoo.com> wrote in message
news:221ADD36-EA26-45DA-A953-D700866C06F6@.microsoft.com...
> Hi,
> I created a table and filled data to it in ACCESS 2002. How can I migrate
the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has
primary key and also has records. I want to append the ACCESS data (2
columns) to the existing table with the same columns names in MSSQL.
> How can I do it?
> Thanks

Migrate ACCESS to MSSQL

Hi,
I created a table and filled data to it in ACCESS 2002. How can I migrate th
e ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has primary
key and also has records. I want to append the ACCESS data (2 columns) to th
e existing table with the s
ame columns names in MSSQL.
How can I do it?
ThanksTom,
you can use the upsizing wizard or DTS. Here are some links to help you:
http://builder.com.com/5100-6388-5059624.html
http://support.microsoft.com/default.aspx?kbid=285829
port/kb/articles/Q237/9/80.ASP&NoWebContent=1" target="_blank">http://support.microsoft.com/defaul...&NoWebContent=1
HTH,
Paul Ibison|||Most folks prefer using DTS to the upsizing wizard... However if you do use
the upsizing wizard, go back and ensure that the sql column data types are
appropriate... My experience with the wizard is that it makes the columns
sizes too large.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <kerocow@.yahoo.com> wrote in message
news:221ADD36-EA26-45DA-A953-D700866C06F6@.microsoft.com...
> Hi,
> I created a table and filled data to it in ACCESS 2002. How can I migrate
the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has
primary key and also has records. I want to append the ACCESS data (2
columns) to the existing table with the same columns names in MSSQL.
> How can I do it?
> Thanks

Middleware to access Sybase databases?

Does anyone use some kind of middleware or linked server
configuration to combine their MSSQL Server data with data
from other relational databases such as Sybase? Thanks
for any help.
SondraHi,
Multiple options , You can either use,
1. Linked server (create a linked server from SQL server to connect to
Sybase or any RDBMS )
2. DTS
3. BCP IN (Sybase), Export ( DB2) , SQLLDR (Oracle) to export data and use
BCP IN in sql server to load
Thanks
Hari
MCDBA
"Sondra" <anonymous@.discussions.microsoft.com> wrote in message
news:5e3801c3e5b3$fc362760$a401280a@.phx.gbl...
> Does anyone use some kind of middleware or linked server
> configuration to combine their MSSQL Server data with data
> from other relational databases such as Sybase? Thanks
> for any help.
> Sondra

Middleware to access Sybase databases?

Does anyone use some kind of middleware or linked server
configuration to combine their MSSQL Server data with data
from other relational databases such as Sybase? Thanks
for any help.
SondraHi,
Multiple options , You can either use,
1. Linked server (create a linked server from SQL server to connect to
Sybase or any RDBMS )
2. DTS
3. BCP IN (Sybase), Export ( DB2) , SQLLDR (Oracle) to export data and use
BCP IN in sql server to load
Thanks
Hari
MCDBA
"Sondra" <anonymous@.discussions.microsoft.com> wrote in message
news:5e3801c3e5b3$fc362760$a401280a@.phx.gbl...

quote:

---

> Does anyone use some kind of middleware or linked server
> configuration to combine their MSSQL Server data with data
> from other relational databases such as Sybase? Thanks
> for any help.
> Sondra

---

Microsoft.ReportingServices.UI ?

does microsoft give away the source for the reporting service ui?
usually installed in
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\Pages
I would love to see the code for this page
<%@. Register TagPrefix="MSRS" Namespace="Microsoft.ReportingServices.UI"
Assembly="ReportingServicesWebUserInterface" %>
<%@. Page language="c#" Codebehind="DataSource.aspx.cs"
AutoEventWireup="false"
Inherits="Microsoft.ReportingServices.UI.DataSourcePage" %>
If they don't give this code away they should it would be very very helpfull.We all wish. But there is some sample UI code you can download.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:8A7902B5-5496-458B-B323-013B9724EF53@.microsoft.com...
> does microsoft give away the source for the reporting service ui?
> usually installed in
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\Pages
> I would love to see the code for this page
> <%@. Register TagPrefix="MSRS" Namespace="Microsoft.ReportingServices.UI"
> Assembly="ReportingServicesWebUserInterface" %>
> <%@. Page language="c#" Codebehind="DataSource.aspx.cs"
> AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.DataSourcePage" %>
> If they don't give this code away they should it would be very very
> helpfull.

Microsoft.ReportingServices.Designer

Hello all,

After reinstalling several times the MSSQL 2005 express, and the SP1 when I'm trying to create a project with the BIDS I've got an error:
Could not load file or assembly
Microsoft.ReportingServices.Designer
Version=9.0.242.0
..
or one of its dependencies. System cannot find the file.

But it exists!!!What can I do?

Many thx!

I've to inform that it looks like this file is already on my machine, and with the same version.

I tried to uninstall all SQL Server instances, NET framework 2.0 and all Vstudio (even 2003 version), restarted my machine and reinstall everything. The order was:

Vstudio for web developers Express

SQLExpress2005 Advanced

SQLExpress 2005 SP1

The installation was fine but the BIDS is causing me problems.

Sugestions?

|||

Finally solved by myself!

The order which I've done it is....

Uninstall all VStudio + SQLServer
Install .NET Framework 2.0
Install SQL Server 2005 Advanced in the default path (C:\)
Install SQL Server 2005 SP1 in the default path (C:\)
Install VStudio 2005 WebDeveloper in the default path (C:\)

Hope this helps to someone

|||

Hi:

Well, I guess the problem is the path of installation...

As most of us seems to be running out of space on 'C' Drive;

we tend to change the default path of installation...

During installation, if one sets all path to be the same drive; then the problem should disappear...

HTH,

|||

hi, i have encountered this same error message. I'm using VS 2005 Professional, it is a mature installation with several plugins and additional components, everything is customised the way i want it.

I cannot accept that i must uninstall VS2005 just to get the BI Dev Studio plugin working. That would represent a total failure of the VS integration in my opinion. all my SQL 2005 components are installed on C: so that solution wouldn't be relevant for me.

is there a response from MS on why this error message happens? and a proper way to resolve it?

hoping for an informative response :)
tim.

|||

hmmm. I have the same issue as you. I cannot uninstall my SQL server as it has production data running on it. I DID go through the painful process of uninstalling the VS 2005 installation and reinstalling it. It did not change a single thing. I am right back to the same error message.

Hope someone figures this out soon. Nothing on MS sites so far that I can find.

Brian King

|||

I had the same problem. I did my install on D: drive, but surprisingly it seems Reporting Services was installed on C: drive.

I solved the problem by copying the content of C:\Program Files\Microsoft Visual Studio 8\Common7\IDE to the same directory on the D: drive. Seems to work fine.

|||I've had the same problem and re-installed VS2005 from scratch, tried to repair .NET 2.0 but all in vain. Confirm that the above copy solution worked for me. Thanks for info Guy.|||the copying worked for me aswell, thanks guy|||

Microsoft documents it's solution here http://support.microsoft.com/?id=823245 but it didn't work for me. After repairing I found the copy
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE to
D:\Program Files\Microsoft Visual Studio 8\Common7\IDE and it worked a treat!

Thanks!!