ASP sp execution returning closed recordset

Can anybody tell me why a) when running a stored proc from an asp page to
return a recordset the command succeeds if the sp queries an existing table
directly, but b) if the stored proc populates results into a different
table, temporary table, global temp table, or table variable, then queries
one of these, the asp page reports that the recordset object is closed. If
using a table, I have set grant, select, update, delete permissions for the
asp page user account, so it doesn't appear to be a permissioning issue. If
run in Query Analyser the sp runs fine of course.

Abridged asp code is as follows:
StoredProc = Request.querystring("SP")
oConn.ConnectionString = "Provider=SQLOLEDB etc"
oConn.Open
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandText = StoredProc
oCmd.CommandType = adCmdStoredProc
oCmd.Parameters.Refresh
'code here that populates the parameters of the oCmd object correctly
Set oRs = Server.CreateObject("ADODB.Recordset")
With oRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
'execute the SP returning the result into a recordset
.Open oCmd
End With
' Save data into IIS response object
Response.ContentType = "text/xml"
oRs.Save Response, adPersistXML
'the line above fails with stored procs from example B below, reporting "not
allowed when object is closed", but works with example A

SP Example A - this one works fine
Create Proc spTestA AS
SELECT ID FROM FileList
GO

SP Example B - this one doesn't work from ASP but runs fine in QA
Create Proc spTestB AS
DECLARE @.Results Table (ID TinyInt)
INSERT INTO @.Results SELECT ID FROM FileList
SELECT ID FROM @.Results
GO

I can see the SP executing using profiler when the asp page is called for
both sp's above, so it doesn't appear to be a problem with the execution.
It's something to do with returning the result set from the table variable.

Thanks,

Robin Hammond"Robin Hammond" wrote:

<snip
> SP Example B - this one doesn't work from ASP but runs fine in QA
> Create Proc spTestB AS
> DECLARE @.Results Table (ID TinyInt)
> INSERT INTO @.Results SELECT ID FROM FileList
> SELECT ID FROM @.Results
> GO

<snip
Robin,

The problem is that you're getting back a closed recordset with "records
affected" info from SQL Server: using the NextRecordset method in ADO will
get the actual recordset you're looking for. A good rule of thumb is to
watch the output from a stored proc in QA: anytime you see a resultset or a
message about records affected, then you know this could pop up.

A more efficient solution (and the one I prefer) if you don't need any data
back but the result of the SELECT is to use SET NOCOUNT...

Create Proc spTestB AS
SET NOCOUNT ON
DECLARE @.Results Table (ID TinyInt)
INSERT INTO @.Results SELECT ID FROM FileList

SET NOCOUNT OFF
SELECT ID FROM @.Results
GO

Craigsql

ASP + ADO + SQL 2005, new column [ORD ID]?

I'm developing some queries using ASP with a componente built in Visual basic 6.0 SP6, I get the data using ADO to a SQL 2005 (version 9.0.1406) when I run a code like this:

Code Snippet

Sub ShowRs(rsAuxi)
Dim Campo
Response.Write "

"
Response.Write " Ord ID"
For Each Campo In rsAuxi.Fields
Response.Write " " & Campo.Name & ""
Next
Response.Write ""
Do While Not rsAuxi.EOF
Response.Write " "
Response.Write " " & rsAuxi.Absoluteposition & ""
For Each Campo In rsAuxi.Fields
Response.Write " " & Campo.Value & ""
Next
rsAuxi.MoveNext
Response.Write ""
loop
Response.Write "

"
end sub

the ADO execute a Store Procedure like this:

Code Snippet

CREATE PROCEDURE [dbo].[SP_TPO_REQ_HEAD]
AS
SET NOCOUNT ON
BEGIN
SELECT
[COD_REQ_HEADER],
[NUM_REQ_HEADER],
[FECHA]
FROM [dbo].[TPO_REQ_HEADER]
END

I supose that when I draw the ADO recordset in the ASP (using the frist code) I get a table with 3 columns, but I get a table with 4 fields!!! and the first column is ORDER ID, now my question is WHO IS ADDING THIS NEW COLUMN?

Thanks in advance

JJ

