How to differentiate SQL 2000 and MSDE installs

Hello!
I am trying to determine how to differentiate a SQL 2000 and MSDE install
without running a query on the database.
In mmc's post and Andrea's answer on 1/18/05, a clear solution using a query
is noted.
I would like to be able to read a registy value to make the determination if
possible.
Thanks very much for the assistance.
Carl
Hi
Gave you installed SQL Server on one machine, MSDE on another one and looked
at the differences?
--
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/
"Carl Langren" <test@.test> wrote in message
news:eNlceBWBFHA.3588@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE install
> without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using a
query
> is noted.
> I would like to be able to read a registy value to make the determination
if
> possible.
> Thanks very much for the assistance.
> Carl
>
|||Hi Mike,
Yes, three servers and six workstations. Mixed MSDE, Developer and Full SQL.
I was really looking more for an answer rather than a question......
I found this post:
http://www.masterado.net/home/ng/for...t14419113.aspx
=================================================
11/16/2004 4:27:03 PM SQL Server 2000 registry values by edition?
Is it possible to determine through the registry which edition of SQL Server
2000 has been installed - Enterprise, Standard, Personal, Developer,
Evalulation, Windows CE, Desktop (MSDE)?
I know it's possible using 'SELECT SERVERPROPERTY('edition')' in
QueryAnalyzer but what about the registry?
Thanks!
==================================================
11/28/2004 4:20:00 AM RE: SQL Server 2000 registry values by edition?
No, but you can use registry key below to differentiate between MSDE and
other sql editions:
For a named instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Setup
For a default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
If one of these keys exist but there is no ProductCode, the instance is one
of the SQL Server 2000 Editions (such as Enterprise, Enterprise Evaluation,
Standard, Developer, or Personal).
If a ProductCode entry is present, the instance is MSDE.
You can also parse the sql errorlog to determine what edition, see KB: INF:
How to Determine the Download Files You Need for Upgrading to SQL Server
2000 Service Pack 3 - http://support.microsoft.com/?id=814618
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||hi Carl,
Carl Langren wrote:
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE
> install without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using
> a query is noted.
> I would like to be able to read a registy value to make the
> determination if possible.
> Thanks very much for the assistance.
> Carl
AFAIK, apart from ProductCode key in the Setup hive you already mentioned,
unfortunately there are no registry difference among SQL Server editions,
and MSDE is one of them, and only directly querying the engine determins a
real answer to this question...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to differentiate between an SQL 2000 installation and an MSDE

Hi
Could any one tell me how to diffentiate between a sql instance and an msde
instance
thanks
mmc
hi,
mmc wrote:
> Hi
> Could any one tell me how to diffentiate between a sql instance and
> an msde instance
> thanks
> mmc
SET NOCOUNT ON
SELECT SERVERPROPERTY ( 'Edition' )
http://msdn.microsoft.com/library/de...a-ses_3mi1.asp
it returns "Desktop Engine" for MSDE
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

how to determine sp level at app install time?

Hi,
I have an application that uses an instance of MSDE we create. What should
our installer check to find out if we need to install msde sp3, when our
users upgrade our software?
thanks,
David
Hi,
Installation of ur application require a valid login to sql server right,
then you could verify the service pack by executing the below query.
SELECT SERVERPROPERTY('PRODUCTLEVEL')
This returns SP3 if the service pack installed is SP3. Based on this u could
continue the installation.
Thanks
Hari
SQL Server Mvp
"David Burson" <DavidBurson@.discussions.microsoft.com> wrote in message
news:C52717FF-A27E-4686-89ED-01AA4FDAF179@.microsoft.com...
> Hi,
> I have an application that uses an instance of MSDE we create. What
> should
> our installer check to find out if we need to install msde sp3, when our
> users upgrade our software?
> thanks,
> David
|||Thanks Hari,
We have a problem with that solution though. I guess we need to use osql to
run the serverproperty query, but we don't know how to run it so that the
user doesn't see it run, and also we don't know how to get the response from
osql back into our installer code.
Do you have any suggestions on how to handle those issues?
Thanks very much!
David
"Hari Prasad" wrote:

> Hi,
> Installation of ur application require a valid login to sql server right,
> then you could verify the service pack by executing the below query.
> SELECT SERVERPROPERTY('PRODUCTLEVEL')
> This returns SP3 if the service pack installed is SP3. Based on this u could
> continue the installation.
> Thanks
> Hari
> SQL Server Mvp
> "David Burson" <DavidBurson@.discussions.microsoft.com> wrote in message
> news:C52717FF-A27E-4686-89ED-01AA4FDAF179@.microsoft.com...
>
>

