By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,934 Members | 1,527 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,934 IT Pros & Developers. It's quick & easy.

Using DTS to migrate Access ot SQL Server

P: n/a
When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

But it executes each query, and created a _table_ to hold the results.

The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.

I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.

--
Wes Groleau

Beware of the man who works hard to learn something, learns
it, and finds himself no wiser than before ... He is full of
murderous resentment of people who are ignorant without having
come by their ignorance the hard way.
-- Kurt Vonnegut
Feb 3 '07 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Wes Groleau wrote:
When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

But it executes each query, and created a _table_ to hold the results.

The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.

I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.
Have you tried using the Upsize wizard?

Jonathan
Feb 3 '07 #2

P: n/a
Jonathan Roberts (gr*******@diynics.com) writes:
Wes Groleau wrote:
>When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

But it executes each query, and created a _table_ to hold the results.

The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.

I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.

Have you tried using the Upsize wizard?
I have never worked with Access myself, but from people that hold
expertise in both Access and SQL Server, I have heard strong recommendations
of staying away from the wizard.

Migrating from Access to SQL Server is quite an undertaking, and there
are many differences. Not the least since Access since is more or less
all-in-one, while with SQL Server you get a client/server setup.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 3 '07 #3

P: n/a
Jonathan Roberts wrote:
Wes Groleau wrote:
>When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

[snip complaint details].

Have you tried using the Upsize wizard?
The upsize Wizard does not work. On the third or fourth
page, it pops up a box saying nothing but "Overflow"
Nothing happens in SQL Server.

--
Wes Groleau
"Lewis's case for the existence of God is fallacious."
"You mean like circular reasoning?"
"He believes in God. Therefore, he's fallacious."
Feb 4 '07 #4

P: n/a
Erland Sommarskog wrote:
Migrating from Access to SQL Server is quite an undertaking, and there
are many differences.
This is very true. But as flawed as it is, importing via DTS
and then "fixing it" is less of an undertaking than building
the whole thing manually from nothing.

And in any case, when the application you wrote or inherited
gets big enough to make Access become unstable, migrating to
SOMEthing becomes imperative.
--
Wes Groleau

Expert, n.:
Someone who comes from out of town and shows slides.
Feb 4 '07 #5

P: n/a
Wes Groleau wrote:
[snip extraneous comments and non-extraneous stuff full of typos].
I guess those flaws sort of obscured my question.

Access upsizing wizard does not work.

DTS in SQL Server 2000 has a wizard that DOES work.

But does there exist any form of automation that will
take all Access QUERIES into SQL Server views and/or sprocs
INSTEAD OF into tables containing the results of the queries?

--
Wes Groleau

Even if you do learn to speak correct English,
whom are you going to speak it to?
-- Clarence Darrow
Feb 4 '07 #6

P: n/a
Erland Sommarskog wrote:
>Have you tried using the Upsize wizard?

I have never worked with Access myself, but from people that hold
expertise in both Access and SQL Server, I have heard strong recommendations
of staying away from the wizard.

Migrating from Access to SQL Server is quite an undertaking, and there
are many differences. Not the least since Access since is more or less
all-in-one, while with SQL Server you get a client/server setup.

Erland,

You're right; it isn't perfect by any stretch. However, it can be a
start. I did it a few years back when an app was breaking due to
Access' problematic use of file locks and networking. There are some
gotchas, but it saved me some time. Once upsized, you can create an ADP
fairly quickly to get your forms up and running (assuming you were using
Access forms in the first place as your interface).

Jonathan
Feb 4 '07 #7

P: n/a
Wes Groleau wrote:
Jonathan Roberts wrote:
>Wes Groleau wrote:
>>When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

[snip complaint details].

Have you tried using the Upsize wizard?

The upsize Wizard does not work. On the third or fourth
page, it pops up a box saying nothing but "Overflow"
Nothing happens in SQL Server.
Glad you mentioned it. You have hit the number one common problem with
the wizard. It does work but you have to overcome this issue. Google
it to find many pages of info. One KB is here:
http://support.microsoft.com/kb/272384. I don't remember the exact fix,
but it wasn't hard to resolve.