You must have been executing another procedure than the one mentioned above. Another attribute is not just added automagically.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

The problem is your own code, which is adding the "new colunm":

' Your code, commented

Sub ShowRs(rsAuxi)

Dim Campo

Response.Write "<table border=1><tbody><tr>"

' This adds the Heading for the Ord ID "field"

Response.Write "<td class=TipoLetra>Ord ID</td>"

' This adds the heading for the fields returned in the Stored Procedure / in your example, 3 fields

For Each Campo In rsAuxi.Fields

Response.Write "<td class=TipoLetra>" & Campo.Name & "</td>"

Next

Response.Write "</tr>"

' This iterates as many records as your recordset (returned from stored procedure)

Do While Not rsAuxi.EOF

Response.Write "<tr>"

' THIS ADDS THE "FIELD", which is just the record number in the recordset !

Response.Write "<td class=TipoLetra>" & rsAuxi.Absoluteposition & "</td>"

' This iterates as many fields in each record, as returned in the recordset (3 in your example)

For Each Campo In rsAuxi.Fields

Response.Write "<td class=TipoLetra>" & Campo.Value & "</td>"

Next

rsAuxi.MoveNext

Response.Write "</tr>"

loop

Response.Write "</tbody></table>"

end sub

|||

thanks... I guess that I need a long vacations...

ASCII and Reporting Services

Can Reporting Services create an ascii file? We are searchring for a tool that can handle with predefinied queries to generate week figures, month figures etc. These figures will be used in paper publications as well as in electronical publications.
Is it better to use DTS?
--
Message posted via http://www.sqlmonster.comOne of the formats you can pick to render in is csv. You can also render in
XML. So, it depends on what you are wanting to do.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> Can Reporting Services create an ascii file? We are searchring for a tool
that can handle with predefinied queries to generate week figures, month
figures etc. These figures will be used in paper publications as well as in
electronical publications.
> Is it better to use DTS?
> --
> Message posted via http://www.sqlmonster.com|||Even if you render in csv, its not a true csv. If you open up that csv in
excel, all the colums appears in one column, you have to than use that wizard
- Data-> text to column to create true csv. Another avidence of that file
not being ASCII, if you save that file you exported as csv, you will see
UNICODE in your save as dialogue. This has been a problem since day one.
This is the biggest issue why we can't use RS for our reports, because all of
our customer needs true csv and don't know how to use Data -> text to column
wizard of excel.
Thanks
Vipul
"Bruce L-C [MVP]" wrote:
> One of the formats you can pick to render in is csv. You can also render in
> XML. So, it depends on what you are wanting to do.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> > Can Reporting Services create an ascii file? We are searchring for a tool
> that can handle with predefinied queries to generate week figures, month
> figures etc. These figures will be used in paper publications as well as in
> electronical publications.
> > Is it better to use DTS?
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||You can specify the format ascii as CSV Rendering Device Information Setting
if you add
&rc:Encoding=ASCII to the url generating the export
"Vipul Shah" wrote:
> Even if you render in csv, its not a true csv. If you open up that csv in
> excel, all the colums appears in one column, you have to than use that wizard
> - Data-> text to column to create true csv. Another avidence of that file
> not being ASCII, if you save that file you exported as csv, you will see
> UNICODE in your save as dialogue. This has been a problem since day one.
> This is the biggest issue why we can't use RS for our reports, because all of
> our customer needs true csv and don't know how to use Data -> text to column
> wizard of excel.
> Thanks
> Vipul
> "Bruce L-C [MVP]" wrote:
> > One of the formats you can pick to render in is csv. You can also render in
> > XML. So, it depends on what you are wanting to do.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> > message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> > > Can Reporting Services create an ascii file? We are searchring for a tool
> > that can handle with predefinied queries to generate week figures, month
> > figures etc. These figures will be used in paper publications as well as in
> > electronical publications.
> > > Is it better to use DTS?
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> >
> >
> >|||Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RDC" <RDC @.discussions.microsoft.com> wrote in message
news:9F3DC72D-8965-4B78-91C3-8D61B23C7E36@.microsoft.com...
> You can specify the format ascii as CSV Rendering Device Information
> Setting
> if you add
> &rc:Encoding=ASCII to the url generating the export
>
> "Vipul Shah" wrote:
>> Even if you render in csv, its not a true csv. If you open up that csv
>> in
>> excel, all the colums appears in one column, you have to than use that
>> wizard
>> - Data-> text to column to create true csv. Another avidence of that
>> file
>> not being ASCII, if you save that file you exported as csv, you will see
>> UNICODE in your save as dialogue. This has been a problem since day
>> one.
>> This is the biggest issue why we can't use RS for our reports, because
>> all of
>> our customer needs true csv and don't know how to use Data -> text to
>> column
>> wizard of excel.
>> Thanks
>> Vipul
>> "Bruce L-C [MVP]" wrote:
>> > One of the formats you can pick to render in is csv. You can also
>> > render in
>> > XML. So, it depends on what you are wanting to do.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> >
>> > "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com>
>> > wrote in
>> > message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
>> > > Can Reporting Services create an ascii file? We are searchring for a
>> > > tool
>> > that can handle with predefinied queries to generate week figures,
>> > month
>> > figures etc. These figures will be used in paper publications as well
>> > as in
>> > electronical publications.
>> > > Is it better to use DTS?
>> > >
>> > > --
>> > > Message posted via http://www.sqlmonster.com
>> >
>> >
>> >

