April 17, 2008 at 5:34 am
I'm trying to execute a stored procedure using the Execute Sql task in dts. The sproc don't have any parameters. But it is using a temporary table ("#mainlist2").
In the "Sql statement section" of Execute sql task,
I gave "Exec sproc name".
When I click the parse query button, I'm getting an error saying "Invalid Object name "#mainlist2".
But the sproc is successfully running in the query analyser.
FYI:The dts package and sproc are not residing on the same machine.
I got totally struck up with this issue.
I really appreciate those who can help me in resolving this. I've pasted few lines of the sproc below.
Stored procedure:
-----------------
ALTER PROCEDURE SP_MigrationEmails AS
if object_id('tempdb..#mainList2') is not null
begin
drop table #mainList2
end
go
begin
create table #mainList2
(main_folder char(254),....
............
insert into #mainList2
(main_folder,
aId,
aObjId
)
select
distinct
b.objname,
b.typeId,
b.objectId
from [dbo].CMS_InfoObjects3 as a,
[dbo].CMS_InfoObjects3 as b
where a.objectId = b.parentId and
a.objectId in(23,18) and --userfolders, root folder
b.objname <> 'cupcr' and
b.objname <> 'compucom' and
b.objname <> 'archived'
-----------insert next level folders and reports
--folder
insert into #mainList2
(main_folder,
folderType,
sub_folder1,
bId,
bObjId
)
select
distinct
a.main_folder,
'folder',
b.objName,
b.typeId,
b.objectId
from #mainlist2 a,
CMS_InfoObjects3 b
where a.aObjId = b.parentId and
ISNULL(a.aId,0) in (1,18) and
b.typeid = 1
April 17, 2008 at 5:47 am
Hi
The temp table that you are creating - will only be available during the session that the SP is executed within.
This means that the temp table may not be seen after the SP is run.
Is there a reason a normal table is not created?
The reason for your error in the parse of the SP - is that you are checking for an object that does not yet exist.
Let us know
Thanks
Kevin
April 17, 2008 at 6:15 am
Thanks for the reply Kevin.
But how the sproc can successfully got executed in query analyser but not in the Execute sql task?
Is there any limitation(like using #temp tables) of running the sprocs through the Execute sql task?
April 17, 2008 at 6:35 am
Hi
It seems as if the execute task requires that the sql syntax is 100%.
If you remove the check for the temp table up front and try parse that.
If you are using the exec - then maybe take the sql code from the SP and leave out the first check.
Let us know how that goes
Thanks
Kevin
April 18, 2008 at 4:24 am
Hi Kevin,
I've created a simple sproc which uses #temp table and tried to execute using Execute sql task in Dts.
I was able to successfully execute the task independently (Right click on task > Execute). But there was no use in that bcoz I was not able to set the output parameter for capturing the resultset. When I clicked the "Parameters" button of Exe.Sql task, I got an error message "Invalid object name : #temp".
Can you help me to execute this sproc in dts and loop through the recordset?
The sproc is as follows:
CREATE proc TempProc
as
begin
set nocount on
create table #temp (col1 int)
insert into #temp values (1)
select * from #temp
end
GO
April 18, 2008 at 4:38 am
What is your stored procedure trying to do? The code you have posted only creates and populates the temp table. It doesn't do anything with it. If the rest of your code doesn't manipulate the data in the table, then you may be able to use a CTE (if you have SQL Server 2005) or a derived table. Please post the whole procedure definition so that we can advise.
John
April 18, 2008 at 4:43 am
Replace the temp table name with that of an actual table temporarily, you can then set the parameters and change the name back.
It's a slight quirk of the DTS designer I believe.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply