If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Connection Closed but still doing business?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: outside the rim
Posts: 998
Connection Closed but still doing business?

Hello,

Not sure if this is the right place, so I will post in both: SQL Server and Excel

I have an sp in SQL Server that catches 2 parameters, does a lookup, and returns the result.

I have a procedure in Excel that opens an ADO connection STATIC, calls the stored procedure with the parameters attached, and fields the result in a local ado recordset. It then closes the connection.

My understanding was that closing the connection orphans the recordset, and that the static parameter would makes the connection read only anyway. I also understood that the sp would return a discrete set of data, not an open connection to the tables.

I have found that these understandings are not correct, because adding data in the local recordset is inserting values into the SQL server tables. This should not be happening.

SQL Server:
Code:
PROCEDURE [dbo].[sp_Ups_ItemExtraData]
	@ItemNum as varchar(25),
	@ItemExtra as varchar(25)

AS
	
BEGIN
	
	SET NOCOUNT ON;	
	SELECT dbo.Items.ItemNo As Item, dbo.ItemExtralineDetails.DescriptionMed As Detail, dbo.ItemExtra.DescriptionMed As Value
	FROM   dbo.Items INNER JOIN
		   dbo.ItemExtra ON dbo.Items.ItemID = dbo.ItemExtra.ItemID INNER JOIN
		   dbo.ItemExtralineDetails ON dbo.ItemExtra.ItemExtralineDetailID = dbo.ItemExtralineDetails.ItemExtralineDetailID
	WHERE (dbo.ItemExtralineDetails.DescriptionMed = @ItemExtra) AND (dbo.Items.ItemNo = @ItemNum)
END
Excel:
Code:
Public Property Get FetchSQLbyADO(sproc As String, rs As ADODB.Recordset, Optional pars As ADODB.Parameters) As Boolean

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim par As ADODB.Parameter

    
    On Error GoTo FetchError

    ' Establish connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = ConnectionString
    cnn.Open

    ' Setup command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = sproc
    cmd.CommandType = adCmdStoredProc
    If Not pars Is Nothing Then
        For Each par In pars
            cmd.Parameters.Append par
        Next
    End If

   ' Open recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CacheSize = 100000
    rs.Open cmd, , adOpenStatic, adLockOptimistic

    FetchSQLbyADO = True

FetchDone:
    Set cnn = Nothing
    Exit Property

FetchError:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
    FetchSQLbyADO = False
    Resume FetchDone

End Property
What am I doing wrong?
__________________
have fun,
tc

Small, custom, unique programs
email
_________________________________________________
Favorite message from Windows:
There was an error displaying the previous error message

Sadly, there was no error number to look up ...
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,522
It is not possible to update a recordset produced by a stored procedure.
You're missing something in your architecture.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: outside the rim
Posts: 998
I understand that is should not be possible.

However, using the exact code shown in SQL Server 2008R2 and Exel 2010, I can reproduce the creation of records where all the fields are null in the SQL server table by adding rows to the recordset in VBA.

The only thing I can figure is that when the recordset is opened in VBA, it creates it's own connection to the datasource. However, the STATIC parameter should still prevent writing data back. Not to mention the sp should be closed once it returns the original dataset.

My work around (because I was in a rush and had to move on) was to create a second recordset in Excel, copy the smallish dataset line by line to the new recordset and close the original recordset. The cost is a few milliseconds of processor time, but it worked.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On