ODBC SQLFetch with MS SQL Server Issues.

[JohnMalv]

I have this odd problem I began describing on the MS SQL Server forum, but was reccommended that there may be some new insight here:

I have an application that uses a basic SQLExecuteDirect, SQLFetch, SQLGetData (and an SQLDescribeCol based function to grab column numbers based on col names (which seems to work fine.) My problem is this: the program works just fine for MySQL, PostgreSQL, Microsoft Text ODBC. But for MS SQL Server 2000, it's grabbing junk data (duplication of 2 or so fields accross all 30 fields or so. I traced the problem back to the SQLFetch function call, which is returning SQL_NO_DATA. However, I have no idea why the Fetch call is consistantly working on the other 3 ODBC interfaces, and not on the MSSQL interface. Is there something special that must be done for MSSQL, or something that the others allow that MS does not? What could cause that SQL_NO_DATA error, and why would it not occur on the other databases (or only occur on MSSQL?)

I appreciate in advance any insight anyone can provide!

 

[Cagliostro]

SQL_NO_DATA means
1. the recordset is empty
2. the end of recordset is reached.
The difference between MSSQLServer, MySQL and pgsql may be rather in the syntax of SQL than at ODBC level. Could you please post your code there?

Ion Filipski

 

[JohnMalv]

Thanks for the response. Actually I did figure out the solution to that part of the mystery (forgot to post it here).

It was actually SQLGetData that was failing, and the reason for the difference was that MSSQL apparently doesn't support SQL_ANY_ORDER (I BELIEVE from memory that's the correct attribute) for seeking data in any order as the other drivers did, it's sequential only, so I simply had to re-order the requests (it generates the queries based on fields in an ini file.)

However there's still one other odd problem occuring, and I don't know if it's something on the code level or something on a system level. I suspect system level, but it's worth a shot here. I've tried the application on about 7 or at programs and it works fine. Someone else tried it on 4, only one of which worked, the other 3 all failed with the same error on SQLConnect. Something to the effect of "No default driver or incorrect driver name specified". It, however, is using the exact same connection string as the machines it did work on, and the driver appears to exist on the system (XP and 2k, installs it with the OS.)

Is there anything I'm not thinking of that I should be altering or searching for that could be causing the problems, or is there just an ODBC problem on the failing systems? Is there anything, ODBC-wise, that could affect such a thing?

 

[Cagliostro]

Try to use SQLDriverConnect instead of SQLConnect. When you're using SQLConnect, you must have a data source set up in the ODBC

Ion Filipski

 

[JohnMalv]

Sorry, I'm actually using SQLDriverConnect, I was going from memory when I sayd SQLConnect .

returncode = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)szdatabase, (short)(strlen(szdatabase)+1), NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

Thanks for catching that though!

 

[Cagliostro]

Ok, the syntax of DSN connection string is
"driver={SQL Server};uid={yourUserName};pwd={password};server={servername};source={databasename}"

see variable szdatabase

Ion Filipski

 

[JohnMalv]

The connection string format in use is:

DRIVER={SQL Server};SERVER=localhost;DATABASE=vrbtest;UID=sa;PWD=admin;

This has worked on most systems I've tried (and modified for a remote SQL server). However, it has also failed on several.

 

[Cagliostro]

SERVER=localhost may be wrong.
1. You should look with EnterpriseManager for the names of servers. Name of a server does not mean at all name of a computer or some host.
2. If you would access some remote server, the SQL Server Agent should be started on the remote computer as well as SQL Server. In the combo box "Server:" of SQL Server service manager you may see all the accessible servers.

Ion Filipski

 

[JohnMalv]

That's an interesting point you bring up about SERVER= not being the host machine, as well as the agent. That could become a problem later on.

However, the problem right now I believe occurs BEFORE it attempts to connect to the server (if it tries to connect to one that isn't valid, it stalls for a while then reports a timeout and unable to connect. The "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" error seems to involve communication with the ODBC driver on the local machine before it even tries to communicate with a server.

 

[Cagliostro]

maybe you don't have SQL Server driver on that machine?

Ion Filipski

 

[JohnMalv]

I had thought of that, but I'm pretty sure it's there. I think it even installs with Win2k and WinXP, it's not optional.

The MDAC was upgraded to 2.71 as well on all machines (working and non)

 

[Cagliostro]

what are the operations you do before connecting?

Ion Filipski

 

[JohnMalv]

Currently the order is:

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

SQLDriverConnect(hdbc, NULL, (SQLCHAR*)szdatabase, (short)(strlen(szdatabase)+1, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

then continues with post-initialization stuff from there (actual queries etc.)