Migrating from MDF File to SQL Server 2005?

I created a web site using the "personal web site" starter kit with Visual Studio 2005. It created an ASPNET MDF file as well as a Personal.MDF in my App_Data directory.

I want to migrate this site to a production server that does not have SQL Express, but does have a full version of SQL Server 2005.

How do I migrate both the database format (tables and SPs) as well as the data to the SQL Server?

I can't find anything that looks like Enterprise Manager or Query Analyzer on my system, and installing the "native client tools" doesn't do anything except install configuration utilities (no clients).

Inside of Visual Studio 2005, I can bring up both databases (one from the MDF file(s) and one using a connection string to the server), but there doesn't appear to be any way to copy from one to the other.

There must be an easy way to do this, right?

Many thanks in advance!

Hello cambler,

You can simply do a straight-forward Attach Database function in SQL Server 2005 Management Studio. You can simply copy the MDF and LDF files from your App_Data folder from your PC to your production server running SQL Server 2005. Then, attach the database pointing to the copy of the MDF file. You need to detach the database files first on your development PC before you can copy the MDF and LDF files as the service is making use of these files.

For more information, visit http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

|||

I am trying to utilize the SQL Server Management Studio to open an MDF file, so I can perform tasks such as importing CSV files. I guess that I need to attach to a an MDF file, but I don't see the that option available.

If there is a nother approach, please advise.

Thanks,

|||From SQL Server Management Studio, you can simply right-click on the Databases folder and select Attach. Then point to the location of your MDF file. That should do the trick.sql

Migrating from Intebrase to SQL Server

Hi.
We've been having many problems with our current Interbase database
(v6.0.2.0). These are mainly corruption issues with the constant need to
backup and restore, and the fact that it is almost impossible to reliably
change metadata in a live environment.
We need to change servers, and our two options are either upgrading to
Interbase 7.1, or migrating to SQL Server 2000.
These are our requirements:
- the ability to reliaby and efficiently cope with huge amounts of data
- the server must be able to cope seemlessly with clients not disconnecting
cleanly and transactions left open (IB seems to have trouble with this), or
at least provide some mechanism to view such transactions and kill them
- the ability to change metada in a live environment with many connected
users (create/drop tables, foreign keys, etc - is this possible with any DB
at all?)
- it is critical that the database is up 24/7 - we can afford no downtime -
is this something SQL Server can provide?
I have also read a lot about how Interbase is unique in that it provides a
multi-generational architecture - presumably this means SQL Server does not,
but what are the implications of this? Could someone explain how SQL's
architecture differs from IB's with respect to this?
Has anyone else had any experience (good or bad) migrating from IB to SQL?
Perhaps our problems would not be solved by this and therefore it would be a
waste of time and money?
We basically need as much info on the subject as possible so we can make an
informed decision.
MikeAs you haven't gotten any replies yet, I'll only chime in with my little comment (I haven't worked
with Interbase):
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
The multi-generation stuff means that when you start reading, you get a "personal snapshot" of the
data. You work with that over time, and other modifying the real data will not block you (but you
have the be extremely careful if you then have to do modifications based on this old data). SQL
Server doesn't have this, so you have blocking. If you try to read something that someone else has
modified but not committed, you will be blocked until the modifier ends the transaction. So, in SQL
Server, you want to use short transactions.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Tremendous" <noone@.hotmail.com> wrote in message news:ONxqCMmrDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi.
> We've been having many problems with our current Interbase database
> (v6.0.2.0). These are mainly corruption issues with the constant need to
> backup and restore, and the fact that it is almost impossible to reliably
> change metadata in a live environment.
> We need to change servers, and our two options are either upgrading to
> Interbase 7.1, or migrating to SQL Server 2000.
> These are our requirements:
> - the ability to reliaby and efficiently cope with huge amounts of data
> - the server must be able to cope seemlessly with clients not disconnecting
> cleanly and transactions left open (IB seems to have trouble with this), or
> at least provide some mechanism to view such transactions and kill them
> - the ability to change metada in a live environment with many connected
> users (create/drop tables, foreign keys, etc - is this possible with any DB
> at all?)
> - it is critical that the database is up 24/7 - we can afford no downtime -
> is this something SQL Server can provide?
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
> Has anyone else had any experience (good or bad) migrating from IB to SQL?
> Perhaps our problems would not be solved by this and therefore it would be a
> waste of time and money?
> We basically need as much info on the subject as possible so we can make an
> informed decision.
> Mike
>

