List specific version of SQL Server Instances in SQLServerSelectLogin dialog

[jignesh_parikh]

Hi,

I have a situation where I want to display only those SQL Server Instances which has version of SQL Server 2005 EE or newer in the SqlServerSelectLogin dialog's combo box list.

What are the alternatives to achieve this? Any help would be greatly appreciated.

Thanks,
Jignesh

[kirann_hegde]

Jignesh,

Do you want to display the list of SQL Servers on the network or just on the local box?
Definitely, there is a way to achieve what you are asking. However i want to understand your requirement first.

Regards,
Kiran Hegde

[jignesh_parikh]

Thanks for your reply Kiran,

Let me make it more descriptive for your better understanding.

I have a default IS dialog SqlServerSelectLogin which is displayed when it comes for the database credentials. It contains from top 1. sql server instance combo box list 2. windows/custom user details and 3. database name.

Here I want to display only those instance in 1. combobox list which are of version sql server 2005 and not sql server version less than 2005.

Thanks
Jigs

[kirann_hegde]

Hello Jignesh,

I have never used the inbuilt SQLServerSelectLogin dialog.
Our product is built around SQL Severs. So we have had the kind of requirements you have currently.For our requirements, we built our own dialog box and populated the combo boxes using the methods which i have listed below.

SQL Server 2005 has a version of 9.0.
SQL Server 2005 SP2 version is 9.0.3042.0
So you might want to check if the version of SQL Server is 9.0 or greater to decide the SQL instances you want to display.

There are two ways you can meet your requirements:
1)By querying the system registry. SQL Server will have the version, edition etc and other details stored in they system registry.
However i do not recommend this as Microsoft does not guarantee against changing the SQL Server registry structure in the future.
Trust me, we have run into issues with this approach.

2)You can connect to each and every SQL Server and figure out the version with the following queries:

You have the SQL function named SERVERPROPERTY. You have to query the information you want from the **SQL Server itself**. However this means that you have to connect to each and every SQL Server and i am not sure if this is feasible in your case.However please note that this is the most **reliable** way to query the information you are looking for.

Take a look at : http://msdn.microsoft.com/en-us/library/ms174396.aspx

Edition: select SERVERPROPERTY('Edition')
Version: select SERVERPROPERTY('ProductVersion')

You can use something like ADO to query this information from the server.
The inbuilt SQL functions from installscript might also be able to help you.



Having said all this, i also observed that in the SQL scripts view, you can specify the configuration to check for SQL Server version. Is that not helping?

Regards,
Kiran Hegde

[jignesh_parikh]

Hello Kiran,

Sorry for very late response. I was not monitoring this thread.

Thank you very much for your detail response. It is really very helpful for me and others as well. I appreciate.

I have not tried the way you suggested but will surely come back here when get time to work on this.

Regards,
Jigs