Can SQL Server 7.0 handle a SQL Server 2000 mdf?

[Mike Watson]

I've got MSDE, SQL Server 7.0, installed. I want to handle an .mdf
developed by someone else so I loaded the .mdf into my /DATA folder.
But, MSDE/ODBC can't see the database. I subsequently discovered that
it was created using SQL Server 2000. Is this the reason that my MSDE
7.0 doesn't see the .mdf? If so, how can I use this database with my
7.0? Backup from 2000, restore to 7.0?

Thanks for any help.

[Erland Sommarskog]

Re: Can SQL Server 7.0 handle a SQL Server 2000 mdf?

Mike Watson (mike@connectin gsoftware.it) writes:[color=blue]
> I've got MSDE, SQL Server 7.0, installed. I want to handle an .mdf
> developed by someone else so I loaded the .mdf into my /DATA folder.
> But, MSDE/ODBC can't see the database. I subsequently discovered that
> it was created using SQL Server 2000. Is this the reason that my MSDE
> 7.0 doesn't see the .mdf? If so, how can I use this database with my
> 7.0? Backup from 2000, restore to 7.0?[/color]

Well, there's actually two problems here.

The fact that MSDE/ODBC can't see the database has nothing to do with
the versions of SQL Server. Putting a file into your data directory has
no effect. You would have to do sp_attach_db to make the database available.
And for sp_attach_db the .mdf is not sufficient; you would need the log
file too. There is sp_attach_singl e_file_db for this situation, but the
database must have been shut down cleanly for this. (Well, this applies
to sp_attach_db as well as I recall.)

But for all this to work, the target server must be the same version or
later than the source server, so you can never do anything with that file
without access to SQL 2000. To restore that database on SQL 7, you would
have to script it and bulk out all data. And if the database makes use
of features not in SQL 7, not even this method would work.

Since MSDE for SQL 2000 is freely available, it appears to be a good idea
to download and install it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mike Watson]

Re: Can SQL Server 7.0 handle a SQL Server 2000 mdf?

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns961984 4A49F0FYazorman @127.0.0.1>...[color=blue]
> Mike Watson (mike@connectin gsoftware.it) writes:[color=green]
> > I've got MSDE, SQL Server 7.0, installed. I want to handle an .mdf
> > developed by someone else so I loaded the .mdf into my /DATA folder.
> > But, MSDE/ODBC can't see the database. I subsequently discovered that
> > it was created using SQL Server 2000. Is this the reason that my MSDE
> > 7.0 doesn't see the .mdf? If so, how can I use this database with my
> > 7.0? Backup from 2000, restore to 7.0?[/color]
>
> Well, there's actually two problems here.
>
> The fact that MSDE/ODBC can't see the database has nothing to do with
> the versions of SQL Server. Putting a file into your data directory has
> no effect. You would have to do sp_attach_db to make the database available.
> And for sp_attach_db the .mdf is not sufficient; you would need the log
> file too. There is sp_attach_singl e_file_db for this situation, but the
> database must have been shut down cleanly for this. (Well, this applies
> to sp_attach_db as well as I recall.)
>
> But for all this to work, the target server must be the same version or
> later than the source server, so you can never do anything with that file
> without access to SQL 2000. To restore that database on SQL 7, you would
> have to script it and bulk out all data. And if the database makes use
> of features not in SQL 7, not even this method would work.
>
> Since MSDE for SQL 2000 is freely available, it appears to be a good idea
> to download and install it.[/color]

Thank you Erland, for your answer. We've got the log file. So,
following your advice I will look for and install MSDE 2000. After
that I presume I have to run the 'sp_attach_db' procedure to make the
db available to MSDE 2000 so that I can create an ODBC to see it from
Access 2000. Could you please confirm if that is correct?

Mike Watson

[Erland Sommarskog]

Re: Can SQL Server 7.0 handle a SQL Server 2000 mdf?

Mike Watson (mike@connectin gsoftware.it) writes:[color=blue]
> Thank you Erland, for your answer. We've got the log file. So,
> following your advice I will look for and install MSDE 2000. After
> that I presume I have to run the 'sp_attach_db' procedure to make the
> db available to MSDE 2000 so that I can create an ODBC to see it from
> Access 2000. Could you please confirm if that is correct?[/color]