How to determine MSDE version and Service Pack level

How can I determine what versioin and service pack of MSDE i have?You might try to look at @.@.VERSION for this information.

Assuming you don't have Query Analyzer, so use the command-line utility osql.

Shell out to a command prompt and type:

osql -E -q "PRINT @.@.VERSION"

Terri|||I tried this and the response was:

[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).

Is there a next step?

Thanks,
Bob|||Those are the messages I get when the SQL Server is not started.

You need to start SQL Server. You do this through the SQL Server Service Manager in your systray.

Also, this is a good link to keep handy:HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility

Terri|||Thank you Terri,

After a reboot and restart it worked and brought me to my next problem(s).

I think what I really need to do is uninstall MSDE and start over. I don't seem to able to get into my DB, the path where MSSQL ended up is too long to work with in a command shell, etc. Is there an easy way to get uninstall all of it? Without a ADD/REMOVE option or Uninstall I'm lost.

Thanks for the first anser and the link. I bookmarked it and will use it on the next go around.

Thanks,
Bob|||Thanks to tmorton for the answer that worked. It was really simple.|||Codfish -- glad to be of help.

Disker -- the path were MSSQL was installed is going to be long -- and it shouldn't matter. You should never need to type it in. The install should add it to your PATH so that you will be able to go to the command shell and type OSQL from anywhere and have it work.

Please don't uninstall/reinstall unless absolutely necessary.

You say that you "don't seem to be able to get into" your DB. What tool(s) are you trying to use for this? What error(s) are you receiving?

Terri|||Thanks for the help Terri,

Some hardware/software specs -

Running XP PRO Sp1, IIS on AMD 900, 512MB. IIS is running as is SQLSERVR, SQLMANGR, and sqlagent. These are listed in Task Manager [processes] as running and also in Services as Started. I have the little server icon on the task bar with a little blue or green arrow blinking every so often. I installed MSDE from a command line after entering SAPWD="mypassword" The password I chose is 9 alpha-numeric.

I did follow your suggestion yesterday and read through the OSQL commands. This did allow me to create another DB. This command returned a message saying that it had created the DB giving the name and the space allocated. I checked on this and did find the two files ending with .mdf and .ldf in C:\Program Files\Microsoft SQL Server\MSSQL\Data.

For SQL management I've been trying a few things. First, I tried SQL Server Web Data Administrator. When I use this the IIS option only gives me a page of HTML. The Cassini model gives me a login screen which asks for username, password, server and the two options of windows authentication or SQL. I have tried both and cannot gain access being given a incorrect password or username message.

My newest attempt and what I would like to use is Enterprise Manager and have downloaded SQLEntMan_0.5.1.zip to use. I unzipped this to a folder named EntMan but cannot get that to start up. I tried browsing to the folder with IE but it just shows me the files.

So it seems that I have installed MSDE semi-correctly in that I can find it and create databases through OSQL. I can see the DB but cannot access it through any interface I have tried so far.

Thanks so much for your time and help. I really appreciate it.|||Instead of this moving target -- let's try to concentrate on getting SQL Server Web Data Administrator working for you. (I prefer this over ASP Enterprise Manager strictly because I have installed and used it myself and can more easily help you troubleshoot it.)

With the hardware/software specs you listed, I am assuming that all of the elements of your environment are local to your workstation (IIS, SQL).

Is your local IIS able to serve up any ASP.NET pages at all? Get back to us on this and we can move forward from there.

Terri|||HI,

If I type http://localhost/or http://desktop/ it takes me to a web page which displays the localstart.asp page which states the following:

Your Web service is now running.
You do not currently have a default Web page established for your users. Any users attempting to connect to your Web site from another machine are currently receiving an Under Construction page. Your Web server lists the following files as possible default Web pages: default.htm,default.asp,index.htm,iisstart.asp. Currently, only iisstart.asp exists.

To add documents to your default Web site, save files in c:\inetpub\wwwroot\.

I assume this means IIS is capable of serving up pages.

I have the SQL Server Web Adm. Version 2.0 installed to c: as is everything else.

I won't do anything else until I hear back.

Thanks,

Bob|||Yes, IIS is serving up pages, but specifically can it serve up ASP.NET pages (.aspx extension)?

