ODBC--call failed #241

[jamier01]

Hi,

We're currently using Access 2013 to manage some data and it need to reference some other information from SQL Server. This normally isn't an issue.
I've written a view that we can use as a linked table to pull selected data into Access but some users (not all) are getting the error: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (#241).

The following query to create the SQL view works for everyone but pulls through all 900k items to the linked table in Access:

SELECT linkRef, DateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL

What we want is the latest date for each reference (~196k items) so the following should work and does for me and a couple of others but not everyone:

SELECT linkRef, MAX(DateUpdated) AS DateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL
GROUP BY linkRef

The aggregate seems to cause the error. I've also tried SELECT linkRef, CAST(MAX(DateUpdated) AS DATETIME2(0)) AS DateUpdated as well as SMALLDATETIME. The data type of DateUpdated is DATETIME2(0) so we shouldn't have conversion issues and all dates in this field appear to be valid.

We have an inefficient work-around (this query is only part of a larger group of queries to create a more complicated view but is the cause of our woes) but I'd like to understand why this is happening to prevent it in the future.

Thanks

 

[Joe4]

Maybe it does not like the fact that you are naming the Aggregate the same as the original field name (especially since you are also using it in your WHERE clause).
What if you try something like:

SELECT linkRef, MAX(DateUpdated) AS MaxDateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL
GROUP BY linkRef

 

[jamier01]

Maybe it does not like the fact that you are naming the Aggregate the same as the original field name (especially since you are also using it in your WHERE clause).

Hi Joe, Thanks for the quick reply. I made the change and it has been tested (I can't replicated the error unfortunately) but it didn't solve the issue.

 

[Joe4]

What we want is the latest date for each reference (~196k items) so the following should work and does for me and a couple of others but not everyone:

So, it is working on some computers but not others?
On a computer where it does work, go to the VB Editor and check with VBA Library References are selected.
Now, go to a computer where it doesn't work and do the same. Are all the same references selected on that computer.

 

[jamier01]

Yes, it works for some people (including me).

There are a few differences with the VBA reference libraries (we were looking at that after a Google, yesterday). My colleague disabled the ones I don’t have and that didn’t change anything. I also enabled the additional references he had but my linked table still works.

There is a difference in the installation – I have 64 bit whereas my colleagues had 32 bit. We upgraded one of my colleagues to 64 bit yesterday and that didn’t have an impact, either.

As we are remotely working (makes diagnostic a bit harder) I also used RDP to work from a colleague’s PC; although under his log on it wouldn’t work, when I looked at the data using my log-on it did. He also RDP’d onto my computer to do the same with the same result (worked for me, not him).

Early on in the diagnostic process, on the SQL Server side I gave the testers db_owner rights (same as me) so I also don't think this is an issue.

I appreciate your time.

 

[Joe4]

There are a few differences with the VBA reference libraries (we were looking at that after a Google, yesterday). My colleague disabled the ones I don’t have and that didn’t change anything. I also enabled the additional references he had but my linked table still works.

Did you try enabling all the references on their computer (so that they have all the same references as a computer that does work)?

It sounds like you have a lot of variables in play here. That could make it quite difficult to pinpoint the issue.

 

[jamier01]

Good morning. Yes, we matched the VBA references on both computers and that didn't work.

As a trial we also linked to the view from Excel with PowerQuery (we're on Office 2013 so are using the add-in rather than the native get and transform data in later versions). Again, I can pull through all the data but my colleague can't. As you say, there are a lot of variables!

 

[Joe4]

Yes, without having access to both computers, it is rather difficult to debug.

If you do a Google search on your error message, you can see some other people who had similar problems, and were able to correct them.
I found this thread here: Date conversion error - MS Access front end querying sql back end, and a few people said that removing a certain VBA Reference Library, then adding it back in worked for them.

So I would recommend searching the Web, and see if any of the suggestions help. If you do figure it out, please post back here with your solution.

 

[jamier01]

Good morning,
To close this off, I discovered that there was a conflict on a data type between SQL and MS Access. MS Access doesn't like the 'BIGINT' data type.
Although this wasn't in the query I was looking at, it was in a parent query that created this data within SQL. I still don't fully understand the reason that the BIGINT was affecting the date but there are multiple conversions between different data sources within the SQL.
Thank you for your help.

 

[Joe4]

Glad you figured it out, and thanks for reporting it back to us.