Microsoft KB Archive/203638

= How to return information about SQL Server linked servers =

Article ID: 203638

Article Last Modified on 2/2/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0b for Macintosh
 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Service Pack 3a

-



This article was previously published under Q203638



SUMMARY
SQL Server 7.0 and later versions of SQL Server allow the addition of external data sources as linked servers. This feature provides access to distributed, heterogeneous queries against OLE DB data sources. When connecting to SQL Server, a cursor listing available tables can be returned with the SQLTables(file_handle) command. The SQLTables command, however, does not return information about tables available in Linked Servers. Information regarding linked servers is returned by three different SQL Server stored procedures. This article describes how to use those stored procedures to return information regarding tables available from Linked Servers.



MORE INFORMATION
SQL Server linked servers are defined as mappings against a specific linked server. Therefore, a linked server may not necessarily be part of a SQL Server database.

SQL Server linked servers are defined using the sp_addlinkedserver system stored procedure. The linked server definition contains all the information needed to locate the OLE DB data source. Remote tables from the linked server name can then be referenced in two ways:


 * The linked server name can be used as the server name in a four-part name used as a table or view reference in a Transact-SQL statement. The other three parts of the name reference an object in the linked server that is exposed as a rowset.
 * The linked server name can be used as an input parameter to an OPENQUERY function. OPENQUERY sends the OLE DB provider a command to execute. The returned rowset can then be used as a table or view reference in a Transact-SQL statement.

The SQL Server stored procedures used to return information about linked servers are:


 * sp_linkedservers
 * sp_tables_ex
 * sp_columns_ex

Each of the SQL Server stored procedures return a different level of detail related to linked servers.

sp_linkedservers returns the following columns:  SRV_NAME            Name of the linked server.

SRV_PROVIDERNAME    Friendly name of the OLE DB provider managing access to the specified linked server.

SRV_PRODUCT         Product name of the linked server.

SRV_DATASOURCE      OLE DB datasource property corresponding to the specified linked server.

SRV_PROVIDERSTRING  OLE DB provider string property corresponding to                        the specified linked server.

SRV_LOCATION        OLE DB location property corresponding to the specified linked server.

SRV_CAT             OLE DB catalog property corresponding to the specified linked server. sp_tables_ex returns information on the tables from the specified linked server. This stored procedure accepts the following arguments:

  TABLE_SERVER         Name of the linked server for which to return table information, with no default.

TABLE_NAME          Name of the table for which to return data type information, with a default of NULL.

TABLE_SCHEMA        Is the table schema, with a default of NULL.

TABLE_CATALOG       Catalog Name of the database in which the specified table resides, with a default of NULL.

TABLE_TYPE          Type of the table to return, with a default of                         NULL.

sp_tables_ex returns the following columns:

  TABLE_CAT            Table qualifier name. (May be null)

TABLE_SCHEM         Table or view owner name.

TABLE_NAME          Table name.

TABLE_TYPE          Table, system table, or view.

REMARKS             SQL Server does not return a value for this column.

sp_columns_ex returns column information, one row per column, for the given linked server table(s). sp_columns_ex returns column information only for the given column if column is specified. This stored procedure accepts the following arguments:

  TABLE_SERVER        Name of the linked server for which to return column information, with no default.

TABLE_NAME         Name of the table for which to return column information, with a default of NULL.

TABLE_SCHEMA       Name of the table for which to return column information, with a default of NULL.

TABLE_CATALOG      Catalog name of the table for which to return column information, with a default of NULL.

COLUMN             Name of the database column for which to provide information, with a default of NULL.

ODBCVer            The version of ODBC being used. ODBCVer is an                      integer, with a default value of 2, indicating ODBC Version 2. Valid values are 2 or 3.

sp_columns_ex returns the following columns:

  TABLE_CAT           Table or view qualifier name. (May be null)

TABLE_SCHEM        Table or view owner name.

TABLE_NAME         Table or view name.

COLUMN_NAME        Column name, for each column of the specified table.

DATA_TYPE          Integer value corresponding to ODBC type indicators.

TYPE_NAME          String representing a data type.

