October 25, 2010 at 1:26 pm
I hope you can help.
We have SQL Server 2005 version. I built procedure with a temporary table
,#mytable, populating it with a result set from other tables.
I am referencing the #mytable to update other tables fields’ date.
When I execute the procedure, it does not update the fields' date.
It doesn't fail, but it just doesn't make the intended updates.
Could you tell me what might be wrong, if it is possible to use #mytable to
make changes to other tables on a procedure?
October 25, 2010 at 1:34 pm
To receive tested help, please post the table, view definition, some test data and expected results and what code you have used.
In order to do that easily please click on the first link in my signature block.
October 25, 2010 at 2:11 pm
Here is the procedure example:
ALTER PROCEDURE [dbo].[My_prodedure] AS
BEGIN
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..##mytemptable')
)
BEGIN
DROP TABLE ##mytemptable
END
SELECT
a.CUST_ACCNO AS CUST_ACCNO,
a.CUST_TERM_DATE AS CUST_TERM_DATE,
b.ID AS ID
INTO ##mytemptable
FROM STAGING_table a
INNER JOIN ID_VALUE v ON v.ID_VALUE = a.CUST_ACCNO
INNER JOIN PERSON p on p.PERSON_ID = v.PERSON_ID
INNER JOIN ID b ON b.ID = p.ID
where CUST_TERM_DATE <> '00/00/00'
UPDATE o
SET END_DT = x.CUST_TERM_DATE
FROM table1 AS o
JOIN ##mytemptable as x
on o.ID = x.ID
UPDATE r
SET ROLE_END_DT = x.CUST_TERM_DATE
FROM table2 AS r
JOIN ##mytemptable as x
on r.ID = x.ID;
UPDATE c
SET USE_END_DT = x.CUST_TERM_DATE
FROM table3 AS c
JOIN ##mytemptable AS x
ON c.ID = x.ID
DROP TABLE ##mytemptable
END
October 25, 2010 at 6:32 pm
I am referencing the #mytable to update other tables fields’ date.
Thie above is from your first posting and does NOT agree with the sample code you posted. My comments refer to the T-SQL you posted.
Note that you have created a global temp table (##) now to see what could be happening, open 2 instances of SSMS in the first run this code:
CREATE TABLE ##MyTempTable(id INT, Something VARCHAR(20))
INSERT INTO ##MYTempTable
SELECT 1, 'more' UNION ALL
SELECT 2, 'or' UNION ALL
SELECT 3, 'less'
DECLARE @DelayLength CHAR(8)
SET @DelayLength = '00:01:00'
WAITFOR DELAY @DelayLength
SELECT * FROM ##MyTempTable
Quickly in the 2nd instance of SSMS run this code. (Copied from your T-SQL as posted)
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..##mytemptable')
)
BEGIN
DROP TABLE ##mytemptable
END
Now go back to displaying the first instance, one minute after you started it you will get the following:
(3 row(s) affected)
Msg 208, Level 16, State 1, Line 12
Invalid object name '##MyTempTable'.
The global temp table was dropped by the 2nd instance. Now is there any chance your code could be running more than once before the first instance has completed?
Another possible trouble shooting technique - use a local temp table that is #MyTempTable and test.
October 27, 2010 at 2:42 pm
Thank you for your help
I tried what you suggested, and I made the temp table #mytemtable, it works fine now.
Laura
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply