I had some spare time today while copying some files between servers today at work. This week I needed an utility to determine if I could connect to a SQL server and retrieve some basic information about the connection, such as server version, installed edition and collation. Unfortunaly, I haven’t found out how to write proper code here at wordpress, so I had to include the source code snippets as pictures.
I wrote a an utility with the following output from my local developer machine:
Connecting using 'Data Source=(local);Initial Catalog=[mydb];User Id=[sqluser];Password=[sqlpsw];'
Successfully connected as '[sqluser]'
Client name: [machine name]
Server version: SQL Server 2008
Server Edition: Developer Edition (64-bit) (Enterprise)
Server collation: Danish_Norwegian_CI_AS
The propterty SqlConnection.ServerVersion in .NET Framework provides ut with the server version as a sumber on the format . By creating a method for parsing the number, I could determine the SQL server version.
I needed some extented version, so I did some googeling and found the trans-act function SERVERPROPERTY. This function had exactly what I was looking. The properties Edition, EngineEdition and Collation provides the rest of the information I needed.
The code above will find the currently logged in user for the active connection oon the format ‘[sql user]’ or ‘[domain]\[username]’. The first when the connection is established with SQL server authentification. The second with windows authentification.
The above code will retrieve the property ‘prop’ from the currently sql connection. The method is used to retrieve the Edition, EngineEdition . The output “Developer Edition (64-bit) (Enterprise)” is generated by the method below.
The SQL server version is returned by the method below. It uses the property SqlConnection.ServerVersion and parse the major part of the return value to check if the connection is a SQL 2000, SQL 2005 or SQL 2008.
See other connection string here for SQL Server.