Migrating from Excel to sql-server

Hello,
I have got the job to migrate data from an Excel-sheet to a
sql-server-database. I have a lot of experience with Access but
Sql-Server is not in my line.
So who can give me some advice about how to do this job?

thanx for your help, HelmutEasiest way is to use the Data Transformation Services (DTS) of SQL Server.
It has a nice UI.

Start > Programs > Microsoft SQL Server > Import and Export Data

HTH,
Dave

"Helmut Blass" <helmut_blass@.tweb.de> wrote in message
news:bplp9f$olf$07$1@.news.t-online.com...
> Hello,
> I have got the job to migrate data from an Excel-sheet to a
> sql-server-database. I have a lot of experience with Access but
> Sql-Server is not in my line.
> So who can give me some advice about how to do this job?
> thanx for your help, Helmut

Migrating from Enterprise to SQL Server 2000 Standard

We are migrating a browser based application developed
with the Enterprise version to a server running the
standard edition. There will be some development on the
standard version before a new vresion of the application
is deployed. Can anyone help me to understand what
issues/problems we should look out for?Since most of the difference between Standard Edition and Enterprise Edition
involves scalability and availability features (>4 processors, large memory
support, clusters) there generally will be no problems migrating
applications from Enterprise to Standard. The few small gotchas include
Transparent Materialized Views and Distributed Partitioned Views, both of
which are only included in Enterprise Edition. (For completeness, Analysis
Services has some similar differences between Standard and Enterprise). If
you have not used these few scalability-related features then you will not
notice application level differences between these editions.
See "Features Supported by the Editions of SQL Server 2000" in Books Online.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"rez" <rezam@.go.com> wrote in message
news:039601c37bc5$bda02a30$a401280a@.phx.gbl...
> We are migrating a browser based application developed
> with the Enterprise version to a server running the
> standard edition. There will be some development on the
> standard version before a new vresion of the application
> is deployed. Can anyone help me to understand what
> issues/problems we should look out for?

Migrating From Development to Production Environment in MSAS 2005

Hi,

I have built a project in Ananlysis Services 2005 and would like to migrate the contents of the Project ( Dimensions, Measures, Cubes Etc) to a New server having analysis Services 2005 , what is the best approach. Does microsoft provide any best practices document. I searched the forum and the topics on migration seem to be from Analysis Services 2000 to Analysis Services 2005, can some one guide me on migration between Analysis Services 2005 and 2005.

Thansks

A backup and restore (like in AS2000) is certainly possible. This is the only method if you need to get the data cube moved in a processed state (with data). Except you could use the new synchronization feature, but that would probably be using this feature for something it was not quite intended.

The recommended method would be using the new AS Deployment Wizard. Make a "build" in BI Development Studio of your database and fire up the AS Deployment Wizard. This will allow you to specify different settings for your cube database when it is deployed to the production server. For more info on how to use the Deployment Wizard, see http://msdn2.microsoft.com/en-US/library/ms176121.aspx

Migrating from Developer Edition to Standard Edition