Create test.aspx and put it into your wwwroot directory and try to access it from your browser (http://localhost/test.aspx):

<%@. Page Language="VB" %>
<html>
 <head>
 <title>ASP.NET Test</title>
 </head
 <body>
 <center>
 <form action="test.aspx" method="post">
 <% Dim I As Integer
 For I = 0 to 7 %>
 <font size="<%=I%>">Welcome to ASP.NET</font><br>
 <% Next %>
 </form>
 </center>
 </body>
</html>

What you should see is "Welcome to ASP.NET" 7 times in increasingly bigger font sizes. If you don't, then IIS is not serving up your .ASPX pages correctly and you will need to repair te IIS mappings:

To repair IIS mappings for ASP.NET with Aspnet_regiis.exe

1. ClickStart, and then clickRun.
2. In theRun dialog box, typecmd, and then clickOK.
3. At the command prompt, type:
"systemroot\Microsoft.NET\Framework\version\aspnet_regiis.exe"-i

In this path,systemroot is the folder containing your operating system files (C:\Windows on Windows XP systems, C:\WinNT on Windows 2000 Server systems, and so on), andversion is the version number of the .NET Framework installed on your server.

Note You must include the quotation marks in the command.

For more information about Aspnet_regiis.exe, use step 3 with the -? option or see the ASP.NET IIS Registration Tool topic in the .NET Framework version 1.1 documentation.|||Ok I created the page but it didn't do what it was supposed to do so I ran the repair. This started and completed installing ASP.NET. The path I used was

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis.exe" -i

Then I checked to see if the ASP.NET State Service was running and it is. Then I rebooted just to be safe, double-checked ASP.NET and tried the page again. What I get is the text for the test page you've given above. I used Frontpage to create the page and did a copy and paste of your text and entered it into the html tab in Frontpage. I then saved the page to wwwroot as a .aspx page. What I get when I use IE to go to the page is the text that was pasted in and not the page as it should appear.

IE works for any other page I go to so I don't see that as a problem. I don't have a firewall running on this box so there is problem there either. I also checked the page from another PC on my mini network using http://desktop/test/aspx and get the same text page as I do locally.

Sorry for all the work. This gets really frustrating.

Bob|||At this point I am going to have to ask you to post this problem to theSetup forum. :-( Something isn't right with your setup. Each time I've seen this, aspnet_regiis.exe -i has solved it. The people over in that forum have seen it all and should be able to get you on the right track.

To be clear -- by "this problem" I mean the inability to view the test page.

Once you are able to view that test page correctly, then give Web Data Administrator another try. (And post back if you are having trouble with it.)

Terri
PS - Don't give up!|||OK, will do.

Thanks Terri. Hope to get back to you with a note saying everything works fine.

Bob|||Back again!

I now have IIS serving .asp pages to my local PC as well as another located on the network. Both seem to be working fine so I take this to mean that my IIS problem is resolved.

Back to the SQL Web Data Administrator - I've tried this again and it's still not working the way it should. When I activate the administrator it pops with the little window saying Web Server Configuration. It lists the following options:

Desired Port:
IIS
Cassini Personal Web Server
Restore Faults

and Start and Stop buttons.

If I leave the setting on IIS and click Start it opens a webpage entitled Web Data Administrator. Moving further down I have a welcome message and then the line -

Please enter a SQL Server name:

Username: [Desktop\bob shadowed entry]
Password
Server (local)

Authentication Method:

Windows Intergrated
SQl Login

and then Login

If I select SQL Login and enter "sa" as the username and then my password leaving the (local) as the server I get the following error:

Invalid username and/or password, you are using a windows login that is not your own, or server does not exist.

This is where I'm stuck at this point. I appreciate your help and patience.

Thanks,
Bob

p.s. Should we move this to another Subject heading or start a new thread?

How to determine MSDE or SQL 2005 Express vs. SQL 2000 or SQL 2005

Hi all,
I need to determine if MSDE or SQL 2005 Express is installed versus the
full version of SQL 2000 or SQL 2005. I only want my installation to
run if the full versions of SQL is installed. Where can I find this
information? Is it in the registry, can I do a SQL query that tells me
this info?
Basically I need a way of distinguishing between the free and full
versions. Any help would greatly be appreciated. Thanks in advance.
Hi,
Sorry, I should've been more clear. This is for an installation
package, so I want the install package to check the version of SQL on a
user's machine. It would need to query the database or check a
registry key, or file version, etc.
If I did select @.@.version would this say it's MSDE ot SQL 2005 Express?
I just found something that I'm not sure will work or not:
SELECT SERVERPROPERTY ('edition')
It looks like it will return either
Personal Edition
or
Standard Edition
depending on what you have (I assume Enterprise Edition as well).
Is this enough to determine whether or not it is MSDE (and SQL 2005
Express) or a full install?
Thanks.
Tom wrote:[vbcol=seagreen]
> If you use the SQL Server Mgt Studio, you right click on the registered
> server and one of the general properties is the 'Product' name, which states
> that it is SQL Server Express or SQL Server Developer etc.
>
> "Flash08" <stuart.karp@.gmail.com> wrote in message
> news:1168982219.854052.130750@.q2g2000cwa.googlegro ups.com...
|||SERVERPROPERTY is probably the safest thing to use. The possible return
values are listed here:
http://msdn2.microsoft.com/en-us/library/ms174396.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Flash08" <stuart.karp@.gmail.com> wrote in message
news:1168988743.254141.202070@.m58g2000cwm.googlegr oups.com...
> Hi,
> Sorry, I should've been more clear. This is for an installation
> package, so I want the install package to check the version of SQL on a
> user's machine. It would need to query the database or check a
> registry key, or file version, etc.
> If I did select @.@.version would this say it's MSDE ot SQL 2005 Express?
> I just found something that I'm not sure will work or not:
> SELECT SERVERPROPERTY ('edition')
> It looks like it will return either
> Personal Edition
> or
> Standard Edition
> depending on what you have (I assume Enterprise Edition as well).
> Is this enough to determine whether or not it is MSDE (and SQL 2005
> Express) or a full install?
> Thanks.
>
> Tom wrote:
>
sql

How to Determine if MSDE 2K running

Hi All
I need to check if MSDE2K service is running on a stand-alone computer using
VB6
I have tried using SQLDMO.ListAvailableServers but find it very
unreliable...
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = CreateObject("SQLDMO.Application")
Set oNames = oApp.ListAvailableSQLServers()
MsgBox oNames.count
If I first run this code it detects my MSDE service oNames.count = 1
(correct)
If I stop MSDE, this code returns oNames.count = 0 (correct)
If I restart MSDE (icon indicates running) oNames.count still returns 0
(incorrect)
Any ideas
Regards
Steve
I know it's not the best way, but how about just writing an ADO application
that executes a test query in one of the databases? If your app fails to
connect to MSDE, then you know you have a problem.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"steve" <sfrancis@.bigpond.net.au> wrote in message
news:etCwYZcjFHA.320@.TK2MSFTNGP09.phx.gbl...
Hi All
I need to check if MSDE2K service is running on a stand-alone computer using
VB6
I have tried using SQLDMO.ListAvailableServers but find it very
unreliable...
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = CreateObject("SQLDMO.Application")
Set oNames = oApp.ListAvailableSQLServers()
MsgBox oNames.count
If I first run this code it detects my MSDE service oNames.count = 1
(correct)
If I stop MSDE, this code returns oNames.count = 0 (correct)
If I restart MSDE (icon indicates running) oNames.count still returns 0
(incorrect)
Any ideas
Regards
Steve
|||hi Steve,
steve wrote:
> Hi All
> I need to check if MSDE2K service is running on a stand-alone
> computer using VB6
> I have tried using SQLDMO.ListAvailableServers but find it very
> unreliable...
> Dim oApp As SQLDMO.Application
> Dim oNames As SQLDMO.NameList
> Set oApp = CreateObject("SQLDMO.Application")
> Set oNames = oApp.ListAvailableSQLServers()
> MsgBox oNames.count
> If I first run this code it detects my MSDE service oNames.count = 1
> (correct)
> If I stop MSDE, this code returns oNames.count = 0 (correct)
> If I restart MSDE (icon indicates running) oNames.count still returns
> 0 (incorrect)
you could use the SQLDMOSQLServer Status property,
http://msdn.microsoft.com/library/de..._p_s_769l.asp,
but this requires you to be already connected ot the SQL Server instance..
and, as you already saw, the ListAvailableServers is not reliable, because
of the nature of the broadcast call of the ODBC SQLBrowseConnect api used by
the DMO method, where the timeframe window is involved as well...
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by MDAC;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
to the besto of my knowledge, as you can see from
http://msdn.microsoft.com/library/de...ob_s_7igk.asp,
SQLServer object does not directly exposes a disconnected property to get
it's state, so you have to connect (and eventually use the Status method,,,
but youll''be already connected)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

How to deploy msde database in web matrix

Dear Sir,
How do I deploy database to remote server in web matrix. It does not have enterprise manager.
I have to use osql.exe command line that runs msde statements that are exuceted against database. Can you
please let me know how to use osql.exe.
Regards,
Farhan

Web Matrix is free so is MSDE so you can buy the Developer edition for $37 to manage MSDE or test drive SQL Server 2005 from the first link below. I would do the former. Hope this helps.
http://www.microsoft.com/sql/downloads/trial-software.mspx

http://www.provantage.com/buy-22053391-microsoft-backoffice-sql-server-2000-developer-edition-shopping.htm