With SQL Server and other client-server databases, you are likely to need a password and other parameters. Fortunately, when you connect from the command line, the ODBC driver manager will prompt you for whatever information is needed. However, in order to run a job in an automatic batch mode with a command file, you need to supply the necessary information automatically.
The simplest way to do this is to take advantage of the fact that ODBC will return a connection string that contains all of the information that you entered. This information is in the proper form to complete the connection again without any prompting from the ODBC driver manager.
You can obtain the connection string from an ODBC data source by connecting interactively to the source and then entering either:
You will find that the connection strings are generally long and complicated, so that typing one into a command file after retrieval is tedious and error prone. Furthermore, because the connection string may contain your password, you might not want it written openly to a command file.
Stat/Transfer solves these difficulties with operators that allow you to write or retrieve the value of any parameter and by allowing encryption.
The Write and Retrieve Operators
The operator '>' allows you to store the value of any parameter in a file, while the operator '<' allows you to read back a stored value.
DBR | DBW parametername > | < storagefile
Thus, in order to write the connection string for an input ODBC data source to a file, connect.str, type:
dbr connstr > connect.str
To retrieve the connection string and make it available to an input ODBC data source, type:
dbr connstr < connect.str
To use a connection string in a batch job, you can connect interactively to the ODBC data source, use the '>' operator to store the connection string in a file and then use the '<' operator with a DBR or DBW command in a command file.
Encrypting Connection Strings
Because the connection string may contain your password, you may not want it written to unprotected files. You can encrypt and decrypt the stored value of any parameter by adding a pound sign to the '>' or '<' operators.
dbr connstr ># connect.str
writes the connection string in encrypted format and
dbr connstr <# connect.str
retrieves and decrypts the connection string.