Hello,
I have Reporting Sevices Developer Edition running on a Windows XP Pro
workstation. I want to migrate it to Reporting Services/SQL Server Standard
Edition on a Windows 2003 server. Can this be done? Since the edition of RS
changes, and the edition of SQL Server changes, and the hardware changes, I
need to know if this is feasible, or if it would be better to redeploy the
reports to the new server. BOL isn't very helpful in this area, and I don't
have time to learn C# to use the RS utility.
Thanks in advance.Given that it is so easy to redeploy and the fact that everything is
changing, my advice is to install new and then redeploy.
Bruce L-C
"Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> Hello,
> I have Reporting Sevices Developer Edition running on a Windows XP Pro
> workstation. I want to migrate it to Reporting Services/SQL Server
Standard
> Edition on a Windows 2003 server. Can this be done? Since the edition of
RS
> changes, and the edition of SQL Server changes, and the hardware changes,
I
> need to know if this is feasible, or if it would be better to redeploy the
> reports to the new server. BOL isn't very helpful in this area, and I
don't
> have time to learn C# to use the RS utility.
> Thanks in advance.|||Others would need to jump in for your answer. I can't really help you with
that.
Bruce L-C
"Cable Guy" <CableGuy@.discussions.microsoft.com> wrote in message
news:40C1D674-60C1-49AB-B8B6-5483EE8BDE2F@.microsoft.com...
> Hi Bruce,
> Thanks for the reply. That is what I was initially persuaded to do, but
> redeploying introduces the possibility of something being overlooked, and
> that is what we want to avoid. Is it possible to use the RS utility to do
> what we need it to do (i.e. copy the logical contents of the database to a
> new database/server/edition) and have it work properly? Do you know of
> any
> good documentation on the RS utility?
> "Bruce Loehle-Conger" wrote:
>> Given that it is so easy to redeploy and the fact that everything is
>> changing, my advice is to install new and then redeploy.
>> Bruce L-C
>> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
>> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
>> > Hello,
>> >
>> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
>> > workstation. I want to migrate it to Reporting Services/SQL Server
>> Standard
>> > Edition on a Windows 2003 server. Can this be done? Since the edition
>> > of
>> RS
>> > changes, and the edition of SQL Server changes, and the hardware
>> > changes,
>> I
>> > need to know if this is feasible, or if it would be better to redeploy
>> > the
>> > reports to the new server. BOL isn't very helpful in this area, and I
>> don't
>> > have time to learn C# to use the RS utility.
>> >
>> > Thanks in advance.
>>|||Hi Bruce,
Thanks for the reply. That is what I was initially persuaded to do, but
redeploying introduces the possibility of something being overlooked, and
that is what we want to avoid. Is it possible to use the RS utility to do
what we need it to do (i.e. copy the logical contents of the database to a
new database/server/edition) and have it work properly? Do you know of any
good documentation on the RS utility?
"Bruce Loehle-Conger" wrote:
> Given that it is so easy to redeploy and the fact that everything is
> changing, my advice is to install new and then redeploy.
> Bruce L-C
> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> > Hello,
> >
> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
> > workstation. I want to migrate it to Reporting Services/SQL Server
> Standard
> > Edition on a Windows 2003 server. Can this be done? Since the edition of
> RS
> > changes, and the edition of SQL Server changes, and the hardware changes,
> I
> > need to know if this is feasible, or if it would be better to redeploy the
> > reports to the new server. BOL isn't very helpful in this area, and I
> don't
> > have time to learn C# to use the RS utility.
> >
> > Thanks in advance.
>
>sql

Migrating from Crystal to SSRS questions

Hi all,
Our company is considering converting all our reports from Crystal to SSRS.
We have done a fair bit of research. I have a number of questions that I
can't find answers to. If anyone with experience using the software could
help me out with some answers, I'd be most grateful.
1. Can you save data with a report? or do you have to connect to data every
time you run a report?
2. I've heard when scrolling a matrix report that the row heading overlaps
values when scrolling and looks very messy. Is this true and is there a way
to stop it happening as a lot of our reports are Matrix based?
3. Is there an equivalent to Crystal's Group Explorer, for just viewing a
certain group?
4. With regards date formatting we have heard it defaults to US date. Is it
possible to permanently overide this or do you have to do it manually for
each report?
Many thanks
JHJH,
I'm having some difficulty getting this feature to work accross the
entire row header. But, what I've found out so far is RS2005 does
offer the feature, but it's not very intuitive to use. The only way
I've found it to work (somewhat) is
1. Add a group to your Matrix/Table.
2. Edit the group by right-clicking the group text-box.
3. Check the box at the bottom of the General tab for "Group header
should remain visible while scrolling".
I haven't figured out yet how to select the entire row (which in my
case contains about 15 measures) and set the row to freeze like in
excel. I even tried to add subsequent groups as separate
measures...can't figure that one out either.
Corey
JH wrote:
> Hi all,
> Our company is considering converting all our reports from Crystal to SSRS.
> We have done a fair bit of research. I have a number of questions that I
> can't find answers to. If anyone with experience using the software could
> help me out with some answers, I'd be most grateful.
> 1. Can you save data with a report? or do you have to connect to data every
> time you run a report?
> 2. I've heard when scrolling a matrix report that the row heading overlaps
> values when scrolling and looks very messy. Is this true and is there a way
> to stop it happening as a lot of our reports are Matrix based?
> 3. Is there an equivalent to Crystal's Group Explorer, for just viewing a
> certain group?
> 4. With regards date formatting we have heard it defaults to US date. Is it
> possible to permanently overide this or do you have to do it manually for
> each report?
> Many thanks
> JH

Migrating from Crystal Reports Charts using TopN and "Other" to Reporting Services