If you don't want to go that route, you can certainly pull out all your
queries manually and create views in SQL Server. I don't know of an
automated way to do this. If you have a lot of queries, this way may
not be practical.

Good luck and keep posting if you need help.

Jonathan
Feb 4 '07 #8

P: n/a
On Feb 3, 12:24 pm, Wes Groleau <groleau+n...@freeshell.orgwrote:
When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

But it executes each query, and created a _table_ to hold the results.

The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.

I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.

--
Wes Groleau

Beware of the man who works hard to learn something, learns
it, and finds himself no wiser than before ... He is full of
murderous resentment of people who are ignorant without having
come by their ignorance the hard way.
-- Kurt Vonnegut
Did you try the SQL Server Migration Assistant for Access?
http://www.microsoft.com/sql/solutio...s/default.mspx

Feb 4 '07 #9

P: n/a
Jonathan Roberts wrote:
Wes Groleau wrote:
>The upsize Wizard does not work. On the third or fourth
page, it pops up a box saying nothing but "Overflow"
Nothing happens in SQL Server.

Glad you mentioned it. You have hit the number one common problem with
the wizard. It does work but you have to overcome this issue. Google
it to find many pages of info. One KB is here:
http://support.microsoft.com/kb/272384. I don't remember the exact fix,
but it wasn't hard to resolve.
"Obtain the latest service pack" You're right--that's not hard.
I didn't bother to look because my predecessor said that the only
fix was to install Access 2003. Installing a new version--or installing
a service pack--is not something our I.S. department will do on demand.
If you don't want to go that route, you can certainly pull out all your
queries manually and create views in SQL Server. I don't know of an
automated way to do this. If you have a lot of queries, this way may
not be practical.
It's not all that bad. Shift to SQL view. select, copy, "New View",
paste, save. But still, automation would be nice.
--
Wes Groleau
http://freepages.rootsweb.com/~wgroleau/Wes
Feb 5 '07 #10

P: n/a
Steve wrote:
On Feb 3, 12:24 pm, Wes Groleau <groleau+n...@freeshell.orgwrote:
>When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

But it executes each query, and created a _table_ to hold the results.

The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.

I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.

--
Wes Groleau

Beware of the man who works hard to learn something, learns
it, and finds himself no wiser than before ... He is full of
murderous resentment of people who are ignorant without having
come by their ignorance the hard way.
-- Kurt Vonnegut

Did you try the SQL Server Migration Assistant for Access?
http://www.microsoft.com/sql/solutio...s/default.mspx
No. That's SQL Server 2005. Thanks anyway.

--
Wes Groleau

In any formula, constants (especially those obtained
from handbooks) are to be treated as variables.
Feb 5 '07 #11

P: n/a
Ben
Wes,

Just a thought. What are you trying to accomplish with the migration or
upsizing of your Access application? What is the Access version?
I used the Upsize Wizard to upsize our Access application. It went well
after I cleared all issues with my Access database. The Access
database structure is less strict and was meant to work on itself. Due to
the imperfections of the Access database the upsize wizard oftentimes
end up giving you a lot of errors the first or even 5th time. This is true
if I use even SQL Server 2005 Express. The data in the Access database
will also cause the upsize wizard to crash because SQL Server will not allow
erroneous data to creep into its tables.

FYI, using DTS will not expose all Access database problems also. You have
to understand and know which errors are caused by what.
Once you successfully upsized your access table, then you can concentrate on
deciding either to keep the queries or create views and stored
procedures in the SQL Server. This approach will be of help to you when you
link the tables from SQL Server in your Access application.

If you also try to google "Upsizing Access to SQL Server", you should find
more information regarding your task.

I also encourage you to use the Upsize Wizard of Access because that is why
it is there. It will definitely be quicker for you in the long run.
The upsize wizard if the access database or data have problems.

Based on my experience, the simplest approach (less work/change approach) is
usually the correct one.

