SQL Server 2000 Database with DTS Components

5 Jan 2007 8:38 PM

fakethingsster

I'm planning on moving a database from one SQL Server 2000 server to
another [also SQL Server 2000]. The application that relies on this
database has a Microsoft Access front-end and relies on some Data
Transformation Services components on the database server. I don't
think that this move should be too hard to accomplish but I fear that
I'm overlooking something. If anyone could review my process and
provide some insight, I'd greatly appreciate it.

My plan is as follows:

1. In Enterprise Manager, open each DTS Package on the old server
(there are only six of them) and perform a Save As to the new server.
2. In Enterprise Manager, use the DTS Import/Export wizard to export
the database from the old server to the new server.
3. Update the File DSN within Administrative Tools > Data Sources
(ODBC) to reflect the new SQL Server.

This application is used infrequently and a small amount of downtime is
not an issue.

Thanks!

5 Jan 2007 11:22 PM

AlterEgo

fakethisnsster,

You might have to change the connection properties within the DTS packages
themselves as well.

-- Bill

<fakethingss***@gmail.com> wrote in message
Show quote

news:1168029489.747898.16860@s34g2000cwa.googlegroups.com...
> I'm planning on moving a database from one SQL Server 2000 server to
> another [also SQL Server 2000]. The application that relies on this
> database has a Microsoft Access front-end and relies on some Data
> Transformation Services components on the database server. I don't
> think that this move should be too hard to accomplish but I fear that
> I'm overlooking something. If anyone could review my process and
> provide some insight, I'd greatly appreciate it.
>
>
> My plan is as follows:
>
> 1. In Enterprise Manager, open each DTS Package on the old server
> (there are only six of them) and perform a Save As to the new server.
> 2. In Enterprise Manager, use the DTS Import/Export wizard to export
> the database from the old server to the new server.
> 3. Update the File DSN within Administrative Tools > Data Sources
> (ODBC) to reflect the new SQL Server.
>
> This application is used infrequently and a small amount of downtime is
> not an issue.
>
>
>
> Thanks!
>

7 Jan 2007 2:35 AM

Dave Patrick

Access only uses the DSN to initially create the linked tables so you'll
need to relink the front-end app to the new server prior to copying to your
user's desktop.

http://www.mvps.org/access/tables/tbl0009.htm

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

<fakethingss***@gmail.com> wrote:

Show quote

> I'm planning on moving a database from one SQL Server 2000 server to
> another [also SQL Server 2000]. The application that relies on this
> database has a Microsoft Access front-end and relies on some Data
> Transformation Services components on the database server. I don't
> think that this move should be too hard to accomplish but I fear that
> I'm overlooking something. If anyone could review my process and
> provide some insight, I'd greatly appreciate it.
>
>
> My plan is as follows:
>
> 1. In Enterprise Manager, open each DTS Package on the old server
> (there are only six of them) and perform a Save As to the new server.
> 2. In Enterprise Manager, use the DTS Import/Export wizard to export
> the database from the old server to the new server.
> 3. Update the File DSN within Administrative Tools > Data Sources
> (ODBC) to reflect the new SQL Server.
>
> This application is used infrequently and a small amount of downtime is
> not an issue.
>
>
>
> Thanks!
>