I am currently migrating several Crystal Reports that have charts. The chart series is set to use the TopN values in a count, and combine the rest of the series into one series titled "Other". I see how to filter the chart using TopN, however I need to still show the rest of the data grouped as "Other". I think the solution I need is to create a group expression that uses some sort of IIF function to create the "Other" group, but I haven't found a function that I can use to get the results I need. Any ideas?

Scott

I found the answer to my question in another forum.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1055659&SiteID=1

I used option 1.

Migrating from Crystal Reports Charts using TopN and "Other" to Reporting Services

I am currently migrating several Crystal Reports that have charts. The chart series is set to use the TopN values in a count, and combine the rest of the series into one series titled "Other". I see how to filter the chart using TopN, however I need to still show the rest of the data grouped as "Other". I think the solution I need is to create a group expression that uses some sort of IIF function to create the "Other" group, but I haven't found a function that I can use to get the results I need. Any ideas?

Scott

I found the answer to my question in another forum.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1055659&SiteID=1

I used option 1.

migrating from crystal report 8.5 to 10.0 using vb .

I am working in migrating my cr application to 10th ver from the existing 8.5 where we have to explicitly make a query in query builder and then attach it. I was using dsn in ms odbc for oracle driver to connect the DB. Now in 10.0 i have removed the query file created using 8.5 query builder and trying to write directly to command editor. the report as stand alone is working fine. but when i try to generate the pdf using my vb application then on .export option it is giving error as "Logon failed.
Details: 01S00:[Microsoft][ODBC driver for Oracle]Invalid connection string attribute". i think the problem might be with cddb_oracle.dll or crdb_oracle.dll file as i am using a evaluation version of crystal report 10. as input to dsn i am providing
1.dsn
2.usid
3.database
4.password
what else is missing . plz help....The next is code I use in VB6 to display in CRViewer10 a CR10 report that read data thru ODBC

Set CRReport = CRApplication.OpenReport("Report.rpt", 1)
For MEnt2 = 1 To CRReport.Database.Tables.Count
Set CRTabla = CRReport.Database.Tables(MEnt2)
Set CRConProp = CRTabla.ConnectionProperties("Provider")
CRConProp.value = "MSDASQL"
Set CRConProp = CRTabla.ConnectionProperties("Data Source")
CRConProp.value = "DSNname"
Set CRConProp = CRTabla.ConnectionProperties("Initial Catalog")
CRConProp.value = "Datapath"
Set CRConProp = CRTabla.ConnectionProperties("User ID")
CRConProp.value = ""
Set CRConProp = CRTabla.ConnectionProperties("Locale Identifier")
CRConProp.value = "2058"
Set CRConProp = CRTabla.ConnectionProperties("OLE DB Services")
CRConProp.value = "-5"
Next MEnt2

frmReport.Show vbModal

Migrating From CR 9 to VS Reporting Services

