3146 ODBC Call Failed Error in only some production instances

OK - this is a very odd problem and everyone I have tried to get help from up to this point has thrown up their hands and given up. Unfortunately, that is just plainly not an option for me.

I have an Access front end to a SQL back end. I go through a process when the user logs on where I look at their settings and grab the data that is pertinent to them from the SQL database and load it locally into local Access back end tables. Are you with me so far? So then they can edit and add records quickly. Once they have completed their work locally they "upload" their data back to the SQL server. I utilize stored procedures to do this as I have users globally. It has taken me many many months to perfect this architecture. It really works very slick and the stored procedures give me a very quick turn around. I went through extensive UAT process with this as well.

I am now in production with this new version of my software. When certain users choose to upload their data back to the SQL Server they get the dreaded "3146 ODBC Call Failed" error message.

Because of other error trapping and other evidence on the server of what's going on - I can pinpoint the stored procedures that are causing this problem. I have checked their ODBC Connection Strings and everything is fine in there.

This is not happening everywhere! I have plenty of users who are running fine! I can't recreate this problem in my own test environment - so I can't step through code to figure out what exactly this error is doing. When I take the users local data file and put it in my test environment - everything runs fine.

All of the other stored procedures that run to load data - lock and unlock data on the SQL Server, etc.... - are running fine - so this can't really be an ODBC error. I have checked the odbc32.dll file on their machines to be sure they are running a current version, and that is fine. All users are running on Windows XP.

As you can probably tell I am very frustrated. I have users who are using the application just fine. And, of course, the ones who are getting the errors (making the application useless) are key users. I can't recreate the errors in order to fix them. I can't even trap the error! It comes up each time as 3146 ODBC Call Failed. That's so generic and tells me nothing. If it was a data issue - like a primary key constraint, etc.... I would be able to recreate the error with their local data file.

I have now spent 3 SLEEPLESS NIGHTS trying to figure this out. I have used NetMeeting to dial in to the user's computer to watch and make sure they're not making some odd user error. They aren't. I'm going insane. Does anyone out there have any ideas? Any thoughts about pointing me in a different direction?

Thank you in advance for any help or direction you can provide!

PamelaDV

Pamela,

This is when I call Microsoft. For only $245 they will fix this. The last time I called, it was for something like what you are dealing with. That is, it was weird. I ended up with 3 VERY well qualified techs working together in two separate locations. They were extremely helpful, and two of them worked overtime (without pay as they are on salary) to get the problem resolved. They have the tools, knowledge, and resources to be able to fix these things quickly. Well, that's quickly compared to how long it would take us.

I'm sorry, but other than calling Microsoft, I don't know of any other ideas. But I do have one question. The PCs where this fails, does it fail on all uploads, just certain uploads, or occasional uploads?

Vic

I have several machines where the upload doesn't work at all. I have a couple of machines where one will work and then subsequent ones won't. And then I have some machines where part of the upload works (it's two stored procedures that run - one runs and the other does not).

It sounds like each machine is consistant in what it will run, and what it won't run. Is that correct?

I took your advice and got Microsoft on the line. They were very helpful. They showed me how to use the ODBC Tracer on the user's machine in order to better determine exactly what was going wrong, when, and why. VERY helpful tool and it's great.

Because this was my first call and falls within my warranty - I got this one as a freebie - but it would have been worth the $245 for sure.

Could you share with us what this ODBC tool is, where to find it, and where to find documentation on how to use it?
I'm assuming all your workstations are now working, or at least it looks like you can solve them all?

Golfer Guy - I will share and put it in a separate post so it's easier to find while searching. Also - I have another issue I'm hoping you can help with and will post that in a separate post as well.

As a side note - I did finally discover what the problem was with the aide of the ODBC Tracker - it showed me exactly what statement was failing - the parameter, etc....

The problem is that many countries around the world store their numbers in a different way. Instead of storing a decimal with a period - for instance .7 - their decimals are stored with a comma - ,7. Thus my problem - sending a decimal to my global server as ,7 instead of .7.

This is all based on their regional settings - so when I would bring their local data file over to my machine where the regional settings are different - it would work because it would send it to the global server with a period instead of a comma.

So now I know what the problem is - I have several different options for attacking it - more on that in a different post. :-)