That should work just fine.

Once you have MSDE 2000 up, do a SELECT @@version, and make sure that
you have at least 8.00.760, so you have SP3 and the fix for the Slammer
worm.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mike Watson]

Re: Can SQL Server 7.0 handle a SQL Server 2000 mdf?

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns961AE5 7BC3805Yazorman @127.0.0.1>...[color=blue]
> Mike Watson (mike@connectin gsoftware.it) writes:[color=green]
> > Thank you Erland, for your answer. We've got the log file. So,
> > following your advice I will look for and install MSDE 2000. After
> > that I presume I have to run the 'sp_attach_db' procedure to make the
> > db available to MSDE 2000 so that I can create an ODBC to see it from
> > Access 2000. Could you please confirm if that is correct?[/color]
>
> That should work just fine.
>
> Once you have MSDE 2000 up, do a SELECT @@version, and make sure that
> you have at least 8.00.760, so you have SP3 and the fix for the Slammer
> worm.[/color]

Erland,

I've downloaded MSDE 2000 (Italian version), expanded the zip, read
the readme.txt, checked the things they tell me to check. I started
the setup with:

setup SAPWD="password " SECURITYMODE=SQ L

The install fails with roughly 3 - 5 seconds remaining with following
message:

"Impossible to configure the Server. For further information examine
the error logs..."

The last 2 messages in the log are:

Warning: override, autoexec procedures skipped.

and

Closing down SQL Server because of a interruption request from the
Service control handler (my translation)

I can't see what's going wrong. Can you help me?

Mike Watson

[Erland Sommarskog]

Re: Can SQL Server 7.0 handle a SQL Server 2000 mdf?

Mike Watson (mike@connectin gsoftware.it) writes:[color=blue]
> I've downloaded MSDE 2000 (Italian version), expanded the zip, read
> the readme.txt, checked the things they tell me to check. I started
> the setup with:
>
> setup SAPWD="password " SECURITYMODE=SQ L
>
> The install fails with roughly 3 - 5 seconds remaining with following
> message:
>
> "Impossible to configure the Server. For further information examine
> the error logs..."
>
> The last 2 messages in the log are:
>
> Warning: override, autoexec procedures skipped.
>
> and
>
> Closing down SQL Server because of a interruption request from the
> Service control handler (my translation)
>
> I can't see what's going wrong. Can you help me?[/color]

Unfortunately, I have no experience of running the MSDE setup, so
there is not much I can assist with.

Do you run the setup on the machine where you already have MSDE 7, or
do you run it on some other machine?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mike Watson]

Re: Can SQL Server 7.0 handle a SQL Server 2000 mdf?

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns961BF3 EFC447BYazorman @127.0.0.1>...[color=blue]
> Mike Watson (mike@connectin gsoftware.it) writes:[color=green]
> > I've downloaded MSDE 2000 (Italian version), expanded the zip, read
> > the readme.txt, checked the things they tell me to check. I started
> > the setup with:
> >
> > setup SAPWD="password " SECURITYMODE=SQ L
> >
> > The install fails with roughly 3 - 5 seconds remaining with following
> > message:
> >
> > "Impossible to configure the Server. For further information examine
> > the error logs..."
> >
> > The last 2 messages in the log are:
> >
> > Warning: override, autoexec procedures skipped.
> >
> > and
> >
> > Closing down SQL Server because of a interruption request from the
> > Service control handler (my translation)
> >
> > I can't see what's going wrong. Can you help me?[/color]
>
> Unfortunately, I have no experience of running the MSDE setup, so
> there is not much I can assist with.
>
> Do you run the setup on the machine where you already have MSDE 7, or
> do you run it on some other machine?[/color]

Erland,

I'm doing the install on another machine where there is no previous
copy of SQL or MSDE. It's Windows XP.

Mike Watson

[Erland Sommarskog]

Re: Can SQL Server 7.0 handle a SQL Server 2000 mdf?

Mike Watson (mike@connectin gsoftware.it) writes:[color=blue]
> I'm doing the install on another machine where there is no previous
> copy of SQL or MSDE. It's Windows XP.[/color]

Darn! Then I can't even suggest that as a remedy. I believe that there
is a newsgroup microsoft.publi c.sqlserver.msd e.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp