With SQL Server back end, call failed error (1 Viewer)

[Margarita]

Hello,
I have found this week that after linking to a SQL Server back end I am having trouble running some queries- I get a 'call failed' error (with no error number). I searched the causes for this error and the first thing that I came across said that this may be due to not having the latest Jet 4.0 service pack installed. Without the latest service pack, the linked db can't process some queries with outer joins, it says. Looking at the help article for getting the latest service pack (http://support.microsoft.com/kb/239114), I am confused. The article guides you to find the version of the service pack you currently have installed. Here is what it says:

<H3 id=tocHeadRef>For computers that are running Microsoft Windows XP

If you are running Windows XP SP2, you have a later version of Jet 4.0 than the version that is included with Jet 4.0 SP8. We recommend that you install the latest service pack for Windows XP. For more information about how to obtain the latest service pack for Windows XP, click the following article number to view the article in the Microsoft Knowledge Base:
322389 (http://support.microsoft.com/kb/322389/ )
How to obtain the latest Windows XP service pack
If you cannot install Windows XP SP2, you can use WindowsXP-KB829558-x86-ENU.exe to install Jet 4.0 Service Pack 8 on computers that are running Windows XP. Click the following link to download and to install the correct version of Jet 4.0 Service Pack 8 on computers that are based on the language version of Windows XP that is running on the computer.

</H3>
I have windows XP SP2 but that is where the article is unclear- it says that SP2 means that you already have a later version than the one offered for download in this article. But then it says it is recommded to download the latest Sp for Windows, which is what I'm confused about. Does it mean that I have to get this widows SP3 thing and that will get me the latest Jet 4.0 service pack in the same shot? Or then I won't have to worry about the Jet service pack? I have to request permission for each separate download so I can't just download things left and right to see what works.
Would anyone familiar with the Call Failed error be able to shed some light on what this article means and what is the true solution to the problem that I should be seeking?
Thank you very much.

 

[mdlueck]

I am sorry you are getting into this tangled mess that you discover yourself in.

Quickly glancing through your post, you do not indicate if you are executing the query in focus in Pass-Through mode, or non Pass-Through which means that first Access's query optimizer will mess with your query syntax, then send it to SQL Server which will further mess with the syntax. In other words, toooo many cooks spoil the pot of food.

I would suggest you use exclusively Pass-Through queries with your SQL back end database (BE DB).

The one thing about Pass-Through queries is that you can not join between Access and SQL tables as when the query gets passed through to be executed by the server, it has no connection to the Access tables. If you have queries like that, further optimization is needed.

With Pass-Through mode, then you have far more SQL capabilities at your disposal, and you should refer to the SQL documentation for the exact version you have to know what is at your disposal.

 

[Margarita]

I am sorry you are getting into this tangled mess that you discover yourself in.

Quickly glancing through your post, you do not indicate if you are executing the query in focus in Pass-Through mode, or non Pass-Through which means that first Access's query optimizer will mess with your query syntax, then send it to SQL Server which will further mess with the syntax. In other words, toooo many cooks spoil the pot of food.

I would suggest you use exclusively Pass-Through queries with your SQL back end database (BE DB).

The one thing about Pass-Through queries is that you can not join between Access and SQL tables as when the query gets passed through to be executed by the server, it has no connection to the Access tables. If you have queries like that, further optimization is needed.

With Pass-Through mode, then you have far more SQL capabilities at your disposal, and you should refer to the SQL documentation for the exact version you have to know what is at your disposal.

Hi Michael, this is what I was dreading- having to re-create everything as pass through queries. After reading your post, I started reading up more on pass through queries and what syntax difficulties to expect.
I posted on a similar topic a couple of weeks ago, before doing the SQL Server linking. I wanted to know whether all queries HAVE to be re-written, or whether it was just a extra icing on the cake kind of step to take in order to make things faster, though they could function without being re-written. I see from trial and error now that some queries do in fact continue to run as they are (though just a little slower) and some, like this complicated query with several joins, has to be re-created as pass through.

Luckily, I can get some part-time help in pass-through queries from someone who is far more experienced in SQL Server than I am. So I'm not so worried about that part. However, the concern that I had in my previous post and something I am still uncertain about is the sql which is written directly in my vba procedures. These procedures use both stored queries (those which I have to wait for the expert to re-create as pass-through) and also sql statements that are built directly in the vba code. I am uncertain about the functionality of that vba code now. Will we have to re-write those sql statements in Transact SQL as well?

Thank you for your reply.

 

[mdlueck]

I do not follow your differentiation between the two types of SQL queries you perceive.

It makes no difference if the SQL is created by Access / VBA and sent to the SQL BE DB, or if you are calling a Stored Procedure on the SQL box. Either way Access still issues the query in Pass-Through mode, the latter just is leveraging additional SQL stored on the server itself.

Here is the SQL which still must be written in the VBA code, executed as a Pass-Through query, in order to invoke a SQL Stored Procedure:

Replace pass-through DAO.QueryDef with DAO execution of Stored Procedure Q's - NoCount and SQL EXEC method
http://www.access-programmers.co.uk/forums/showthread.php?t=223414#post1140657

Note: The blue code at the top of that thread provides the context that SQL is being executed in.

 

[Margarita]

I do not follow your differentiation between the two types of SQL queries you perceive.

It makes no difference if the SQL is created by Access / VBA and sent to the SQL BE DB, or if you are calling a Stored Procedure on the SQL box. Either way Access still issues the query in Pass-Through mode, the latter just is leveraging additional SQL stored on the server itself.

Here is the SQL which still must be written in the VBA code, executed as a Pass-Through query, in order to invoke a SQL Stored Procedure:

Replace pass-through DAO.QueryDef with DAO execution of Stored Procedure Q's - NoCount and SQL EXEC method
http://www.access-programmers.co.uk/forums/showthread.php?t=223414#post1140657

Note: The blue code at the top of that thread provides the context that SQL is being executed in.

This is VERY helpful. Thank you. I did not see this thread before.