ERROR: 3704 – Operation is not allowed when the object is closed.

Attempting to access a database running on MS-SQL 2000 or MS-SQL 2008 from an Excel Spreadsheet via a macro using Active X Data Objects Library 2.8.  The macro runs a stored procedure.  If the stored procedure issues only a SELECT the result is data, for UPDATE, INSERT or DELETE the result is 3704.  The stored procedure below does not always produce output.

Here’s the VBA code:

Sub Get_DBDATA(sWkSheet As String, sLocation As String, sClearArea As String, sSQL As String)

‘ FOR THIS CODE TO WORK
‘ In  VBE you need to go Tools  References and check Microsoft Active X Data  Objects 2.x library
‘ sWKsheet to be the worksheet where data is to be placed
‘ sLocation is top left hand cell of where data is to be placed
‘ sSQL is SQL Server comand string (This can be a stored procedure)

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Dim TopLeftCellColNum As Integer
Dim TopLeftCellRowNum As Integer

On Error GoTo SQLError

Server_Name = Range(“SELECTED_DBSERVERS”).Value         ‘ Enter your server name here
Database_Name = Range(“SELECTED_DATABASES”).Value       ‘ Enter your  database name here
‘User_ID = Range(“SELECTED_DBUSERID”).Value              ‘ enter your user ID here
‘Password = Range(“SELECTED_DBUSERPWRD”).Value           ‘ Enter your password here

TopLeftCellColNum = Worksheets(sWkSheet).Range(sLocation).Column
TopLeftCellRowNum = Worksheets(sWkSheet).Range(sLocation).Row

‘Clear data return area
Worksheets(sWkSheet).Range(sClearArea).Clear

Set Cn = New ADODB.Connection
‘ Build connection string with passed variables/Cell values
‘ Cn.Open “Driver={SQL Server};Server=” & Server_Name & “;Database=” & Database_Name & “;Uid=” & User_ID & “;Pwd=” & Password & “;”
Cn.Open “Driver={SQL Server};Server=” & Server_Name & “;Database=” & Database_Name & “;Trusted_Connection=True;”
‘Get the data
RS.Open sSQL, Cn, adOpenStatic

‘Add the column headers
If RS.EOF = False Then

r = TopLeftCellRowNum ‘row in which recordset data is to be placed
C = TopLeftCellColNum ‘first column to be used for headings etc…
For Each RSField In RS.Fields
Worksheets(sWkSheet).Cells(r, C) = RSField.name
C = C + 1
Next

Worksheets(sWkSheet).Cells(TopLeftCellRowNum + 1, TopLeftCellColNum).CopyFromRecordset RS  ‘ add 1 to row to allow for column header

End If

‘Tidy up
RS.Close
Set RS = Nothing
Cn.Close
Set Cn = Nothing

Exit Sub

SQLError:

Dim sErr As String
‘Capture error number and description
sErr = “ERROR: ” & CStr(Err.Number) & ” – ” & Err.Description
‘Publish error at data return location
Worksheets(sWkSheet).Range(sLocation).Value = sErr
‘Tidy up resource
On Error Resume Next
Set RS = Nothing
Set Cn = Nothing

End Sub

Sub Test_Get_DBData()

Range(“K1″).Select
ActiveWorkbook.Names.Add name:=”SELECTED_DBSERVERS”, RefersToR1C1:=”=Test!R1C11″
Range(“K1”).Value = “WXP_PeteFish”
Range(“K2″).Select
ActiveWorkbook.Names.Add name:=”SELECTED_DATABASES”, RefersToR1C1:=”=Test!R2C11″
Range(“K2”).Value = “Pete”

Call Get_DBDATA(“Test”, “A1”, “A1”, “EXEC Test_GetDbData INS”)
Call Get_DBDATA(“Test”, “B1”, “B1”, “EXEC Test_GetDbData UPD”)
Call Get_DBDATA(“Test”, “C1”, “C1”, “EXEC Test_GetDbData DEL”)
Call Get_DBDATA(“Test”, “D1”, “D1”, “EXEC Test_GetDbData SEL”)

End Sub

Here’s the SQL fro the SQL 2000 server:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_GetDBData]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[TEST_GetDBData]
GO

CREATE PROCEDURE [DBO].[TEST_GetDBData]

@Param1 char(3)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT OFF

Declare @Value int
Select @Value = isnull(Max([Col1]),0) +1 from [Pete].[DBO].[t_One]

