Professional > Data management scripting > Data Management Script (DMS) file > Sections in the DMS file > GlobalSQLVariables section
 
GlobalSQLVariables section
The GlobalSQLVariables section is an optional section that you can use to define global SQL variables, which provide a means of exchanging data between different data sources.
Syntax
GlobalSQLVariables(name [, "description"])
   ConnectionString = "
<connection_string>"
   SelectQuery = "
<select_query>"
End GlobalSQLVariables
Parameters
name and description define a name and description for the section and should be of type Text.
ConnectionString
A connection string that defines the OLE DB connection properties, which define the OLE DB provider to be used to access the case data and all of the details about the physical data source, such as its name and location.
If you are using the UNICOM Intelligence Data Model, specify the UNICOM Intelligence OLE DB Provider by setting the Provider connection property to mrOleDB.Provider.n (where n is the version number). The UNICOM Intelligence OLE DB Provider has some custom connection properties that define the CDSC that is to be used to access the case data, the Metadata Document (.mdd) file or other metadata source and MDSC to be used, etc. For more information, see Connection properties. The CDSC you are using must be read-enabled. This means that you cannot use a GlobalSQLVariables section with a Quantum data source.
You can specify file locations using a relative path (relative to the folder in which the DMS file is located when you run it). Generally, you do not need to specify a connection property for which the default value is being used.
An easy way to create the connection string is to use the UNICOM Intelligence Professional Connection String Builder. For more information, see 3. Transferring different types of data.
SelectQuery
An SQL query that defines the global variables. You define a global variable in the query using the AS clause and by prefixing the column name with the at sign (@). The query can specify a simple column or an expression based on one or more columns, using the SQL syntax supported by the OLE DB provider you are using. For more information, see Basic SQL queries.
Example
The following example shows using a global SQL variable in a DMS file that is being used to clean batches of case data and write it to a different data source, which is being used to store the clean data. The example shows only the GlobalSQLVariables, InputDataSource, and OutputDataSource sections of the DMS file:
GlobalSQLVariables section
This specifies a connection string for the output (clean) database and a query that defines a global SQL variable called @LastTransferred, which stores the maximum value of the DataCollection.FinishTime variable. This is a system variable that stores the date and time that an interview is stopped or completed. This means that the global variable stores the date and time of the most recent (“newest”) respondent record in the output data source. For more information, see System variables.
InputDataSource section
This specifies a connection string for the input (live) data source and a query that selects respondent records that have the Completed status and whose finish time is after that of the newest record in the output database.
OutputDataSource section
This specifies a connection string for the output (clean) database.
Note This example is provided as a sample DMS file (called GlobalSQLVariable.dms) that is installed with the UNICOM Intelligence Developer Documentation Library. You need to run the GlobalSQLVariableSetUp.dms sample first to set up the output data source and transfer the first records. For more information, see Sample DMS files. You can use the RunGlobalSQLVariableExample.bat sample batch file to run the two files in sequence (see Sample batch files).
GlobalSQLVariables(myGlobals, "My globals section")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.ddf; _
    Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.mdd"
  SelectQuery = "SELECT MAX(DataCollection.FinishTime)
                 As @LastTransferred FROM VDATA"
End GlobalSQLVariables

InputDataSource(myInputDataSource)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.ddf; _
    Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.mdd"
  SelectQuery = "SELECT * FROM VDATA WHERE
                 (DataCollection.FinishTime > '@LastTransferred')
                 AND (DataCollection.Status = {Completed})"
End InputDataSource

OutputDataSource(myOutputDataSource)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.ddf"
  MetaDataOutputName = "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\GlobalSQLVariable.mdd"
End OutputDataSource
See also
Sections in the DMS file