|
 |
asp_databases thread: Temporary Table....
Message #1 by "Owen Mortensen" <ojm@a...> on Fri, 23 Mar 2001 16:59:47 -0700
|
|
Anyone know if I can return a query (SELECT statement) on a temporary table
created inside a stored procedure? The stored procedure I have creates a
temporary table from several queries on other tables. I'd like to be able
to return those results back to my ASP program, but I keep getting back a
closed recordset. Here's the code to call the stored procedure:
Dim objComm
Dim objRS
set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnectString
objComm.CommandType = adCmdStoredProc
objComm.CommandText = "StoredProcName"
objComm.Parameters.Append objComm.CreateParameter("@Username", adChar,
adParamInput, 50)
objComm.Parameters("@Username") = "omortensen"
Set objRS = objComm.Execute()
The recordset I get back is always closed. When I create a really simple
query in the stored procedure, I can get back an open recordset. But I
really need to get back the temporary table that was created. I can run the
stored procedure from SQL Server Query Analyzer and I get back the records
from the temporary table.
[SQL Server 7]
TIA,
Owen
Here's a similar stored procedure:
CREATE Procedure StoredProcedureName @Username char (50)
As
CREATE TABLE [dbo].[#TempTable](
[CourseID] [nchar] (50) NOT NULL,
[CompDate] [datetime] NOT NULL,
[CourseDescription] [ntext] NOT NULL,
[ClosedStatus] [ntext] NULL,
[CourseMinutes] [int] NOT NULL)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO #TempTable (CourseID, CourseDescription, CompDate,
CourseMinutes)
SELECT cc.ID, cc.Description, CompletionDate, Minutes
FROM Case cc, UserCase cuc
WHERE cc.ID = cuc.CaseID and cuc.username=@Username
UPDATE #TempTable
set ClosedStatus = ccs.Description
from UserCase cuc, [#TempTable] tt, Case cc, ClosedRequest ccr, ClosedStatus
ccs
WHERE cuc.username=@Username AND
cuc.caseID=cc.ID AND
cuc.ClosedRequestID=ccr.ID AND
ccr.ClosedStatusID=ccs.ID AND
tt.CourseID = cc.ID
update #TempTable
Set ClosedStatus = 'none' where
ClosedStatus is null
SELECT *
FROM #TempTable
Message #2 by "Walter Burrough" <lists@c...> on Sat, 24 Mar 2001 10:58:48 -0000
|
|
Hi Owen,
Try these two things:
- 1: put SET NOCOUNT ON at the top of your stored proc
- 2: when you do your final SELECT, put in the field names
Walter
> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: 24 March 2001 00:00
> To: ASP Databases
> Subject: [asp_databases] Temporary Table....
>
>
> Anyone know if I can return a query (SELECT statement) on a
> temporary table
> created inside a stored procedure? The stored procedure I have creates a
> temporary table from several queries on other tables. I'd like to be able
> to return those results back to my ASP program, but I keep getting back a
> closed recordset. Here's the code to call the stored procedure:
>
> Dim objComm
> Dim objRS
>
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnectString
> objComm.CommandType = adCmdStoredProc
> objComm.CommandText = "StoredProcName"
> objComm.Parameters.Append objComm.CreateParameter("@Username", adChar,
> adParamInput, 50)
> objComm.Parameters("@Username") = "omortensen"
> Set objRS = objComm.Execute()
>
> The recordset I get back is always closed. When I create a really simple
> query in the stored procedure, I can get back an open recordset. But I
> really need to get back the temporary table that was created. I
> can run the
> stored procedure from SQL Server Query Analyzer and I get back the records
> from the temporary table.
>
> [SQL Server 7]
>
> TIA,
> Owen
>
> Here's a similar stored procedure:
> CREATE Procedure StoredProcedureName @Username char (50)
> As
> CREATE TABLE [dbo].[#TempTable](
> [CourseID] [nchar] (50) NOT NULL,
> [CompDate] [datetime] NOT NULL,
> [CourseDescription] [ntext] NOT NULL,
> [ClosedStatus] [ntext] NULL,
> [CourseMinutes] [int] NOT NULL)
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> INSERT INTO #TempTable (CourseID, CourseDescription, CompDate,
> CourseMinutes)
> SELECT cc.ID, cc.Description, CompletionDate, Minutes
> FROM Case cc, UserCase cuc
> WHERE cc.ID = cuc.CaseID and cuc.username=@Username
>
> UPDATE #TempTable
> set ClosedStatus = ccs.Description
> from UserCase cuc, [#TempTable] tt, Case cc, ClosedRequest ccr,
> ClosedStatus
> ccs
> WHERE cuc.username=@Username AND
> cuc.caseID=cc.ID AND
> cuc.ClosedRequestID=ccr.ID AND
> ccr.ClosedStatusID=ccs.ID AND
> tt.CourseID = cc.ID
>
> update #TempTable
> Set ClosedStatus = 'none' where
> ClosedStatus is null
>
> SELECT *
> FROM #TempTable
>
>
>
>
Message #3 by "Tomm Matthis" <matthis@b...> on Mon, 26 Mar 2001 09:51:30 -0500
|
|
The temp table existing only for the duration of the SPROC....
How much data are you getting back? If more than one row, you may want
to create a non-temp table, return your RS and then DROP the table...
Tomm
> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: Friday, March 23, 2001 7:00 PM
> To: ASP Databases
> Subject: [asp_databases] Temporary Table....
>
>
> Anyone know if I can return a query (SELECT statement) on a
> temporary table
> created inside a stored procedure? The stored procedure I have
creates a
> temporary table from several queries on other tables. I'd like to be
able
> to return those results back to my ASP program, but I keep getting
back a
> closed recordset. Here's the code to call the stored procedure:
>
> Dim objComm
> Dim objRS
>
> set objComm =3D Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection =3D strConnectString
> objComm.CommandType =3D adCmdStoredProc
> objComm.CommandText =3D "StoredProcName"
> objComm.Parameters.Append objComm.CreateParameter("@Username", adChar,
> adParamInput, 50)
> objComm.Parameters("@Username") =3D "omortensen"
> Set objRS =3D objComm.Execute()
>
> The recordset I get back is always closed. When I create a really
simple
> query in the stored procedure, I can get back an open recordset. But
I
> really need to get back the temporary table that was created. I
> can run the
> stored procedure from SQL Server Query Analyzer and I get back the
records
> from the temporary table.
>
> [SQL Server 7]
>
> TIA,
> Owen
>
> Here's a similar stored procedure:
> CREATE Procedure StoredProcedureName @Username char (50)
> As
> CREATE TABLE [dbo].[#TempTable](
> [CourseID] [nchar] (50) NOT NULL,
> [CompDate] [datetime] NOT NULL,
> [CourseDescription] [ntext] NOT NULL,
> [ClosedStatus] [ntext] NULL,
> [CourseMinutes] [int] NOT NULL)
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> INSERT INTO #TempTable (CourseID, CourseDescription, CompDate,
> CourseMinutes)
> SELECT cc.ID, cc.Description, CompletionDate, Minutes
> FROM Case cc, UserCase cuc
> WHERE cc.ID =3D cuc.CaseID and cuc.username=3D@U...
>
> UPDATE #TempTable
> set ClosedStatus =3D ccs.Description
> from UserCase cuc, [#TempTable] tt, Case cc, ClosedRequest ccr,
> ClosedStatus
> ccs
> WHERE cuc.username=3D@U... AND
> cuc.caseID=3Dcc.ID AND
> cuc.ClosedRequestID=3Dccr.ID AND
> ccr.ClosedStatusID=3Dccs.ID AND
> tt.CourseID =3D cc.ID
>
> update #TempTable
> Set ClosedStatus =3D 'none' where
> ClosedStatus is null
>
> SELECT *
> FROM #TempTable
>
>
>
>
Message #4 by "Owen Mortensen" <ojm@a...> on Mon, 26 Mar 2001 09:28:48 -0700
|
|
I just found in the SQL Server documentation that a temporary table only
exists while the stored procedure is running: it gets deleted before
returning from the stored procedure. I can't create a non-temp table
because more than one user will most definitely be accessing the data at the
same time. Unless there's a better way to do what I'm trying to do. I
tried to do an outer join, but it won't let me do that.
Owen
-----Original Message-----
From: Tomm Matthis [mailto:matthis@b...]
Sent: Monday, March 26, 2001 7:52 AM
To: ASP Databases
Subject: [asp_databases] RE: Temporary Table....
The temp table existing only for the duration of the SPROC....
How much data are you getting back? If more than one row, you may want to
create a non-temp table, return your RS and then DROP the table...
Tomm
> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: Friday, March 23, 2001 7:00 PM
> To: ASP Databases
> Subject: [asp_databases] Temporary Table....
>
>
> Anyone know if I can return a query (SELECT statement) on a
> temporary table
> created inside a stored procedure? The stored procedure I have creates a
> temporary table from several queries on other tables. I'd like to be able
> to return those results back to my ASP program, but I keep getting back a
> closed recordset. Here's the code to call the stored procedure:
>
> Dim objComm
> Dim objRS
>
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnectString
> objComm.CommandType = adCmdStoredProc
> objComm.CommandText = "StoredProcName"
> objComm.Parameters.Append objComm.CreateParameter("@Username", adChar,
> adParamInput, 50)
> objComm.Parameters("@Username") = "omortensen"
> Set objRS = objComm.Execute()
>
> The recordset I get back is always closed. When I create a really simple
> query in the stored procedure, I can get back an open recordset. But I
> really need to get back the temporary table that was created. I
> can run the
> stored procedure from SQL Server Query Analyzer and I get back the records
> from the temporary table.
>
> [SQL Server 7]
>
> TIA,
> Owen
>
> Here's a similar stored procedure:
> CREATE Procedure StoredProcedureName @Username char (50)
> As
> CREATE TABLE [dbo].[#TempTable](
> [CourseID] [nchar] (50) NOT NULL,
> [CompDate] [datetime] NOT NULL,
> [CourseDescription] [ntext] NOT NULL,
> [ClosedStatus] [ntext] NULL,
> [CourseMinutes] [int] NOT NULL)
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> INSERT INTO #TempTable (CourseID, CourseDescription, CompDate,
> CourseMinutes)
> SELECT cc.ID, cc.Description, CompletionDate, Minutes
> FROM Case cc, UserCase cuc
> WHERE cc.ID = cuc.CaseID and cuc.username=@Username
>
> UPDATE #TempTable
> set ClosedStatus = ccs.Description
> from UserCase cuc, [#TempTable] tt, Case cc, ClosedRequest ccr,
> ClosedStatus
> ccs
> WHERE cuc.username=@Username AND
> cuc.caseID=cc.ID AND
> cuc.ClosedRequestID=ccr.ID AND
> ccr.ClosedStatusID=ccs.ID AND
> tt.CourseID = cc.ID
>
> update #TempTable
> Set ClosedStatus = 'none' where
> ClosedStatus is null
>
> SELECT *
> FROM #TempTable
Message #5 by "Owen Mortensen" <ojm@a...> on Mon, 26 Mar 2001 10:10:42 -0700
|
|
Walter,
Thanks! That worked. It's really strange, though, that setting NOCOUNT
would have the side-effect of allowing a recordset from a temporary table to
be returned from a stored procedure. How did you find that?
Owen
-----Original Message-----
From: Walter Burrough [mailto:lists@c...]
Sent: Saturday, March 24, 2001 3:59 AM
To: ASP Databases
Subject: [asp_databases] RE: Temporary Table....
Hi Owen,
Try these two things:
- 1: put SET NOCOUNT ON at the top of your stored proc
- 2: when you do your final SELECT, put in the field names
Walter
> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: 24 March 2001 00:00
> To: ASP Databases
> Subject: [asp_databases] Temporary Table....
>
>
> Anyone know if I can return a query (SELECT statement) on a
> temporary table
> created inside a stored procedure? The stored procedure I have creates a
> temporary table from several queries on other tables. I'd like to be able
> to return those results back to my ASP program, but I keep getting back a
> closed recordset. Here's the code to call the stored procedure:
>
> Dim objComm
> Dim objRS
>
> set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = strConnectString
> objComm.CommandType = adCmdStoredProc
> objComm.CommandText = "StoredProcName"
> objComm.Parameters.Append objComm.CreateParameter("@Username", adChar,
> adParamInput, 50)
> objComm.Parameters("@Username") = "omortensen"
> Set objRS = objComm.Execute()
>
> The recordset I get back is always closed. When I create a really simple
> query in the stored procedure, I can get back an open recordset. But I
> really need to get back the temporary table that was created. I
> can run the
> stored procedure from SQL Server Query Analyzer and I get back the records
> from the temporary table.
>
> [SQL Server 7]
>
> TIA,
> Owen
>
> Here's a similar stored procedure:
> CREATE Procedure StoredProcedureName @Username char (50)
> As
> CREATE TABLE [dbo].[#TempTable](
> [CourseID] [nchar] (50) NOT NULL,
> [CompDate] [datetime] NOT NULL,
> [CourseDescription] [ntext] NOT NULL,
> [ClosedStatus] [ntext] NULL,
> [CourseMinutes] [int] NOT NULL)
> ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> INSERT INTO #TempTable (CourseID, CourseDescription, CompDate,
> CourseMinutes)
> SELECT cc.ID, cc.Description, CompletionDate, Minutes
> FROM Case cc, UserCase cuc
> WHERE cc.ID = cuc.CaseID and cuc.username=@Username
>
> UPDATE #TempTable
> set ClosedStatus = ccs.Description
> from UserCase cuc, [#TempTable] tt, Case cc, ClosedRequest ccr,
> ClosedStatus
> ccs
> WHERE cuc.username=@Username AND
> cuc.caseID=cc.ID AND
> cuc.ClosedRequestID=ccr.ID AND
> ccr.ClosedStatusID=ccs.ID AND
> tt.CourseID = cc.ID
>
> update #TempTable
> Set ClosedStatus = 'none' where
> ClosedStatus is null
>
> SELECT *
> FROM #TempTable
>
>
>
>
Message #6 by "S. M. Haig" <smhaig@t...> on Mon, 26 Mar 2001 12:40:08 -0500
|
|
A temp table, known only to the user can be done in the following ways. The
first creates a unique table with the userid from nt logon as part of the
table name. Perhaps you can use another identifies, since this is a web app.
The second creates a table owned by the user who created it.
A third way involves keeping a field in one table that puts in the name of
the user id SUSER_SNAME() So you can do a select statement on the field
with the userid to get just their records.
Hope this helps
SM Haig
this creates a table tab + the userid with one field for a name (for example
purposes)
CREATE PROCEDURE mktab
as DECLARE @sql VARCHAR (300)
SET @sql = 'CREATE TABLE tab' + rtrim(SUSER_SNAME()) + ' (name
char(30))'
EXEC (@sql)
@sql size would depend, of course, on number of fields being created. Also,
might want something to trunc susername. I have included a function of mine
that I use when I put the suser_sname into a field.
Function TruncUserName(GlobalUser As String)
Dim pos As Integer, sright As String
pos = InStrRev(GlobalUser, "\")
If pos = 0 Then
sright = GlobalUser
Else
sright = Mid(GlobalUser, pos + 1)
sright = Trim(sright)
End If
If Len(sright) <= 20 Then
TruncUserName = sright
Else
TruncUserName = Left(sright, 20)
End If
End Function
On the second way:
"A database can contain multiple tables with the same name, as long as the
tables have different owners. The full name of a table has three parts, int
his form:
database.owner.tablename ..."
-----Original Message-----
From: Owen Mortensen [mailto:ojm@a...]
Sent: Monday, March 26, 2001 11:29 AM
To: ASP Databases
Subject: [asp_databases] RE: Temporary Table....
I just found in the SQL Server documentation that a temporary table only
exists while the stored procedure is running: it gets deleted before
returning from the stored procedure. I can't create a non-temp table
because more than one user will most definitely be accessing the data at the
same time. Unless there's a better way to do what I'm trying to do. I
tried to do an outer join, but it won't let me do that.
Owen
Message #7 by "Walter Burrough" <lists@c...> on Tue, 27 Mar 2001 10:07:46 +0100
|
|
Yes, v odd. I reckon it has something to do with the record count confusing
ADO.
Anyone know?
walter
> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: 26 March 2001 18:11
> To: ASP Databases
> Subject: [asp_databases] RE: Temporary Table....
>
>
> Walter,
>
> Thanks! That worked. It's really strange, though, that setting NOCOUNT
> would have the side-effect of allowing a recordset from a
> temporary table to
> be returned from a stored procedure. How did you find that?
>
> Owen
>
> -----Original Message-----
> From: Walter Burrough [mailto:lists@c...]
> Sent: Saturday, March 24, 2001 3:59 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Temporary Table....
>
>
> Hi Owen,
> Try these two things:
> - 1: put SET NOCOUNT ON at the top of your stored proc
> - 2: when you do your final SELECT, put in the field names
> Walter
>
> > -----Original Message-----
> > From: Owen Mortensen [mailto:ojm@a...]
> > Sent: 24 March 2001 00:00
> > To: ASP Databases
> > Subject: [asp_databases] Temporary Table....
> >
> >
> > Anyone know if I can return a query (SELECT statement) on a
> > temporary table
> > created inside a stored procedure? The stored procedure I have
> creates a
> > temporary table from several queries on other tables. I'd like
> to be able
> > to return those results back to my ASP program, but I keep
> getting back a
> > closed recordset. Here's the code to call the stored procedure:
> >
> > Dim objComm
> > Dim objRS
> >
> > set objComm = Server.CreateObject("ADODB.Command")
> > objComm.ActiveConnection = strConnectString
> > objComm.CommandType = adCmdStoredProc
> > objComm.CommandText = "StoredProcName"
> > objComm.Parameters.Append objComm.CreateParameter("@Username", adChar,
> > adParamInput, 50)
> > objComm.Parameters("@Username") = "omortensen"
> > Set objRS = objComm.Execute()
> >
> > The recordset I get back is always closed. When I create a
> really simple
> > query in the stored procedure, I can get back an open recordset. But I
> > really need to get back the temporary table that was created. I
> > can run the
> > stored procedure from SQL Server Query Analyzer and I get back
> the records
> > from the temporary table.
> >
> > [SQL Server 7]
> >
> > TIA,
> > Owen
> >
> > Here's a similar stored procedure:
> > CREATE Procedure StoredProcedureName @Username char (50)
> > As
> > CREATE TABLE [dbo].[#TempTable](
> > [CourseID] [nchar] (50) NOT NULL,
> > [CompDate] [datetime] NOT NULL,
> > [CourseDescription] [ntext] NOT NULL,
> > [ClosedStatus] [ntext] NULL,
> > [CourseMinutes] [int] NOT NULL)
> > ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >
> > INSERT INTO #TempTable (CourseID, CourseDescription, CompDate,
> > CourseMinutes)
> > SELECT cc.ID, cc.Description, CompletionDate, Minutes
> > FROM Case cc, UserCase cuc
> > WHERE cc.ID = cuc.CaseID and cuc.username=@Username
> >
> > UPDATE #TempTable
> > set ClosedStatus = ccs.Description
> > from UserCase cuc, [#TempTable] tt, Case cc, ClosedRequest ccr,
> > ClosedStatus
> > ccs
> > WHERE cuc.username=@Username AND
> > cuc.caseID=cc.ID AND
> > cuc.ClosedRequestID=ccr.ID AND
> > ccr.ClosedStatusID=ccs.ID AND
> > tt.CourseID = cc.ID
> >
> > update #TempTable
> > Set ClosedStatus = 'none' where
> > ClosedStatus is null
> >
> > SELECT *
> > FROM #TempTable
> >
> >
> >
> >
>
>
Message #8 by "Owen Mortensen" <ojm@a...> on Tue, 27 Mar 2001 08:43:00 -0700
|
|
The users for this particular application are internet users. They DO have
to be logged in to use this functionality, so I could create a table that
includes their username, but I shouldn't have to. I thought this is just
what temporary tables were for....
Owen
-----Original Message-----
From: S. M. Haig [mailto:smhaig@t...]
Sent: Monday, March 26, 2001 10:40 AM
To: ASP Databases
Subject: [asp_databases] RE: Temporary Table....
A temp table, known only to the user can be done in the following ways. The
first creates a unique table with the userid from nt logon as part of the
table name. Perhaps you can use another identifies, since this is a web app.
The second creates a table owned by the user who created it.
A third way involves keeping a field in one table that puts in the name of
the user id SUSER_SNAME() So you can do a select statement on the field
with the userid to get just their records.
Hope this helps
SM Haig
this creates a table tab + the userid with one field for a name (for example
purposes)
CREATE PROCEDURE mktab
as DECLARE @sql VARCHAR (300)
SET @sql = 'CREATE TABLE tab' + rtrim(SUSER_SNAME()) + ' (name
char(30))'
EXEC (@sql)
@sql size would depend, of course, on number of fields being created. Also,
might want something to trunc susername. I have included a function of mine
that I use when I put the suser_sname into a field.
Function TruncUserName(GlobalUser As String)
Dim pos As Integer, sright As String
pos = InStrRev(GlobalUser, "\")
If pos = 0 Then
sright = GlobalUser
Else
sright = Mid(GlobalUser, pos + 1)
sright = Trim(sright)
End If
If Len(sright) <= 20 Then
TruncUserName = sright
Else
TruncUserName = Left(sright, 20)
End If
End Function
On the second way:
"A database can contain multiple tables with the same name, as long as the
tables have different owners. The full name of a table has three parts, int
his form:
database.owner.tablename ..."
-----Original Message-----
From: Owen Mortensen [mailto:ojm@a...]
Sent: Monday, March 26, 2001 11:29 AM
To: ASP Databases
Subject: [asp_databases] RE: Temporary Table....
I just found in the SQL Server documentation that a temporary table only
exists while the stored procedure is running: it gets deleted before
returning from the stored procedure. I can't create a non-temp table
because more than one user will most definitely be accessing the data at the
same time. Unless there's a better way to do what I'm trying to do. I
tried to do an outer join, but it won't let me do that.
Owen
Message #9 by "Owen Mortensen" <ojm@a...> on Tue, 27 Mar 2001 08:59:19 -0700
|
|
The documentation for stored procedures specifically says that a temporary
table ceases to exist when you exit a stored procedure (From the SQL-Server
Books-online documentation: "If you create a private temporary table inside
a stored procedure, the temporary table exists only for the purposes of the
stored procedure; it disappears when you exit the stored procedure.") I
don't know why the NOCOUNT option causes that to be invalid....
Owen
-----Original Message-----
From: Walter Burrough [mailto:lists@c...]
Sent: Tuesday, March 27, 2001 2:08 AM
To: ASP Databases
Subject: [asp_databases] RE: Temporary Table....
Yes, v odd. I reckon it has something to do with the record count confusing
ADO.
Anyone know?
walter
> -----Original Message-----
> From: Owen Mortensen [mailto:ojm@a...]
> Sent: 26 March 2001 18:11
> To: ASP Databases
> Subject: [asp_databases] RE: Temporary Table....
>
>
> Walter,
>
> Thanks! That worked. It's really strange, though, that setting NOCOUNT
> would have the side-effect of allowing a recordset from a
> temporary table to
> be returned from a stored procedure. How did you find that?
>
> Owen
>
> -----Original Message-----
> From: Walter Burrough [mailto:lists@c...]
> Sent: Saturday, March 24, 2001 3:59 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Temporary Table....
>
>
> Hi Owen,
> Try these two things:
> - 1: put SET NOCOUNT ON at the top of your stored proc
> - 2: when you do your final SELECT, put in the field names
> Walter
>
> > -----Original Message-----
> > From: Owen Mortensen [mailto:ojm@a...]
> > Sent: 24 March 2001 00:00
> > To: ASP Databases
> > Subject: [asp_databases] Temporary Table....
> >
> >
> > Anyone know if I can return a query (SELECT statement) on a
> > temporary table
> > created inside a stored procedure? The stored procedure I have
> creates a
> > temporary table from several queries on other tables. I'd like
> to be able
> > to return those results back to my ASP program, but I keep
> getting back a
> > closed recordset. Here's the code to call the stored procedure:
> >
> > Dim objComm
> > Dim objRS
> >
> > set objComm = Server.CreateObject("ADODB.Command")
> > objComm.ActiveConnection = strConnectString
> > objComm.CommandType = adCmdStoredProc
> > objComm.CommandText = "StoredProcName"
> > objComm.Parameters.Append objComm.CreateParameter("@Username", adChar,
> > adParamInput, 50)
> > objComm.Parameters("@Username") = "omortensen"
> > Set objRS = objComm.Execute()
> >
> > The recordset I get back is always closed. When I create a
> really simple
> > query in the stored procedure, I can get back an open recordset. But I
> > really need to get back the temporary table that was created. I
> > can run the
> > stored procedure from SQL Server Query Analyzer and I get back
> the records
> > from the temporary table.
> >
> > [SQL Server 7]
> >
> > TIA,
> > Owen
> >
> > Here's a similar stored procedure:
> > CREATE Procedure StoredProcedureName @Username char (50)
> > As
> > CREATE TABLE [dbo].[#TempTable](
> > [CourseID] [nchar] (50) NOT NULL,
> > [CompDate] [datetime] NOT NULL,
> > [CourseDescription] [ntext] NOT NULL,
> > [ClosedStatus] [ntext] NULL,
> > [CourseMinutes] [int] NOT NULL)
> > ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >
> > INSERT INTO #TempTable (CourseID, CourseDescription, CompDate,
> > CourseMinutes)
> > SELECT cc.ID, cc.Description, CompletionDate, Minutes
> > FROM Case cc, UserCase cuc
> > WHERE cc.ID = cuc.CaseID and cuc.username=@Username
> >
> > UPDATE #TempTable
> > set ClosedStatus = ccs.Description
> > from UserCase cuc, [#TempTable] tt, Case cc, ClosedRequest ccr,
> > ClosedStatus
> > ccs
> > WHERE cuc.username=@Username AND
> > cuc.caseID=cc.ID AND
> > cuc.ClosedRequestID=ccr.ID AND
> > ccr.ClosedStatusID=ccs.ID AND
> > tt.CourseID = cc.ID
> >
> > update #TempTable
> > Set ClosedStatus = 'none' where
> > ClosedStatus is null
> >
> > SELECT *
> > FROM #TempTable
> >
> >
> >
> >
>
>
|
|
|
View Cart
