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

ArrayList -> System.Object-Variable

Hi,

I have a custom task to execute a package. This task sets dynamically the values of the child package at runtime before execution of the child.

Everything works fine, as long as Im not trying to use an Object Variable in the child package, trying to fill with an ArrayList. Then, during package validation at runtime, the Properties of the custom task have NULL-Values. I don't know why all Properties got NULL or 0 (int), only adding an ArrayList-Property to the Task-Code.

The ArrayList gets initialized in the constructor of the Task. Values to add in execute(). But the process never gets to the execute()-Method, due to my check in validate() for null Values. So the List is initialized but empty.

Anyone an idea, maybe I'm doing something wrong at all. Anybody experiences with Object-Variables in Packages? A HowTo?

Thanks a lot

Thorsten

When you say you have an System.Object variable, are you referring to an SSIS variable of type Object, or a .NET variable/property of type System.Object?

Also, to get the picture correct. It sounds like this custom task is similar to an Execute Package Task, except that it sets the "values" of the child package at run-time. I assume you're familar with parent package configurations for non-object variables. Anyway, when you say "dynamically sets the values " are you referring literally to properties, as in pkg.SomeAttribute, or variables, as in pkg.Variables...?|||

Hi,

to get some light into the deep darkness I provided. Maybe, man should read over and over and over a post to provide all information :-). Sorry.

I refer to a SSIS Variable of Type Object. The task is similar to a execute package task, except of the UI, which provides a user friendly (non-technics :-)) GUI to set some properties of the task. These properties control the behavior of the package the task executes at runtime.

Yes I'm familiar with package configurations, but they need variables, one can change without the ability to know they link to a special task. Not so with properties, cos you have to explicitit mark the task to change them.

At runtime, I'm setting the variables of the package I wanna execute, by

Variables variables = null;
variableDispenser.LockOneForWrite(name, ref variables);
variables[0].Value = value;
variables.Unlock();

Everything works as expected, as long as I'm not trying to set a SSIS-Object Variable with an ArrayList. Then "NONE" of the vars gets setted.

are sqlclr stored procs subject to the same SQL Execution Model as other sessions?