COLUMN_SIZE        Number of significant digits.

BUFFER_LENGTH      Transfer size of the data.

DECIMAL_DIGITS     Number of digits to the right of the decimal point.

NUM_PREC_RADIX     Is the base for numeric data types.

NULLABLE           Specifies nullability.

REMARKS            This field always returns NULL.

COLUMN_DEF         Default value of the column.

SQL_DATA_TYPE      Value of the SQL data type as it appears in the TYPE field of the descriptor.

SQL_DATETIME_SUB   Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.

CHAR_OCTET_LENGTH  Maximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.

ORDINAL_POSITION   Ordinal position of the column in the table. IS_NULLABLE        Nullability of the column in the table. This column returns a zero-length string if nullability is                       unknown. The value returned for this column is                      different from the value returned for the NULLABLE column.

SS_DATA_TYPE SQL   Server data type, used by Open Data Services extended stored procedures.

 Open the ODBC Administrator and create an ODBC DataSource named "LINKSRV".

Note Because linked servers are listed under the SQL Server Security tab, it is not necessary to set a default database when creating the DS.  Create a program file named "SP_tables.prg" using the following code:

* NOTE: This code is based on the following assumptions: * A DSN named LINKSRV pointing to a SQL Server  data source exists. * A Linked Server has been defined in SQL Server. Connect_String="'LINKSRV','SA',''" * Connect to SQL Server gnConnHandle=SQLCONNECT(&Connect_String) IF gnConnHandle>0 * Create a command string to pass to SQL Server via SQLExec. CREATE CURSOR SQLSRVDAT (Server c(128),Table_Name c(128), ;        Column_name c(128)) * Create a command string to pass to SQL Server via SQLExec. SQLCommand="sp_linkedservers" * Return a cursor with the names of available linked servers. QRYVal=SQLExec(gnConnHandle,SQLCommand,'SQLSvr') IF QRYVal>0 SELECT SQLSvr DO WHILE !EOF * Build a command to pass to SQL Server to return a cursor * of tables available from the linked server. SQLCommand="sp_tables_ex '"+ALLTRIM(SQLSvr.SRV_NAME)+"'" * Return a cursor with the names of tables available from * the specified linked server. tables_val=SQLExec(gnConnHandle,SQLCommand,'SQLTabs') IF tables_val>0 SELECT sqltabs LOCA DO WHILE !EOF * Build a command to pass to SQL Server to return a                 * cursor with the fields from the specified * table of the defined linked server. SQLCommand="sp_columns_ex '"+ALLTRIM(SQLSvr.SRV_NAME) + ; "','"+ALLTRIM(sqltabs.Table_Name)+"'" * Return a cursor with the names of the columns * in the specified table of the linked server. ColVal=SQLExec(gnConnHandle,SQLCommand,'sqlcols') IF ColVal>0 SELECT sqlcols DO WHILE !EOF INSERT INTO SQLSRVDAT VALUES ; (SQLSvr.SRV_NAME,sqltabs.Table_Name, ;                          sqlcols.Column_name) SELECT sqlcols SKIP ENDDO ENDIF SELECT sqltabs SKIP ENDDO ENDIF SELECT SQLSvr SKIP ENDDO ELSE =AERROR(L_Server) =MESSAGEBOX(L_Server[1,2]+CHR(13)+L_Server[1,3],32,'Query Failed') ENDIF =SQLDISCONN(gnConnHandle) SELECT SQLSRVDAT IF RECCOUNT=0 =MESSAGEBOX('No Linked Servers Detected',64,'Linked Servers') ELSE CALCULATE MAX(LEN(ALLTRIM(SQLSRVDAT.column_Name))) TO max_wide BROW TITLE "Linked Server Information" WIDTH max_wide ENDIF ELSE =AERROR(s_failed) * The linked server doesn't exist, so display a message. =MESSAGEBOX(s_failed[1,2]+CHR(13)+ ;        IIF(!ISNULL(s_failed[1,3]),s_failed[1,3],"null"),32,'Failed') ENDIF RETURN * End Code.   In the Command window, type the following:

  DO SP_TABLES </ol>

<div class="references_section">