Database Move

I'm needing to move a database from SQL 7 to SQL 2000. What is the
best way to accomplish this and keep all tables, data, permissions, etc
in place throughout the move.

Should I use BCP, Replication or something else?

Thanks for any help.Hi

http://msdn.microsoft.com/library/d...pgrade_6nqr.asp

http://msdn.microsoft.com/library/d...pgrade_5jw3.asp
(and it's sub-pages)

http://support.microsoft.com/defaul...kb;en-us;261334

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"cfmx" <kent@.klhs.net> wrote in message
news:1111854538.017241.304760@.l41g2000cwc.googlegr oups.com...
> I'm needing to move a database from SQL 7 to SQL 2000. What is the
> best way to accomplish this and keep all tables, data, permissions, etc
> in place throughout the move.
> Should I use BCP, Replication or something else?
> Thanks for any help.|||cfmx (kent@.klhs.net) writes:
> I'm needing to move a database from SQL 7 to SQL 2000. What is the
> best way to accomplish this and keep all tables, data, permissions, etc
> in place throughout the move.
> Should I use BCP, Replication or something else?

It depends a little. If the server collation is the same on the two
servers, you can use backup/restore or sp_detach_db/sp_attach_db. The
database will automatically be upgraded to SQL 2000 format (and can
thus not be moved back.) To see if the collations are the same run
sp_helpsort.

If the collations are different, you can still use backup/restore or
detach/attach, since, in difference to SQL7 on SQL 2000 you can mix
collations on the same server. Howver, code that uses temp table and
joins them with permanent tables, will not work if the database collation
is different from the server collation.

For more details see these KB articles:
http://support.microsoft.com/?kbid=224071
http://support.microsoft.com/?kbid=325335

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Database Mail is not working for me on 64 bit SQL 2005

hmm..what login is the mail service running under? Are you sure that user
has permissions to read the msdb system database?
"John D." <JohnD@.discussions.microsoft.com> wrote in message
news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>I am trying to get Database mail working on our win 2003 EE 64 bit SQL 2005
> SP 1 EE 64bit server. I have done a ton of searching around other posts &
> have checked everything that people have said would work. Nothing has
> worked
> so far with me. The mail service is enabled, broker service is enabled &
> relay permission is allowed for the service. Here is the error message I
> am
> getting: Could not retrieve items from the queue. If I look at the
> queue,
> it does have several test messages in there waiting to be sent. Any ideas
> on
> what else I could try to get this to work?
> Thanks!
> John
>I am trying to get Database mail working on our win 2003 EE 64 bit SQL 2005
SP 1 EE 64bit server. I have done a ton of searching around other posts &
have checked everything that people have said would work. Nothing has worke
d
so far with me. The mail service is enabled, broker service is enabled &
relay permission is allowed for the service. Here is the error message I am
getting: Could not retrieve items from the queue. If I look at the queue,
it does have several test messages in there waiting to be sent. Any ideas o
n
what else I could try to get this to work?
Thanks!
John|||hmm..what login is the mail service running under? Are you sure that user
has permissions to read the msdb system database?
"John D." <JohnD@.discussions.microsoft.com> wrote in message
news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>I am trying to get Database mail working on our win 2003 EE 64 bit SQL 2005
> SP 1 EE 64bit server. I have done a ton of searching around other posts &
> have checked everything that people have said would work. Nothing has
> worked
> so far with me. The mail service is enabled, broker service is enabled &
> relay permission is allowed for the service. Here is the error message I
> am
> getting: Could not retrieve items from the queue. If I look at the
> queue,
> it does have several test messages in there waiting to be sent. Any ideas
> on
> what else I could try to get this to work?
> Thanks!
> John
>|||Pardon my ignorance but how where do you set what login the mail service is
running under? Does it use the login that SQL Agent is running under? I
enabled it in the surface area configuration tool.
"Tim Greenwood" wrote:

> hmm..what login is the mail service running under? Are you sure that user
> has permissions to read the msdb system database?
>
> "John D." <JohnD@.discussions.microsoft.com> wrote in message
> news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>
>|||Pardon my ignorance but how where do you set what login the mail service is
running under? Does it use the login that SQL Agent is running under? I
enabled it in the surface area configuration tool.
"Tim Greenwood" wrote:

> hmm..what login is the mail service running under? Are you sure that user
> has permissions to read the msdb system database?
>
> "John D." <JohnD@.discussions.microsoft.com> wrote in message
> news:28596E29-6110-4A44-806F-6E82C2A86983@.microsoft.com...
>
>