are sqlclr stored proc sessions running in the same SQLOS execution model (Running, runnable and suspended status, Runnable Queue and Wait List) as other types of sessions and therefore ending up in the same queues, contending for very similar resources (except maybe for memory type because of MTL's role) etc?...or do they get treated in a very different way?

They are part of the execution model.

Niels

Are 'inserted' and 'deleted' tables or views?

How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?I have been told recently, by a reliable source (Itzik Ben-Gan), that in
SQL 2000 the insert & deleted virtual tables accessible in triggers are
implemented in memory from the transaction log of the DB (ie. it builds
a table in RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is
being accessed because there's no better way to say, in an execution
plan, that it's actually the transaction log that's being accessed. I
guess you could interpret the execution plan step as "scan the change
history for the MY_TABLE object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using
the new row versioning technology that has been added to SQL 2005.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
BGL wrote:

>How are 'inserted' and 'deleted' implemented by SQL Server?
>When I look at the execution plan of a trigger that selects from 'inserted'
,
>I can see that the user table is queried instead and aliased as 'inserted':
>
>Execution Tree
>--
>Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
>BOL referers to inserted as "temporary, memory-resident tables to test the
>effects of certain data modifications".
>Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted
'
>is instead a view on top of MY_TABLE that accesses the new version of a
>record, and deleted accesses the old version?
>
>
>
>|||That's close, but there actually is not really any building going on. The ro
ws are already in memory because the inserts or deletes have been logged. Yo
u can think of inserted and deleted as views of the transaction log containi
ng the changed rows for the particular transaction.
Since the log records refer to the base table they are based on, the query p
lan uses that for the table name.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:egh
s3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL
2000 the insert & deleted virtual tables accessible in triggers are impleme
nted in memory from the transaction log of the DB (ie. it builds a table in
RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is being
accessed because there's no better way to say, in an execution plan, that it
's actually the transaction log that's being accessed. I guess you could in
terpret the execution plan step as "scan the change history for the MY_TABLE
object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using the ne
w row versioning technology that has been added to SQL 2005.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
BGL wrote:
How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?|||Hi Mike,
I actually like the term views rather than virtual tables. What I said was t
hat inserted and deleted are views on top of the section in the log that con
tains the change that fired the trigger.
And since these log records are also buffered, chances are that if the secti
on in the log is not really big, it's in cache.
The reason it's important to realize this is that referring to inserted and
deleted means reading from the log, postponing its other activities.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:egh
s3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL
2000 the insert & deleted virtual tables accessible in triggers are impleme
nted in memory from the transaction log of the DB (ie. it builds a table in
RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is being
accessed because there's no better way to say, in an execution plan, that it
's actually the transaction log that's being accessed. I guess you could in
terpret the execution plan step as "scan the change history for the MY_TABLE
object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using the ne
w row versioning technology that has been added to SQL 2005.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
BGL wrote:
How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?|||Thanks for the clarification (I was close - it was a long day for my
brain on Wednesday ;-) ).
<OffTopic>
Wow - posts from both Kalen & Itzik in the same thread. I've just given
myself a big headache by listening to a Podcast on .NET Rocks (that went
for about 100min - wmplayer sucked up 980MB of virtual RAM!) of Kim
Tripp talking about snapshot & read committed snapshot isolations levels
(among other topics) in SQL 2005. It took me about 3 hours because I
kept having to go back and replay bits over and over again to understand
what she was talking about. And that was immediately after listening to
a 1 hour Podcast of Kalen talking about MSSQL history. (Kalen when are
you going to do a course or seminar in Sydney (much better city than
Melbourne)?)
I downloaded another of Kim's Podcasts (about the same size) for the
train trip home but I'm scared to start it for fear of exasperating my
headache. :-\
</OffTopic>
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Itzik Ben-Gan wrote:
> Hi Mike,
> I actually like the term views rather than virtual tables. What I said
> was that inserted and deleted are views on top of the section in the
> log that contains the change that fired the trigger.
> And since these log records are also buffered, chances are that if the
> section in the log is not really big, it's in cache.
> The reason it's important to realize this is that referring to
> inserted and deleted means reading from the log, postponing its other
> activities.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com <http://www.SolidQualityLearning.com>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:eghs3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
> I have been told recently, by a reliable source (Itzik Ben-Gan),
> that in SQL 2000 the insert & deleted virtual tables accessible in
> triggers are implemented in memory from the transaction log of the
> DB (ie. it builds a table in RAM from reading the tlog of the
> database).
> At a guess I'd say that the execution plan tells you that MY_TABLE
> is being accessed because there's no better way to say, in an
> execution plan, that it's actually the transaction log that's
> being accessed. I guess you could interpret the execution plan
> step as "scan the change history for the MY_TABLE object", or
> something along those lines.
> In SQL 2005 the inserted & deleted tables are created in tempdb
> using the new row versioning technology that has been added to SQL
> 2005.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> BGL wrote:
>|||Did you see Kim's whitepaper on Snapshot Isolation? That might have much of
the same info, and if you had read that first, you might not have to keep re
winding the podcast!
We were planning on doing my course in Sydney, but not enough people signed
up. So we're doing the second w in Canberra. Hope you can make it!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
mp%23IsAaFHA.720@.TK2MSFTNGP15.phx.gbl...
Thanks for the clarification (I was close - it was a long day for my brain o
n Wednesday ;-) ).
<OffTopic>
Wow - posts from both Kalen & Itzik in the same thread. I've just given mys
elf a big headache by listening to a Podcast on .NET Rocks (that went for ab
out 100min - wmplayer sucked up 980MB of virtual RAM!) of Kim Tripp talking
about snapshot & read committed snapshot isolations levels (among other topi
cs) in SQL 2005. It took me about 3 hours because I kept having to go back
and replay bits over and over again to understand what she was talking about
. And that was immediately after listening to a 1 hour Podcast of Kalen tal
king about MSSQL history. (Kalen when are you going to do a course or semin
ar in Sydney (much better city than Melbourne)?)
I downloaded another of Kim's Podcasts (about the same size) for the train t
rip home but I'm scared to start it for fear of exasperating my headache. :
-\
</OffTopic>
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Itzik Ben-Gan wrote:
Hi Mike,
I actually like the term views rather than virtual tables. What I said was t
hat inserted and deleted are views on top of the section in the log that con
tains the change that fired the trigger.
And since these log records are also buffered, chances are that if the secti
on in the log is not really big, it's in cache.
The reason it's important to realize this is that referring to inserted and
deleted means reading from the log, postponing its other activities.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:egh
s3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL
2000 the insert & deleted virtual tables accessible in triggers are impleme
nted in memory from the transaction log of the DB (ie. it builds a table in
RAM from reading the tlog of the database).
At a guess I'd say that the execution plan tells you that MY_TABLE is being
accessed because there's no better way to say, in an execution plan, that it
's actually the transaction log that's being accessed. I guess you could in
terpret the execution plan step as "scan the change history for the MY_TABLE
object", or something along those lines.
In SQL 2005 the inserted & deleted tables are created in tempdb using the ne
w row versioning technology that has been added to SQL 2005.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
BGL wrote:
How are 'inserted' and 'deleted' implemented by SQL Server?
When I look at the execution plan of a trigger that selects from 'inserted',
I can see that the user table is queried instead and aliased as 'inserted':
Execution Tree
--
Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
BOL referers to inserted as "temporary, memory-resident tables to test the
effects of certain data modifications".
Why does the plan reveal that MY_TABLE is queried? It looks like 'inserted'
is instead a view on top of MY_TABLE that accesses the new version of a
record, and deleted accesses the old version?|||re: > In SQL 2005 the inserted & deleted tables are created in tempdb usin
g
the new row versioning technology that has been added to SQL 2005
The overhead of the writes to #temp should slow down trigger performance vs
the current implementation of viewing the log records in memory?
Thanks for the posts, all, I've enjoyed the feedback. -- BGL
"Itzik Ben-Gan" wrote:

> Hi Mike,
> I actually like the term views rather than virtual tables. What I said was
that inserted and deleted are views on top of the section in the log that c
ontains the change that fired the trigger.
> And since these log records are also buffered, chances are that if the sec
tion in the log is not really big, it's in cache.
> The reason it's important to realize this is that referring to inserted an
d deleted means reading from the log, postponing its other activities.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news
:eghs3%239ZFHA.796@.TK2MSFTNGP09.phx.gbl...
> I have been told recently, by a reliable source (Itzik Ben-Gan), that in SQL 200
0 the insert & deleted virtual tables accessible in triggers are implemented in memo
ry from the transaction log of the DB (ie. it builds a table in RAM from reading the
tl
og of the database).
> At a guess I'd say that the execution plan tells you that MY_TABLE is being acce
ssed because there's no better way to say, in an execution plan, that it's actually
the transaction log that's being accessed. I guess you could interpret the executio
n p
lan step as "scan the change history for the MY_TABLE object", or something along those lin
es.
> In SQL 2005 the inserted & deleted tables are created in tempdb using th
e new row versioning technology that has been added to SQL 2005.
> HTH
> --
> mike hodgson | database administrator | mallesons stephen jaques
> T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
> E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
> BGL wrote:
> How are 'inserted' and 'deleted' implemented by SQL Server?
> When I look at the execution plan of a trigger that selects from 'inserted
',
> I can see that the user table is queried instead and aliased as 'inserted'
:
> Execution Tree
> --
> Inserted Scan(OBJECT:[dbo].[MY_TABLE] AS [inserted]))
> BOL referers to inserted as "temporary, memory-resident tables to test the
> effects of certain data modifications".
> Why does the plan reveal that MY_TABLE is queried? It looks like 'inserte
d'
> is instead a view on top of MY_TABLE that accesses the new version of a
> record, and deleted accesses the old version?
>
>
>|||I actually understood it all because Itzik covered it with a good demo
in the T-SQL course in Sydney last Wednesday. I think half the problem
was that I was trying to do some logic puzzles (at http://laser.narr.as)
at the same time as trying to concentrate on what Kim was saying (and
the fact that it was late Friday arvo). I'll be sure to check out her
whitepaper though. Thanks for the tip.
I'd love to come to your Canberra session but the missus & I are
expecting our 3rd baby at the beginning of July so I'm afraid I'll be a
tad busy but I hope your time down under will be great (I'm sure your
SQL sessions will be fantastic).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Kalen Delaney wrote:
> Did you see Kim's whitepaper on Snapshot Isolation? That might have
> much of the same info, and if you had read that first, you might not
> have to keep rewinding the podcast!
> We were planning on doing my course in Sydney, but not enough people
> signed up. So we're doing the second w in Canberra. Hope you can
> make it!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com <http://www.SolidQualityLearning.com>
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23mp%23IsAaFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks for the clarification (I was close - it was a long day for
> my brain on Wednesday ;-) ).
> <OffTopic>
> Wow - posts from both Kalen & Itzik in the same thread. I've just
> given myself a big headache by listening to a Podcast on .NET
> Rocks (that went for about 100min - wmplayer sucked up 980MB of
> virtual RAM!) of Kim Tripp talking about snapshot & read committed
> snapshot isolations levels (among other topics) in SQL 2005. It
> took me about 3 hours because I kept having to go back and replay
> bits over and over again to understand what she was talking
> about. And that was immediately after listening to a 1 hour
> Podcast of Kalen talking about MSSQL history. (Kalen when are you
> going to do a course or seminar in Sydney (much better city than
> Melbourne)?)
> I downloaded another of Kim's Podcasts (about the same size) for
> the train trip home but I'm scared to start it for fear of
> exasperating my headache. :-\
> </OffTopic>
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Itzik Ben-Gan wrote:
>