ODBC MS SQL Server Non-Standard? Or Code Flaw?

[JohnMalv]

I have a rather odd situation:

I have an application writting in C++ (MSVC++ 6), which uses ODBC to handle some database querys. I am using standard SQLDriverConnect, SQLExecDirect, SQLFetch, and SQLGetData. I do the fetch's while fetch returns a valid result, and then do GetData (searching by column name with (getting the column number via SQLDescribeCol given a column name.) I get all the fields I need and then do another fetch until there are none left. Pretty basic. (the results being generated by a simple SQL query through SQLExecDirect, "SELECT * FROM [tablename] where [something]=[somethinglese]."

This method returns very proper results from MySQL, PostgreSQL, and Microsoft Text ODBC driver, however when used with MS SQL Server 2000 (SP2), I keep getting a situation where it's pulling one or two fields and filling every GetData request with the same data from those like there was no new data to obtain.

The obtaining of column numbers seems to be accurate (I have it logging that.) It gets different numbers for each field of inquiry, and since it works for other database, I have no reason to believe that part is false.

That said, is there anything "special" I should be doing for MSSQL that wouldn't need to be done for the other databases, or is there a fundamental flaw in what I'm doing that all the other formats could ignore? I'm really at a loss for what could be wrong.

I spared the code pastes if someone has an answer with out it (it could be rather lengthy since a lot of things get passed through objects, etc, so you'd probably need the full class to understand some things, and it's not a small application .

Thanks in advance for any attempts to help on this...it's driving me quite crazy!

~JohnMalv

 

[donutman]

I'd try installing sp3a and see if that helped.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JohnMalv]

Sadly, that didn't seem to help (Thanks for the try though, I had forgotten to patch it...but I'm still pulling the same problem data (repeated data and blank data, using the same app that works fine using the above mentioned alternate DBS systems.)

Any other ideas?

 

[donutman]

I would still focus on the drivers. How about MDAC 2.71 on the workstation?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Just a long shot... do the tables you're pulling from have proper primary keys? You can get strange behavior if there's no primary key defined, making the DB engine hand you the wrong record in strange circumstances. I've seen it...

 

[JohnMalv]

Hmm, I should probably move this to a new thread now, as I have a little more information. I'll post it here for now to see if you have more info, if not, I'll put it on a new thread.

It seems the point of faulre is SQLFetch. It's returning SQL_NO_DATA. The question is why? Since it's working fine on the other databases, what could be causing an SQL_NO_DATA result when using MSSQL? Is there any special/typical means of preventing a NO_DATA reply (either on the server config side, or the application side?

For reference I installed MSSQL and SP3a for both the server and the workstation now.

 

[JohnMalv]

Just as an update, I tried setting a primary key for column 1, but it didn't help.

Still getting SQL_NO_DATA for the SQLFetch(), but ONLY for MSSQL, not MySQL, MS Text, or PostgreSQL...

 

[ESquared]

Maybe it's time to post to the C++ forum as well?

Have you tried any other ODBC method of reading from the MSSQL database, such as from Access or at a command prompt with the osql utility, just to help isolate where the problem is?

 

[JohnMalv]

Yes, perhaps I'll post on the C++ forum.

I just tried osql, and the query (same one being executed (successfully) in the application is being executed successfully there too. It looks like good data. The SQL_NO_DATA on SQLFetch is definitely the problem...