Determine SQL Version

[Bluejay07]

Hello,

I have a program (which works well) that I am modifying. The program sets up a connection to an SQL database using an ODBC connection.

The modifications are to determine which version of SQL is being used. The result will affect how certain items are accessed and displayed. The problem is how to I connect to SQL when I don't yet have the information needed to connect to SQL.

Here is essentially what I am looking for.

Public Function SQLVersion() As Long
' Verify the SQL version being used.

   Dim l_strSQl As String
   Dim rs As ADODB.Recordset
   Dim CONN As ADODB.Connection
   
   On Error GoTo ERR_Handler
   
   Set CONN = New ADODB.Connection
   Set rs = New ADODB.Recordset
   
   l_strSQl = "SELECT SERVERPROPERTY('productversion') As Version;"
   rs.Open l_strSQl, CONN, adOpenForwardOnly, adLockOptimistic, adCmdText

   If Not (rs.BOF And rs.EOF) Then
      rs.MoveFirst
      SQLVersion = rs.Fields("Version")
   End If
   rs.Close
   Set rs = Nothing
   Exit Function
   
ERR_Handler:
   Msgbox(err.description)
End Function

I hope this is clear. If not, I can try to explain further.

Thanks.

 

[gmmastros]

I'm confused.

The problem is how to I connect to SQL when I don't yet have the information needed to connect to SQL.

Do you know where the database is (server)?
Do you know the name of the database?
Do you have a username & password (or using windows authentication)?

Also.... Is there a specific reason why you are using ODBC? In my apps, I use OLEDB to connect to the database. OLEDB is (in my experience) faster that ODBC. Unless you are relying on certain aspects of ODBC, I would encourage you to start using OLEDB instead. I tested the following code to connect to a SQL2000 database and a SQL2005 database (I don't have a SQL2008 database to test this on).

Public Function SQLVersion() As Long
' Verify the SQL version being used.

   Dim l_strSQl As String
   Dim rs As ADODB.Recordset
   Dim CONN As ADODB.Connection
   
   On Error GoTo ERR_Handler
   
   Set CONN = New ADODB.Connection
   Set rs = New ADODB.Recordset
   
   [green]' Following 2 lines added[/green]
   CONN.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=[!]LoginName[/!];Initial Catalog=[!]DatabaseName[/!];Data Source=[!]ServerName[/!]\[!]InstanceName[/!];Password=[!]Password[/!];"
   CONN.Open
   
   l_strSQl = "SELECT SERVERPROPERTY('productversion') As Version;"
   rs.Open l_strSQl, CONN, adOpenForwardOnly, adLockOptimistic, adCmdText

   If Not (rs.BOF And rs.EOF) Then
      rs.MoveFirst
      SQLVersion = [!]Val(rs.Fields.Item("Version").Value)[/!]
   End If
   rs.Close
   Set rs = Nothing
   Exit Function
   
ERR_Handler:
   MsgBox (Err.Description)
End Function

SQLOLEDB.1 is a SQL2000 provider. SQLNCLI is a 2005/2008 provider. I'm told that you can get a slight boost in performance if you use the SQLNCLI provider instead of SQLOLEDB (but I haven't been able to verify this for myself).

My point is.... it seems as though any of the providers for SQL Server can connect to any of the SQL Database versions. I can certainly understand the desire to know the version of the database, but needing this information before connecting to it isn't one of them.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatrickIRL]

Ok, I'm confused (I think).

What you have will work and return the version, I get 10.0.1600.22 when I run it.

So, then in VB you're going to do some conditional branching based on the version?

Yes/No/Maybe ??

Patrick

 

[Bluejay07]

Hi George,

Thanks for the reply. I'll try to explain more.

First of all our programs use Sage Accpac and Accpac connections use ODBC so for consistency our programs use ODBC connections. Plus that's how they were originally programmed.

Next, I am modifying the setup of the program. Therefore, I do not know where the database is, nor do I know the name of the database, the user name, the password or any other information. The section being modified is where the user specifies all of the above information.

I am changing the setup because of SQL 2008. All ODBC connections for SQL 2000 and SQL 2005 have used 'SQL Server'. Now with SQL 2008, we need to use 'SQL Native Client'.

The setup reads the ODBC connections and displays them for the user to continue setting up the program.

Here the catch.
In order to read the ODBC information, I need to know what SQL version is being used, but I can't access SQL because I don't yet have the connection information. (FYI SQL Native Client doesn't exist for SQL 2000, and was never used in SQL 2005 due to conflicts)

@Patrick,
When trying the code I provided, I get the error: Data source name not found and no default driver specified.

Currently, I am only retrieving the 'SQL Server' ODBC connections. If the user is using Sql 2008 I also want all 'SQL Native Client' ODBC connection displayed.

 

[gmmastros]

(FYI SQL Native Client doesn't exist for SQL 2000, and was never used in SQL 2005 due to conflicts)

I know you can use the SQL Native Client to connect to a SQL2000 database. So... can you install SQL Native client and then only list those?

http://www.microsoft.com/downloads/...60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Scroll down to Microsoft SQL Server Native Client


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Thanks for the link George, but it still doesn't help setting up the connection to determine the version.
We don't want users to setup the ODBC connections using Native Client in earlier versions of SQL.

I think the only thing I can do is to have the user select an option (eg. SQL 2005 and earlier or SQL 2008 and later)

 

[dilettante]

Ahh, so the question here is about the version of SQL Server (a DMBS product, e.g. SQL Server 2000) and not about the SQL version (a query language, e.g. ANSI SQL-92).

 

[PatrickIRL]

I wonder if SQLDMO or SQLSMO will give you the information you need?
Using these you can enumerate through the servers/databases and there may be a property that will give you the version of each database.

Patrick

 

[Bluejay07]

Dilettante,
You are correct. Perhaps I should have labelled the thread a bit better.

PatrickIRL,
Thanks for the suggestion. When I have more time, I can look into those further.