This is the mail archive of the xsl-list@mulberrytech.com mailing list .


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]

RE: XSLT and #temptable


PRB: Error Messaging Referencing #Temp Table with ADO-SQLOLEDB.

Q235340


----------------------------------------------------------------------------
----
The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5, 2.6
Microsoft Visual Basic Learning and Enterprise Editions for Windows,
versions 5.0, 6.0

----------------------------------------------------------------------------
----


SYMPTOMS
When attempting to create a ADO recordset based on a #Temp table created
within a stored procedure called by ADO using the SQLOLEDB provider, you
might see one of the following error messages:

3704 - The operation requested by the application is not allowed if the
object is closed.
-or-
Run-time error '3704': Operation is not allowed when the object is closed.



CAUSE
The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to
provide more accurate information to the caller about what happened in the
procedure. Each SQL statement within a stored procedure returns a "result,"
either a count of rows affected, or a resultset.

The SQL Server ODBC provider (MSDASQL) does not provide information about
the results of individual SQL statements within a stored procedure. The only
result that comes back from a stored procedure execution is the result of
the SELECT statement if it has one. This is why the problem does not
manifest with MSDASQL.



RESOLUTION
To get SQLOLEDB to behave as the SQL ODBC driver did, you must use SET
NOCOUNT ON. Put SET NOCOUNT ON in the stored procedure or in the SQL
statement used with ADO as shown in the code sample below. Alternatively,
you could use MSDASQL.



STATUS
This behavior is by design.



MORE INFORMATION
To reproduce the error:

Create a new Visual Basic Standard EXE project and paste the following code
in the General Declarations section of a Form.


Set a reference to Microsoft ActiveX Data Objects Library.


Change the connection string as necessary for your environment:



Private Sub Form_Load()

Dim adoCn As adoDb.Connection
Dim adoRs As adoDb.Recordset
Dim adoCm As adoDb.Command
Dim strSQL As String

Set adoCn = New adoDb.Connection
With adoCn
    .ConnectionString = "Provider=SQLOLEDB;Server=<ServerName>;" & _
                                  "Database=Pubs;Uid=sa;Pwd="
    '.ConnectionString = "Provider=MSDASQL;Driver={SQL
Server};Server=<ServerName>;" & _
                                  "Database=Pubs;Uid=sa;Pwd="
    .CursorLocation = adUseServer
    .Open
End With

Set adoCm = New adoDb.Command
With adoCm
    Set .ActiveConnection = adoCn
    .CommandType = adCmdText
    .CommandText = "if exists (select * from sysobjects " & _
                            "where id = object_id('dbo.spADOTempTest') " & _
                            "and sysstat & 0xf = 4) " & _
                            "drop procedure dbo.spADOTempTest"
    .Execute
    .CommandText = "Create procedure spADOTempTest " & _
                            "as CREATE TABLE #test (field1 VARCHAR(20) NULL)
" & _
                            "INSERT INTO #test(field1) SELECT fname FROM
Employee " & _
                            "SELECT * FROM #test go"
    .Execute
    .CommandType = adCmdStoredProc
    .CommandText = "spADOTempTest"
    'the default for Prepared statements is false.
    '.Prepared = False
End With

Set adoRs = New adoDb.Recordset
With adoRs
    Set .ActiveConnection = adoCn
    .LockType = adLockOptimistic
    .CursorLocation = adUseServer
    .CursorType = adOpenForwardOnly
    'Uncomment the next line with the SQLOLEDB provider to fix the error.
    '.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProc

MsgBox "Recordset returned...", vbOKOnly

While Not adoRs.EOF
    Debug.Print adoRs.Fields(0).Value
    adoRs.MoveNext
Wend

adoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing

End Sub



REFERENCES
SQL Books Online, Temporary Tables

Additional query words:

Keywords : kbADO kbOLEDB kbSQLServ kbStoredProc kbVBp500 kbVBp600 kbGrpVBDB
kbGrpMDAC kbDSupport kbADO250 kbMDAC260 kbADO260
Issue type : kbprb
Technology : kbAudDeveloper kbADOsearch

Ciao Chris

XML/XSL Portal
http://www.bayes.co.uk/xml


>-----Original Message-----
>From: owner-xsl-list@lists.mulberrytech.com
>[mailto:owner-xsl-list@lists.mulberrytech.com]On Behalf Of
>pcaspian@iafrica.com
>Sent: 25 May 2001 15:19
>To: xsl-list@lists.mulberrytech.com
>Subject: [xsl] XSLT and #temptable
>
>
>Hey, I am persisting to a file and I want to save the SQL recordset to it.
>
>I incur a problem when I want to make use of a temporary table in my SQL
>query.
>Say for instance I want to have a large SQL query that saves information
>into a
>temporary table #table1 and then I go "select * from #table1"
>
>set PA = conn.execute(SQLsearch)
>szFile = Server.MapPath(xmlfile)
>PA.Save szFile, adPersistXML <- error falls on this line.
>
>('Operation is not allowed when the object is closed.')
>
>Is this just a normal xsl problem ?
>
>* Another question is a problem I have with regard to choosing between xsl
>and VBScript.
>I am reasonably adequate with SQL queries and make use of these in my ASP
>work. Now for instance
>I want to create a XML file that contains information from 2 tables (a
>union as such), but if the same PK is
>found in the second table, it will always use the row from the second table
>and discard the value from
>the first table. Now I can do this in SQL OK, but how difficult would it be
>in XSL ? I have no actual XSL
>books, just XML books containing XSL segments, so Im just curious if I
>should rather just stick with making use of SQL with regard to this sort of
>problem. I obviously would prefer to keep in touch with XSL, but would it
>be a very steep learning curve ?
>
>Also what is the efficiency of reading from a XML file compared to a SQL
>query. I can see obviously the reduction in load on the SQL server should I
>use more XML, but what about the impact on IIS. I mean, how fast would it
>really be to read from am XML file, convert with XSL and then display ?
>
>* on another note, thanks to everyone that has helped me with queries so
>far. Much appreciated.
>
>Thanks
>
>Karlo
>
>
>
> XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
>
>


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]