SQL Server 2005, Varchar Field Type returning Null Value (Bug) 1

[ByteMyzer]

I have an SQL Server 2005 database with a table that contains a field set as type varchar(8000). I am running a Microsoft Access 2003 front-end application which reads the database, using DAO. One function in the FE opens a snapshot type of DAO.Recorset to read this table, and here's where it gets weird:

The bug has to do with repeated reads of this field on the same record. If I attempt the following piece of code, here is what happens:

Dim rs As DAO.Recordset

' cn: a DAO Connection through an ODBCDirect Workspace
'     to the SQL Server Database
Set rs = cn.OpenRecordset("SELECT T1.MyField FROM Mytable AS T1", dbOpenSnapshot)

' The first time, the program returns the value
Debug.Print rs.Fields("MyField").Value
' The second time, and any successive times,
' the program returns  Null
Debug.Print rs.Fields("MyField").Value
' NOTE: This is without moving to any other records


rs.Close
Set rs = Nothing

My program requires populating three variables with the field value. So, my short term solution is to set a placeholder variant with the field value, then fill the three variables from the placeholder variant.

Has anyone else come across this bug?

 

[genomon]

Why in the name of all that is holy would you have 8000 characters in a single variable?

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[mrdenny]

I would assume that bug is in the frontend rather that SQL Server as I can select the same field all day long if I'd like.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ByteMyzer]

genomon,

ntext (memo) fields are not searchable with string comparators, and the next largest field type you can use is varchar(8000).


mrdenny,

The bug seems to be with DAO recordsets in MS Access 2003. ADODB Recordsets do not exhibit this adverse behavior. The catch-22 here is that DAO recordsets are faster than ADO at retrieving data from an SQL Server datasource.

 

[mrdenny]

I'd report this to

http://connect.microsoft.com

either way and have them take a look at it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLDenis]

>>ntext (memo) fields are not searchable with string comparators, and the next largest field type you can use is varchar(8000).

That is not true, you said you were using SQL Server 2005, then use what is available!
use varchar(max), goes to 2GB and does not have the ntext and text nasty limitations

VFP 6? shouldn't you use 9 by now? Or better yet dump it since MS abandoned the product anyway

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ByteMyzer]

Ahhh, that did the trick. Not only do I have more room, but I can do criteria searching/filtering, AND that nasty bug does not manifest itself. I am modifying the field in the production database to type varchar(max) right now. Thank you, SQLDenis.

VFP 6? What is that?

 

[genomon]

VFP is Visual Fox Pro, not sure what it has do do with the Access frontend in the original post, though...

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.