1

We're working with a customer that has a SQL Server of unknown vintage running on a server we do not have direct access to. Our only link is via a DSN name that is the same on every client machine, but we do not know the details of that DSN. I mention this because I think there is a driver issue, and I can't tell you what driver it is.

We are using VBA to do some basic SQL reporting. The problem is that when we are fetching data from a table that has a column called "description", any attempt to retrieve data after that in the select statement will cause the RecordSet to return null values even though there is data in the fields. For instance....

SELECT description, one, two, three FROM tNasty

will cause...

DS!description

to print the expected value, but...

DS!one

will return null (and two and three). Now if all we do is re-arrange the fields to...

SELECT one, two, three, description FROM tNasty

Then all the values work fine!

I copied the data to my own machine's Express instance, and connected to it using a connection string instead of the DSN. Then it works fine no matter what the order is. This seems to imply it's something in that DSN.

Has anyone seen anything like this before? Or have suggestions on how to figure out what those DSN settings may be without being able to see them through the GUI?

Maury Markowitz
  • 9,082
  • 11
  • 46
  • 98
  • 1
    My guess is that "description" is a TEXT field or similar. There are definitely known issues along exactly the lines you're describing; certainly I've seen them personally with the ADO driver. The recommendation--officially, from Microsoft themselves--is always to retrieve TEXT columns *last* in the list. I've definitely discussed this on SO before; I'll try to dig up a link. – Matt Gibson Mar 31 '16 at 14:36
  • 1
    You could also do a query to determine which version of sql server is being used. select @@version – Sean Lange Mar 31 '16 at 14:44
  • Here: does [this look like it could be the issue](http://stackoverflow.com/a/32993509/300836)? Is "description" a TEXT, BLOB, or other large type? – Matt Gibson Mar 31 '16 at 14:46
  • You could try putting brackets around description. – Wes Palmer Mar 31 '16 at 14:58
  • Well that sounded promising, but the field is NVARCHAR(max-1), is that the same thing? – Maury Markowitz Mar 31 '16 at 17:30
  • Ahhh, according to another thread on SO, NVARCHAR(MAX) *is* the same thing as TEXT, both are stored out-of-table and so I assume both have the same problem. However, this machine in question is very new, with Windows 8 and the latest Excel, so one expects the ADO driver is likewise new. So if this is the bug, guess it's still in there! – Maury Markowitz Mar 31 '16 at 17:34
  • 1
    Yeah, `(N)VARCHAR(MAX)` would be the modern equivalent of the now-deprecated `TEXT` type. I can't comment on which bit of the stack might be screwing things up -- maybe it really is related to your customer's elderly server -- but hopefully the tips on dealing with the problem will lead you to a solution, at least. – Matt Gibson Mar 31 '16 at 20:44

0 Answers0