Hi,
I'm not that familiar with VS RS and I have to convert reports from Crystal
Report 9 to VS RS.
Is it possible ? If not, is there any equivalent of the "unbound fields"
from CR9 in VS RS ?
Thanks in Advance.
wadjaHello Wadja,
I apologize for contacting you on an unsolicited basis, but I read a
few of your postings on Google's Crystal Developers users group and
hoped that you'd be willing to help me.
I'm a journalist with Application Development Trends magazine (ADT,
http://www.adtmag.com), and I'm working on a story on reporting tools
from a developer's perspective. As you no doubt know, Crystal has
long been pretty much the only game in town in this respect, as the
former Crystal Decisions seeded the market with this tool (it's shipped
with Visual Studio for more than a decade, for example, and it ships
with a host of other applications and tools, too).
Now, however, developers have a choice of which reporting tool to use
when they need to incorporate reporting functionality into their apps.
Microsoft announced its SQL Server 2000 Reporting Services last year,
and there's even an open source alternative called BIRT (that's short
for BI Reporting Tool). I'd like to take a look at the positives and
negatives of each of these solutions -- Crystal still seems hard to
beat for straight-up reporting, but both Reporting Services and BIRT
promise better (that is, cheaper) report lifecycle management
capabilities, which could make them better choices for many BI-related
reporting applications.
Would you mind having a go at a few of the questions I've appended
below? I'd love to hear your thoughts on this issue. If your employer
prohibits you from speaking on the record to journalists, I don't
have a mind abstracting its identity (e.g., "a major pharmaceutical
company based in the Northeast," or "a large financial services
firm based in the American South"). So don't let that stop you.
Thanks very much for your time. I appreciate it, and I apologize, once
again, for contacting you on an unsolicited basis.
Best,
Steve
1. Is your organization invested in Crystal, or have you transitioned
over to Reporting Services? If the former, for what purposes or in
support of which applications do you use Crystal? (I'm trying to get
a sense for whether you use Crystal mostly as a default reporting tool
for app dev purposes, whether it's tied to the use of any specific
application, or whether you're using it mostly as a reporting
front-end for data warehousing and BI.)
2. Did your organization opt for Crystal as a matter of choice -
e.g., you evaluated CR in addition to a host of other tools - or did
it go with Crystal largely because it was bundled with your default
development environment, or core applications that you use?
3. In comparison with Reporting Services and other reporting solutions,
what features about Crystal do you most like? What features about it
would you most like to change? How well do you feel Crystal
addresses/addressed your core use requirements?
4. Re: Reporting Services, are you currently using it? If not, have you
looked into using it? What features, if any, do you most like? What
features, if any, are deal-breakers for you? How do you feel that
Reporting Services compares with Crystal and other established
reporting solutions?
5. Do you have any interest in an open source reporting tool based on
the Eclipse development framework? If so, have you looked at the BIRT
project?|||Hello Steve,
As I said before, I'm new with reporting tools and with my company.
Therefore I can hardly make objective answers to you.
All I can tell is that they (developers before I step in) used Crystal
Report (CR) to test it. Now, we currently use Reporting Services(RS). The
reasons that made us change :
- We don't have to invoice our clients for using CR, it is included in
Microsoft Licences (we have a partnership with this company).
- Several developers complained about the technical support given with CR.
They hardly find professional help they needed.
- I was said designing on RS was much more easier than on CR.
For the moment, we are not interested in other report tools, as RS meets our
satisfaction.
I hope I gave any relevant information for you.
Best,
wadja
"daedalus" <stephen.swoyer@.gmail.com> a écrit dans le message de news:
1117634907.794866.228080@.f14g2000cwb.googlegroups.com...
> Hello Wadja,
> I apologize for contacting you on an unsolicited basis, but I read a
> few of your postings on Google's Crystal Developers users group and
> hoped that you'd be willing to help me.
> I'm a journalist with Application Development Trends magazine (ADT,
> http://www.adtmag.com), and I'm working on a story on reporting tools
> from a developer's perspective. As you no doubt know, Crystal has
> long been pretty much the only game in town in this respect, as the
> former Crystal Decisions seeded the market with this tool (it's shipped
> with Visual Studio for more than a decade, for example, and it ships
> with a host of other applications and tools, too).
> Now, however, developers have a choice of which reporting tool to use
> when they need to incorporate reporting functionality into their apps.
> Microsoft announced its SQL Server 2000 Reporting Services last year,
> and there's even an open source alternative called BIRT (that's short
> for BI Reporting Tool). I'd like to take a look at the positives and
> negatives of each of these solutions -- Crystal still seems hard to
> beat for straight-up reporting, but both Reporting Services and BIRT
> promise better (that is, cheaper) report lifecycle management
> capabilities, which could make them better choices for many BI-related
> reporting applications.
> Would you mind having a go at a few of the questions I've appended
> below? I'd love to hear your thoughts on this issue. If your employer
> prohibits you from speaking on the record to journalists, I don't
> have a mind abstracting its identity (e.g., "a major pharmaceutical
> company based in the Northeast," or "a large financial services
> firm based in the American South"). So don't let that stop you.
> Thanks very much for your time. I appreciate it, and I apologize, once
> again, for contacting you on an unsolicited basis.
> Best,
> Steve
> 1. Is your organization invested in Crystal, or have you transitioned
> over to Reporting Services? If the former, for what purposes or in
> support of which applications do you use Crystal? (I'm trying to get
> a sense for whether you use Crystal mostly as a default reporting tool
> for app dev purposes, whether it's tied to the use of any specific
> application, or whether you're using it mostly as a reporting
> front-end for data warehousing and BI.)
> 2. Did your organization opt for Crystal as a matter of choice -
> e.g., you evaluated CR in addition to a host of other tools - or did
> it go with Crystal largely because it was bundled with your default
> development environment, or core applications that you use?
> 3. In comparison with Reporting Services and other reporting solutions,
> what features about Crystal do you most like? What features about it
> would you most like to change? How well do you feel Crystal
> addresses/addressed your core use requirements?
> 4. Re: Reporting Services, are you currently using it? If not, have you
> looked into using it? What features, if any, do you most like? What
> features, if any, are deal-breakers for you? How do you feel that
> Reporting Services compares with Crystal and other established
> reporting solutions?
> 5. Do you have any interest in an open source reporting tool based on
> the Eclipse development framework? If so, have you looked at the BIRT
> project?
>sql

Migrating from ASP

I have a bunch of contracts built in asp and I need them to be

published on report server. Is there any easier way than rebuilding

them in report designer?

There is not migration path from ASP to Reporting Services reports. You can store the asp contracts in reporting services as resources, but they will not be executed as reports.

Migrating from Acess 2003 to Sql server - Function Val()

Hi folks

I do a lot of work with address data. I have been using Acess 2003 as my database but now need to upgrade to SQL Server.

My queries have been making extensive use of the Val function to order my queries on address columns. A typical address column might be:

12 The Avenue .......

by using Order By Val(AddressCol) I can sort these rows in ascending house number.

I can't find anything in SQL Server that will acomplish this. Does anybody have any ideas.

Thanks

Paul

why do you need to reference the val() function? is there something in your column that you want to exclude?

Does: "order by AddressCol" in TSQL not accomplish what you want?

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 from Access to SQL Server with a web front end

Afternoon all,

Apologies for cross-posting but as my query covers both Access and SQL
Server I thought I'd send it both!

I have inherited a project to migrate a fairly complex series of
Access databases into a single proper SQL database with a web front
end.

Its quite a nasty job as people are working on a variety of data sets
at several Universities around the world and the data has got very
messy; hence the requirement to put it all on one live web enabled
database server and provide a web-based front end (particularly as
some users insist on using Macs so can't run Access as a front end
anyway).

If anyone could give me hints on how to perform such a migration or if
anyone knows of any good books or other documents on this I'd be
grateful for assistance.

Many thanks

Rich May
Museum of LondonFirst off, congratulations - this is a great move to make.

As far as converting the tables, use DTS (Data Transformation Services) in
sql server. It is pretty much wizard driven and works really well. As for
the queries, you would do best to forget you even have them in Access and
rewrite everything using Stored Procedures. My favorite book for "stepping"
between access and sql server is "Microsoft Access Developer's Guide to SQL
Server" by Chipman and Baron. It's wonderful.

As for your web front end... that is where the headache starts. You can
pretty much choose two paths if you want to stay on the Microsoft path. You
can do it using ASP or ASP.NET. Both will run on IIS (.net needs framework
installed).

If you are serving to Macs, you may deliver your app better using ASP
because you will be able to better control the rendered output (very good
for Netscape). I use Visual Basic to build "ActiveX DLL" components to
alleviate the complexity of the ASP pages so you would want to be proficient
in Visual Basic and instantiating COM components. Next, you need to take
ADO (ActiveX Data Objects) head on and learn it well, that is what you will
use to connect to sql, execute queries, and work with data.

If you are using IE on all clients and have control over them, you will find
that Visual Studio.NET does a great job at deploying web applications in
ease and speed, once you pass the high learning curve. I VERY highly
recommend "Programming Microsoft Visual Basic .Net" by Francesco Balena
(1600 pages).

If it all sounds complex, don't get me wrong... it is. If you need help,
let me know, we do this for a living.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||several choices, depending on what you're comfy with ..
1) sql server, IIS & ASP
2) mySQL, apache, php