"Wes Groleau" <gr**********@freeshell.orgwrote in message
news:1Yyxh.1926$5U4.1714@trnddc07...
Steve wrote:
>On Feb 3, 12:24 pm, Wes Groleau <groleau+n...@freeshell.orgwrote:
>>When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.

But it executes each query, and created a _table_ to hold the results.

The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.

I have to manually create the views/procedures and paste in the SQL.
And then I still have to translate it into standard SQL.

--
Wes Groleau

Beware of the man who works hard to learn something, learns
it, and finds himself no wiser than before ... He is full of
murderous resentment of people who are ignorant without having
come by their ignorance the hard way.
-- Kurt Vonnegut

Did you try the SQL Server Migration Assistant for Access?
http://www.microsoft.com/sql/solutio...s/default.mspx

No. That's SQL Server 2005. Thanks anyway.

--
Wes Groleau

In any formula, constants (especially those obtained
from handbooks) are to be treated as variables.

Feb 5 '07 #12

P: n/a
Wes Groleau (gr**********@freeshell.org) writes:
"Obtain the latest service pack" You're right--that's not hard.
I didn't bother to look because my predecessor said that the only
fix was to install Access 2003. Installing a new version--or installing
a service pack--is not something our I.S. department will do on demand.
In that case... Do a check with SELECT @@version in your SQL 2000 instance
to check that you have at least SP3. The version number should read
8.00.760 for SP3 and 8.00.2039 for SP4. If it's lower than 760, you should
insist on an upgrade.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 5 '07 #13

P: n/a
Erland Sommarskog wrote:
Wes Groleau (gr**********@freeshell.org) writes:
>"Obtain the latest service pack" You're right--that's not hard.
I didn't bother to look because my predecessor said that the only
fix was to install Access 2003. Installing a new version--or installing
a service pack--is not something our I.S. department will do on demand.

In that case... Do a check with SELECT @@version in your SQL 2000 instance
to check that you have at least SP3. The version number should read
8.00.760 for SP3 and 8.00.2039 for SP4. If it's lower than 760, you should
insist on an upgrade.


--
Wes Groleau
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^
^ A UNIX signature isn't a return address, it's the ASCII equivalent ^
^ of a black velvet clown painting. It's a rectangle of carets ^
^ surrounding a quote from a literary giant of weeniedom like ^
^ Heinlein or Dr. Who. ^
^ -- Chris Maeda ^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^
Feb 6 '07 #14

P: n/a
Erland Sommarskog wrote:
Wes Groleau (gr**********@freeshell.org) writes:
>"Obtain the latest service pack" You're right--that's not hard.
I didn't bother to look because my predecessor said that the only
fix was to install Access 2003. Installing a new version--or installing
a service pack--is not something our I.S. department will do on demand.

In that case... Do a check with SELECT @@version in your SQL 2000 instance
to check that you have at least SP3. The version number should read
8.00.760 for SP3 and 8.00.2039 for SP4. If it's lower than 760, you should
insist on an upgrade.
THANKS, I will. My colleague is meeting tomorrow with I.S. to
discuss exactly what they are supposed to do and what we are
supposed to do. I gave him the MS KB article; I'll give him
this info as well.

--
Wes Groleau
"To know what you prefer, instead of humbly saying
Amen to what the world tells you you should prefer,
is to have kept your soul alive."
-- Robert Louis Stevenson
Feb 6 '07 #15

P: n/a
Ben wrote:
I also encourage you to use the Upsize Wizard of Access because that is why
it is there. It will definitely be quicker for you in the long run.
The upsize wizard if the access database or data have problems.
_It_doesn't_work_ It gives NO errors the first few "next" screens
and then the wonderfully informative "Overflow" popup. This behavior
is consistent with several unrelated Access databases--including ones
that are working perfectly and have small amounts of data.

This is a known problem with Access 2000 unless a particular
update is installed. But I did not know about that update until
I learned of it in this thread. I had been believing the numerous
people (including the author of a published SQL Server book) who
said the bug could only be fixed by getting Access 2003.

--
Wes Groleau
http://freepages.rootsweb.com/~wgroleau/Wes
Feb 6 '07 #16

This discussion thread is closed

Replies have been disabled for this discussion.