IF @Param1 = ‘INS’     INSERT INTO [Pete].[dbo].[T_One]           ([Col1])     VALUES           (@Value)
IF @Param1 = ‘UPD’ UPDATE [Pete].[DBO].[t_One] SET [Col1] =  @Value + 1 Where [Col1] = @Value
IF @Param1 = ‘DEL’ Delete [Pete].[DBO].[t_One] Where [Col1] >= @Value
IF @Param1 = ‘SEL’ Select * from [Pete].[DBO].[t_One]

END
GO

TRUNCAte table [Pete].[dbo].[T_One]

exec [Pete].[dbo].[TEST_GetDBData] ‘INS’
exec [Pete].[dbo].[TEST_GetDBData] ‘UPD’
exec [Pete].[dbo].[TEST_GetDBData] ‘DEL’
exec [Pete].[dbo].[TEST_GetDBData] ‘SEL’

There is a lot of discussion about ‘Set NoCount’ on various web sites.  Either way the result is the same- Error 3704

 

The answer is simple, if unwanted – make sure the stored procedure produces output!

Internal connection fatal error.

A small upgrade to fix a relatively minor problem and suddenly my SQL call result in “Internal connection fatal error.”.  Google, for once doesn’t give an immediate solution.  Too many people get too technical too quickly.  I examined the program carefully to ensure that the queries and updates are all using the correct connections.  This is a multi-threaded, multi-connection application.  There were minor errors.  I fixed them.  I couldn’t understand why I couldn’t reproduce the errors on my own workstation.  It has to be environmental.

Then I came across this. Thanks to Bob whose comment at the end sent me on the MDAC track.

Microsoft provide a nice utility to check the versions of MDAC. It reveals that I have generated and tested the program in a MDAC 2.81.1132 (from XP Sp3) environment, and am running it in a MDAC 2.81.1117 (from XP SP2) environment.  Never clever!

I set up a test environment with the correct version of MDAC – no change, the problem still occurs.  I examined my trace.  The problem always occurs after two threads have processed and their trace entries are intermingled.  If the dispose of one connection occurs while the connection to the other thread is active – Bang!

Much more careful program searching found that the connection that is failing is established on the secondary thread and passed to the primary via an ‘invoke’.  I’ve not found anything that says I shouldn’t do this, but it was the only one and always the one that failed.

Having now changed the code to ensure that connections are not used across a thread the program is functioning correctly and has been stable for over a week.

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

msdn seems to have a reasonable description of the problem here.  However my SQL Server log does not contain any entries for the time of the failure.

The last message in the latest SQL ErrorLog file is at 04:29:58.48

My application reports the connection status as open at 05:21:37.781

The failure occurs at 05:21:37.875 – less than 0.1 seconds later!

There are no messages in the eventlog in this time period.

How to get an empty table from a sql query in VB 2005 (.net) without timeouts

Its simple – write a select that guarantees the return of nothing.  You will need to know your data.  In my case this is for adding rows to a log table.  The rows are timestamped when they are entered, so I had:

SELECT [Time], [Message] FROM [LogTable]

where [Time] > {fn Now() }

It worked.

Then it began to time out.

Every time the SELECT was run a table scan occurred.  The log now has just over 500,000 rows.

I removed some to a history table, down to 100,000, but still no good.

The execution plan says table scan, so that is the obvious cause.  I soon discounted indexes, the table is rarely read, and I’m prepared for the table scan when it is, as the select is likely to include [Message] Like ‘%something%’

Then I tried

SELECT [Time], [Message] FROM [LogTable]

where 1 = 2

the execution plan says ‘constant scan’, and the query analyser reports 00:00:00 as the execution time.

FIXED!

bcp and brackets [x_x].[dbo].[T_TableName] SQLState = 37000, NativeError = 4060

Bizarrely the format [x_x].[dbo].[TableName] in the BCP command gives

SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login ‘[x_x]’. Login fails.

however in the format  x_x.dbo.[TableName] it works.  Which seems to contradict the MSDN advice.

Full working command line:

C:\Documents and Settings\Peter>bcp x_x.dbo.[T_TableName] out “Z:\Packages\Db\T_TableName.TXT”  -S SERVER -T -f “Z:\Packages\generate packages\T_TableName.FMT”

Full failing command line:

C:\Documents and Settings\Peter>bcp [x_x].[dbo].[T_TableName] out “Z:\Packages\Db\T_TableName.TXT”  -S SERVER -T -f “Z:\Packages\generate packages\T_TableName.FMT”