August 7, 2008 at 4:07 pm
Hello
I have Pass through queries that I can run successfully when opened directly
in the Access Queries window, but when I try to run the queries from VBA code
module I end up with ODBC -- Call Failed. I have increased the timeout
values, I have switched to ADO command where I get a Timeout expired err. I
can run the stored procedures in SQL Server 2005 Query window with no
problems as well. I have run the SQL Server Profiler utility and while trying
to run the pass through queries and the stored procedure that is called will
show up and then end after the time out period of time and then it kicks back
to my VBA code with the ODBC -- call failed message.
The ODBC connection string is:
ODBC;DRIVER=sql server;SERVER=US0223333-WP05;APP=Microsoft Office
2003;WSID=US0223333-WP05;DATABASE=GME;Trusted_Connection=Yes
When I tried ADO the connection string:
Provider=SQLOLEDB.1;Initial Catalog=GME;Data Source=US0223333-WP05;Extended
Properties="Trusted_Connection=yes"
It makes the connection I have forms and views based on recordsets that are
called with the above that open with no problem. There seems to be some
problem with calling the pass through with VBA code. The code I am using
worked just fine with SQL Server 2000. Here is a sample:
szSQL = "DelAllGMEResidentErrs"
Set qd = D.QueryDefs("LineErrorsR")
qd.Connect = D.TableDefs("dbo_Resident_v").Connect
qd.SQL = szSQL
qd.Close
qd.Execute
The stored procedure DelAllGMEResidentErrs on SQL Server 2005 looks like:
USE [GME]
GO
/****** Object: StoredProcedure [dbo].[DelAllGMEResidentErrs] Script
Date: 08/07/2008 12:24:47 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[DelAllGMEResidentErrs]
AS
TRUNCATE TABLE GME_Resident_Errors
Am I missing some setting on SQL Server 2005 that allows it to process the stored procedure when it is passed in with a pass through query???
Any insights any of you may have will be greatly appreciated!
Joe
August 7, 2008 at 8:48 pm
What events are you profiling? I would check the locks and lock timeouts and blocking. You could also run sp_who ro sp_who2 which will show if your process is being blocked.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 9:42 pm
You have to set the returms records to false, since your stored proc doesn't return anything. That's why the ODBC is timing out (it's expecting to get rows back, and that ain't happening.)
I forget if you can use ExecuteNonQuery (or one of the method not trying to return rows) in 2003, but that's also the same idea.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 8, 2008 at 6:33 am
Good catch Matt, I don't enough experience in Access, for which I am thankful, to have experienced that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2008 at 12:25 pm
Thank you for your response.
I created the Pass through with the returns records set to No, but just to be sure I have added the following to my code:
Set qd = D.QueryDefs("Passthru")
qd.Connect = D.TableDefs("dbo_Resident_v").Connect
qd.SQL = szSQL
qd.ReturnsRecords = False
qd.Execute
qd.Close
Alas after adding the explicit qd.ReturnsRecords = False it still fails with the same err.
The weird thing is this code worked against SQL Server 2000 and is now failing. I can't help but think that it is some setting that I am missing on SQL Server 2005.
Any other thoughts you may have are greatly appreciated.
Joe
August 8, 2008 at 12:48 pm
Thank you for your response and the links on how to properly post!
Jack wrote:
What events are you profiling? I would check the locks and lock timeouts and blocking. You could also run sp_who ro sp_who2 which will show if your process is being blocked.
I set up SQL Server profiler to run a trace and then executed my code, while it was running I switched to SQL Server Management tool and ran sp_who it reported on the last line my process:
61 suspended US\jwquinn US0223333-WP05 58 GMETRUNCATE TABLE 0
The status column showed suspended.
The trace for this period of time showed:
SQL:BatchStartingDelAllGMEResidentErrsMicrosoft Office 2003jwquinnUS\jwquinn4740612008-08-08 11:33:23.260
SQL:BatchStartingsp_who
Microsoft SQL Server Management Studio - QueryjwquinnUS\jwquinn2520522008-08-08 11:33:43.480
SQL:BatchCompletedsp_who
Microsoft SQL Server Management Studio - QueryjwquinnUS\jwquinn00002520522008-08-08 11:33:43.4802008-08-08 11:33:43.493
SQL:BatchCompletedDelAllGMEResidentErrsMicrosoft Office 2003jwquinnUS\jwquinn000603194740612008-08-08 11:33:23.2602008-08-08 11:34:23.323
So it shows my DelAllGMEResidentErrs Batch starting, my running of sp_who and then after the timeout the DelAllGMEResidentErrs shows BatchCompleted but without Truncating the table!
Repeating the process with sp_who2 shows the same Suspended status here are the lines out of the Trace window just for the sake of completeness.
Audit Login-- network protocol: Named Pipes
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Microsoft Office 2003jwquinnUS\jwquinn4740612008-08-08 11:44:03.813
SQL:BatchStartingSET TEXTSIZE 2147483647Microsoft Office 2003jwquinnUS\jwquinn4740612008-08-08 11:44:03.813
SQL:BatchCompletedSET TEXTSIZE 2147483647Microsoft Office 2003jwquinnUS\jwquinn00004740612008-08-08 11:44:03.8132008-08-08 11:44:03.813
SQL:BatchStartingDelAllGMEResidentErrsMicrosoft Office 2003jwquinnUS\jwquinn4740612008-08-08 11:44:03.813
SQL:BatchStartingsp_who2
Microsoft SQL Server Management Studio - QueryjwquinnUS\jwquinn2520522008-08-08 11:44:15.377
SQL:BatchCompletedsp_who2
Microsoft SQL Server Management Studio - QueryjwquinnUS\jwquinn0225272520522008-08-08 11:44:15.3772008-08-08 11:44:15.377
SQL:BatchCompletedDelAllGMEResidentErrsMicrosoft Office 2003jwquinnUS\jwquinn000602644740612008-08-08 11:44:03.8132008-08-08 11:45:03.833
Audit LogoutMicrosoft Office 2003jwquinnUS\jwquinn000600204740612008-08-08 11:44:03.8132008-08-08 11:45:03.833
August 12, 2008 at 7:19 am
Just as an update to this topic. I completeley removed the Access VBA code and pass through query that was failing to execute to a new database. The code ran flawlessly. The problem must be coming from the record sets that I am opening in my active database. Somehow the Trunctate table command is being blocked from excuting status shows "Suspended". At this point I can only surmise that SQL Server 2005 does not want a bunch of views and recordsets being open at any one time. I am going to try and narrow it down to the offending recordset.
August 13, 2008 at 5:31 am
Instead of
TRUNCATE TABLE GME_Resident_Errors
can you try
DELETE GME_Resident_Errors
maybe it's a permissions problem.
August 13, 2008 at 10:44 am
Thank you for your response,
Yes, I was previously using
Delete From GME_Resident_Errors_v
Deleting everthing by using my the view that I had set up.
I replaced it with the TRUNCATE table command in an attempt to speed things up, which it did when it worked.
I resolved the issue by closing all forms and dropping all linked views. I then re open everything after all processing is finished.
Maybe I need to rethink opening views, just to have them available, perhaps I should only open a view when it is needed and then close it immediately. I need to research what is the best preactice.
Thank you to everyone who responded. I really apppreciate it.
Joe
August 15, 2008 at 9:34 am
The problem is probably not with Views. It may very well be that having any bound form (bound directly to the table, to a view or to an Access query) may be blocking certain operations - like truncate table where the table is open in a currently bound form. This can create a deadlock.
Todd Fifield
August 15, 2008 at 10:35 am
This is from the SQL Server 2005 Books Online (BOL):
----
You cannot use TRUNCATE TABLE on tables that:
Are referenced by a FOREIGN KEY constraint.
Participate in an indexed view.
Are published by using transactional replication or merge replication.
For tables with one or more of these characteristics, use the DELETE statement instead.
----
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
----
Hope that helps.
April 30, 2009 at 8:30 am
Here is a code example from Access 2003 using pass-through.
First, it deletes the previous invoice information based on the users ID on SQL server.
Then it adds invoice information back to the Pass Through Query view.
Note: the (user_name()) is understood by SQL Server 2005 in the pass-through.
70 strSQL = "DELETE FROM tInvoice WHERE strUser=(user_name()) " & _
"Insert tInvoice ( lngCustomerID, INumber, strCompanyName, strDeliveryPointDesc, strMarketPipeline, TermStart, TermEnd, lngContractNo, AvgOflngVolume, TotalVolume, Days, TPrice, strObligationDesc, strInvoiceAttn, strPhone, dMonth ...... a full screen of SQL follows.
200 Set qdef = CurrentDb.QueryDefs("PTInvoice")
210 qdef.SQL = strSQL
220 qdef.Connect = strODBCPTConnect
230 qdef.Close
240 CodeDb.Execute "PTInvoice", dbSeeChanges
The query PTInvoice in Access 2003 is a Passthrough query, it will reflect the SQL statement above (including dates, customer strings and other information from an Access Form input).
In an office environment of under 100 active employees, this works efficiently. It is very fast.
I would offer that the disadvantage is, this is difficult to troubleshoot.
Microsoft has limited information about this process and even less about the error codes.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply