Informatie ophalen over gekoppelde servers van SQL Server


Samenvatting


Met SQL Server 7,0 en nieuwere versies van SQL Server worden de toevoeging van externe gegevensbronnen als gekoppelde servers toegestaan. Deze functie biedt toegang tot gedistribueerde, heterogene query's voor OLE DB-gegevensbronnen. Wanneer u verbinding maakt met SQL Server, kunt u de beschikbare tabellen van de cursor weergeven met de opdracht SQLTables (file_handle). De opdracht SQLTables () retourneert echter geen informatie over tabellen die beschikbaar zijn op gekoppelde servers. Informatie over gekoppelde servers wordt geretourneerd door drie verschillende opgeslagen procedures van SQL Server. In dit artikel wordt uitgelegd hoe u deze opgeslagen procedures gebruikt om informatie te retourneren over tabellen die beschikbaar zijn via gekoppelde servers.

Meer informatie


Gekoppelde servers van SQL Server worden gedefinieerd als toewijzingen aan een specifieke gekoppelde server. Daarom maakt een gekoppelde server wellicht niet noodzakelijkerwijs deel uit van een SQL Server-database. Gekoppelde servers van SQL Server worden gedefinieerd met behulp van de opgeslagen procedure sp_addlinkedserver systeem. De definitie van de gekoppelde server bevat alle informatie die nodig is voor het vinden van de OLE DB-gegevensbron. Voor externe tabellen van de naam van de gekoppelde server waarnaar wordt verwezen op twee manieren:
  • De naam van de gekoppelde server kan worden gebruikt als de naam van de server in de naam van een vierkant die wordt gebruikt als tabel of weergave in een Transact-SQL-instructie. De andere drie delen van de naamverwijzing een object op de gekoppelde server dat wordt weergegeven als een Rijset.
  • De naam van de gekoppelde server kan worden gebruikt als invoerparameter voor een OPENQUERY-functie. Met QUERYOPENEN wordt de OLE DB-provider verzonden die een opdracht uitvoert. De geretourneerde Rijset kan vervolgens worden gebruikt als tabel of weergave-verwijzing in een Transact-SQL-instructie.
De door SQL Server opgeslagen procedures die worden gebruikt om informatie over gekoppelde servers te retourneren, zijn:
  • sp_linkedservers
  • sp_tables_ex
  • sp_columns_ex
Elk van de door SQL Server opgeslagen procedures levert een ander detailniveau met betrekking tot gekoppelde servers. sp_linkedservers de volgende kolommen als resultaat:
   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 retourneert informatie over de tabellen van de opgegeven gekoppelde server. Deze opgeslagen procedure accepteert de volgende argumenten:
   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 retourneert de volgende kolommen:
   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 retourneert kolom gegevens, één rij per kolom, voor de opgegeven gekoppelde server tabel (en). sp_columns_ex retourneert alleen kolom gegevens voor de opgegeven kolom als de kolom is opgegeven. Deze opgeslagen procedure accepteert de volgende argumenten:
   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 retourneert de volgende kolommen:
   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. Open de ODBC-beheerder en maak een ODBC-gegevensbron met de naam ' LINKSRV '. Opmerking Aangezien gekoppelde servers worden weergegeven onder het tabblad SQL Server-beveiliging, is het niet nodig om een standaarddatabase in te stellen bij het maken van de DS.
  2. Maak een programmabestand met de naam ' SP_tables. PRG ' met behulp van de volgende 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.
  3. Typ in het opdrachtvenster het volgende:
       DO SP_TABLES 

Verwijzingen


Help voor Transact-SQL zoeken op: ' sp_addlinkedserver '; ' sp_tables_ex '; ' sp_linkedservers '; ' sp_columns_ex '