Access 2003, ADO, & SQL Server 8

[dBjason]

Hello,


I have a stored procedure which generates a table w/ financial information. When I execute it in query analyzer, it runs great (although it takes 2 hours). Now I'm trying to call it from an Access application. I'm calling it like this:

' Open ADO connection
strCnxn = "Provider='sqloledb';Data Source='SDSQL2';" & _
        "Initial Catalog='TimberlineWarehouse';Integrated Security='SSPI';"
Set CnXn = New ADODB.Connection
CnXn.Open strCnxn

'Execute stored procedures CorporateAuditFootnote for Timberline & Yardi
CnXn.Execute "EXEC dbo.CorporateAuditFootnoteInitialize_UnconsolidatedYardi"
CnXn.Execute "EXEC dbo.CorporateAuditFootnote_UnconsolidatedYardi '" & FiscalYear & "'"

When I run the code, it does call the stored procedure. The problem is, it only runs for about 3 minutes and doesn't populate a tenth of the information it normally would if I run the exact same stored procedure in SQL Server Query Analyzer. The first SP runs just fine (it initializes the table), but the second one seems to bomb out after three minutes. I don't get any error messages, it just seems to quit as though it finished on it's own.

Any ideas why it'll run fine through Query Analyzer, but not when called through ADO???

Any ideas would be GREATLY appreciated.

Thanks,
Jason

 

[cmmrfrds]

strCnxn.CommandTimeout = 0

Try setting the connection time out to zero - unlimited time.

 

[dBjason]

Added it --

' Open ADO connection
strCnxn = "Provider='sqloledb';Data Source='SDSQL2';" & _
        "Initial Catalog='TimberlineWarehouse';Integrated Security='SSPI';"
Set CnXn = New ADODB.Connection
CnXn.Open strCnxn
CnXn.CommandTimeout = 0

'Execute stored procedures CorporateAuditFootnote for Timberline & Yardi
CnXn.Execute "EXEC dbo.CorporateAuditFootnoteInitialize"
CnXn.Execute "EXEC dbo.CorporateAuditFootnote '" & FiscalYear & "'"
CnXn.Execute "EXEC dbo.CorporateAuditFootnoteExclusion"

'Close ADO connection
CnXn.Close

But it still quit early. Could it be anything else?

 

[dBjason]

So, I tried converting to DAO. Only the DAO version does even less than it's ADO counterpart (see below).

' Open DAO
Set WrkSpc = CreateWorkspace("", "admin", "", dbUseODBC)
Set dbs = WrkSpc.OpenDatabase("TimberlineWarehouse", dbDriverNoPrompt, True, "ODBC;Driver={SQL Server};Server=SDSQL2;Database=TimberlineWarehouse;Trusted_Connection=Yes")

dbs.Execute "EXEC dbo.CorporateAuditFootnoteInitialize_UnconsolidatedYardi"
dbs.Execute "EXEC dbo.CorporateAuditFootnote_UnconsolidatedYardi '" & FiscalYear & "'"

'Close DAO
dbs.Close
Set dbs = Nothing

What the hell?!?!

I've never bumped into anything like this before. Is it some wierd Access bug that terminates longer-running SQL Stored Procedures?


ANY help is greatly appreciated!!

 

[cmmrfrds]

I believe I gave you the wrong property or insufficient. Try the
CnXn.ConnectionTimeout

actually you may have to set both to 0.

The connection will timeout by default if it does not receive any response in 15 seconds which is of shorter duration than the commandtimeout which default is 30 seconds where the command has not completed the data retrieval.

 

[dBjason]

I tried using both the Connection & Command timeouts; come to find out one of my sub stored procedures was returning a warning (row exceeds 8060 bytes) and that was causing the hitch. I had to set up a job on the server to catch it.

Thanks cmmrfrds, for your help.

 

[cmmrfrds]

Typically, ADO is going to return a high level error that may not be very descriptive or even misleading. Probably a good idea to wrap ADO code in an error handler to look at all the errors returned.

Public Function DoSomeADO()
On Error GoTo ErrHandler
Dim cn As New ADODB.Connection

Some ADO code..........

Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.Description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.Description
Debug.Print "err source = "; Err.Source
Next
End Function