March 21, 2012 at 12:26 pm
I am writing a small Setup program to go out with a small Application I've written and I want to ensure that SQL Server is not installed onto a machine on which it already exists. In this particular instance this isn't very likely, but in future Applications it is, so I'd like to get the logic and the code in place and tested now.
To this end, I have created a basic Windows Form that lists the instances of SQL Server that are on the machine where the Setup is being run. However, my Listbox is not being populated and I don't know why. If I open a Command window and type "sqlcmd /L" I get a response of "Servers:" and then nothing, so it seems that with neither of these methods showing a SQL Server instance, there is not one installed or running ...
... HOWEVER, I know that SQL Server 2008 R2 Express is installed on this machine and if I run Microsoft SQL Server Management Studio it connects me to "MEDESKTOP\SQLEXPRESS (SQL Server 10.50.2500 - MeDesktop\Gary)". In fact, when I start MSSMS it opens up the "Connect To Server" window with "MEDESKTOP\SQLEXPRESS" in the "Server Name" field. If I click on the down arrow & select "<Browse for more...> I get a little window that lists Network Servers & Local Servers, so I'm sure that what I want to do is possible, I just don't know how to do it !!!
The code I have used is as follows & it executes just fine, it just doesn't find "MEDESKTOP\SQLEXPRESS", which is a bit of a FAIL really !!!
Using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Sql;
using System.Drawing;
using System.Linq;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Windows.Forms;
namespace KAELCsetup
{
public partial class KAELCForm1 : Form
{
public KAELCForm1()
{
InitializeComponent();
DataTable dt = SqlDataSourceEnumerator.Instance.GetDataSources();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
KAELClistBox1.Items.Add(dr["Name"]);
}
}
}
private void KAELCForm1_Load(object sender, EventArgs e)
{
}
}
}
What am I doing wrong ?!?
March 21, 2012 at 12:57 pm
I'm pretty sure SSMS reads from the Windows Registry when it gets the local instance names. Would that be easier?
It's under HKEY_LOCAL_MACHINE.SOFTWARE.Microsoft.Microsoft SQL Server.Instance Name.SQL.
I seem to remember that .NET functions can query the registry pretty easily. Is that correct? (Been a while since I wrote anything other than T-SQL, so I'm rusty on skills that barely existed in the first place.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2012 at 1:26 pm
Hi GSquared, thanks for your reply ... I guess that wherever I get the information from is fine, especially at the moment when I am still at the bottom of a very steep learning curve, the only thing that worries me (long term, i.e. not now !!) about using the Registry is that I read on the MS Forums that they don't recommend using the Registry as "it's theirs and they might change it" ...
Will that Registry key be the same under Windows Vista (this machine which I use for all my development & testing), WinXP (my laptop that I use to test deployment) & Windows 7 (the likely OS of the person who is going to actually use the Application) ?!?!?
GSquared (3/21/2012)
I'm pretty sure SSMS reads from the Windows Registry when it gets the local instance names. Would that be easier?It's under HKEY_LOCAL_MACHINE.SOFTWARE.Microsoft.Microsoft SQL Server.Instance Name.SQL.
I seem to remember that .NET functions can query the registry pretty easily. Is that correct? (Been a while since I wrote anything other than T-SQL, so I'm rusty on skills that barely existed in the first place.)
March 23, 2012 at 7:25 am
So far as I know, that's the same in the registries of all of those. I could be wrong, but it will be easy for you to check.
I'm on Windows 7, and that path I listed is from that. Check your XP and Vista machines and see if it matches.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2012 at 8:47 am
Gary,
.Net is expected to work integrated with SQL Server.
You can try this link:
http://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx
I just tested the example code and it worked, listing all servers/instances in my network.
Note a instance name is not required to a single instance server.
You can read the register to find if its installed local but the register ill not know if its running.
March 23, 2012 at 1:00 pm
Thanks everybody, this is a long slow process, but I am learning as I go along, I'm also finding that a lot of the time it's that I don't know the right question to ask !!!
I am creating a Deployment program here (I am usingg VS2010 C# Express, so the option to automatically create a Deployment Project isn't available to me, but I am happy doing this, I am learning a lot) and the reason I was looking for instances of SQL Server was so that I can (a) automatically install SQL Server Express if there are no instances installed, (b) grab the name of the installed instance if there is just 1 installed or (c) list the instances in a dropdown box and let the user choose which instance he wants to use. I seem to have got that working now, with the help of everybody here, thanks again.
However, the thing that got me started on this, was that when I create the Published Project in VS2010 C# Express, it doesn't include the database, which is obviously a pretty fundamental part of a SQL Server project !!! This Setup / Deployment program has got to the stage now, where I want to write the code to copy my database to the installing PC & this is proving a problem.
My SQL Server installation is on my O: Drive, so I know that the installation can be made anywhere, but I can't find these details anywhere in the Registry. My database files, KAELC_DB.mdf & KAELC_DB.log are in the folder O:\microsoft sql server\mssql10_50.sqlexpress\mssql\data so I assume that I need to find the same Folder on the target machine to copy them into. The instance of SQL Server on my machine is named SQLEXPRESS & the value is MSSQL10_50.SQLEXPRESS, so I know that I can put together the subFolder names to do the copy, but where do I find the location of the top-level Folder ?!?
Thanks in advance for your help, there is just so much to learn !!!
March 23, 2012 at 1:03 pm
jcb (3/23/2012)
Gary,.Net is expected to work integrated with SQL Server.
You can try this link:
http://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx
I just tested the example code and it worked, listing all servers/instances in my network.
Note a instance name is not required to a single instance server.
You can read the register to find if its installed local but the register ill not know if its running.
You don't need to know if it's running or not in order to determine if it should be installed. That was the original question.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2012 at 1:38 pm
You are right GSquare the OP dont asked for it but he ill need it if he want to create a auto-install app.
He can also choose to reinstall it if instance is found but is not running.:-D
Anyway, these paths can change dont assume it ill be the same at every server.
There are some path variables you can get from windows.
I dont know how to do it right now but I guess is not diferent to find out the system folders from windows.
A second though. Maybe you can query for the master db or tempdb files location and use it.
One little sugestion to test your deploy app.
Create some VMs, if possible with no US-en windows and a mix of windows versions.
Its great for test for different enviroments.
March 23, 2012 at 5:39 pm
That is one thing, jcb, that I did find ... but I didn't want to bring it to table straight away, in case it was a "quick 'n' easy" solution that wasn't necessarily the correct one ... anyway ...
Under the Key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\Parameters are 3 entries :
SQLArg0 ... value : -do:\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf
SQLArg1 ... value : -eo:\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG
SQLArg2 ... value : -lo:\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
It looks like I could use these ... if anybody thinks / knows differently, please let me know !!!
This particular project is very small, I am trying to help somebody out from doing a LOT of manual work, but at the same time do it in a structured & logical fashion so that I can learn going forward and when I write "THE" program for him that I need to do, I will know a lot more about C#, SQL Server & VS2010 ... I am wondering, quite a lot, if I've bitten off more than I can chew, but I must doff my hat at you lot on here, lots of help & ... as yet ... no condescending put-downs !!!
Tomorrow (or possibly Monday, we'll see !!!) I'll give that a go & see how it turns out ... thanks again, to everybody ...
jcb (3/23/2012)
You are right GSquare the OP dont asked for it but he ill need it if he want to create a auto-install app.He can also choose to reinstall it if instance is found but is not running.:-D
Anyway, these paths can change dont assume it ill be the same at every server.
There are some path variables you can get from windows.
I dont know how to do it right now but I guess is not diferent to find out the system folders from windows.A second though. Maybe you can query for the master db or tempdb files location and use it.
One little sugestion to test your deploy app.
Create some VMs, if possible with no US-en windows and a mix of windows versions.
Its great for test for different enviroments.
March 28, 2012 at 3:50 am
Apologies for not getting back sooner on this topic, but I am having other problems with C#.
Those registry keys appear to work fine, so that problem seems to be resolved ... if anybody can shed light on this problem http://forums.asp.net/t/1785384.aspx/1?Getting+the+correct+Path+to+the+USB+on+the+deployment+machine then I'll be able to get back to my actual program !!!
Thanks to all for your help anyway ...
March 29, 2012 at 9:28 am
Watch out for installed instances you aren't allowed to use; sometimes they're named instances (ACT! is a common example), sometimes they're default instances, but there may be technical and/or contractual impediments to using them for anything else.
March 29, 2012 at 11:57 am
To the OP: You can't use the code you originally supplied, I think for several reasons:
* it will go out to the network versus looking at the local machine
* I think that it could skip some instances that are not discoverable. IOW, if SQL Browser is not on.
* I'm not 100% sure it discovers later version SQL Server instances ? You would need to check this but the docs I just looked at were unclear.
BTW when I run your code I don't get a data table with the same column names as yours: dr["Name"] would not work for me but dr["ServerName"] would. So, for future reference, even if you could use that code I think you would would need to make sure and redist your copy of System.Data.Sql for your local work to ensure that setup would work.
In my version of the class, and possibly in yours, you would be able to get the instance names (such as SQLExpress), which is a separate column in my dt. And there is also a separate column which would give you the version information that you should be thinking about-- no matter what method you use for setup. Please see below.
Nadrek (3/29/2012)
Watch out for installed instances you aren't allowed to use; sometimes they're named instances (ACT! is a common example), sometimes they're default instances, but there may be technical and/or contractual impediments to using them for anything else.
Bingo. Proper setup behavior would be something liket his:
* -- look on the machine, using its registry. If you can't read the registry, let them know your installation needs to run with sufficient rights (you may already have handled this previous to getting to this point, I just want to make sure you know this is a possibility).
* -- don't find any instances of a version/edition you support? let the user know you must install SQL Server, and what version and edition. (Presumably Express in this case). Give them a chance to confirm, or to back out and install an instance manually, using a version/edition that they have licensed and would prefer, and that you support, first, instead. Then they can come back and install your main app.
* -- do find instances with appropriate version/edition? List them, with this relevant information. Ask the user to identify the instance that matches your requirements and that they would like you to use. Remind them (as the previous poster says) that there may be technical or contractual reasons *not* to re-use an instance. Give the user an option to choose to create a separate instance if desired, or to back out entirely until they do an explicit SQL Server install.
For the second case (instances are found) it would be nice if you could contact each instance to supply more information about what's in the environment. This way you could discard any existing instances that don't meet *your* requirements up front, rather than asking the user to identity an appropriate instance considering both your app's needs and his/her existing requirements for these instances. (Make sure your prompt says something like: "No SQL Server instance meeting our requirements is installed on this machine. You can create one now, or cancel." This will prevent any confusion on their part, if they know they do have SQL installed on the box.)
To get the version/edition info:
Since SELECT @@Version or whatever might require you to have credentials to contact the instance, but I expect the user is running under local admin privileges already, if you are successfully reading the registry, and doing other installation stuff, right?
So to check version and edition, you can either try to contact each instance, error handling if the local admin doesn't have rights. Or if you prefer asking the user explicitly for credentials to be used in contacting each instance upfront.
Alternatively, you can check the registry for this information instead. To get the version/edition information from the registry, for each instance name that you found in the registry, you can step "up" into the tree for a node of that name, and then "down" into MSSQLServer.CurrentVersion for the information you need. I would recommend this approach -- because you're writing registry-reading code already and know you have those rights.
* after the above, have you and the user identified an instance you can use? Now you have to ask for identity/credentials, etc, for your database.
* alternatively, after the above, has the user indicated that s/he wants you to install an instance? You still need to ask about identity/credentials, Also give them a chance to confirm/change the instance name you are about to create and any path/folder information involved.
I really, really wish that products that have a dependency on SQL Server, and the capability to install SQL Server -- including MS owned products -- did all these things. And this is off the top of my head. I've probably left out a couple of items.
>L<
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply