Hi there,
Env. VS2005 professional, SQL2005 Developer Edition.
I have a problem on calling a stored procedure that uses a temporary table through SqlConnection and SqlCommand component added on a form.
After enabling these components to appear on the toolbox (I am converting a VS2003 application), I put these components on the form, setting all parameters to connect to the db and calling the procedure.
When the sistem ask me if I want to regenerate parameters and confirming it, I get the error message:
'Invalid object: #Rows'.
What is wrong or what is changed from VS2003
Below the table definition and the SP you can use to reproduce the behaviour.
Thanks
Antonio
USE [tempdb]
GO
/****** Object: Table [dbo].[T1] Script Date: 02/21/2006 11:49:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T1](
[F1] [nchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[F2] [nchar](10) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[F1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
USE [tempdb]
GO
/****** Object: StoredProcedure [dbo].[T1_Copy] Script Date: 02/21/2006
11:50:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[T1_Copy]
(
@F1 nchar(10),
@DestinationF1 nchar(10)
)
AS
BEGIN
SELECT
F1,
F2
INTO
#Rows
FROM T1
WHERE F1 = @F1;
INSERT INTO T1
SELECT
@DestinationF1,
#Rows.F2
FROM T1
INNER JOIN #Rows on (#Rows.F1 = T1.F1)
WHERE t1.F1 = @F1
END
|
| ||
|
SqlCommand calling stored procedure with temporary table problem
rathor
bad2bone
Thanks Zhao but it is not my case. I am using the temporary table only within the SP itself just to keep track about some information to be used in a subsequent command (the script I provide is just an example to reproduce the error, the real SP will do different elaboration).
I also tried to remove the parameter from the first select that creates the temporary table, putting there a costant, but the problem is still there. In fact, if you create the SP and execute it using the SSMS it works fine, I get the problem only calling it using a SqlCommand from VS, I think there should be someting wrong with the designer when it asks to regenerate parameters.
Any hint
Thanks
Antonio
mikeyblee
Here is the good discussion about creating temp tables in store procedure.
http://www.sqlteam.com/item.asp ItemID=2029
Here is the part you may be interested in:
Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes excuting. If stored procedure A creates a temp table and calls stored procedure B, then B will be able to use the temp table that A created.
If you want to access outside of the store procedure, you can use a global temp table or create it outside any store procedure.
Another pitfall is that: even if you create session temp tables out side of store procedure, but if you command including any Sql parameter, the temp table will be dropped when the command finishes executing. So make sure the batch using to create session temp table includes no parameter.