Connecting Native C / C++ to SQL Server

I'm not sure how to tell which version it is, though

Open SSMS and connect to SQL Server instance and open a new query and run the following command:

SELECT @@VERSION

Select allOpen in new window

What is the correct way for me to establish a DB connection?

You may need to post here the portion of code you're using for connecting to SQL Server so we can help you better.

Thank you!
The version (per the above query) is: Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)

My connection code looks like this:

SQLHANDLE sqlenvhandle = NULL;
SQLHANDLE sqlconnectionhandle = NULL;
SQLHANDLE sqlstatementhandle = NULL;
SQLRETURN retcode;

if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlenvhandle)) {
		goto FINISHED;
	}
		
if (SQL_SUCCESS != SQLSetEnvAttr(sqlenvhandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0)){
		goto FINISHED;
	}

if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle)){
		goto FINISHED;
	}

SQLWCHAR retconstring[1024];
switch (SQLDriverConnect(sqlconnectionhandle,
		NULL,
		(SQLWCHAR*)"DRIVER={SQL Server};SERVER=localhost;DATABASE=Prod;UID=user;PWD=pass;",
		SQL_NTS,
		retconstring,
		1024,
		NULL,
		SQL_DRIVER_NOPROMPT)){
case SQL_SUCCESS_WITH_INFO:
		show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
		break;
	case SQL_INVALID_HANDLE:
	case SQL_ERROR:
		show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
		goto FINISHED;
	default:
		break;
	}

	if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle)){
		goto FINISHED;
	}

	if (SQL_SUCCESS != SQLExecDirect(sqlstatementhandle, (SQLWCHAR*)"select * from testtable", SQL_NTS)){
		show_error(SQL_HANDLE_STMT, sqlstatementhandle);
		goto FINISHED;
	}
	else{
		char name[64];
		char address[64];
		int id;
		while (SQLFetch(sqlstatementhandle) == SQL_SUCCESS){
			SQLGetData(sqlstatementhandle, 1, SQL_C_ULONG, &id, 0, NULL);
			SQLGetData(sqlstatementhandle, 2, SQL_C_CHAR, name, 64, NULL);
			SQLGetData(sqlstatementhandle, 3, SQL_C_CHAR, address, 64, NULL);
			cout << id << " " << name << " " << address << endl;
		}
	}

FINISHED:
	if (sqlstatementhandle != NULL) {
		SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle);
	}
	if (sqlconnectionhandle != NULL) {
		SQLDisconnect(sqlconnectionhandle);
		SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
	}
	if (sqlenvhandle != NULL) {
		SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);
	}

Select allOpen in new window

You really have a very old SQL Server instance. MSSQL 2000 isn't supported anymore.
Can you see in the Event Log for a more clear error message?

You can try to use System.Data.SqlClient namespace for work with this old version of SQL Server.

I don't see anything obvious in the logs. I will look into SqlClient.

you can try to connect by ODBC.

open the odbcad32.exe applet and try to create a system datasource pointing to your database.

if that works you can access the database by means of this odbc datasource for example by using mfc CDatabase and CRecordset classes.

Sara

I was able to create a System DSN and test it manually, so I decided to try the CDatabase route. I put some MFC CDatabase code in but I have a couple of questions. The first is: What do I do about windows.h for MFC? I get a compilation error if I include it (saying not to include it for MFC), but I also get compilation errors if I remove it. Also, what should my dsn connection string look like? I found several for Access in CDatabase examples, but didn't really find much for SQL Server. My guess was something like this:

sDsn.Format((SQLWCHAR*)"ODBC;DRIVER={%s};Server=%s;Database=%s;UID=user;PWD=pass;", sDriver, sMc, sFile);

Thanks!
Amy

You have an ODBC namespace: System.Data.Odbc

for mfc sources you normally would include stdafx.h which then includes windows.h. that is independent from using precompiled headers (PCH) via stdafx.h or not. if you turn a win32 program to use mfc, the existing stdafx.h perhaps doesn't include afx.h (or atlafx.h) what is the main header of mfc. the easiest is to create a dummy mfc project (type mfc application) and copy the stdafx.h of this new project to your current project. save the old stdafx.h - if any - somewhere, but actually there are no problems to expect by doing so. after that include the new stdafx.h at top of all your .cpp files (regardless of PCH switched on or off)

what should my dsn connection string look like?

you may try with "ODBC;<dsnname>" what could be sufficient if your datasource already was working.

UID and PWD is ok but I would not add too many other information which may require special syntax or refer to not existing drivers or symbols. if you have a valid odbc datasource you should be able to connect with a minimal connect string.

sDsn.Format((SQLWCHAR*)"ODBC;DRIVER={%s};Server=%s;Database=%s;UID=user;PWD=pass;", sDriver, sMc, sFile);

to cast a char * to SQLWCHAR* probably isn't a good idea. I would configure the mfc project with multi-byte character set rather than with Unicode. but if your gui is Unicode you also could switch to Unicode. if that is the case you should use the L prefix to using wide character literals or use the _T or TEXT macros provided. don't mix wide and narrow characters if it is not absolutely necessary.

note, by using the class wizard (available via the resource editor) you can create a new CRecordset derived class where you would be able to connect to your datasource and if that worked, the wizard would create a class that has a valid connect string in member function GetDefaultConnect.

Sara

Thank you very much! I took your advice and created/configured an MFC application, then copied the stdafx header from that and placed it in my win32 application. Everything thing seems to work almost perfectly, except for one small thing...

This application needs to run from a batch script, so no dialogs, etc. I can't seem to keep the DSN dialog from popping up when using the CDatabase. I'm guessing there is still something wrong with my connection string. Here is what I have:

sDsn.Format("ODBC;DSN='MyDSN';UID=user;PWD=pass;");
	database.noOdbcDialog;
if (database.Open("MyDSN", false, false, sDsn))
	{
		//success
	}

Select allOpen in new window

Thank you for the excellent help!