Information regarding the versions of Microsoft SQL Server can be useful when you are writing commands that are intended to be specific to a particular version of SQL Server.

To check for version information, use one of the following methods:

• Use the "@@version" global variable.
• Use the "xp_msver" extended stored procedure.
• Query the version column of the sysdatabases table.

All of these methods return the same information. This article describes how to obtain the SQL Server version information from within Visual FoxPro.

Example one

The first example illustrates the use of SQL Server's @@version global variable to obtain SQL Server versioning information.

Note You must change UID <username> and PWD <strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

1. Create a program file named Sqlvers1.prg that contains the following code:

         connection_string='DRIVER={SQL Server};SERVER=MYSERVER;' + ;
            'DATABASE=PUBS;UID=<user name>;PWD=<strong password>'
         gnConnHandle=SQLSTRINGCONN(connection_string)
         IF gnConnHandle>0
            TEST=SQLEXEC(gnConnHandle,'select @@version','getversion')
            SELECT getversion
            versioninfo=EXP
            sqldisconn(gnConnHandle)
            =MESSAGEBOX(versioninfo,48,"SQL Server Version")
         ELSE
            =MESSAGEBOX("Connection Failed",16,"ODBC Problem")
         ENDIF

   If you are connecting to SQL Server 7.0 or later, use this code

           connection_string='DRIVER={SQL Server};SERVER=MYSERVER;' + ;
            'DATABASE=PUBS;UID=<user name>;PWD=<stong password>'
         gnConnHandle=SQLSTRINGCONN(connection_string)
         IF gnConnHandle>0
            TEST=SQLEXEC(gnConnHandle,'select @@version','getversion')
            SELECT getversion
            versioninfo=STRCONV(exp,10) && Convert Unicode to UTF-8 
            sqldisconn(gnConnHandle)
            =MESSAGEBOX(versioninfo,48,"SQL Server Version")
         ELSE
            =MESSAGEBOX("Connection Failed",16,"ODBC Problem")
         ENDIF

2. From the Command window, type the following command:
   DO SQLVERS1

Example two

The second example illustrates the use of SQL Server's xp_msver extended stored procedure to obtain SQL Server versioning information.

1. Create a program file named Sqlvers2.prg that contains the following code:

         connection_string='DRIVER={SQL Server};SERVER=MYSERVER;' + ;
            'DATABASE=PUBS;UID=<user name>;PWD=<strong password>'
         gnConnHandle=SQLSTRINGCONN(connection_string)
         IF gnConnHandle>0
            TEST=SQLEXEC(gnConnHandle,'exec xp_msver','getversion')
            SELECT getversion
            versioninfo=EXP
            sqldisconn(gnConnHandle)
            =MESSAGEBOX(versioninfo,48,"SQL Server Version")
         ELSE
            =MESSAGEBOX("Connection Failed",16,"ODBC Problem")
         ENDIF

   If you are using SQL Server 7.0 or later, use this code:

         connection_string='DRIVER={SQL Server};SERVER=<server name>;' + ;
            'DATABASE=PUBS;UID=<user name>;PWD=<Strong password>'
         gnConnHandle=SQLSTRINGCONN(connection_string)
         IF gnConnHandle>0
            TEST=SQLEXEC(gnConnHandle,'exec xp_msver','getversion')
            SELECT getversion
            versioninfo=STRCONV(exp,10) && Convert Unicode to UTF-8
            sqldisconn(gnConnHandle)
            =MESSAGEBOX(versioninfo,48,"SQL Server Version")
         ELSE
            =MESSAGEBOX("Connection Failed",16,"ODBC Problem")
         ENDIF

2. From the Command window, type following command:
   DO SQLVERS2
3. Compare the output with the information that you obtained using the @@version global variable.

Example three

The third example illustrates the use of information contained in the version column of the sysdatabases table to obtain SQL Server version information.

1. Create a program file named Sqlvers3.prg that contains the following code:

         connection_string='DRIVER={SQL Server};SERVER=MYSERVER;' + ;
            'DATABASE=PUBS;UID=<user name>;PWD=<strong password>'
         gnConnHandle=SQLSTRINGCONN(connection_string)
         IF gnConnHandle>0
            sqlcommand="SELECT version FROM sysdatabases WHERE dbid = 1"
            TEST=SQLEXEC(gnConnHandle,sqlcommand,'getversion')
            SELECT getversion
            versioninfo=EXP
            sqldisconn(gnConnHandle)
            =MESSAGEBOX(versioninfo,48,"SQL Server Version")
         ELSE
            =MESSAGEBOX("Connection Failed",16,"ODBC Problem")
         ENDIF

   If you are using SQL Server 7.0 or later, use this code:

         connection_string='DRIVER={SQL Server};SERVER= <server name>;' + ;
            'DATABASE=PUBS;UID=<user name>;PWD=<strong password>'
         gnConnHandle=SQLSTRINGCONN(connection_string)
         IF gnConnHandle>0
            sqlcommand="SELECT version FROM sysdatabases WHERE dbid = 1"
            TEST=SQLEXEC(gnConnHandle,sqlcommand,'getversion')
            SELECT getversion
            versioninfo=STRCONV(exp,10) && Convert Unicode to UTF-8 &&EXP
            sqldisconn(gnConnHandle)
            =MESSAGEBOX(versioninfo,48,"SQL Server Version")
         ELSE
            =MESSAGEBOX("Connection Failed",16,"ODBC Problem")
         ENDIF

2. From the Command window, type the following command:
   DO SQLVERS3
3. Compare the output with the information that you obtained using the @@version global variable and the xp_msver extended stored procedure.