stored procedures not saving temp or local file

Question

stored procedures not saving temp or local file

cljolly

Ten Centuries

Points: 1179
More actions
July 11, 2012 at 5:17 pm

#263684

When I execute this:
Select *
into #tbl_casualty
From Openquery(oracletable,
'
select
LPAD(WORK_ORD_NBR,5,''0'')as WO,
INCD_DATE AS OCCDATE,
REQS_DATE AS REQS_DATE,
PROJ_DESC AS PROJ_DESC,
CLTY_TYPE AS CLTY_TYPE
from EES.EES_CLTY_PROJ
WHERE WORK_ORD_NBR IS NOT NULL
order by work_ord_nbr
'
)
GO
I get 3900 rows which is correct.
When I run this (which is from a scripted table):
USE [ENG_BUDGET]
GO
/****** Object: StoredProcedure [dbo].[sql_casualties_over_30_days] Script Date: 07/11/2012 16:51:53 ******/
SET ANSI_NULLS ON
GO
--SET QUOTED_IDENTIFIER ON
--GO
ALTER PROCEDURE [dbo].[sql_casualties_over_30_days]
AS
Select *
into #tbl_casualty
From Openquery(oracletable,
'
select
LPAD(WORK_ORD_NBR,5,''0'')as WO,
INCD_DATE AS OCCDATE,
REQS_DATE AS REQS_DATE,
PROJ_DESC AS PROJ_DESC,
CLTY_TYPE AS CLTY_TYPE
from EES.EES_CLTY_PROJ
WHERE WORK_ORD_NBR IS NOT NULL
order by work_ord_nbr
'
)
GO
I get a command completed sucessfully message, but when I try to look at the table , it wasn't created.
What is the difference between a stored procedure query and a regular query.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

cljolly (7/11/2012)
When I execute this:
Select *
into #tbl_casualty
From Openquery(oracletable,
'
select
LPAD(WORK_ORD_NBR,5,''0'')as WO,
INCD_DATE AS OCCDATE,
REQS_DATE AS REQS_DATE,
PROJ_DESC AS PROJ_DESC,
CLTY_TYPE AS CLTY_TYPE
from EES.EES_CLTY_PROJ
WHERE WORK_ORD_NBR IS NOT NULL
order by work_ord_nbr
'
)
GO
I get 3900 rows which is correct.
When I run this (which is from a scripted table):
USE [ENG_BUDGET]
GO
/****** Object: StoredProcedure [dbo].[sql_casualties_over_30_days] Script Date: 07/11/2012 16:51:53 ******/
SET ANSI_NULLS ON
GO
--SET QUOTED_IDENTIFIER ON
--GO
ALTER PROCEDURE [dbo].[sql_casualties_over_30_days]
AS
Select *
into #tbl_casualty
From Openquery(oracletable,
'
select
LPAD(WORK_ORD_NBR,5,''0'')as WO,
INCD_DATE AS OCCDATE,
REQS_DATE AS REQS_DATE,
PROJ_DESC AS PROJ_DESC,
CLTY_TYPE AS CLTY_TYPE
from EES.EES_CLTY_PROJ
WHERE WORK_ORD_NBR IS NOT NULL
order by work_ord_nbr
'
)
GO
I get a command completed sucessfully message, but when I try to look at the table , it wasn't created.
What is the difference between a stored procedure query and a regular query.
Thanks

When you are running the select directly in SSMS, your subsequent query of the temporary table is running within the same session. If you were to run your subsequent query in a separate query window from the one that creates the temposry table, you won't get anything.

When run inside the stored procedure the temporary table is created within the scope of the execution of the stored procedure. When you stored procedure completes, the temporary table is automaticlly dropped as it goes out of scope.

cljolly Ten Centuries Points: 1179 More actions · Answer 2

Thanks. I'm sorry, I should have added this code after the example code I included in my post.

select *

from #tbl_casualty

In the SSMS example, I get data back. In the stored procedure example, I get a object not found error.

I also tried saving to a local table with the same results. There must be something wrong with my stored procedure code. Any other ideas?

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 3

a table created inside a procedure is destroyed when the procedure completes.

if you want to a proc to load your data, you have the following options:

1. have the same procedure put it in a permenant table isntead of a temp table

2. have the same procedure put it a global temp table (##TempTable)

3 .create the temp table prior to the procedure and change the procedure to insert into the table, instead of creating the table on the fly.

4. don't use a proc to populate the table, but query the openquery data directly.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

justmohit SSC Veteran Points: 280 More actions · Answer 4

If you want to see the data inserted in the table(#tbl_casualty) and want to use that data.

Then you can create a temp table outside the procedure and inside the procedure you can insert the data into that temporary table and after the execution of the procedure you can see the data as well as use it.

Like-

Create table #tbl_casualty(table body......)

In procedure insert into #tbl_casualty table

execute procedure

after execution select data from #tbl_casualty table

----------------------------

Hope it will be helpful for you...