How to obtain SQL Server version information from Visual FoxPro (191867)

MORE INFORMATION

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.