Subject: RE: [xsl] XSLT and #temptable From: "Chris Bayes" <Chris@xxxxxxxxxxx> Date: Fri, 25 May 2001 17:00:19 +0100 |
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@xxxxxxxxxxxxxxxxxxxxxx >[mailto:owner-xsl-list@xxxxxxxxxxxxxxxxxxxxxx]On Behalf Of >pcaspian@xxxxxxxxxxx >Sent: 25 May 2001 15:19 >To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx >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
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
[xsl] XSLT and #temptable, pcaspian | Thread | [xsl] RE: Match values from XML-fil, "Sellmer-Brüls, Barb |
Re[2]: [xsl] Problem with key(), Kevin Burges | Date | Re: [xsl] Cascading Attributes?, Michael Beddow |
Month |