Cómo devolver información sobre los servidores vinculados de SQL Server


Resumen


SQL Server 7,0 y las versiones posteriores de SQL Server permiten agregar orígenes de datos externos como servidores vinculados. Esta característica proporciona acceso a consultas heterogéneas distribuidas en orígenes de datos OLE DB. Al conectarse a SQL Server, se puede devolver un registro de las tablas disponibles con el comando SQLTables (file_handle). El comando SQLTables (), sin embargo, no devuelve información sobre las tablas disponibles en los servidores vinculados. La información relacionada con los servidores vinculados es devuelta por tres procedimientos almacenados diferentes de SQL Server. En este artículo se describe cómo usar esos procedimientos almacenados para devolver información relacionada con las tablas disponibles de los servidores vinculados.

Más información


Los servidores vinculados de SQL Server se definen como asignaciones en un servidor vinculado específico. Por lo tanto, es posible que no necesariamente forme parte de una base de datos de SQL Server. Los servidores vinculados de SQL Server se definen mediante el procedimiento almacenado del sistema sp_addlinkedserver. La definición del servidor vinculado contiene toda la información necesaria para localizar el origen de datos OLE DB. A continuación, se puede hacer referencia a las tablas remotas desde el nombre del servidor vinculado de dos maneras:
  • El nombre del servidor vinculado se puede usar como nombre de servidor en un nombre de cuatro partes usado como una referencia de tabla o vista en una instrucción Transact-SQL. Las otras tres partes del nombre hacen referencia a un objeto del servidor vinculado que se expone como un conjunto de filas.
  • El nombre del servidor vinculado se puede usar como parámetro de entrada para una función OPENQUERY. OPENQUERY envía al proveedor de OLE DB un comando para ejecutar. El conjunto de filas devuelto puede usarse como una referencia de tabla o vista en una instrucción Transact-SQL.
Los procedimientos almacenados de SQL Server que se usan para obtener información acerca de los servidores vinculados son:
  • sp_linkedservers
  • sp_tables_ex
  • sp_columns_ex
Cada uno de los procedimientos almacenados de SQL Server devuelve un nivel diferente de detalle relacionado con los servidores vinculados. sp_linkedservers devuelve las siguientes columnas:
   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 devuelve información sobre las tablas del servidor vinculado especificado. Este procedimiento almacenado acepta los siguientes argumentos:
   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 devuelve las siguientes columnas:
   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 devuelve información de la columna, una fila por columna, de las tablas del servidor vinculado. sp_columns_ex devuelve información de columna solo para la columna especificada si se especifica Column. Este procedimiento almacenado acepta los siguientes argumentos:
   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 devuelve las siguientes columnas:
   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.  
  1. Abra el administrador de ODBC y cree un origen de orígenes ODBC denominado "LINKSRV". Nota Debido a que los servidores vinculados se muestran en la pestaña seguridad de SQL Server, no es necesario establecer una base de datos predeterminada al crear el DS.
  2. Cree un archivo de programa denominado "SP_tables. PRG" con el código siguiente:
       * 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.
  3. En la ventana de comandos, escriba lo siguiente:
       DO SP_TABLES 

Referencias


Ayuda de referencia de Transact-SQL; Buscar en: "sp_addlinkedserver"; "sp_tables_ex"; "sp_linkedservers"; "sp_columns_ex"