here's a RAD tool that I found the other day to create web pages for
either option (I haven't tried it yet)
http://www.citybusinesslogic.com/apwab.php

and somewhere I've got vba code to convert ms-access tables to mysql
format

rmay@.museumoflondon.org.uk (Rich May) wrote in message news:<e12c70e0.0311140943.722ed0ba@.posting.google.com>...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||Hi Rich,

I think there are 3 options. a) Access' Upsizing Migration Wizard
b)Sql Server's DTS and c) setting Access as a linked server and
programmatically importing the data. I believe the main problem
you'll run into is that Access' datatypes are not 100% compatible with
Sql server's. - Louis|||louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311142220.41dff853@.posting.google.com>...
> Hi Rich,
> I think there are 3 options. a) Access' Upsizing Migration Wizard
> b)Sql Server's DTS and c) setting Access as a linked server and
> programmatically importing the data. I believe the main problem
> you'll run into is that Access' datatypes are not 100% compatible with
> Sql server's. - Louis

Another problem I have seen with the upsize wizard is it makes all
columns with the type nvarchar i.e. unicode which doubles the space
requirements.

Duncan|||"duncan" wrote

> Another problem I have seen with
> the upsize wizard is it makes all
> columns with the type nvarchar i.e.
> unicode which doubles the space
> requirements.

