Using DTS to migrate Access ot SQL Server

[Wes Groleau]

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

[Jonathan Roberts]

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

[Erland Sommarskog]

Jonathan Roberts (gr*******@diyn ics.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****@sommarsk og.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

[Wes Groleau]

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

[Wes Groleau]

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.

[Wes Groleau]

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

[Jonathan Roberts]

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

[Jonathan Roberts]

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

[Steve]

On Feb 3, 12:24 pm, Wes Groleau <groleau+n...@f reeshell.orgwro te:

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

[Wes Groleau]

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