Hi Everyone,
I am fairly new to MS-SQL so excuse my ignorance if I have any.
I am trying to add and link a new table to an existing database. I can add the database and colums and add some data into it, and even run a query on it. However, if I try to run a query using the new table and and existing table (linked) I get the following error:
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve collation conflict for equal to operation. (#446)
Any ideas?
my Server and SQL specs: SBS2003 & MSSQL ver 8.0what's the query you are executing?|||A Simple or Single Join Query|||post it here.|||Here is the query :
SELECT ChangeCustidLog.NewCustid, Customer.CustId, Customer.CustName
FROM ChangeCustidLog INNER JOIN Customer ON ChangeCustidLog.NewCustid = Customer.CustId;|||try this:
SELECT ChangeCustidLog.NewCustid, Customer.CustId, Customer.CustName
FROM ChangeCustidLog
INNER JOIN Customer ON
ChangeCustidLog.NewCustid COLLATE Latin1_General_CI_AS =
Customer.CustId COLLATE Latin1_General_CI_AS;|||thank you,
the query works fine with the 'collate' phrase, when run in SQL server. I am having trouble when the query runs in Access (and thats where the linked tables are)..
is there a way to fix this 'collate' problem??
Showing posts with label odbc-call. Show all posts
Showing posts with label odbc-call. Show all posts
Monday, March 12, 2012
ODBC-Call Failed - Access
We have this fairly simple Access database with a link table to a view in an SQL Server. The Select Query in Access is a simple select from this view with a view criteria set. Nothing fancy. What is driving is moderately crazy is that this Select Query in Access seems to work fine for months and than all of a sudden (same query performed around the same time with about the same workload on the SQL-Server) it responses with the helpful "ODBC-Call Failed" message. Closing the Access database, taking a coffee, coming back againg and try the exact same Select Query normally works, no "ODBC-Call Failed" message.
It not happens always, mosty we're fine.
When it happens it happens for all subsequent queries tried. We have to close and reopen the Access database and pray to the good Lord that it works.
We would be very interested in someone with a solution to this problem.
What ODBC driver and version are you using and to what version of SQL Server are you connecting?
You might want to try out a different ODBC driver in your DSN to see if that resolves the problem.
Vaughn
|||We are having the same problem using Access 2003 and SQL Server 8.0. I'm using the standard SQL Server driver in the DSN. Any suggestions?|||Reading a bit it sounds like the "ODBC-Call Failed" message is coming from Access. I found this KB article that describes a similar problem for DB2 and suggests updating Jet as the solution.
http://support.microsoft.com/kb/227413/
I see you're using Access 2003 so this isn't likely the cause. Have you tried ODBC tracing through the ODBC Data Source Administrator control panel? Correlating that log to when the failure occurs might provide more clues as to the cause.
Vaughn
|||The problem is that I don't know what is the cause of the error. Is it a timeout on the client (MSAccess) side? Is it a deadlock situation? Is it table lock that couldn not be caried out? etc. Neither SQL Server nor MSAccess seem to bother to explain what's wrong. If if happend all the time I would look at replacing drivers but now I feel this should be somewhere else. Btw ODBC tracing gives me a bunch data wich not really eplains what 's going on.|||So, this might have been a timeout afterall. In design view I changed the properties of the view from Snapshot to "Dynaset (Inconsistent Updates)" (although MS fails to describe in the online help what that exactly means). I would go with Snapshot as there is no need to update the data, but due to the fairly large amount of records, MS recommends using Dynaset. Furthermore the Timeout of the view was changed to 900 seconds (15min), if our SQL Server is doing some real work this is probably not enough. It would be helpfull that instead of ODBC-Call failed, MS-Access would report something like "Timeout expired", but there probably is a deeper reason why this is not possible.
Leaving the question: Dynaset, Dynaset (Inconsistent Updates), Snapshot? As an experienced P/SQL and T/SQL I have no clue what MSAccess is talking about here. The documentation is pretty dodgy also. I fact I only need to read the data (it's for reporting).
|||I found some information that may shed some light on the differences at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproRecordsetType_HV05187937.asp
Basically, a Dynaset allows you to make changes in controls bound to a single table or tables with a one-to-one relationship. You can also edit the fields from a table on the "many" side of a one-to-many relationship.
A Dynaset (Inconsistent) allows you to update all data fields.
ODBC-Call Failed - Access
We have this fairly simple Access database with a link table to a view in an SQL Server. The Select Query in Access is a simple select from this view with a view criteria set. Nothing fancy. What is driving is moderately crazy is that this Select Query in Access seems to work fine for months and than all of a sudden (same query performed around the same time with about the same workload on the SQL-Server) it responses with the helpful "ODBC-Call Failed" message. Closing the Access database, taking a coffee, coming back againg and try the exact same Select Query normally works, no "ODBC-Call Failed" message.
It not happens always, mosty we're fine.
When it happens it happens for all subsequent queries tried. We have to close and reopen the Access database and pray to the good Lord that it works.
We would be very interested in someone with a solution to this problem.
What ODBC driver and version are you using and to what version of SQL Server are you connecting?
You might want to try out a different ODBC driver in your DSN to see if that resolves the problem.
Vaughn
|||We are having the same problem using Access 2003 and SQL Server 8.0. I'm using the standard SQL Server driver in the DSN. Any suggestions?|||Reading a bit it sounds like the "ODBC-Call Failed" message is coming from Access. I found this KB article that describes a similar problem for DB2 and suggests updating Jet as the solution.
http://support.microsoft.com/kb/227413/
I see you're using Access 2003 so this isn't likely the cause. Have you tried ODBC tracing through the ODBC Data Source Administrator control panel? Correlating that log to when the failure occurs might provide more clues as to the cause.
Vaughn
|||The problem is that I don't know what is the cause of the error. Is it a timeout on the client (MSAccess) side? Is it a deadlock situation? Is it table lock that couldn not be caried out? etc. Neither SQL Server nor MSAccess seem to bother to explain what's wrong. If if happend all the time I would look at replacing drivers but now I feel this should be somewhere else. Btw ODBC tracing gives me a bunch data wich not really eplains what 's going on.|||So, this might have been a timeout afterall. In design view I changed the properties of the view from Snapshot to "Dynaset (Inconsistent Updates)" (although MS fails to describe in the online help what that exactly means). I would go with Snapshot as there is no need to update the data, but due to the fairly large amount of records, MS recommends using Dynaset. Furthermore the Timeout of the view was changed to 900 seconds (15min), if our SQL Server is doing some real work this is probably not enough. It would be helpfull that instead of ODBC-Call failed, MS-Access would report something like "Timeout expired", but there probably is a deeper reason why this is not possible.
Leaving the question: Dynaset, Dynaset (Inconsistent Updates), Snapshot? As an experienced P/SQL and T/SQL I have no clue what MSAccess is talking about here. The documentation is pretty dodgy also. I fact I only need to read the data (it's for reporting).
|||I found some information that may shed some light on the differences at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproRecordsetType_HV05187937.asp
Basically, a Dynaset allows you to make changes in controls bound to a single table or tables with a one-to-one relationship. You can also edit the fields from a table on the "many" side of a one-to-many relationship.
A Dynaset (Inconsistent) allows you to update all data fields.
ODBC-Call Failed - Access
We have this fairly simple Access database with a link table to a view in an SQL Server. The Select Query in Access is a simple select from this view with a view criteria set. Nothing fancy. What is driving is moderately crazy is that this Select Query in Access seems to work fine for months and than all of a sudden (same query performed around the same time with about the same workload on the SQL-Server) it responses with the helpful "ODBC-Call Failed" message. Closing the Access database, taking a coffee, coming back againg and try the exact same Select Query normally works, no "ODBC-Call Failed" message.
- It not happens always, mosty we're fine. When it happens it happens for all subsequent queries tried. We have to close and reopen the Access database and pray to the good Lord that it works.
We would be very interested in someone with a solution to this problem.
What ODBC driver and version are you using and to what version of SQL Server are you connecting?
You might want to try out a different ODBC driver in your DSN to see if that resolves the problem.
Vaughn
|||We are having the same problem using Access 2003 and SQL Server 8.0. I'm using the standard SQL Server driver in the DSN. Any suggestions?|||Reading a bit it sounds like the "ODBC-Call Failed" message is coming from Access. I found this KB article that describes a similar problem for DB2 and suggests updating Jet as the solution.
http://support.microsoft.com/kb/227413/
I see you're using Access 2003 so this isn't likely the cause. Have you tried ODBC tracing through the ODBC Data Source Administrator control panel? Correlating that log to when the failure occurs might provide more clues as to the cause.
Vaughn
|||The problem is that I don't know what is the cause of the error. Is it a timeout on the client (MSAccess) side? Is it a deadlock situation? Is it table lock that couldn not be caried out? etc. Neither SQL Server nor MSAccess seem to bother to explain what's wrong. If if happend all the time I would look at replacing drivers but now I feel this should be somewhere else. Btw ODBC tracing gives me a bunch data wich not really eplains what 's going on.|||So, this might have been a timeout afterall. In design view I changed the properties of the view from Snapshot to "Dynaset (Inconsistent Updates)" (although MS fails to describe in the online help what that exactly means). I would go with Snapshot as there is no need to update the data, but due to the fairly large amount of records, MS recommends using Dynaset. Furthermore the Timeout of the view was changed to 900 seconds (15min), if our SQL Server is doing some real work this is probably not enough. It would be helpfull that instead of ODBC-Call failed, MS-Access would report something like "Timeout expired", but there probably is a deeper reason why this is not possible.
Leaving the question: Dynaset, Dynaset (Inconsistent Updates), Snapshot? As an experienced P/SQL and T/SQL I have no clue what MSAccess is talking about here. The documentation is pretty dodgy also. I fact I only need to read the data (it's for reporting).
|||I found some information that may shed some light on the differences at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproRecordsetType_HV05187937.asp
Basically, a Dynaset allows you to make changes in controls bound to a single table or tables with a one-to-one relationship. You can also edit the fields from a table on the "many" side of a one-to-many relationship.
A Dynaset (Inconsistent) allows you to update all data fields.
ODBC--call failed
I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
records in the linked table they come up fine, but after about 30
seconds it dings and i get the pop up ODBC--call failed. If i close the
form and reopen it, the records are there again, but the connection
drops again in another 30 seconds.
I'm stumped here because my connection does work, it just keeps
dropping.
Any help or ideas would be greatly appreciated.
Thanks!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
What happens if after you open the form, you go to the last
record and then go about and do whatever with the form? Does
the same thing still happen?
ODBC call failed can happen for a lot of different reasons.
A good way to track them down is to turn on ODBC tracing to
look for specific errors. You would want to make sure to
turn it back off after you get the error as it will really
slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Thu, 13 Jan 2005 13:19:15 -0800, vespo
<anonymous@.devdex.com> wrote:
>
>I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
>via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
>records in the linked table they come up fine, but after about 30
>seconds it dings and i get the pop up ODBC--call failed. If i close the
>form and reopen it, the records are there again, but the connection
>drops again in another 30 seconds.
>I'm stumped here because my connection does work, it just keeps
>dropping.
>Any help or ideas would be greatly appreciated.
>Thanks!
>Vicki
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Thanks Sue!
That ODBC Trace really helped! It turns out it is an issue with Access
97 where even if you bracket an alias fieldname in a SQL View, it
considers reserved words as reserved words and can't bring them across.
I was trying to enable my client the usage of their old Access 97 forms
and reports by linking to the data i migrated into SQL, so to do that i
had created a view that mimicked their old fields names ("Phone Number",
"Date Updated", etc) and had no problems staying connected when i tried
it on my server (which has Access 2000) but unfortunately my client
isn't ready to upgrade their Access version.
Oddly though -- reports will work in Access 97 using the reserved field
name, it's just the forms and opening the table directly which caused
the ODBC to drop.
Thanks again!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
records in the linked table they come up fine, but after about 30
seconds it dings and i get the pop up ODBC--call failed. If i close the
form and reopen it, the records are there again, but the connection
drops again in another 30 seconds.
I'm stumped here because my connection does work, it just keeps
dropping.
Any help or ideas would be greatly appreciated.
Thanks!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
What happens if after you open the form, you go to the last
record and then go about and do whatever with the form? Does
the same thing still happen?
ODBC call failed can happen for a lot of different reasons.
A good way to track them down is to turn on ODBC tracing to
look for specific errors. You would want to make sure to
turn it back off after you get the error as it will really
slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Thu, 13 Jan 2005 13:19:15 -0800, vespo
<anonymous@.devdex.com> wrote:
>
>I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
>via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
>records in the linked table they come up fine, but after about 30
>seconds it dings and i get the pop up ODBC--call failed. If i close the
>form and reopen it, the records are there again, but the connection
>drops again in another 30 seconds.
>I'm stumped here because my connection does work, it just keeps
>dropping.
>Any help or ideas would be greatly appreciated.
>Thanks!
>Vicki
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Thanks Sue!
That ODBC Trace really helped! It turns out it is an issue with Access
97 where even if you bracket an alias fieldname in a SQL View, it
considers reserved words as reserved words and can't bring them across.
I was trying to enable my client the usage of their old Access 97 forms
and reports by linking to the data i migrated into SQL, so to do that i
had created a view that mimicked their old fields names ("Phone Number",
"Date Updated", etc) and had no problems staying connected when i tried
it on my server (which has Access 2000) but unfortunately my client
isn't ready to upgrade their Access version.
Oddly though -- reports will work in Access 97 using the reserved field
name, it's just the forms and opening the table directly which caused
the ODBC to drop.
Thanks again!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
ODBC--call failed
I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
records in the linked table they come up fine, but after about 30
seconds it dings and i get the pop up ODBC--call failed. If i close the
form and reopen it, the records are there again, but the connection
drops again in another 30 seconds.
I'm stumped here because my connection does work, it just keeps
dropping.
Any help or ideas would be greatly appreciated.
Thanks!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!What happens if after you open the form, you go to the last
record and then go about and do whatever with the form? Does
the same thing still happen?
ODBC call failed can happen for a lot of different reasons.
A good way to track them down is to turn on ODBC tracing to
look for specific errors. You would want to make sure to
turn it back off after you get the error as it will really
slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Thu, 13 Jan 2005 13:19:15 -0800, vespo
<anonymous@.devdex.com> wrote:
>
>I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
>via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
>records in the linked table they come up fine, but after about 30
>seconds it dings and i get the pop up ODBC--call failed. If i close the
>form and reopen it, the records are there again, but the connection
>drops again in another 30 seconds.
>I'm stumped here because my connection does work, it just keeps
>dropping.
>Any help or ideas would be greatly appreciated.
>Thanks!
>Vicki
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!|||Thanks Sue!
That ODBC Trace really helped! It turns out it is an issue with Access
97 where even if you bracket an alias fieldname in a SQL View, it
considers reserved words as reserved words and can't bring them across.
I was trying to enable my client the usage of their old Access 97 forms
and reports by linking to the data i migrated into SQL, so to do that i
had created a view that mimicked their old fields names ("Phone Number",
"Date Updated", etc) and had no problems staying connected when i tried
it on my server (which has Access 2000) but unfortunately my client
isn't ready to upgrade their Access version.
Oddly though -- reports will work in Access 97 using the reserved field
name, it's just the forms and opening the table directly which caused
the ODBC to drop.
Thanks again!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
records in the linked table they come up fine, but after about 30
seconds it dings and i get the pop up ODBC--call failed. If i close the
form and reopen it, the records are there again, but the connection
drops again in another 30 seconds.
I'm stumped here because my connection does work, it just keeps
dropping.
Any help or ideas would be greatly appreciated.
Thanks!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!What happens if after you open the form, you go to the last
record and then go about and do whatever with the form? Does
the same thing still happen?
ODBC call failed can happen for a lot of different reasons.
A good way to track them down is to turn on ODBC tracing to
look for specific errors. You would want to make sure to
turn it back off after you get the error as it will really
slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Thu, 13 Jan 2005 13:19:15 -0800, vespo
<anonymous@.devdex.com> wrote:
>
>I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
>via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
>records in the linked table they come up fine, but after about 30
>seconds it dings and i get the pop up ODBC--call failed. If i close the
>form and reopen it, the records are there again, but the connection
>drops again in another 30 seconds.
>I'm stumped here because my connection does work, it just keeps
>dropping.
>Any help or ideas would be greatly appreciated.
>Thanks!
>Vicki
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!|||Thanks Sue!
That ODBC Trace really helped! It turns out it is an issue with Access
97 where even if you bracket an alias fieldname in a SQL View, it
considers reserved words as reserved words and can't bring them across.
I was trying to enable my client the usage of their old Access 97 forms
and reports by linking to the data i migrated into SQL, so to do that i
had created a view that mimicked their old fields names ("Phone Number",
"Date Updated", etc) and had no problems staying connected when i tried
it on my server (which has Access 2000) but unfortunately my client
isn't ready to upgrade their Access version.
Oddly though -- reports will work in Access 97 using the reserved field
name, it's just the forms and opening the table directly which caused
the ODBC to drop.
Thanks again!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Subscribe to:
Posts (Atom)