How to add a data source (ODBC)

You can add a data source by using ODBC Administrator, programmatically (by using SQLConfigDataSource), or by creating a file.

To add a data source by using ODBC Administrator

1. On the Start menu, point to Settings, and then click Control Panel.
   
   
2. Double-click ODBC.
   
   
3. Click the User DSN, System DSN, or File DSN tab, and then click Add.
   
   
4. Click SQL Server, and then click Finish.
   
   
5. Complete the Steps in the Create a New Data Source to SQL Server Wizard.

To add a data source programmatically

• Call SQLConfigDataSource with the fOption set to either ODBC_ADD_DSN or ODBC_ADD_SYS_DSN.

To add a file data source

• Call SQLDriverConnect with a SAVEFILE=file_name parameter in the connect string. If the connect is successful, the ODBC driver creates a file data source with the connection parameters in the location pointed to by the SAVEFILE parameter.

Examples

A. Create a data source using SQLConfigDataSource

#include <stdio.h>
#include <windows.h>
#include "sql.h"
#include <odbcinst.h>

int main()
{
RETCODE retcode;

UCHAR   *szDriver = "SQL Server";
UCHAR   *szAttributes =
"DSN=MyDSN\0DESCRIPTION=SQLConfigDSN Sample\0"
"SERVER=MySQL\0ADDRESS=MyServer\0NETWORK=dbmssocn\0"
"DATABASE=pubs\0";

retcode = SQLConfigDataSource(NULL,
                       ODBC_ADD_DSN,
                       szDriver,
                       szAttributes);

B. Create a file data source

Use the SAVEFILE keyword in SQLDriverConnect to create a file data source, and then use SQLDriverConnect to connect with the file data source. This example has been simplified by removing error handling.

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

#define MAXBUFLEN   255

SQLHENV      henv = SQL_NULL_HENV;
SQLHDBC      hdbc1 = SQL_NULL_HDBC;

int main() {

   RETCODE      retcode;

   // This format of the SAVEFILE keyword saves a successful
   // connection as the file Myfiledsn.dsn in the ODBC default
   // directory for file DSNs.
   SQLCHAR      szConnStrIn[MAXBUFLEN] =
            "SAVEFILE=MyFileDSN;DRIVER={SQL Server};SERVER=MySQL;"
            "NETWORK=dbmssocn;UID=sa;PWD=MyPassWord;";

   SQLCHAR      szConnStrOut[MAXBUFLEN];
   SQLSMALLINT   cbConnStrOut = 0;

    // Allocate the ODBC Environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

   //Notify ODBC that this is an ODBC 3.0 application.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

   // Allocate an ODBC connection handle and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   retcode = SQLDriverConnect(hdbc1,      // Connection handle
                     NULL,         // Window handle
                     szConnStrIn,      // Input connect string
                     SQL_NTS,         // Null-terminated string
                     szConnStrOut,   // Addr of output buffer
                     MAXBUFLEN,      // Size of output buffer
                     &cbConnStrOut,   // Address of output length
                     SQL_DRIVER_NOPROMPT);

   // Disconnect, set up a new connect string, and then test file DSN.
   SQLDisconnect(hdbc1);
   strcpy(szConnStrIn, "FILEDSN=MyFileDSN;UID=sa;PWD=MyPassWord;");
   retcode = SQLDriverConnect(hdbc1,      // Connection handle
                     NULL,         // Window handle
                     szConnStrIn,      // Input connect string
                     SQL_NTS,         // Null-terminated string
                     szConnStrOut,   // Addr of output buffer
                     MAXBUFLEN,      // Size of output buffer
                     &cbConnStrOut,   // Address of output length
                     SQL_DRIVER_NOPROMPT);

   /* Clean up. */
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   return(0);
}

See Also

Adding or Deleting an ODBC Data Source