First, I don't think that is true -- all _text_ columns, perhaps, but
certainly not numeric and date/time columns.

Secondly, were you under the impression that Access does NOT use unicode? It
has since Access 2000. There is an option for "unicode compression" to
reduce the impact; I don't know if there is such an option in SQL Server,
but would think that it would have a similar feature.

Larry Linson
Microsoft Access MVP|||Is Cold Fusion a viable alternative to ASP in this kind of situation?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||In Ms SQL...

Char is fixed length and pads with spaces
NChar is fixed length and pads with spaces, plus is unicode
VarChar is variable character (with max length spec)
NVarChar is variable (maxlength), plus is unicode

Since I don't deploy to any other language and don't plan to, I never use
the N' datatypes.

Also in SQL, the "SmallDateTime" field type is 4 bytes and the "DateTime" is
8 bytes). If it's 4 bytes in Access, then the equiv is "SmallDateTime".

In SQL Numeric DT's, there is tinyint, smallint, int, real, bigint, float,
money, smallmoney, and decimal. All with their mix of capacity.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Larry Linson" <bouncer@.localhost.not> wrote in message
news:bpytb.58064$p9.53071@.nwrddc02.gnilink.net...
> "duncan" wrote
> > Another problem I have seen with
> > the upsize wizard is it makes all
> > columns with the type nvarchar i.e.
> > unicode which doubles the space
> > requirements.
> First, I don't think that is true -- all _text_ columns, perhaps, but
> certainly not numeric and date/time columns.
> Secondly, were you under the impression that Access does NOT use unicode?
It
> has since Access 2000. There is an option for "unicode compression" to
> reduce the impact; I don't know if there is such an option in SQL Server,
> but would think that it would have a similar feature.
> Larry Linson
> Microsoft Access MVP|||Sure, I looked into it some time ago when I was a beginner, but then I
discovered the unimaginable amount of documentation and books from multiple
authors in Microsoft products and I didn't even give it a second thought.
To me, no product is satisfactory if I have to spend days looking for
answers, and especially when they aren't there to be found.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"John Callaghan" <jackit2@.yahoo.com> wrote in message
news:3fb88be3$0$194$75868355@.news.frii.net...
> Is Cold Fusion a viable alternative to ASP in this kind of situation?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||In my experience, the data conversion part of a project like this is almost
trivial compared to the front-end application challenge.

Access forms and reports can be incredibly complex, with subforms, simple
data binding, no-code dynamic behavior (record navigation, filtering,
sorting, finding, column-reordering, all those are built in to Access, not
at all in asp), and the list goes on and on. If your Access front-end
application is complex, it could be impossibly time-consuming to do in asp,
slightly less so in asp.net.

You would need some other tool for reporting (Crystal or others), I've made
crude html reports in asp and it is nearly impossible to do even the easy
stuff Access reports can do.

As a example, I wrote a full maintenance application for a 30 table database
(with lots of RI between tables), with multiple subforms, dropdown
navigation, and lots of other features, all in one day of work.. ASP would
have taken weeks to do even close to the same functionality.

So overall, my suggestion is if the Access forms are complex, plan for 1000
to 2000% more development time over Access forms. You will definitely need a
better reporting tool than html/asp. Hope this helps,

Steve Nyberg

"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of Londonsql

migrating from Access to SQL Server

