July 11, 2012 at 5:17 pm
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
July 11, 2012 at 10:08 pm
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.
July 12, 2012 at 5:29 am
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
July 12, 2012 at 5:47 am
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
July 12, 2012 at 11:49 am
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...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply