sql server 2000 - dts can't 'see' stored procedure

[Bruce Lester]

I am trying to get the DTS Execute SQL task to run a simple sql server 2000
no-parameter stored procedure.

The procedure runs without error when using SQL Analyzer but DTS Execute SQL
reports "could not find stored procedure "[dbo].[test]"

The stored procedure is as follows:

create procedure test as

declare tnames_cursor CURSOR
for
select database_name, tmp_table_name from
[DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]

open tnames_cursor

declare @tablename sysname
declare @databasename sysname

while (@@FETCH_STATUS <> -1)
begin
select @databasename = rtrim(@databasename)
select @tablename = rtrim(@tablename)
exec ("delete from " + @databasename + ".dbo." + @tablename)
fetch next from tnames_cursor into @databasename, @tablename
end
close tnames_cursor
deallocate tnames_cursor
GO

Any ideas?

[Hayat]

First thing is that DTS is not able to find stored
procedure.........two possibilities:

1. Stored Procedure don't exist in the SQL server
2. Stored procedures exists but u r not referring it correctly. May be
instead of [dbo].[test] it should be [<DB NAME>].[<DB USER>].[test]

Can you check this?

-Hayat
www.mysticssoft.com

"Bruce Lester" <br**********@email.com> wrote in message news:<FQ********************@rwcrnsc51.ops.asp.att .net>...

I am trying to get the DTS Execute SQL task to run a simple sql server 2000
no-parameter stored procedure.

The procedure runs without error when using SQL Analyzer but DTS Execute SQL
reports "could not find stored procedure "[dbo].[test]"

The stored procedure is as follows:

create procedure test as

declare tnames_cursor CURSOR
for
select database_name, tmp_table_name from
[DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]

open tnames_cursor

declare @tablename sysname
declare @databasename sysname

while (@@FETCH_STATUS <> -1)
begin
select @databasename = rtrim(@databasename)
select @tablename = rtrim(@tablename)
exec ("delete from " + @databasename + ".dbo." + @tablename)
fetch next from tnames_cursor into @databasename, @tablename
end
close tnames_cursor
deallocate tnames_cursor
GO

Any ideas?