Our organization has about 10 multiuser Access databases that I'm
considering migrating to SQL Server. Currently, these split databases
perform reasonably well in Access over our LANs, but slow over the T1 lines.
However, we occasionally run into corruption problems, and we would
eventually like to develop some browser-based applications to work with our
data.
Most of these databases have dozens of queries and form controls also based
on queries, so the most realistic strategy for us in the short term would
probably be to just migrate the data, and link the remaining Access front
ends to the tables in SQL Server.
However, I'm wondering if we will accomplish much by taking such a step,
since all the queries will still be in the front end. I understand there
would be a performance improvement if we had the resources (which we
currently don't) to convert those queries to views and stored procedures in
SQL Server. But if we only move the data tables to the back end, do we
really obtain enough of a benefit to justify taking even that step?
Thanks in advance,
Paul
Hi,
Please do not misunderstand moving data from Acess to SQL Server would
increase the performance, it is still based on the design of the databases
and all the indexes. I would recommend to move from Access to SQL Server
since SQL Server can support more concurrent users and it provides better
security and data import/export functions.
Ed
"Paul Ponzelli" wrote:

> Our organization has about 10 multiuser Access databases that I'm
> considering migrating to SQL Server. Currently, these split databases
> perform reasonably well in Access over our LANs, but slow over the T1 lines.
> However, we occasionally run into corruption problems, and we would
> eventually like to develop some browser-based applications to work with our
> data.
> Most of these databases have dozens of queries and form controls also based
> on queries, so the most realistic strategy for us in the short term would
> probably be to just migrate the data, and link the remaining Access front
> ends to the tables in SQL Server.
> However, I'm wondering if we will accomplish much by taking such a step,
> since all the queries will still be in the front end. I understand there
> would be a performance improvement if we had the resources (which we
> currently don't) to convert those queries to views and stored procedures in
> SQL Server. But if we only move the data tables to the back end, do we
> really obtain enough of a benefit to justify taking even that step?
> Thanks in advance,
> Paul
>
>
|||Good points. Thanks, Ed.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:1F01575D-FA7D-4C73-ADC6-F205B7B035DD@.microsoft.com...[vbcol=seagreen]
> Hi,
> Please do not misunderstand moving data from Acess to SQL Server would
> increase the performance, it is still based on the design of the databases
> and all the indexes. I would recommend to move from Access to SQL Server
> since SQL Server can support more concurrent users and it provides better
> security and data import/export functions.
> Ed
> "Paul Ponzelli" wrote:
|||"Paul Ponzelli" <begone@.spam.forever> wrote in message
news:enIRb3vPFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Our organization has about 10 multiuser Access databases that I'm
> considering migrating to SQL Server. Currently, these split databases
> perform reasonably well in Access over our LANs, but slow over the T1
lines.
> However, we occasionally run into corruption problems, and we would
> eventually like to develop some browser-based applications to work with
our
> data.
In addition to the valid points that Ed made, SQL Server is far more
resilient to data corruption than Access.
Steve
|||Another reason to make the move.
Thanks, Steve.

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.
Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison
|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:

> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:
> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL Server

I recently migrated some user tables from Access to SQL Server while leaving
the forms, reports, and queries in Access with Linked tables to the SQL
instance. Some of the users queries/forms are not working correctly? It
seems that Access Queries return a single empty row when no matches are foun
d
while SQL Server returns an empty result set. In Access, the users use a
query to search for a customer number, if no matching number is found the
customer form is displayed with one record containing no values and the user
s
can then input new customer information. When the query/form is pointed to
SQL Server, when no matching customer is found a blank page is displayed. I
s
there a way to force SQL Server to return an empty row if none are found?Hi,
probably the attached script would help to you (I'm not sure how to
implement it within Access enviroment):
create table t (col1 int null, col2 varchar(10) null)
--without variables
select col1,col2 from t
union all
select null, null
--with variables
declare @.r as int
select * from t
set @.r=@.@.rowcount
if @.r>0
return
else
select null as col1, null as col2
drop table t
Denis
"DT" wrote:

> I recently migrated some user tables from Access to SQL Server while leavi
ng
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are fo
und
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the us
ers
> can then input new customer information. When the query/form is pointed t
o
> SQL Server, when no matching customer is found a blank page is displayed.
Is
> there a way to force SQL Server to return an empty row if none are found?
>|||Although your solution does cause the form to be displayed (progress!) I can
not enter data on the form? Am I missing something?
"Denis" wrote:
> Hi,
> probably the attached script would help to you (I'm not sure how to
> implement it within Access enviroment):
> create table t (col1 int null, col2 varchar(10) null)
> --without variables
> select col1,col2 from t
> union all
> select null, null
> --with variables
> declare @.r as int
> select * from t
> set @.r=@.@.rowcount
> if @.r>0
> return
> else
> select null as col1, null as col2
>
> drop table t
> Denis
> "DT" wrote:
>|||There is no such thing as an "empty" row in SQL Server. The functionality
you are wanting would probably be a property of the recordset or data grid.
"DT" <DT@.discussions.microsoft.com> wrote in message
news:0D425E66-49D7-4C46-97E2-7813DB70A6D0@.microsoft.com...
>I recently migrated some user tables from Access to SQL Server while
>leaving
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are
> found
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the
> users
> can then input new customer information. When the query/form is pointed
> to
> SQL Server, when no matching customer is found a blank page is displayed.
> Is
> there a way to force SQL Server to return an empty row if none are found?
>