Simple way of telling if I'm using MSDE or full SQL Server?

[Robin Tucker]

I'm looking for a simple way of telling (inside a stored procedure) if I'm
currently using MSDE or a full SQL server. Ideally, there is some
pre-defined environment variable that won't cause me too much overhead. The
reason I'm doing this is because my system "rolls over" databases when it
reaches the 2Gb limit with MSDE, but obviously I want to avoid this overhead
if the user installs onto a full SQL server instance.

Thanks

Robin

[p_le_sueur_1]

Hi Robin,

Use the SQL statement:

select @@version

if it is MSDE it should have "...Desktop Engine..." in the returned
string, as opposed to eg "...Standard Edition..." on a "Standard"
install. (check to see what yours returns)

Hope this helps!

Paul.

[Danny]

Robin,

There is no 2GB database size limit in MSDE. That's just a rumor. It
simply has less features and a query governor.

Danny
<p_**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi Robin,

Use the SQL statement:

select @@version

if it is MSDE it should have "...Desktop Engine..." in the returned
string, as opposed to eg "...Standard Edition..." on a "Standard"
install. (check to see what yours returns)

Hope this helps!

Paul.

[Leythos]

In article <Go*****************@newssvr17.news.prodigy.com> ,
is****@flash.net says...

Robin,

There is no 2GB database size limit in MSDE. That's just a rumor. It
simply has less features and a query governor.

Try again, directly from MS:
http://msdn.microsoft.com/library/de...l=/library/en-
us/replsql/replimpl_89cy.asp

The following limitations are MSDE 2000 limitations that can impact
replication:

* MSDE 2000 limits the size of the database to 2 gigabytes (GB). If MSDE
2000 is part of the replication topology, the size of the replicated
database is limited to 2 GB. If MSDE 2000 is the Subscriber, replication
agents fail after the database exceeds 2 GB. At that point, consider
upgrading the Subscriber to SQL Server Standard Edition.

* MSDE 2000 includes a workload governor, which starts slowing down the
database engine when more than eight operations are actively running at
the same time. The workload governor can affect replication performance.
In an active replication environment where there are a large number of
Subscribers, Microsoft recommends that you use either SQL Server
Standard Edition or Enterprise Edition as the Publisher or Distributor
instead of MSDE 2000.

http://msdn.microsoft.com/vstudio/do...examining.aspx

Q. How much data can MSDE 1.0 store?

A. MSDE 1.0 databases are limited to 2 gigabytes (GB) of data. This
limit is per database, and not per server, so a single MSDE server can
support multiple MSDE databases, each containing up to the 2 GB limit.
If you anticipate that your database is currently or will grow beyond 2
GB, consider upgrading to Microsoft SQL Server=3F 2000 Standard Edition
for a more scalable database platform on which to build.

--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[Robin Tucker]

Thanks for the tip :-p

"Leythos" <vo**@nowhere.org> wrote in message
news:MP************************@news-server.columbus.rr.com...

In article <Go*****************@newssvr17.news.prodigy.com> ,
is****@flash.net says...

Robin,

There is no 2GB database size limit in MSDE. That's just a rumor. It
simply has less features and a query governor.

Try again, directly from MS:
http://msdn.microsoft.com/library/de...l=/library/en-
us/replsql/replimpl_89cy.asp

The following limitations are MSDE 2000 limitations that can impact
replication:

* MSDE 2000 limits the size of the database to 2 gigabytes (GB). If MSDE
2000 is part of the replication topology, the size of the replicated
database is limited to 2 GB. If MSDE 2000 is the Subscriber, replication
agents fail after the database exceeds 2 GB. At that point, consider
upgrading the Subscriber to SQL Server Standard Edition.

* MSDE 2000 includes a workload governor, which starts slowing down the
database engine when more than eight operations are actively running at
the same time. The workload governor can affect replication performance.
In an active replication environment where there are a large number of
Subscribers, Microsoft recommends that you use either SQL Server
Standard Edition or Enterprise Edition as the Publisher or Distributor
instead of MSDE 2000.

http://msdn.microsoft.com/vstudio/do...examining.aspx

Q. How much data can MSDE 1.0 store?

A. MSDE 1.0 databases are limited to 2 gigabytes (GB) of data. This
limit is per database, and not per server, so a single MSDE server can
support multiple MSDE databases, each containing up to the 2 GB limit.
If you anticipate that your database is currently or will grow beyond 2
GB, consider upgrading to Microsoft SQL Server=3F 2000 Standard Edition
for a more scalable database platform on which to build.

--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

[Erland Sommarskog]

Robin Tucker (id*************************@reallyidont.com) writes:

I'm looking for a simple way of telling (inside a stored procedure) if
I'm currently using MSDE or a full SQL server. Ideally, there is some
pre-defined environment variable that won't cause me too much overhead.
The reason I'm doing this is because my system "rolls over" databases
when it reaches the 2Gb limit with MSDE, but obviously I want to avoid
this overhead if the user installs onto a full SQL server instance.

This should cut it:

select serverproperty('Edition')
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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