AS2005. Strange MDX behaviour.

Hi, MDX gurus,

I have a unexplainable problem with pretty easy MDX.

Following MDX queries

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

//
select
Filter([Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

provide the same result as expected.

This query

//
select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = CDate("11.07.2004"))
on 0,
{} on 1
from [Adventure Works]

returns as expected one member

but this query

select
Filter([Date].[Calendar].[Month].members,
[Date].[Date].CurrentMember.MemberValue = VBA![dateadd]("yyyy", -2, VBA![Date]()) )
on 0,
{} on 1
from [Adventure Works]

retuns nothing. This is strange, isn't it?
Can anybody explain it?

Thanks in advance,

Vladimir Chtepa

The puzzle for me is not why your fourth query doesn't return anything - I don't think it should - but why the third query does return July 2004. In your third query you're filtering the members on [Date].[Calendar].[Month] and for each one checking the currentmember on [Date].[Date] - but the currentmember should be the All Member on [Date].[Date] in all cases, as the following queries show:

with member measures.test as [Date].[Date].CurrentMember.membervalue

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

and

with member measures.test as [Date].[Date].CurrentMember.membervalue = CDate("11/07/2004")

select measures.test on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Very strange...

Chris

|||

I don't know why 3-d query returns "expected" result.

It will be great If anybody from developer team could explain it.

AS/400 Parameters

Connected to AS/400 server through ODBC. Can submit queries, just built a matrix report, looks good.

However I want to incorporate a Parameter and have tried prefacing the parameter COSTCENTER with everything under the sun:

@.COSTCENTER (SQL Server version of parameter declaration)

:COSTCENTER (Oralce version of parameter declaration)

%COSTCENTER

#COSTCENTER

etc. etc.

I keep getting the same error:

Error in WHERE clause near '@.'. Unable to parse query text.

My inquiries are as follows:

1. Can you use parameters with an AS/400 connection?

2. If yes, howshould the parameter be declared?

I searched BOL, GOOGLE, so far nothing...

Which ODBC driver are you using?

AS/400 Parameters

Connected to AS/400 server through ODBC. Can submit queries, just built a matrix report, looks good.

However I want to incorporate a Parameter and have tried prefacing the parameter COSTCENTER with everything under the sun:

@.COSTCENTER (SQL Server version of parameter declaration)

:COSTCENTER (Oralce version of parameter declaration)

%COSTCENTER

#COSTCENTER

etc. etc.

I keep getting the same error:

Error in WHERE clause near '@.'. Unable to parse query text.

My inquiries are as follows:

1. Can you use parameters with an AS/400 connection?

2. If yes, howshould the parameter be declared?

I searched BOL, GOOGLE, so far nothing...

Which ODBC driver are you using?

AS stops working, Eventid 22, category 256

Hi!

During the last week my AS2005 server has behaved strangely. It works for two-three days and then it stops and cant handle any more queries. However i can list the catalog and cubes in ProClarity but when i try to connect to a cube it does not work. The same when i go to Management Studio. If i restart the service it works again, probably for another 2-3 days.

Also AS2005 send a lot of error events to the eventlog. All like this(i think one for every query that fails but i am not sure). Event id looks like this:

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (256)
Event ID: 22
Date: 2006-08-23
Time: 13:09:47
User: N/A
Computer: XXXXX
Description:
The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Internal error: An unexpected exception occured.

It is a x64 box with 4GB ram and Windows Server 2003 R2. I have SP1 installed and its only AS 2005 running on it.

Any clue to what the reason could be? Could it be something that is fixed in the cumalitve hotfix package?

Description for the error code 22 is "An unexpected exception occured." doesnt give you much.

There is not much I can suggest you here. Try contacting Microsoft customer support services and report your problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

are old school joins slower

The Exec Plans from these two simple queries are identical. Of course theres
not much data here though. Are the old type of joins in query 1 typically
slower, or just not liked/ ANSI standard?
select a.*,titleauthor.title_id
from authors a,titleauthor
where a.au_id = titleauthor.au_id
select a.*,ta.title_id from authors a
inner join titleauthor ta on a.au_id = ta.au_id
TIA, ChrisRNo difference between them and infixed joins as far as performance
goes. But the old style is easier to read when you have n-ary
relationships or a Star schema. For fun try to re-write these as
infixed:
SELECT *
FROM A, B, C
WHERE A.x BETWEEN B.y AND C.z;
SELECT *
FROM A, B, C
WHERE A.x IN (B.y, C.z, 42)
AND B.y IN (A.xx, C.zz, 41);|||There should not be any difference though one is much clearer than the
other.
-oj
"ChrisR" <noemail@.bla.com> wrote in message
news:uke6cW6ZFHA.3712@.TK2MSFTNGP09.phx.gbl...
> The Exec Plans from these two simple queries are identical. Of course
> theres not much data here though. Are the old type of joins in query 1
> typically slower, or just not liked/ ANSI standard?
> select a.*,titleauthor.title_id
> from authors a,titleauthor
> where a.au_id = titleauthor.au_id
>
> select a.*,ta.title_id from authors a
> inner join titleauthor ta on a.au_id = ta.au_id
>
> TIA, ChrisR
>|||The two statements are equivalent and should result in the same execution
plan and performance. In my experience the optimizer usually treats the
INNER JOIN syntax in exactly the same way as the equivalent join implemented
in a WHERE clause. In fact your first example is still ANSI standard and
perfectly valid. It is only the old outer join *= notation that is
deprecated.
David Portas
SQL Server MVP
--|||"oj" <nospam_ojngo@.home.com> wrote in message
news:%23MEWSl6ZFHA.4068@.TK2MSFTNGP10.phx.gbl...
> There should not be any difference though one is much clearer than the
> other.
Hopefully it's clear which one you mean ;-)
David Portas
SQL Server MVP
--|||On 2 Jun 2005 12:26:20 -0700, --CELKO-- wrote:

> No difference between them and infixed joins as far as performance
> goes. But the old style is easier to read when you have n-ary
> relationships or a Star schema. For fun try to re-write these as
> infixed:
Ugh ... here goes, just for my own challenge amusement:

> SELECT *
> FROM A, B, C
> WHERE A.x BETWEEN B.y AND C.z;
SELECT *
FROM A
INNER JOIN B ON A.x >= B.y
INNER JOIN C ON A.x <= C.z

> SELECT *
> FROM A, B, C
> WHERE A.x IN (B.y, C.z, 42)
> AND B.y IN (A.xx, C.zz, 41);
This one is really pathological ... I can't seem to do it|||that one. can't you see it clearly. ;~)
since it's an inner join, i don't worry too much about it.
now if it's an outer join, i prefer ansi join style.
-oj
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:86qdndw9p8uXwgLfRVn-2g@.giganews.com...
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23MEWSl6ZFHA.4068@.TK2MSFTNGP10.phx.gbl...
> Hopefully it's clear which one you mean ;-)
> --
> David Portas
> SQL Server MVP
> --
>